topic Re: Parse JSON Iin SAS dynamically using keynodes and values in SAS Programming

Parse JSON Iin SAS dynamically using keynodes and values

Sepideh_B — Thu, 04 Apr 2019 14:08:01 GMT

I have this JSOn string

@json='{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}';

and I would like to create a programin sas that is going to dynamically loop through all the nodes and their values and create me a final table with 2 columns:

column 1: all the key nodes of the JSON object

column2: all the values corresponding to those nodes.

desired output:

keynode value

-----------------------------

name John

Surname Doe

Age 45

Re: Parse JSON Iin SAS dynamically using keynodes and values

Ksharp — Fri, 05 Apr 2019 14:01:43 GMT

data x;
x="@json='"||'{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}'||"'";
run;
data want;
 set x;
 n+1;
 temp=translate(scan(x,-1,"'{}"),'""','[]');
 do i=1 to countw(temp,',','q')	;
   t=scan(temp,i,',','q');
   node=dequote(scan(t,1,':'));
   value=scan(t,2,':');
   output;
 end;
 drop i x t temp;
run;

Re: Parse JSON Iin SAS dynamically using keynodes and values

Sepideh_B — Fri, 05 Apr 2019 20:27:48 GMT

This is great! Thanks so much!!

if I have a columnin an existing table in SAS that contains JSON strings, is there a way that i can iterate through the columns of that table and parse them like this?

so instead of 1 JSON string, lets say I have 20 and i wanna parse them each and place in the second table. How can i build a do loop on top of the existing do - loop?

Re: Parse JSON Iin SAS dynamically using keynodes and values

Ksharp — Sat, 06 Apr 2019 11:52:46 GMT

If there is only one column, replace table name and column name is enough.

data want;
 set x; /*<-- replace table name  and replace variable name X */

Re: Parse JSON Iin SAS dynamically using keynodes and values

fanfanpao — Sun, 07 Apr 2019 13:49:12 GMT

if you use sas9.4 m4 or above, you can simply use json libname for your question