topic Re: Extracting text from a complex string in SAS Programming

Extracting text from a complex string

SDasari2 — Fri, 21 Dec 2018 21:18:28 GMT

Hi All,

I need help in extracting the column0 value alone from this entire string in the variable name data. Currently its "Nursing Facility Care", as this is just 1 row of the entire data. There can be multiple values in column0, hence i need the column0 value post "column0": to be extracted.

data
{"rows":{"row2":{"column0":"Nursing Facility Care","column1":"Hospital Leave Days","column2":"N\/A","column3":"7","column4":"Days","column5":"1","column6":"Per Week","column7":"activity","column8":"","column9":"","column10":""},"row3":{"column0":"Nursing Facility Care","column1":"Hospital Leave Days","column2":"N\/A","column3":"7","column4":"Days","column5":"1","column6":"Per Week","column7":"activity","column8":"","column9":"","column10":""}}}

I am currently using

 findw(data,"column0") , but not getting any results to then use substr function

Thanks In Advance!

Re: Extracting text from a complex string

Reeza — Fri, 21 Dec 2018 21:20:38 GMT

Are they all like that? It's easy to post a solution to this specific situation, but I suspect it won't scale at all.

Because all the words are strung together, no spaces, SAS may need you to tell it what the delimiters are. Check the options in the FINDW() documentation.

Otherwise, I'd probably recommend using the SCAN() function instead. Make sure to specify a quote as a delimiter unless that doesn't make sense.

x = scan(data, 4, '":{}');

@SDasari2 wrote:

Hi All,

I need help in extracting the column0 value "Nursing Facility Care" alone from this entire string in the variable name data.

data
{"rows":{"row2":{"column0":"Nursing Facility Care","column1":"Hospital Leave Days","column2":"N\/A","column3":"7","column4":"Days","column5":"1","column6":"Per Week","column7":"activity","column8":"","column9":"","column10":""},"row3":{"column0":"Nursing Facility Care","column1":"Hospital Leave Days","column2":"N\/A","column3":"7","column4":"Days","column5":"1","column6":"Per Week","column7":"activity","column8":"","column9":"","column10":""}}}

I am currently using
 findw(data,"column0") , but not getting any results to then use substr function
Thanks In Advance!

Re: Extracting text from a complex string

novinosrin — Fri, 21 Dec 2018 21:59:36 GMT

data have;
string='{"rows":{"row2":{"column0":"Nursing Facility Care","column1":"Hospital Leave Days","column2":"N\/A","column3":"7","column4":"Days","column5":"1","column6":"Per Week","column7":"activity","column8":"","column9":"","column10":""},"row3":{"column0":"Nursing Facility Care","column1":"Hospital Leave Days","column2":"N\/A","column3":"7","column4":"Days","column5":"1","column6":"Per Week","column7":"activity","column8":"","column9":"","column10":""}}}';
run;

data want;
set have;
t1=find(string,'column0')+8;
t2=find(string,'column1')-t1-1 ;
want=compress(substr(string,t1,t2),,'p');
drop t:;
run;

Re: Extracting text from a complex string

SDasari2 — Fri, 28 Dec 2018 15:28:23 GMT

Thanks a lot for the solution. This works ! You are Awesome!

Re: Extracting text from a complex string

SDasari2 — Fri, 28 Dec 2018 15:29:26 GMT

Thanks a lot for the response! this option works too!

Re: Extracting text from a complex string

Vince_SAS — Sat, 29 Dec 2018 02:57:12 GMT

Assuming that the JSON data is in a file, then you should probably use the JSON LIBNAME engine to access the data.

filename myjson      'C:\temp\temp.json';
libname  myjson json 'C:\temp\temp.json';

title 'ROWS_ROW2 Table';
proc print data=myjson.rows_row2; run; quit;

title 'ROWS_ROW3 Table';
proc print data=myjson.rows_row3; run; quit;

Vince DelGobbo

SAS R&D

Re: Extracting text from a complex string

Reeza — Sat, 29 Dec 2018 20:13:31 GMT

@SDasari2 please mark the question as solved.

Re: Extracting text from a complex string

SDasari2 — Mon, 31 Dec 2018 17:48:11 GMT

Hi Vince,

its actually loaded in GP database and i am extracting it through SAS. And actually i need to pull column0 values from all the rows. If you see this string has row2, row3, row4 and each of the rows have column0. This examples has only 4 rows, but there can be multiple based on the number of services/support they receive. Is there a way to extract each of the column0 onto seperate columns?

data
{"rows":{"row2":{"column0":"Personal Care","column1":"15","column2":"Hours Per Week","column3":"Home","column4":"KARING NURSING REGISTRY INC (Group)","column5":"12\/01\/2018","column6":"11\/30\/2019","column7":"8363","column8":"1","column9":"4","column10":"41"},"row3":{"column0":"Homemaker Services","column1":"10","column2":"Hours Per Week","column3":"Home","column4":"KARING NURSING REGISTRY INC (Group)","column5":"12\/01\/2018","column6":"11\/30\/2019","column7":"8366","column8":"1","column9":"67","column10":"41"},"row4":{"column0":"Adult Sized Disposable Incontinence Product - Protective Underwear\/Pull-On, XL (each) - Consumable","column1":"60","column2":"Items Per Request","column3":"Home","column4":"SURFMED DME (Group)","column5":"12\/01\/2018","column6":"11\/30\/2019","column7":"8375","column8":"1","column9":"115","column10":"41"}}}

Re: Extracting text from a complex string

Vince_SAS — Wed, 02 Jan 2019 15:29:39 GMT

Using the JSON LIBNAME engine might be the best way to solve this problem.

The general steps are as follows:

1. Retrieve the data from the database, and then store it in an external file. The first DATA step code below mimics that.

2. Use the JSON LIBNAME engine to access the data as individual SAS tables. The PROC CONTENTS step shows the available tables.

3. Extract the COLUMN0 data from the MYJSON.ALLDATA table. The second DATA step performs this action.

filename myjson temp;

*  Extract data from database, and then save in an external file;

data _null_;
length data $32767; 
infile cards4 truncover;
file myjson;
input data $char32767.;
put data;
cards4;
{"rows":{"row2":{"column0":"Personal Care","column1":"15","column2":"Hours Per Week","column3":"Home","column4":"KARING NURSING REGISTRY INC (Group)","column5":"12\/01\/2018","column6":"11\/30\/2019","column7":"8363","column8":"1","column9":"4","column10":"41"},"row3":{"column0":"Homemaker Services","column1":"10","column2":"Hours Per Week","column3":"Home","column4":"KARING NURSING REGISTRY INC (Group)","column5":"12\/01\/2018","column6":"11\/30\/2019","column7":"8366","column8":"1","column9":"67","column10":"41"},"row4":{"column0":"Adult Sized Disposable Incontinence Product - Protective Underwear\/Pull-On, XL (each) - Consumable","column1":"60","column2":"Items Per Request","column3":"Home","column4":"SURFMED DME (Group)","column5":"12\/01\/2018","column6":"11\/30\/2019","column7":"8375","column8":"1","column9":"115","column10":"41"}}}
;;;;
run;

libname myjson json;

proc contents data=myjson._all_; run; quit;

data work.want (keep=value rename=(value=column0));
set myjson.alldata;

if (lowcase(p3) eq 'column0' and
    lowcase(p1) eq 'rows' and
    lowcase(substr(p2, 1, 3)) eq 'row');

run;

proc print data=work.want; run; quit;

You will have to adjust the sample code to meet your needs.

Vince DelGobbo

SAS R&D

Re: Extracting text from a complex string

SDasari2 — Wed, 02 Jan 2019 14:42:19 GMT

I am getting the below error:

ERROR: Insufficient authorization to access /opt/sas/sas94m3/SASDev/config/Lev2/SASDev/myjson.dat.

Looks like we dont have access to perform that run.

Re: Extracting text from a complex string

SDasari2 — Wed, 02 Jan 2019 14:51:33 GMT

Issue is still not solved, the scan function is extrating the column0 value from row2 only. In the string there could be multiple rows.

This string has from row2 to row4 and each row has column0. I need help in extracting all the column0's from each row into seperate columns.

Please help! Thanks in Advance!

Re: Extracting text from a complex string

Vince_SAS — Wed, 02 Jan 2019 15:36:21 GMT

You get this error because you have a FILENAME statement like this, or you omitted the FILENAME statement:

filename myjson 'myjson.dat';

The file attempts to be written to the server's startup directory location, and you don't have access to that. The solution is to write the file to a space that you have access to, or to use a temporary file.

I updated the code in my earlier response to use a temporary file, namely:

filename myjson temp;

Vince DelGobbo

SAS R&D

Re: Extracting text from a complex string

Vince_SAS — Wed, 02 Jan 2019 15:40:12 GMT

In my opinion, string parsing using SCAN or other functions is cumbersome and error-prone. I suggest that you use the JSON LIBNAME engine instead.

Vince DelGobbo

SAS R&D

Re: Extracting text from a complex string

SDasari2 — Wed, 02 Jan 2019 19:23:46 GMT

I still getting an error. Below is the same.

25 GOPTIONS ACCESSIBLE;
26 libname myjson json;
ERROR: The JSON engine cannot be found.
ERROR: Error in the LIBNAME statement.
27
28 proc contents data=myjson._all_; run;

ERROR: Libref MYJSON is not assigned.

Re: Extracting text from a complex string

andreas_lds — Thu, 03 Jan 2019 06:15:33 GMT

@SDasari2 wrote:

I still getting an error. Below is the same.

25 GOPTIONS ACCESSIBLE;
26 libname myjson json;
ERROR: The JSON engine cannot be found.
ERROR: Error in the LIBNAME statement.
27
28 proc contents data=myjson._all_; run;

ERROR: Libref MYJSON is not assigned.

Looks like an older SAS release is installed at your site. Which version is installed?

Re: Extracting text from a complex string

SDasari2 — Thu, 03 Jan 2019 14:33:44 GMT

I work on EG and The platform version is 9.4.3.0

Re: Extracting text from a complex string

andreas_lds — Fri, 04 Jan 2019 06:35:50 GMT

@SDasari2 wrote:

I work on EG and The platform version is 9.4.3.0

Interesting version number. Looks like 9.4m3 - afaik the json-engine has been added in m4 or m5.