topic Re: Extract certain numeric from a string in SAS Programming

Extract certain numeric from a string

achen — Mon, 16 Dec 2019 19:38:56 GMT

Here is my data:

E90K
G1006AfsX51
W1001X
R1014X
D102A
G1036D
R1035G fsX22
R1035fsX1056

C52AfsX8

I want:

1006

1001

1014

102

1036

1035

Re: Extract certain numeric from a string

PaigeMiller — Mon, 16 Dec 2019 19:45:57 GMT

So, does the numeric always begin in the second character? If so

data want;
    set have;
    string=substr(string,2); /* Throw out first character, we don't need it */
    numeric=input(substr(string,1,notdigit(string)-1),10.0);
run;

Re: Extract certain numeric from a string

Tom — Mon, 16 Dec 2019 19:50:12 GMT

One simple way is to use SCAN() and tell that everything except digits are the delimiter characters.

So to get it as another character variable use something like this:

data want ;
  set have ;
  numb = scan(string,1,compress(string,,'d'));
run;

Or add an INPUT() function call to convert it to an actual number.

data want ;
  set have ;
  numb = input(scan(string,1,compress(string,,'d')),??32.);
run;

Obs    string          numb

  1    E90K              90
  2    G1006AfsX51     1006
  3    W1001X          1001
  4    R1014X          1014
  5    D102A            102
  6    G1036D          1036
  7    R1035G fsX22    1035
  8    R1035fsX1056    1035
  9                       .
 10    C52AfsX8          52

Re: Extract certain numeric from a string

achen — Mon, 16 Dec 2019 19:54:51 GMT

There are a few cases start with a number

448 insG
639+5G>A

I want:

448

639

Re: Extract certain numeric from a string

PaigeMiller — Mon, 16 Dec 2019 19:58:15 GMT

@achen wrote:

There are a few cases start with a number

448 insG
639+5G>A

I want:

448

639

Are there any other cases we need to know about?

Re: Extract certain numeric from a string

achen — Mon, 16 Dec 2019 20:01:59 GMT

Tom, thank you and it works great!

Re: Extract certain numeric from a string

Ksharp — Tue, 17 Dec 2019 12:05:17 GMT

Tom,

scan() has built-in modified option for this kind of situation.

data have;
input string $20.;
cards;
E90K
G1006AfsX51
W1001X
R1014X
D102A
G1036D
R1035G fsX22 
R1035fsX1056
C52AfsX8
;
data want ;
  set have ;
  numb = scan(string,1,,'kd');
run;