topic Re: Extracting a substring by using regular expressions in SAS Programming

Extracting a substring by using regular expressions

skyvalley81 — Wed, 25 Jan 2017 21:54:45 GMT

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

Re: Extracting a substring by using regular expressions

ballardw — Wed, 25 Jan 2017 22:07:02 GMT

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

Re: Extracting a substring by using regular expressions

skyvalley81 — Wed, 25 Jan 2017 22:30:21 GMT

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

Re: Extracting a substring by using regular expressions

PGStats — Wed, 25 Jan 2017 23:12:00 GMT

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;

Re: Extracting a substring by using regular expressions

skyvalley81 — Thu, 26 Jan 2017 00:48:03 GMT

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("/[^',)]+$/")

Re: Extracting a substring by using regular expressions

Patrick — Thu, 26 Jan 2017 01:23:46 GMT

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;

Re: Extracting a substring by using regular expressions

skyvalley81 — Thu, 26 Jan 2017 01:37:37 GMT

Hi Patrick,

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

Re: Extracting a substring by using regular expressions

PGStats — Thu, 26 Jan 2017 04:09:42 GMT

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

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