topic Re: Issue with proc sql truncating data in SAS Programming

Issue with proc sql truncating data

Erockanderson — Tue, 25 Jun 2019 16:05:09 GMT

Hello. I pulling data from a cache database. Upon execution of the query the field I need is being truncated to 80 characters. I have tried setting the length, informat, and format on the pull but no success. Code is below. I would appreciate any suggestions on how to have it read in the full value. I have tried the pull with other SQL tools and can pull the full value with no setting or options added.

libname k odbc dsn=Kaiser1 schema=SqLUSeR readbuff=10000;

proc sql;
create table calls1a as
select t1.Name_Of_VTrak_File informat $2000. format $2000. length=2000 as Name_Of_VTrak_Filez
from k.call_logging_audit(dbkey=Date_Call_Began) t1
where Date_Call_Began = &start. and Recorded_Voicetrak=1;
quit;

Re: Issue with proc sql truncating data

koyelghosh — Tue, 25 Jun 2019 16:23:21 GMT

Did you try using DATA step. If DATA step is working, then the ODBC is not truncating anything before passing it to SAS. Otherwise may be it is your ODBC settings that is truncating the value to 80 characters before it reaches SAS for processing. This is my guess.

Re: Issue with proc sql truncating data

Erockanderson — Tue, 25 Jun 2019 17:30:59 GMT

I get the same results when running as a data step. 80 characters. How
would I go about seeing if the odbc settings are limiting the length. This
field in particular was lengthened recently due to an upgrade of the
system. Does SAS store database characteristics it could be defaulting to
somewhere?

Re: Issue with proc sql truncating data

koyelghosh — Tue, 25 Jun 2019 17:58:47 GMT

Not sure if this will run as I don't have access to your database (to test it out) but worth a try. Replace ABC, DEF and GHI, below, appropriately.

proc sql;
connect to odbc as mycon
   (datasrc=ABC user=DEF password=GHI);

select *
   from connection to mycon
      (Your Raw SQL query here);

disconnect from mycon;
quit;

Try some simple query like fetching a column which has more than 80 characters in its contents