BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
skyvalley81
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

7 REPLIES 7
ballardw
Super User

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
Obsidian | Level 7

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
Opal | Level 21

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
Obsidian | Level 7
Hi Patrick,

Thank you so much, your approach it is also very helpful
PGStats
Opal | Level 21

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
skyvalley81
Obsidian | Level 7

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

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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