topic Re: Extract informations in serialised data. in SAS Visual Analytics

Extract informations in serialised data.

Genesis007 — Fri, 23 Dec 2022 08:56:10 GMT

Hi everybody,

In a specific field from my database, I've got a serialized informations that I would like to parse.

The string looks like something like that :

I need to extract the highlighted informations. In each field I can have more than one "MATXXX" info and I need to create a new table with these informations.

I've seen some example on the forum with regular expression, but I don't understand how to use them.

Help me please !

Best rergards,

Michel.

Re: Extract informations in serialised data.

SASKiwi — Fri, 23 Dec 2022 22:41:09 GMT

Can you post some sample data in a DATA step using DATALINES please? You can't copy and paste from screenshots. It will look something like this:

data Have;
  input @1 MyField $500.;
  datalines;
<paste your sample data here>
;
run;

Re: Extract informations in serialised data.

Genesis007 — Mon, 26 Dec 2022 07:54:59 GMT

Hi SASKiwi,

thanks for your answer.

Here is your code with my sample data :

data Have;
input @1 MyField $500.;
datalines;
a:2:{i:0;a:2:{s:8:"material";a:3:{s:4:"name";s:8:"material";s:4:"type";s:5:"input";s:4:"data";s:6:"MAT020";}s:10:"percentage";a:3:{s:4:"name";s:10:"percentage";s:4:"type";s:13:"quantityValue";s:4:"data";a:2:{s:5:"value";N;s:6:"unitId";s:1:"1";}}}i:1;a:2:{s:8:"material";a:3:{s:4:"name";s:8:"material";s:4:"type";s:5:"input";s:4:"data";s:6:"MAT021";}s:10:"percentage";a:3:{s:4:"name";s:10:"percentage";s:4:"type";s:13:"quantityValue";s:4:"data";a:2:{s:5:"value";N;s:6:"unitId";s:1:"1";}}}}
;
run;

When I run this code, I've got some errors with this message :
ERROR 180-322: Statement is not valid or it is used out of proper order.

Regards,

Michel.

Re: Extract informations in serialised data.

Ksharp — Mon, 26 Dec 2022 09:49:40 GMT

data Have;
infile datalines truncover;
input  MyField $500.;
datalines4;
a:2:{i:0;a:2:{s:8:"material";a:3:{s:4:"name";s:8:"material";s:4:"type";s:5:"input";s:4:"data";s:6:"MAT020";}s:10:"percentage";a:3:{s:4:"name";s:10:"percentage";s:4:"type";s:13:"quantityValue";
s:4:"data";a:2:{s:5:"value";N;s:6:"unitId";s:1:"1";}}}i:1;a:2:{s:8:"material";a:3:{s:4:"name";s:8:"material";s:4:"type";s:5:"input";s:4:"data";s:6:"MAT021";}s:10:"percentage";a:3:{s:4:"name";
s:10:"percentage";s:4:"type";s:13:"quantityValue";s:4:"data";a:2:{s:5:"value";N;s:6:"unitId";s:1:"1";}}}}
;;;;
run;
data want;
 set have;
pid=prxparse('/MAT\d+/io');
s=1;e=length(myfield);
call prxnext(pid,s,e,myfield,p,l);
do while(p>0);
 want=substr(myfield,p,l);output;
 call prxnext(pid,s,e,myfield,p,l);
end;
drop pid s e p l;
run;

Re: Extract informations in serialised data.

Genesis007 — Mon, 26 Dec 2022 11:13:32 GMT

Hi Ksharp,

thanks for your answer.
I've got my result !! Thanks a lot !

Can you confirm that I understood your code correctly?

In the first part of the code, your goal is to create a dataset with the information contained in my string.

The second part will search the regular expression defined in the PID variable, with the prxparse function (a string with MAT, and an undefined number of digits ??)

s is the start point of the search in the string, e is the length of the string.

call prxnext launch the search of the "pid" in "myfield", from start position defined in s, to the length of "myfield".
The function return the starting position (p) of my string and its length (l). I think if there's no match found, the prxnext function return a p value < 0 ?

With the substr instruction, I just have to extract the part of the string corresponding to the start point of the substring and its length returned by the prxnext...

Is it correct ?

Thanks again.

Re: Extract informations in serialised data.

Ksharp — Tue, 27 Dec 2022 08:32:03 GMT

"a string with MAT, and an undefined number of digits ??)"
Yes. a string with MAT, and one or more digits followed .

"s is the start point of the search in the string, e is the length of the string.
call prxnext launch the search of the "pid" in "myfield", from start position defined in s, to the length of "myfield".
The function return the starting position (p) of my string and its length (l). I think if there's no match found, the prxnext function return a p value < 0 ?"
Yes. Correct. You can refer to the documentation of PRXNEXT() , there are many examples you can refer .

Re: Extract informations in serialised data.

Genesis007 — Tue, 27 Dec 2022 15:57:01 GMT

Once again, thanks for your help !