Extracting a substring by using regular expressions

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Extracting a substring by using regular expressions

Hi Everyone,

 

I would like to extract a list of codes from a particular dataset. 

I tried to use regular expressions but unfortunatly didnt manage to exract the whole list of codes but only first occurence of the pattern. The codes are quoted, consist from 3 to 5 digits, start with V or E or numbers {0-9} and continue with numbers.

 

What I have:

 

data test;
length codelist $120;
input codelist $;
datalines;
DX(i)('410','412')CC_GRP_1
DX(i)('39891','40201','40211','40291','40401','40403','40411','40413','40491','40493',
'4254','4255','4257','4258','4259','428')CC_GRP_2
DX(i)('0930','4373','440','441','4431','4432','4438','4439','4471','5571','5579','V434')
DX(i)('36234','430','431','432','433','434','435','436','437','438')CC_GRP_4
;

run;


data test2;
set test;
retain pattern_num ;
if _n_ = 1 then pattern_num = prxparse("/('E|V|\d\d*')/");
call prxsubstr(pattern_num,strip(codelist),start, lenght);
run;

 

Desired output:

'410','412'
'39891','40201','40211','40291','40401','40403','40411','40413','40491','40493'
'4254','4255','4257','4258','4259','428'
'0930','4373','440','441','4431','4432','4438','4439','4471','5571','5579','V434'
'36234','430','431','432','433','434','435','436','437','438'

 

Thanks in advance


Accepted Solutions
Solution
‎01-25-2017 08:35 PM
Respected Advisor
Posts: 4,606

Re: Extracting a substring by using regular expressions

Here is how you could get the codes (this goes a step further than just getting the code strings)

 

data want;
if not prx1 then prx1 + prxparse("/'[EV0-9]\d{2,4}'/");
if not prx2 then prx2 + prxparse("/[^',)]+$/");
set test;
length line 8 trailler $12 code $8;
line = _n_;
if prxmatch(prx2, codelist) then trailler = prxPosn(prx2, 0, codelist);
start = 1;
stop = length(codelist);
call prxNext(prx1, start, stop, codelist, pos, len);
  do while (pos > 0);
     code = dequote(substr(codelist, pos, len));
     output;
     call prxNext(prx1, start, stop, codelist, pos, len);
  end;
keep line trailler code;
run;
PG

View solution in original post


All Replies
Grand Advisor
Posts: 10,210

Re: Extracting a substring by using regular expressions

Is your actual data for the second and third observations two separate observations or should that actually be one row of data in the original source?

 

If there wasn't a hard break there this would work:

data test;
length codelist pattern $220;
input codelist $;
pattern= scan(codelist,3,'() ');
datalines;
DX(i)('410','412')CC_GRP_1
DX(i)('39891','40201','40211','40291','40401','40403','40411','40413','40491','40493','4254','4255','4257','4258','4259','428')CC_GRP_2
DX(i)('0930','4373','440','441','4431','4432','4438','4439','4471','5571','5579','V434')
DX(i)('36234','430','431','432','433','434','435','436','437','438')CC_GRP_4
;
run;

It really looks like your list should start with "DX(i) ( " and end with ") CC_GRP" but if the pattern isn't consistent the regular expressions may become some what less regular...

 

New Contributor
Posts: 4

Re: Extracting a substring by using regular expressions

Hi ballardw,

 

Thank you very much for your prompt reply.

Each line is a separate observation.

Your idea is really good but because there is no consistent pattern the scan function might not work. For example, there are cases that there is no parenthesis at the end of the string. With this regex I can find the position of first match of the pattern. However this not suffice to extract the whole list. I was wondering if there is any way to find the last occurrence of the pattern then I could easily extract the substring.

 

Thanks again

Respected Advisor
Posts: 3,825

Re: Extracting a substring by using regular expressions

Here another option where you first remove any characters before the first and after the last single quote from the input buffer.

data test;
  infile datalines truncover dlm=',' ;
  input @;
  /* remove all characters from input buffer before the first and after the last quote */  
  _infile_=prxchange("s/^[^']*('.+')[^']*$/\1/o",1,_infile_);

  /* map variable against modified input buffer */
  source_rec_no=_n_;
  do while(1);
    input myvar :$20. @;
    if missing(myvar) then leave;
    myvar=dequote(myvar);
    output;
  end;

datalines;
DX(i)('410','412')CC_GRP_1
DX(i)('39891','40201','40211','40291','40401','40403','40411','40413','40491','40493',
'4254','4255','4257','4258','4259','428')CC_GRP_2
DX(i)('0930','4373','440','441','4431','4432','4438','4439','4471','5571','5579','V434')
DX(i)('36234','430','431','432','433','434','435','436','437','438')CC_GRP_4
;
run;
New Contributor
Posts: 4

Re: Extracting a substring by using regular expressions

Hi Patrick,

Thank you so much, your approach it is also very helpful
Solution
‎01-25-2017 08:35 PM
Respected Advisor
Posts: 4,606

Re: Extracting a substring by using regular expressions

Here is how you could get the codes (this goes a step further than just getting the code strings)

 

data want;
if not prx1 then prx1 + prxparse("/'[EV0-9]\d{2,4}'/");
if not prx2 then prx2 + prxparse("/[^',)]+$/");
set test;
length line 8 trailler $12 code $8;
line = _n_;
if prxmatch(prx2, codelist) then trailler = prxPosn(prx2, 0, codelist);
start = 1;
stop = length(codelist);
call prxNext(prx1, start, stop, codelist, pos, len);
  do while (pos > 0);
     code = dequote(substr(codelist, pos, len));
     output;
     call prxNext(prx1, start, stop, codelist, pos, len);
  end;
keep line trailler code;
run;
PG
New Contributor
Posts: 4

Re: Extracting a substring by using regular expressions

Hi PGStats,

 

Thank you very much for your solution the code is exactly what i was looking for Smiley Happy

 

Could I ask you to explain me the second regex prxparse if it is possible

prxparse("/[^',)]+$/")

 

Respected Advisor
Posts: 4,606

Re: Extracting a substring by using regular expressions

"/[^',)]+$/"

matches any character except a single quote, a comma, or a closing parenthesis, repeated one or more times until the end of the string. 

PG
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 244 views
  • 1 like
  • 4 in conversation