Solved
Contributor
Posts: 23

# 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
Super User
Posts: 8,214

## 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

All Replies
Solution
‎01-19-2018 03:06 PM
Super User
Posts: 8,214

## 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 !

Super User
Posts: 8,214

## 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

Super User
Posts: 2,049

## 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;``````

Super User
Posts: 2,049

## Re: Using Substring and scan function to pull number

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;``````
☑ This topic is solved.

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

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