topic Re: Parsing double stringify JSON data in SAS Programming

Parsing double stringify JSON data

ellen09 — Thu, 12 Mar 2020 17:54:09 GMT

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"
}
]
}

Re: Parsing double stringify JSON data

ChrisNZ — Thu, 12 Mar 2020 20:56:35 GMT

What's the desired output?

Re: Parsing double stringify JSON data

ellen09 — Fri, 13 Mar 2020 12:56:18 GMT

I am hoping to get a data set like this:

Re: Parsing double stringify JSON data

ChrisNZ — Sat, 14 Mar 2020 10:38:40 GMT

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

Re: Parsing double stringify JSON data

ellen09 — Thu, 09 Apr 2020 20:40:14 GMT

That helps. Thank you