Parse JSON object in SAS macro

Question

Here is the input JSON file. It have to parse in SAS dataset.

"results":
[
 {
    "acct_nbr": 1234,
    "firstName": "John",
    "lastName": "Smith",
    "age": 25,
    "address": {
        "streetAddress": "21 2nd Street",
        "city": "New York",
        "state": "NY",
        "postalCode": "10021"
        }
 }
,
{
    "acct_nbr": 3456,
    "firstName": "Sam",
    "lastName": "Jones",
    "age": 32,
    "address": {
        "streetAddress": "25 2nd Street",
        "city": "New Jersy",
        "state": "NJ",
        "postalCode": "10081"
        }
 }
]

And I want the output for only Address field in SAS dataset like this :

ACCT_NBR    FIELD_NAME  FIELD_VALUE
1234    streetAddress   21 2nd Street
1234    city    New York
1234    state   NY
1234    postalCode  10021
3456    streetAddress   25 2nd Street
3456    city    New Jersy
3456    state   NJ
3456    postalCode  10081

I have tried separate way, but no similar output. even tried scanover from PDF ... but cannot get desired output...

here is my code......and output....

LIBNAME src  '/home/user/read_JSON';

filename data '/home/user/read_JSON/test2.json';
data src.testdata2;
    infile data lrecl = 32000 truncover scanover;
        input @'"streetAddress": "' streetAddress $255. @'"city": "' city $255. @'"state": "' state $2. @'"postalCode": "' postalCode $255.;
        streetAddress = substr(streetAddress,1,index(streetAddress,'",')-2);
        city = substr( city,1,index( city,'",')-2);
        state = substr(state,1,index(state,'",')-2);
        postalCode = substr(postalCode,1,index(postalCode,'",')-2);
run;

proc print data=src.testdata2;
RUN;

My OUTPUT in .lst file

The SAS System   09:44 Tuesday, January 14, 2014   1
           street                            postal
 Obs      Address         city      state     Code

  1     21 2nd Stree    New Yor       NY      10021"
  2     25 2nd Stree    New Jers      NJ      10081"

Answer 1

To answer your question with a SAS-only solution, your problems are twofold:

• Use SCAN instead of substr to get the un-comma/quotationed portion
• acct_nbr is a number, so you need to remove the final quotation mark from the input.

Here's the correct code (I changed directories, you'll need to change them back):

filename data 'c:\temp\json.txt';
data testdata2;
    infile data lrecl = 32000 truncover scanover;
        input 
            @'"acct_nbr": ' acct_nbr $255.
            @'"streetAddress": "' streetAddress $255. 
            @'"city": "' city $255. 
            @'"state": "' state $2. 
            @'"postalCode": "' postalCode $255.;

        acct_nbr=scan(acct_nbr,1,',"');
        streetAddress = scan(streetAddress,1,',"');
        city = scan(city,1,',"');
        state = scan(state,1,',"');
        postalCode = scan(postalCode,1,',"');
run;

proc print data=testdata2;
RUN;