BookmarkSubscribeRSS Feed
Anotherdream
Quartz | Level 8

Hello all. I had a question relating to a sas Libname statements for a sql server database, using an odbc connection (dsn).

In our database, we have specific columns that have to be defined as varchar(max) since they are often over 12,000 text length long. (This cannot be broken out, as it is one "answer"). If I want to use a libname statement to connect to this database for a Proc Sql insert into, I have to set the dbmax_text setting to a large number (using 32,000). This way Sas will not truncate any values inserted into this column to a length of 1 (since sas does not recognize the length of varchar(MAX) by default.

I have run this libname statement code on numerous occasions and the step works, however sas always creates the following warning message.

"WARNING: Truncating character column XXX to 32000 characters long, based on dbmax_text setting."

This is a problem because our code is quite large, and loops through numerous tables and does numerous inserts. These warnings are cluttering up the log and making it almost impossible to debug real occuring issues.

Does anyone know an option to turn this specific warning message off? (Or all warning messages with another setting to turn them immediately back on).
If no such setting exists, can anyone suggest a different way to move data between Sas and sql server without a libname statement that does not produce this warning?

Thank you!


3 REPLIES 3
art297
Opal | Level 21

What version of SAS are you on?  It may have been corrected in 9.2 http://support.sas.com/kb/30/741.html

Anotherdream
Quartz | Level 8

Sadly this "fix" in 9.2 did not solve the WARNING issue (at least not on my computer). If anyone has 9.2 and runs code similar to this, does this same warning message occur for them? If it does not, I will probably have to un-install and re-install (really hoping to avoid that).

For a bit more clarification, I am using the Microsoft ODBC Data Source Administrator  "Data Sources (ODBC)" to define a system DSN, which I am then refrencing in my libname statement as following.

Libname Myodbc odbc dsn='MY_DEFINED_SYSTEM_DSN_NAME' schema=dbo bulkload=yes DMBAX_text=32000;

MumSquared
Calcite | Level 5

Yep, fun and games with long fields. I found it better to use pass-through sql than the libname statement.

I download lots of tables using pass through SQL with the database dbmax_text=32767 option.

I use global macro variables for the database log on information:

&GLB_USER

&GLB_PASSWORD

&GLB_DBname

&GLB_DSN_NAME

&GLB_ServerIPaddr


** macro to pull all the data from the table ;
%macro SQLPullDataBig (outputdataset,SQLtableName,DatasetLabel);

PROC SQL;
CONNECT TO ODBC AS ODBCref
  (NOPROMPT="UID=&GLB_USER;PSW=&GLB_PASSWORD;DSN=&GLB_DSN_NAME;

SERVER=&GLB_ServerIPaddr;DATABASE=&GLB_DBname"  dbmax_text=32767);
Create table &outputdataset(compress=binary label="&DatasetLabel") as
Select * from connection to ODBCref (
Select  *
From &SQLtableName
);
quit;

%mend;

%SQLPullDataBig(libName.outputDatasetName,SQLtableName,Label for you dataset);

Now when the fields are longer than 32,767 characters life get more interesting as the only way seems to be to chop the field up and download from SQL in sections. I have SAS code which does this by working out the maximum length of the field and generating the download code as required. If anyone is interested.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2973 views
  • 0 likes
  • 3 in conversation