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
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;
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;
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?
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 */
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.