topic Re: How to extract numeric values after indexing (with the indexed point being right of the values) in SAS Programming

How to extract numeric values after indexing (with the indexed point being right of the values)

JamieTee — Tue, 26 May 2020 22:01:09 GMT

I have a variable 'description' that has something like 'bob walked 5 km' or 'joe swam for 3.2 km yesterday night'

I'm trying to extract the values of '5' and '3.2' from this variable into a new variable, however, I'm stuck trying to figure out a way after i find the position of the "km" string. I haven't been able to find a way to read left of the indexed position. As a note, there is always a space before the "km"

I have this

Person	Description
Bob	Bob walked 5 km at 10PM
Joe	Joe swan for 3.2 km yesterday

I ideally want to create a table that looks like this

Person	Description	Distance
Bob	Bob walked 5 km at 10PM	5
Joe	Joe swan for 3.2 km yesterday	3.2

Re: How to extract numeric values after indexing (with the indexed point being right of the values)

PaigeMiller — Tue, 26 May 2020 21:58:27 GMT

Use the ANYDIGIT function to find the first digit. Then use SCAN to find the next "word".

where = anydigit(description);
number = scan(substr(description,where),1);

Of course, this and just about any other manipulation requires an assumption or two, which you need to make sure is valid for the larger data set.

Re: How to extract numeric values after indexing (with the indexed point being right of the values)

JamieTee — Tue, 26 May 2020 22:01:54 GMT

Thank you so much! I've never seen this function before - it looks super useful!

Sorry - would the ANYDIGIT also work if I have other digits in the cell too? Thank you again, I really appreciate your advice.

i.e.,

Person	Description
Bob	Bob walked 5 km at 10PM
Joe	Joe swan for 3.2 km yesterday

Re: How to extract numeric values after indexing (with the indexed point being right of the values)

PaigeMiller — Tue, 26 May 2020 22:02:59 GMT

ANYDIGIT finds the location of the first digit in the string.

Re: How to extract numeric values after indexing (with the indexed point being right of the values)

biopharma — Tue, 26 May 2020 22:16:25 GMT

If you are specifically looking for digits preceding "km" and want to guard against extracting digits that appear way before "km", here is one way to do it. Not very elegant but seems to do the job. If you want functions to search backwards, use negative integers for the position.

data want ;
   set have ;
   distance = input(scan(substr(description,1,index(description,"km")-1),-1," "),best.) ;
run ;

Re: How to extract numeric values after indexing (with the indexed point being right of the values)

JamieTee — Tue, 26 May 2020 22:25:16 GMT

This is the perfect solution thank you! What does the 'best' function do here?

Re: How to extract numeric values after indexing (with the indexed point being right of the values)

biopharma — Tue, 26 May 2020 22:28:25 GMT

BEST is used as the informat for the INPUT function. It is also a format when used in the PUT function.

Re: How to extract numeric values after indexing (with the indexed point being right of the values)

novinosrin — Tue, 26 May 2020 23:23:13 GMT


data have;
 input Person $	Description	$30.;
 cards;
Bob	Bob walked 5 km at 10PM
Joe	Joe swan for 3.2 km yesterday
;
data want;
 set have;
 distance =scan(Description,findw(Description,'km',' ','ie')-1,' ');
run;

Re: How to extract numeric values after indexing (with the indexed point being right of the values)

novinosrin — Tue, 26 May 2020 23:47:53 GMT

data have;
 input Person $	Description	$30.;
 cards;
Bob	Bob walked 5 km at 10PM
Joe	Joe swan for 3.2 km yesterday
;
data want;
 set have;
 distance=prxchange('s/(.*?)(\d+?\.?\d*?)(\skm)(.*)/$2/', -1, Description);
run;

Re: How to extract numeric values after indexing (with the indexed point being right of the values)

Ksharp — Wed, 27 May 2020 11:59:47 GMT

data have;
 input Person $	Description	$30.;
 cards;
Bob	Bob walked 5 km at 10PM
Joe	Joe swan for 3.2 km yesterday
;
data want;
 set have;
 pid=prxparse('/[\d\. ]+(?=km)/i');
 if pid then do;
  call prxsubstr(pid,Description,p,l);
  want=substr(Description,p,l);
 end;
 drop pid p l;
run;

proc print;run;