How to parse JSON array and store values in SAS Column

[newboy1218]

Hi All,

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

ID	Data
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.

ID	name	email	attachments_documentId	attachments_name	attachments_result_status	attachments_result_time
1	John Peter	john.peter@outlook.com
2	Mary White	mary1111@outlook.com	a284h162	AlainDLback.jpg	approve	2021-03-05

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

[AMSAS]

Take a look at the JSON engine

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

[Ksharp]

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 · Accepted solution]

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.

[AllanBowe]

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