Extracting text from a complex string

[SDasari2]

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!

[Reeza]

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!

---

[SDasari2]

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

[novinosrin]

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;

[SDasari2]

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

[Reeza]

@SDasari2 please mark the question as solved.

[SDasari2]

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

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"}}}

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!

[Vince_SAS]

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

[Vince_SAS]

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

[SDasari2]

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"}}}

[Vince_SAS]

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 

[SDasari2]

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.

[Vince_SAS]

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

[SDasari2]

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.

[andreas_lds]

---

@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?