Reading character variables longer than 1024 using the SAS/ACCESS® LIBNAME engine or PROC SQL Pass-Through


Using SAS 8 and SAS 9, if you access a character value that is 1024 in length or greater, the resulting SAS Dataset variable defaults to 1024 in length. To create a SAS Dataset variable longer than 1024 characters you must specify the DBMAX_TEXT= option either on the LIBNAME statement or in the PROC SQL Pass-Through connection parameters depending on which method you're using to access the data. Valid values for DBMAX_TEXT are 1 to 32767.

For example:

proc sql;
connect to oracle (user=xxxxx password=yyyyy path=V2o8040 dbmax_text=32767);
create table work.test as
select * from connection to oracle (select * from DEPT);
quit;

libname mylib oracle user=xxxxx password=yyyyy path=V2o8040 dbmax_text=32767;

Note that although the example uses ORACLE as the database, this is not a database specific issue. This applies to ODBC, SYBASE, DB2, etc as well as ORACLE.