Desktop productivity for business analysts and programmers

Using Substring and scan function to pull number

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

Using Substring and scan function to pull number

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

 

 

 


Accepted Solutions
Solution
‎01-19-2018 03:06 PM
PROC Star
Posts: 8,101

Re: Using Substring and scan function to pull number

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

 

View solution in original post


All Replies
Solution
‎01-19-2018 03:06 PM
PROC Star
Posts: 8,101

Re: Using Substring and scan function to pull number

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

 

Contributor
Posts: 23

Re: Using Substring and scan function to pull number

Thank you for your help; It worked ! 

PROC Star
Posts: 8,101

Re: Using Substring and scan function to pull number

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

 

PROC Star
Posts: 1,296

Re: Using Substring and scan function to pull number

[ Edited ]

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;

 

PROC Star
Posts: 1,296

Re: Using Substring and scan function to pull number

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

 

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

data want;
set have;
want=substr(compress(strip(groupname),'','kd'),3,2) ;
run;
☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 214 views
  • 0 likes
  • 3 in conversation