Thanks Tom,
it turns out that the json formatted field can have different set of variables within it so i slightly modiifed your query:
data want; /*string='{"c3data":{"userCostCenter":1020, "countryCode":"CA", "userMutualFundUpdateAccess":"true", "keepAliveURL":"https://c3w78.sys.c3.tdgroup.com/c3/KeepAliveServlet", "returnURL":"https://c3w78.sys.c3.tdgroup.com/c3/DiscoveryToolTransferInServlet", "languageCode":"en", "sessionLength":10800, "userLogonID":"TESTC35"}}';;*/
/*string='{"CD_Q4":["C"],*/ /* "isExistingCustomer":"true",*/ /* "CD_Q2":["C"],*/ /* "isProfessionalStudent":"false",*/ /* "isN2C":"true",*/ /* "PSI_Q14":3,*/ /* "PSI_Q1A":"C",*/ /* "PSI_Q1B":"C",*/ /* "PSI_Q7":100000,*/ /* "PSI_Q8":10000,*/ /* "PSI_Q9B":100,*/ /* "PSI_Q2":"B",*/ /* "PSI_Q3":"C",*/ /* "PSI_Q5":"B",*/ /* "PSI_Q6":"C",*/ /* "PSI_Q12":"C",*/ /* "PSI_Q4":"D",*/ /* "CD_Q6":["NONE"],*/ /* "secondaryNeeds":["N2C-Package","PSI-Products"],*/ /* "wealthConfirmationCheckbox":"true",*/ /* "AdditionalQ1":"C",*/ /* "AdditionalQ1_Text":"test",*/ /* "showInterestInd":"false"}';*/
string='{ "TotalBorrowers":"1", "CPAT_Q2":[{"borrower":"primaryBorrower", "secondaryNeeds":["N2C-Package","PSI-Products"], "age":67, "CD_Q2":["C"], "province":"ON"}]}'; do index=1 to countw(string,'{,}','q'); pair=scan(string,index,'{,}','q'); name=dequote(scan(pair,1,':','q')); value=scan(pair,2,':','q'); output; end; drop index string pair; run;
and am getting all variables parsed but names of variables in one column and their values in the other which means i will have to transpose this to get the dataset i want. Transposing not an issue but along with the json formatted fields the text file also has other fields which will make transposing difficult, So any chance :
1. name value
var1 x
var2 y
var3 z
can be switched to
var1 var2 var3
x y z
2. to suppress /remove variables such as CPAT*,CD*,PSI* from the parsed field? I dont need those
3. I am seeing "[","]" as names /values in "name" and "value ". Can that be removed?
4.["N2C-Package","PSI-Products"], should be a value but i see PSI-Products under "names" and i am sure this got something to do with the comma but no idea how to fix it
... View more
