SAS proc sql pass-through AWS json field is cut short

[citizben]

Hi, 

One of my AWS field name "message_json" is a JSON type variable containing longer than 32767 characters. The follow query runs no error but the returned JSON field string is shorter than actual. So later on when export it as text file to feed libname JSON engine to extract it fails. I run the same Sql in AWS Athena and I can confirmed the field content is cut shorter. can anybody suggest how to keep the JSON field length as-is in the AWS source?

options compress=yes obs=max;
Proc sql;
connect to redshift as xyz (dsn=d1s2n3 user=sasdummyid password="1234567");
Create Table app_detail as Select * from connection to xyz
(
select applicant_id,message_json
from <a AWS data table>
where applicant_id=xxxxx and as_on_dt='20221231000000'
);
disconnect from xyz;
quit;

[SASKiwi]

How long actually is message_json? SAS 9.4's character variable length limit is 32767 and that can't be changed. A workaround would be to create a series of substrings, shorter than 32767, from the AWS column and read those into SAS variables. Then you could concatenate them by writing them out to a text file to reconstruct the original JSON layout.

It would look a bit like this:

Proc sql;
connect to redshift as xyz (dsn=d1s2n3 user=sasdummyid password="1234567");
Create Table app_detail as Select * from connection to xyz
(
select applicant_id,
          substring(message_json, 1, 20000) as message_json1,
         substring(message_json, 20001, 20000) as message_json2,
         substring(message_json, 40002, 20000) as message_json3
from <a AWS data table>
where applicant_id=xxxxx and as_on_dt='20221231000000'
);
disconnect from xyz;
quit;

Not sure if SUBSTRING is the correct syntax for Redshift, but you can look that up.

[Patrick]

@citizben 

"message_json" is a JSON type variable containing longer than 32767 characters

Redshift doesn't document a data type JSON. Is it a VARCHAR? https://docs.aws.amazon.com/redshift/latest/dg/c_Supported_data_types.html

SAS 9.4 type tables are limited to character variables of max 32767 bytes. SAS Viya CAS tables allow for longer character variables. Which SAS version are you using? 
Please let us know the result when running %put &=sysvlong;  Same as below from my environment.

 69         %put &=sysvlong;
 SYSVLONG=9.04.01M7P080620

With SAS9.4 you will likely need to split your variable into chunks of 32767 characters (=creating multiple variables) that you then put together on the SAS side when writing to a text file. 

Should you have Viya CAS then you should be able to load the long variable from Redshift directly into a CAS Varchar and then write to a text file directly out of CAS.

Also SAS9.4 M5 and later got a Varchar data type that can store more than 32767 characters and that's available both within DS2 and the SAS data step BUT you can't write it to a SAS table and more importantly I believe the SAS/Access engine won't allow you to retrieve more than a 32767 character string from a database so you'll never get the full string into SAS even though you could directly write it out to a text file without the need to store it in a SAS table.
https://support.sas.com/resources/papers/proceedings18/2690-2018.pdf