Hi
If i am given string like that (I need to read in from many TXT files, each text file containing a string like this)
[{"question":"Reference Number","answer":"5e57064595e4da0011efcde8"},{"question":"Timestamp","answer":"Thu, 27 Feb 2020 07:59:01 AM"},{"question":"I am","answer":"Delivery Staff"},{"question":"Please specify other purpose of visit","answer":""},{"question":"Going to...","answer":"Others:"},{"question":"Location - Wards/Clinic","answer":""},{"question":"Location-Nursing Homes/Care Centres","answer":""}]
when it is logically
Looks like JSON. Try using the JSON libname engine.
Hi @Yiang
Here is an approach to do this:
data have;
text ='[{"question":"Reference Number","answer":"5e57064595e4da0011efcde8"},{"question":"Timestamp","answer":"Thu, 27 Feb 2020 07:59:01 AM"},{"question":"I am","answer":"Delivery Staff"},{"question":"Please specify other purpose of visit","answer":""},{"question":"Going to...","answer":"Others:"},{"question":"Location - Wards/Clinic","answer":""},{"question":"Location-Nursing Homes/Care Centres","answer":""}]';
run;
data split;
set have;
length question answer $ 200;
text = prxchange('s/\[|\]//',-1,text);
do i=1 to countw(text,"{");
extract = scan(text,i,"{");
question = prxchange('s/"question":"(.*)","answer":"(.*)"\},?/$1/',1,extract);
answer = prxchange('s/"question":"(.*)","answer":"(.*)"\},?/$2/',1,extract);
output;
end;
drop i text extract;
run;
Best,
As noted, the JSON Libname engine can handle the example data you supplied. With the example data in a text file I named JSON_SAS_COMM_627718.json, the Libname statement is:
libname x json './JSON_SAS_COMM_627718.json' automap=reuse;
proc print data=x.alldata(drop=p v);
run;
This produces:
Obs P1 Value 1 question Reference Number 2 answer 5e57064595e4da0011efcde8 3 question Timestamp 4 answer Thu, 27 Feb 2020 07:59:01 AM 5 question I am 6 answer Delivery Staff 7 question Please specify other purpose of visit 8 answer 9 question Going to... 10 answer Others: 11 question Location - Wards/Clinic 12 answer 13 question Location-Nursing Homes/Care Centres 14 answer
Sorry, too much going on this morning. I printed the wrong table. The table I originally posted has the question and answers in different observations in the table. Using the ROOT table fixes that problem.
libname x json './JSON_SAS_COMM_627718.json' automap=reuse;
proc print data=x.root(drop=ordinal_root);
run;
Produces:
Obs question answer 1 Reference Number 5e57064595e4da0011efcde8 2 Timestamp Thu, 27 Feb 2020 07:59:01 AM 3 I am Delivery Staff 4 Please specify other purpose of visit 5 Going to... Others: 6 Location - Wards/Clinic 7 Location-Nursing Homes/Care Centres
Just to add to what @BillM_SAS writes here a fully working self contained example.
For your real data just point filename json to the source of your actual json file, remove the data _NULL_ step and things should just work.
filename have temp recfm=n;
data _null_;
file have;
infile datalines truncover;
input;
put _infile_;
datalines;
[{"question":"Reference Number","answer":"5e57064595e4da0011efcde8"},
{"question":"Timestamp","answer":"Thu, 27 Feb 2020 07:59:01 AM"},
{"question":"I am","answer":"Delivery Staff"},
{"question":"Please specify other purpose of visit","answer":""},
{"question":"Going to...","answer":"Others:"},
{"question":"Location - Wards/Clinic","answer":""},
{"question":"Location-Nursing Homes/Care Centres","answer":""}]
;
libname x json "%sysfunc(pathname(have))" automap=reuse;
proc print data=x.root(drop=ordinal_root);
run;
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.