SAS truncates SQL columns to length 1024

At first I thought SAS was giving me errors because the input data was malformed. Before I found data entry operators typing invalid dates such as “3/30/33/2009” :

NOTE: Invalid argument to function INPUT at line 1248 column 26
NOTE: Mathematical operations could not be performed at the following places.
The results of the operations have been set to missing values.
Each place is given by: (Number of times) at (Line):(Column).
2 at 1248:26

That was not the reason. I discovered the PROC SQL statement in SAS truncates long text columns to 1024 characters, so my first instinct was to modify the SELECT statement:

CREATE TABLE waste_disk_space AS
SELECT wide_column format $8192 informat=$8192. length 8192
FROM big_table;

The data set grew 800% and the data set properties showed SAS accepted the wider length, format, and informat, but the text columns were still truncated at 1024 characters. After some searching, I discovered the LIBNAME option DBMAX_TEXT.

LIBNAME dwshared odbc read_isolation_level = RU schema=dbo CONNECTION=SHARED
DBMAX_TEXT=8192 /* prevent text column truncation */
driver=sql server;

Too bad SAS still wastes disk space (and degrades performance) on text columns with widely-varying lengths because SAS data sets can’t store the equivalent of SQL’s VARCHAR.

Environment: SAS 9.1.3, Microsoft Windows XP Professional SP3, and Microsoft SQL Server 2000.

One thought on “SAS truncates SQL columns to length 1024

  1. This was totally wonderful and saved my friday from being really crappy. Thanks for suffering through this so I was able to get the help I needed.🙂

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s