I am trying to read a JSON file with 30 arrays. Here's the code:
filename in "Source.json"; filename map 'WorkingMap.Map'; libname in json map=Map automap=reuse; libname j json "Source.json" map=Map;
By default this syntax creates a separate table for each array. For the example below, the code above creates Proc1-Proc4 columns in a table PROC with Ordinal_Root link to the main ROOT dataset.
"proc": [ 2800, 2980, 4090, 4170 ],
I have to merge multiple files into one for my purposes and also aggregate multiple columns into one. On top of that, I am processing dozens of JSONS that need to be stacked to there's a ton of re-merging going on.
Is there an option or a mapping method/trick to read arrays as simple text strings? For the PROC example above, rather than a dedicated separate table with multiple columns for each value of the array, I would like a single column that contains them all in one cell just like in the JSON:
[ 2800, 2980, 4090, 4170 ]
Need help!!!
I'm confused as to what you want as the SAS data from reading that JSON.
If you read the data:
"proc": [ 2800, 2980, 4090, 4170 ],
Do you want the output to be a SAS dataset with one record and one character variable named PROC with the value "[ 2800, 2980, 4090, 4170 ]" ?
Or do you want the output to be a SAS dataset with one numeric variable PROC with four records, i.e.:
PROC 2800 2980 4090 4170
Or do you want something else?
Be sure to check out the alldata table, that might be helpful to you.
I want a single column PROC in the SAS dataset that contains value PROC = "[ 2800, 2980, 4090, 4170 ]".
I don't think you can do that with the JSON engine. You basically want to read an array of JSON elements into a single character variable.
If you write your own program to parse the json file using INPUT, you should be able to do it.
Or maybe you could pre-process the JSON file to convert the arrays into string values, i.e. change:
"proc": [ 2800, 2980, 4090, 4170 ],
to:
"proc": "[ 2800, 2980, 4090, 4170 ]",
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.