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 */
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.