Extract conditional information from text

[zqkal]

Hi everyone, 

I really appreciate all you help on this. I have racked my brain for hours to solve it using perl reg experation but no luck. 

I wish to create a variable called "name" from the variable "text" from the below dataset. 

data Have;
ID=1;
text='total16z01_model_avg_jan_2016';
output;
ID=2;
text='total17z02_model_1_avg_feb_2017';
output;
ID=3;
text='total13b1_model_2_avg_2013';
output;
run;

desired output: 

ID text name

1 total16z01_model_avg_jan_2016 total16z01

2 total17z02_model_1_avg_feb_2017 total17z02_1

3 total13b1_model_2_avg_2013 total13b1_2

Thanks for you help 

[art297 · Accepted solution]

Here is one way:

data want;
  set have;
  name=scan(text,1,'_');
  if anydigit(scan(text,3,'_')) then name=
    catx('_',name,scan(text,3,'_'));
run;

Art, CEO, AnalystFinder.com

[Astounding]

Without knowing much about regular expressions, I would just have to solve it the old-fashioned way:

data want;

set have;

name = scan(text, 1, '_');

word3 = scan(text, 3, '_');

if input(word3, ??5.) > . then name = trim(name) || '_' || word3;

drop word3;

run;

[slchen]

name=prxchange('s/_[a-z]+|_\d+$//i',-1,strip(text));

[zqkal]

Thank you for you help. This solution also works