SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mtgkooks
Fluorite | Level 6

I am using an Oracle pass-thru in SAS to bring in the first 10 rows of a dataset that has twelve columns, ten of which contain identifying data and the remaining two ("REQUEST_PAYLOAD" and "RESPONSE_PAYLOAD"), each containing a separate JSON file.

 

Due to the lengths of these strings, I have had to break them into 3200 character chunks. (Each JSON file contains 5,700+ key/value pairs, and has a very complex structure.)

 

Here's how I'm bringing in the data:

libname ora_test ORACLE USER=XXXXXX password=XXXXXXXX
PATH=XXXXXX SCHEMA=XXXXXXXX;

proc sql;
connect using ora_test;
create table work.qdata as 
select * from connection to ora_test

(select qid,qrefid,created_date,updated_date,qtype,lob,srcsys,gwqid,brefid,pnum,
        dbms_lob.substr(REQUEST_PAYLOAD,3200,1) as ReqChunk1,
        dbms_lob.substr(REQUEST_PAYLOAD,3200,3201) as ReqChunk2,
        dbms_lob.substr(REQUEST_PAYLOAD,3200,6401) as ReqChunk3,
        dbms_lob.substr(REQUEST_PAYLOAD,3200,9601) as ReqChunk4,
        dbms_lob.substr(REQUEST_PAYLOAD,3200,12801) as ReqChunk5,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,16001) as ReqChunk6,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,19201) as ReqChunk7,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,22401) as ReqChunk8,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,25601) as ReqChunk9,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,28801) as ReqChunk10,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,32001) as ReqChunk11,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,35201) as ReqChunk12,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,38401) as ReqChunk13,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,41601) as ReqChunk14,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,44801) as ReqChunk15,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,48001) as ReqChunk16,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,51201) as ReqChunk17,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,54401) as ReqChunk18,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,57601) as ReqChunk19,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,60801) as ReqChunk20,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,1) as RespChunk1,
        dbms_lob.substr(RESPONSE_PAYLOAD,3200,3201) as RespChunk2,
        dbms_lob.substr(RESPONSE_PAYLOAD,3200,6401) as RespChunk3,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,9601) as RespChunk4,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,12801) as RespChunk5,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,16001) as RespChunk6,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,19201) as RespChunk7,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,22401) as RespChunk8,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,25601) as RespChunk9,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,28801) as RespChunk10,
        dbms_lob.substr(RESPONSE_PAYLOAD,3200,32001) as RespChunk11,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,35201) as RespChunk12,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,38401) as RespChunk13,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,41601) as RespChunk14,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,44801) as RespChunk15,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,48001) as RespChunk16,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,51201) as RespChunk17,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,54401) as RespChunk18,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,57601) as RespChunk19,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,60801) as RespChunk20
	from XXXXXX.INFO t fetch next 10 rows only);

disconnect from ora_test;

quit;

REQUEST_PAYLOAD breaks into 12 pieces and totals around 38,000 characters. RESPONSE_PAYLOAD breaks into 15 pieces and totals around 47,000 characters. Due to the combined length of these strings, I can't concatenate either JSON into a single field to be parsed using the JSON libname engine.

 

I'm wondering if anyone can offer some insight into how I might go about doing this.

 

Note: I would love to post an example of the output but I cannot, as the file contains PII data.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Don't.  Since Oracle created that monster find out what the Oracle syntax is for parsing out the data from JSON blobs and use pass thru SQL to query the data in Oracle.

 

If you want to explore what is in one of those files then use a data step to convert it back into a file and then you can use the JSON libname engine to parse it.

filename json temp;
data _null_;
  set qdata (obs=1);
  file json recfm=n ;
  put (ReqChunk1-ReqChunk20)  ($char3200.) ;
run;

libname json json ;

proc contents data=json._all_; 
run;

proc copy inlib=json outlib=work;
run;

If you really have to deal with those JSON blobs on the SAS side then figure out how to replicate that for every one of them.

View solution in original post

1 REPLY 1
Tom
Super User Tom
Super User

Don't.  Since Oracle created that monster find out what the Oracle syntax is for parsing out the data from JSON blobs and use pass thru SQL to query the data in Oracle.

 

If you want to explore what is in one of those files then use a data step to convert it back into a file and then you can use the JSON libname engine to parse it.

filename json temp;
data _null_;
  set qdata (obs=1);
  file json recfm=n ;
  put (ReqChunk1-ReqChunk20)  ($char3200.) ;
run;

libname json json ;

proc contents data=json._all_; 
run;

proc copy inlib=json outlib=work;
run;

If you really have to deal with those JSON blobs on the SAS side then figure out how to replicate that for every one of them.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 1235 views
  • 1 like
  • 2 in conversation