topic Re: Parsing a JASON File in SAS Programming

Parsing a JSON File

Tal — Thu, 16 Sep 2021 14:46:30 GMT

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?

Re: Parsing a JASON File

sbxkoenk — Thu, 16 Sep 2021 13:58:40 GMT

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

Re: Parsing a JSON File

BillM_SAS — Thu, 16 Sep 2021 19:06:38 GMT

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

Re: Parsing a JSON File

Tal — Thu, 16 Sep 2021 19:30:55 GMT

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