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?
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
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.