DATA Step, Macro, Functions and more

Extract conditional information from text

Accepted Solution Solved
Reply
Contributor
Posts: 43
Accepted Solution

Extract conditional information from text

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

total16z01_model_avg_jan_2016 total16z01

total17z02_model_1_avg_feb_2017 total17z02_1

total13b1_model_2_avg_2013 total13b1_2

 

Thanks for you help 

 


Accepted Solutions
Solution
‎06-23-2017 12:30 PM
PROC Star
Posts: 7,360

Re: Extract conditional information from text

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

View solution in original post


All Replies
Solution
‎06-23-2017 12:30 PM
PROC Star
Posts: 7,360

Re: Extract conditional information from text

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

Super User
Posts: 5,080

Re: Extract conditional information from text

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;

Super Contributor
Posts: 275

Re: Extract conditional information from text

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

Contributor
Posts: 43

Re: Extract conditional information from text

Thank you for you help. This solution also works 

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 116 views
  • 2 likes
  • 4 in conversation