topic Re: Tokenising a string in SAS Programming

Tokenising a string

Yiang — Thu, 27 Feb 2020 00:50:58 GMT

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

{
- "question" : Reference Number,
- "answer" : 5e57064595e4da0011efcde8
},
-{
- "question" : Timestamp,
- "answer" : Thu, 27 Feb 2020 07:59:01 AM
},
-{
- "question" : I am,
- "answer" : Visitor
},
-{
- "question" : Please specify other purpose of visit,
- "answer" :
},
-{
- "question" : Going to...,
- "answer" : Others:
},
-{
- "question" : Location ,
- "answer" : Shop
},
-{
- "question" : Location2,
- "answer" : Blk 1
}

]

May i know is there a way for me to convert it into a table such that

Each question is 1 field, answer is the value the field and i need to create a table to store the data.

The flow is as such

for each TXT file,

Read in the string

Tokenise the string

Append the answer into Table1, where the each field represents a "question"

I often use SAS only for basic proper table data analysis. But i do not have much knowledge about big data handling but am keen to learn the techniques.

Thank you

Yiang

Re: Tokenising a string

Tom — Thu, 27 Feb 2020 01:25:26 GMT

Looks like JSON. Try using the JSON libname engine.

https://documentation.sas.com/?docsetId=lestmtsglobal&docsetTarget=n1jfdetszx99ban1rl4zll6tej7j.htm&docsetVersion=9.4&locale=en

Re: Tokenising a string

ed_sas_member — Thu, 27 Feb 2020 09:22:59 GMT

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,

Re: Tokenising a string

BillM_SAS — Thu, 27 Feb 2020 14:30:31 GMT

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

Re: Tokenising a string

Patrick — Thu, 27 Feb 2020 15:13:05 GMT

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;

Re: Tokenising a string

BillM_SAS — Thu, 27 Feb 2020 15:09:58 GMT

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