Parsing a JSON File

[Tal]

Hi I have a JSON file  with a  field which i want to parse  

Field Name:JOURNEY_RESPONSES
{"CPAT_Q2":[{"borrower":"primaryBorrower","age":57,"province":"ON"},{"borrower":"coBorrower1","age":53,"province":"ON"}],"CPAT_Q4LOCHELOC":{"balanceAmt":150000,"paymentAmt":500,"paymentFrequency":"MONTHLY","limitAmt":150000,"revolvingLimitAmt":150000,"termAmt":70000,"clkReferenceNumber":"12345678-01"},"CPAT_Q5":[{"borrower":"primaryBorrower","income":60000},{"borrower":"coBorrower1","income":60000}],"CPAT_Q5_COMBINED":{"savings":150000,"expenses":2000}}

and i want something like  this:

borrower1                 age1    province1      borrower2            age2    province2

primaryBorrower      57         on                  coBorrower1      53          on

what i am doing  is this but it only  gives me  the data  of the co-borrower

do index=1 to countw(JOURNEY_RESPONSES,'{,}','q');
pair = scan(JOURNEY_RESPONSES,index,'{,}','q');
name = dequote(scan(pair,1,':','q'));
value = dequote(scan(pair,2,':','q'));
select (name);
when ('borrower') Borrower=value;
when ('age') Age=input(value,2.);
when ('province') Province=value;
when ('clkReferenceNumber') CR_REF_Number=value;
otherwise put 'NOTE: Unknown field. ' pair=;
end;
end;

How can i fix this, any advice please? 

[sbxkoenk]

Hello,

You should use PROC JSON and PROC HTTP as explained in this paper:

paper: https://support.sas.com/resources/papers/proceedings17/0856-2017.pdf

Paper 856-2017
Parsing JSON with SAS® in 2017
John Kennedy, Mesa Digital

[EDIT] the paper also uses the JSON LIBNAME engine and contains an example where data is retrieved from a JSON-endpoint (URL).

Koen

[BillM_SAS]

I am not exactly clear one what you want as output from the data. The example you supply only shows part of the data in the output. I suspect you want more output from the data. I am able to replicate what you say you want based on the supplied example output. I hope that code I am supplying will allow you to get an idea of how to solve your problem.

I put your JSON file into the file named "sasComm768136.json". From there, I ran this code:

libname x json './sasComm768136.json';

data temp(drop=ordinal_root ordinal_cpat_q2 borrower age province);
set x.cpat_q2 end=lastOne;
retain borrower1 age1 province1;
if (_N_ eq 1)
   then do;
       borrower1 = borrower;
	   age1 = age;
	   province1 = province;
     end;
   else do;
       borrower2 = borrower;
	   age2 = age;
	   province2 = province;
     end;
  if lastOne
     then do;
       output;
	   end;
run;

proc print data=work.temp; run;

This produced:

 Obs       borrower1       age1    province1     borrower2     age2    province2
   1     primaryBorrower     57        ON        coBorrower1     53        ON

[Tal]

Thanks BillM_SAS,

the jason file has more fields  that i need to  output actually

so lets say the original  file has 3  fields  A,  B. and  Joruney_Response  and as an output i want

A        B        Borrower1                age1     province1     borrower2         age2      province2

-----------------------------------------------------------------------------------------------------------------------

10     44        primaryborrower        57          on             coborrower1       53           on