Truncation issue in proc sql when picking large strings

[Priyanka2019]

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

[SASKiwi · Accepted solution]

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

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

[Priyanka2019]

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.

[Reeza]

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.

---

[Priyanka2019]

It worked thank you

[Priyanka2019]

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