Hi,
I retrieve a JSON file which looks like below, and I am trying to parse it. The value of name "C" is quoted as a string, however, it is also an array. I am trying to parse it with both libname json engine and DS2 procedure, but I don't know how to parse inside the value of "C". Any help appreciated!
{
"result": {
"status": "good"
},
"reviews": [
{
"A": 32,
"B": "name1",
"C": "{\"x1\": {\n \"A1\": {\"y1\": [\n {\n \"fname\": \"p1\",\n \"mname\": \"p2\",\n }\n ]\n}}",
"D": "New"
},
{
"A": 38,
"B": "name2",
"C": "{\"x1\": {\n \"B1\": {\"y1\": [\n {\n \"fname\": \"q1\",\n \"mname\": \"q2\",\n }\n ]\n}}",
"D": "New"
}
]
}
What's the desired output?
I am hoping to get a data set like this:
The simplest if the JSON's structure is quite constant:
FNAME=scan(C, 9,':\"{}');
MNAME=scan(C,12,':\"{}');
If the string varies more, you may have to look for the positions of fname and mname.
If it varies a lot, you may have to resort to regular expressions to isolate the bits you want, or use several statements.
A data step allows you to read the JSON string using an input statement, but DS2 cannot afaik:
https://communities.sas.com/t5/SAS-Data-Management/Parsing-a-string-variable/td-p/157459
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.