Extracting a substring by using regular expressions

[skyvalley81]

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

[ballardw]

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

[skyvalley81]

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

[Patrick]

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;

[skyvalley81]

Hi Patrick,

Thank you so much, your approach it is also very helpful

[PGStats · Accepted solution]

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;

[skyvalley81]

Hi PGStats,

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

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

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

[PGStats]

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

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