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

 

Hi All,

 

I am trying to easily extract specific distance information from a string.

 

I am interested in creating a new variable with the extraction of distance in meters (m).

 

Here is an example of my data and desired result. I think there will be quite a bit of string work to get the answer.

 

Any help would be greatly appreciated.

 

DataResultComments
RED 1500M STBL_2035 ZN042 MOND TER1500 
V 2km STL_28 ZN011 RT2000KM' or 'km' is 1000 time m (meters)
EEP 360M _47 Z012 BRAN360 
1400m 48047b TN0099 BR1400 
RET 117M CC S23_ITHACA S 1117 
ECOE 1711T DUP DE0no m / M or km / KM
RP HB/OB8 S4 P0no m / M or km / KM
TEP 200m 11P & 700m 5200Return the first m only
P 1110m HUP CN27151110 
RF FD 79 LE U5 TO L SU80no m / M or km / KM
TTP 39V Mea Ceder ge 8_1371m1371 
1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

Like this?

 

data HAVE;
  input STR $80.;
  if prxmatch('/\d+k?m/i',STR) then
    STR1=prxchange('s/.*?(\d+k?m).*/$1/i',-1,STR);
  DIST=input(compress(STR1,,'dk'),8.)*ifn(find(STR1,'k','i'),1000,1);
cards;
RED 1500M STBL_2035 ZN042 MOND TER	1500	 
V 2km STL_28 ZN011 RT	2000	KM' or 'km' is 1000 time m (meters)
EEP 360M _47 Z012 BRAN	360	 
1400m 48047b TN0099 BR	1400	 
RET 117M CC S23_ITHACA S 1	117	 
ECOE 1711T DUP DE	0	no m / M or km / KM
RP HB/OB8 S4 P	0	no m / M or km / KM
TEP 200m 11P & 700m 5	200	Return the first m only
P 1110m HUP CN2715	1110	 
RF FD 79 LE U5 TO L SU8	0	no m / M or km / KM
TTP 39V Mea Ceder ge 8_1371m	1371	 
run;

 

STR STR1 DIST
RED 1500M STBL_2035 ZN042 MOND TER 1500 1500M 1500
V 2km STL_28 ZN011 RT 2000 KM' or 'km' is 1000 time m (meters) 2km 2000
EEP 360M _47 Z012 BRAN 360 360M 360
1400m 48047b TN0099 BR 1400 1400m 1400
RET 117M CC S23_ITHACA S 1 117 117M 117
ECOE 1711T DUP DE 0 no m / M or km / KM   .
RP HB/OB8 S4 P 0 no m / M or km / KM   .
TEP 200m 11P & 700m 5 200 Return the first m only 200m 200
P 1110m HUP CN2715 1110 1110m 1110
RF FD 79 LE U5 TO L SU8 0 no m / M or km / KM   .
TTP 39V Mea Ceder ge 8_1371m 1371 1371m 1371

View solution in original post

4 REPLIES 4
ChrisNZ
Tourmaline | Level 20

Like this?

 

data HAVE;
  input STR $80.;
  if prxmatch('/\d+k?m/i',STR) then
    STR1=prxchange('s/.*?(\d+k?m).*/$1/i',-1,STR);
  DIST=input(compress(STR1,,'dk'),8.)*ifn(find(STR1,'k','i'),1000,1);
cards;
RED 1500M STBL_2035 ZN042 MOND TER	1500	 
V 2km STL_28 ZN011 RT	2000	KM' or 'km' is 1000 time m (meters)
EEP 360M _47 Z012 BRAN	360	 
1400m 48047b TN0099 BR	1400	 
RET 117M CC S23_ITHACA S 1	117	 
ECOE 1711T DUP DE	0	no m / M or km / KM
RP HB/OB8 S4 P	0	no m / M or km / KM
TEP 200m 11P & 700m 5	200	Return the first m only
P 1110m HUP CN2715	1110	 
RF FD 79 LE U5 TO L SU8	0	no m / M or km / KM
TTP 39V Mea Ceder ge 8_1371m	1371	 
run;

 

STR STR1 DIST
RED 1500M STBL_2035 ZN042 MOND TER 1500 1500M 1500
V 2km STL_28 ZN011 RT 2000 KM' or 'km' is 1000 time m (meters) 2km 2000
EEP 360M _47 Z012 BRAN 360 360M 360
1400m 48047b TN0099 BR 1400 1400m 1400
RET 117M CC S23_ITHACA S 1 117 117M 117
ECOE 1711T DUP DE 0 no m / M or km / KM   .
RP HB/OB8 S4 P 0 no m / M or km / KM   .
TEP 200m 11P & 700m 5 200 Return the first m only 200m 200
P 1110m HUP CN2715 1110 1110m 1110
RF FD 79 LE U5 TO L SU8 0 no m / M or km / KM   .
TTP 39V Mea Ceder ge 8_1371m 1371 1371m 1371
Go210
Obsidian | Level 7
That's awesome Chris. I had a number of string functions that were strarting to do my head in. Thanks for your help.
Go210
Obsidian | Level 7
I haven't seen prxmatch function before. Cheers
ChrisNZ
Tourmaline | Level 20

Regular expressions are off-putting at first, but they are a must if you manipulate strings.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 4 replies
  • 740 views
  • 3 likes
  • 2 in conversation