Extract informations in serialised data.

[Genesis007]

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.

[SASKiwi]

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; 

[Genesis007]

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.

[Ksharp · Accepted solution]

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;

[Genesis007]

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.

[Ksharp]

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

[Genesis007]

Once again, thanks for your help !