topic Re: Columns truncated when saving table using database and odbc in SAS Programming

Columns truncated when saving table using database and odbc

peter_sjogarde — Wed, 12 Nov 2014 12:59:16 GMT

Hi,

I am using a datastep to save tables to local disc from a database using odbc. The simple code looks like this:

data LD.Diva; DD.Diva;

where LD is the local directory and DD is the assigned database connection. It works fine except for columns containing long strings (above 1024 char), these are truncated.

Does anyone have a solution for this problem?

Re: Columns truncated when saving table using database and odbc

Kurt_Bremser — Wed, 12 Nov 2014 13:08:47 GMT

Looks like a limit in ODBC and/or the ODBC driver of the database. Use direct access to the database, or do an export to a file and import that.

Re: Columns truncated when saving table using database and odbc

peter_sjogarde — Wed, 12 Nov 2014 15:22:48 GMT

Thank you very much for this. For now I can export and import, but I would like to find a solution without manual steps. Do you have a description or a link on how to set up a direct access?

Re: Columns truncated when saving table using database and odbc

Kurt_Bremser — Thu, 13 Nov 2014 07:16:24 GMT

You need SAS/ACCESS for your database licensed.

You also need the database client installed on your SAS machine, and it needs to be set up so that it can connect to the database in question.

Look for the documentation on SAS/ACCESS to relational databases.

Be prepared to spend some time getting that running.

I've given up on DB/2, I only use the "export to file - import file in SAS" method. This also gives you a fixed data point (the database changes permanently, the file keeps the state of its creation).

Re: Columns truncated when saving table using database and odbc

peter_sjogarde — Thu, 13 Nov 2014 07:37:05 GMT

Thank you very much for your help! I do not have a license for SAS/ACCESS at the moment, but I will consider this solution.