Parse JSON Iin SAS dynamically using keynodes and values

[Sepideh_B]

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

[Ksharp · Accepted solution]

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;

[Sepideh_B]

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?

[Ksharp]

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 */

[fanfanpao]

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