BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
newboy1218
Quartz | Level 8

Hi All,

 

I need to work with a JSON text string column from a SAS dataset. Below is sample records of this column.

 

IDData
1 {"name":"John Peter","email":"john.peter@outlook.com", "attachments":[]}
2{"name":"Mary White","email":"mary1111@outlook.com", "attachments":[{"documentId":"a284h162","name":"AlainDLback.jpg","result":{"status":"approve","time":"2021-03-05"}}]}

 

Desired output is to transform the dataset to the below form.

 

IDnameemailattachments_documentIdattachments_nameattachments_result_statusattachments_result_time
1John Peterjohn.peter@outlook.com    
2Mary Whitemary1111@outlook.coma284h162AlainDLback.jpgapprove2021-03-05

 

Any pointers on the approach to follow would be very helpful. Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

The JSON libname engine is the best way to read JSON-formatted content as data within SAS. However, the JSON engine has two requirements:

  • The data must be complete, well-formed JSON.
  • The source must be referenced as a fileref, not as a character variable or string literal or any "in-memory" SAS construct.

What this means for you: to read a data set where each row contains complete JSON content, you must run a first pass to extract all of the data and assemble into a larger JSON structure that SAS can read all at once. (Alternatively, you could read each row and write to a standalone JSON file, then use the JSON libname to read that file -- repeating for each row. Tedious, but for large JSON chunks you might find it useful.)

 

Here's an example -- using your example data -- for assembling a larger JSON file and then using SAS to read it as a single data source.

 

data have;
infile cards truncover;
input ID Data $200.;
cards;
1  {"name":"John Peter","email":"john.peter@outlook.com", "attachments":[]}
2 {"name":"Mary White","email":"mary1111@outlook.com", "attachments":[{"documentId":"a284h162","name":"AlainDLback.jpg","result":{"status":"approve","time":"2021-03-05"}}]}
;

filename alljson temp;
data _null_;
 set have end=done;
 file alljson;
 if _n_=1 then put '[{"records":['; /* outer wrapper for JSON */
 put data;
 if done=1 then put ']}]'; /* close outer wrapper */
   else put ','; /* each 'record' must be separated by a comma */
run;

libname data json fileref=alljson;

/* show the tables in the resulting library */
proc datasets lib=data; quit;

/* example of pulling out one table */
data records;
 set data.records;
run;

title "Records data";
proc print data=records; run;

title "Records and their attachments";
proc sql;
 select t1.name, t1.email, t2.documentId, t2.name as docName
 from data.records t1 left join data.records_attachments t2
 on (t1.ordinal_records = t2.ordinal_records);
quit;

 

The resulting library is like a relational database with tables that you can join to create the output you need, as shown in the PROC SQL code in the example.

 

ChrisHemedinger_0-1638795593857.png

 

Learn from the Experts! Check out the huge catalog of free sessions in the Ask the Expert webinar series.

View solution in original post

4 REPLIES 4
AMSAS
SAS Super FREQ

Take a look at the JSON engine

There's a detailed example below the heading "Creating and Editing a JSON MAP"

 

Ksharp
Super User
data have;
infile cards truncover;
input ID Data $200.;
cards;
1  {"name":"John Peter","email":"john.peter@outlook.com", "attachments":[]}
2 {"name":"Mary White","email":"mary1111@outlook.com", "attachments":[{"documentId":"a284h162","name":"AlainDLback.jpg","result":{"status":"approve","time":"2021-03-05"}}]}
;


data temp;
 set have;
 do i=1 to countw(data,'{[]},','q');
  temp=scan(data,i,'{[]},','q');
  vname=dequote(strip(scan(temp,1,':')));
  value=dequote(strip(scan(temp,-1,':')));
  output;
 end;
 keep id vname value;
run;

data temp1;
 set temp;
 by id;
 retain found;
 if first.id then found=0;
 if missing(vname) then delete;
 if find(vname,'attachments','i') then do; found=1;delete;end;
 if found then vname=cats('attachments','_',vname);
run;

proc transpose data=temp1 out=want(drop=_:) ;
by id ;
var value;
id vname;
run;
ChrisHemedinger
Community Manager

The JSON libname engine is the best way to read JSON-formatted content as data within SAS. However, the JSON engine has two requirements:

  • The data must be complete, well-formed JSON.
  • The source must be referenced as a fileref, not as a character variable or string literal or any "in-memory" SAS construct.

What this means for you: to read a data set where each row contains complete JSON content, you must run a first pass to extract all of the data and assemble into a larger JSON structure that SAS can read all at once. (Alternatively, you could read each row and write to a standalone JSON file, then use the JSON libname to read that file -- repeating for each row. Tedious, but for large JSON chunks you might find it useful.)

 

Here's an example -- using your example data -- for assembling a larger JSON file and then using SAS to read it as a single data source.

 

data have;
infile cards truncover;
input ID Data $200.;
cards;
1  {"name":"John Peter","email":"john.peter@outlook.com", "attachments":[]}
2 {"name":"Mary White","email":"mary1111@outlook.com", "attachments":[{"documentId":"a284h162","name":"AlainDLback.jpg","result":{"status":"approve","time":"2021-03-05"}}]}
;

filename alljson temp;
data _null_;
 set have end=done;
 file alljson;
 if _n_=1 then put '[{"records":['; /* outer wrapper for JSON */
 put data;
 if done=1 then put ']}]'; /* close outer wrapper */
   else put ','; /* each 'record' must be separated by a comma */
run;

libname data json fileref=alljson;

/* show the tables in the resulting library */
proc datasets lib=data; quit;

/* example of pulling out one table */
data records;
 set data.records;
run;

title "Records data";
proc print data=records; run;

title "Records and their attachments";
proc sql;
 select t1.name, t1.email, t2.documentId, t2.name as docName
 from data.records t1 left join data.records_attachments t2
 on (t1.ordinal_records = t2.ordinal_records);
quit;

 

The resulting library is like a relational database with tables that you can join to create the output you need, as shown in the PROC SQL code in the example.

 

ChrisHemedinger_0-1638795593857.png

 

Learn from the Experts! Check out the huge catalog of free sessions in the Ask the Expert webinar series.
AllanBowe
Barite | Level 11

One rather elegant way to import JSON into SAS is to use PROC LUA.  Lua can handle arrays, very long strings, nested data structures etc with ease.  It also has all necessary plumbing to write that data straight back into SAS.

 

To save time, we've compiled an open source Lua JSON parser into a SAS Macro here: https://core.sasjs.io/ml__json_8sas.html

An example of this being used is in this macro, which fetches the code for a SAS job from the Viya APIs:  https://core.sasjs.io/mv__getjobcode_8sas.html

 

 

/Allan
SAS Challenges - SASensei
MacroCore library for app developers
SAS networking events (BeLux, Germany, UK&I)

Data Workflows, Data Contracts, Data Lineage, Drag & drop excel EUCs to SAS 9 & Viya - Data Controller
DevOps and AppDev on SAS 9 / Viya / Base SAS - SASjs

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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
  • 4 replies
  • 7056 views
  • 2 likes
  • 5 in conversation