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 */
required=
"
driver=sql server;
database=foo;
server=bar;
"
;


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.