topic Re: Truncation issue in proc sql when picking large strings in SAS Programming

Truncation issue in proc sql when picking large strings

Priyanka2019 — Fri, 27 Sep 2019 02:05:59 GMT

In SAS 9.04 version when i am trying to select a column in table on sql server 2014 which contains json string 17 k long as is, it is truncating to 1000-1180.

I tried giving length and format in select query eg
Proc sql
Create table t1 as select busobj length=15000, format = $15000. From table1;
Quit;

It seems this creates target sas column with size 15k in output data but actual string is trucated at approx 1173. Being different for each row picked but in similar range.

Could this be due to sas db connector, server setting on sas or sql server please guide. On sql server the string is 17 k as checked

Re: Truncation issue in proc sql when picking large strings

SASKiwi — Fri, 27 Sep 2019 02:15:16 GMT

Try using the DBMAX_TEXT option: https://documentation.sas.com/?docsetId=acreldb&docsetTarget=p14o73fqwbby7xn1iqvjbdiixuyj.htm&docsetVersion=9.4&locale=en

The default is 1024 which seems to be what you are experiencing. The maximum length you can specify is 32K.

Re: Truncation issue in proc sql when picking large strings

Priyanka2019 — Fri, 27 Sep 2019 02:41:40 GMT

Thanks for the response but it seems it has restriction to exactly what i need. On sql server column is ddfined as varchar and need to use while creating sas dataset. Taken from link:-

Restrictions: This option applies when you retrieve, append, and update rows in an existing table. It does not apply when you create a table.
This option is ignored for CHAR and VARCHAR2 data types.

Re: Truncation issue in proc sql when picking large strings

Reeza — Fri, 27 Sep 2019 03:56:08 GMT

Did you try it?

I believe that means creating tables on the server, but you're creating tables in SAS so its a bit different. You can always create a table ahead of time anyways and then insert into if needed.

@Priyanka2019 wrote:
Thanks for the response but it seems it has restriction to exactly what i need. On sql server column is ddfined as varchar and need to use while creating sas dataset. Taken from link:-

Restrictions: This option applies when you retrieve, append, and update rows in an existing table. It does not apply when you create a table.
This option is ignored for CHAR and VARCHAR2 data types.

Re: Truncation issue in proc sql when picking large strings

Priyanka2019 — Fri, 27 Sep 2019 05:04:37 GMT

It worked thank you

Re: Truncation issue in proc sql when picking large strings

Priyanka2019 — Fri, 27 Sep 2019 05:06:00 GMT

Thanks Saskiwi and Reeza was setting it at wrong place. It is resolved now.