BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JamieTee
Fluorite | Level 6

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

PersonDescription
BobBob walked 5 km at 10PM
JoeJoe swan for 3.2 km yesterday

I ideally want to create a table that looks like this

 

PersonDescriptionDistance
BobBob walked 5 km at 10PM5
JoeJoe swan for 3.2 km yesterday3.2
1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller

View solution in original post

9 REPLIES 9
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
JamieTee
Fluorite | Level 6

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., 

PersonDescription
BobBob walked 5 km at 10PM
JoeJoe swan for 3.2 km yesterday
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
biopharma
Quartz | Level 8

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 ;
JamieTee
Fluorite | Level 6

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

biopharma
Quartz | Level 8
BEST is used as the informat for the INPUT function. It is also a format when used in the PUT function.
novinosrin
Tourmaline | Level 20

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;
novinosrin
Tourmaline | Level 20
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;
Ksharp
Super User
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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1327 views
  • 3 likes
  • 5 in conversation