Using Substring and scan function to pull number

[chrisjab]

I am trying to extract the middle 2 numbers from the group names below: 

• VIPER170891
• HEAT170992
• BULLS170691

I applied the formula below; however, I am getting an error. 

SUBSTR(RIGHT(SCAN(t1.GROUPNAME, -1, ' ')),4,2)

Can someone help ? 

Thanks, 

Chris

[art297 · Accepted solution]

Here is one way:

data have;
  informat groupname $15.;
  input groupname;
  want=substr(groupname,anydigit(groupname)+2,2);
  cards;
VIPER170891
HEAT170992
BULLS170691
;

Art, CEO, AnalystFinder.com

[chrisjab]

Thank you for your help; It worked ! 

[art297]

After posting my suggestion, I noticed that your initial code looked like it was pulled from a proc sql instance. If so, the following would work:

data have;
  informat groupname $15.;
  input groupname;
  cards;
VIPER170891
HEAT170992
BULLS170691
;

proc sql;
  create table want as
    select substr(t1.groupname,anydigit(groupname)+2,2) as want
      from have t1
  ;
quit;

Art, CEO, AnalystFinder.com

[novinosrin]

if your pattern holds true for all obs

data have;
  informat groupname $15.;
  input groupname;
  want=reverse(substr(reverse(strip(groupname)),3,2));
  cards;
VIPER170891
HEAT170992
BULLS170691
;


/*if reading a dataset*/
data have;
 informat groupname $15.;
 input groupname;
 cards;
VIPER170891
HEAT170992
BULLS170691
;

data want;
set have;
want=reverse(substr(reverse(strip(groupname)),3,2));
run;

[novinosrin]

Another way, this 'kd' thing was shared by Art to me only yesterday 🙂

data have;
  informat groupname $15.;
  input groupname;
  cards;
VIPER170891
HEAT170992
BULLS170691
;

data want;
set have;
want=substr(compress(strip(groupname),'','kd'),3,2) ;
run;