JSON
{ "totalCount": 2, "facets": {}, "content": [ [ { "name": "customer_ID", "value": "1" }, { "name": "customer_name", "value": "John" } ] ] }
How to get the values under "name" using a json map.
i.e i need to get the out as "customer_ID" and "customer_name" using a json map.
Below is my json map.
{ "DATASETS": [ { "DSNAME": "customers", "TABLEPATH": "/root/content", "VARIABLES": [ { "NAME": "name", "TYPE": "CHARACTER", "PATH": "/root/content/name" }, { "NAME": "name", "TYPE": "CHARACTER", "PATH": "/root/content/name" }, { "NAME": "value", "TYPE": "CHARACTER", "PATH": "/root/content/value" }, { "NAME": "value", "TYPE": "CHARACTER", "PATH": "/root/content/value" } ] } ] }
The output i am getting is only "customer_name".
Please help.
Thanks.
I just cut out the duplication in the map file that you supplied.
Map file named ./sasuser/json.map
{ "DATASETS": [ { "DSNAME": "customers", "TABLEPATH": "/root/content", "VARIABLES": [ { "NAME": "name", "TYPE": "CHARACTER", "PATH": "/root/content/name" }, { "NAME": "value", "TYPE": "CHARACTER", "PATH": "/root/content/value" } ] } ] }
Existing JSON file (named ./sasuser/json.sas):
{ "totalCount": 2, "facets": {}, "content": [ [ { "name": "customer_ID", "value": "1" }, { "name": "customer_name", "value": "John" } ] ] }
SAS code run:
filename mapRef './sasuser/json.map';
libname x json './sasuser/json.sas' map=mapRef;
proc print data=x.customers; run;
OUTPUT:
Obs name value 1 customer_ID 1 2 customer_name John
I answered this on StackOverflow (and Reddit! Hitting all of the channels!) where it seems that the original poster wants the "name" values to be proper SAS variables. Achievable using what @BillM_SAS suggests, plus a transpose step:
libname j json fileref=test;
proc transpose
data=j.content
out=want;
id name;
var value;
run;
Output:
customer_ customer_
Obs _NAME_ ID name
1 value 1 John
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.