topic Re: Using Substring and scan function to pull number in SAS Enterprise Guide

Using Substring and scan function to pull number

chrisjab — Fri, 19 Jan 2018 19:12:11 GMT

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

Re: Using Substring and scan function to pull number

art297 — Fri, 19 Jan 2018 19:21:32 GMT

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

Re: Using Substring and scan function to pull number

art297 — Fri, 19 Jan 2018 19:25:50 GMT

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

Re: Using Substring and scan function to pull number

novinosrin — Fri, 19 Jan 2018 19:45:08 GMT

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;

Re: Using Substring and scan function to pull number

novinosrin — Fri, 19 Jan 2018 19:56:44 GMT

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;

Re: Using Substring and scan function to pull number

chrisjab — Fri, 19 Jan 2018 20:07:08 GMT

Thank you for your help; It worked !