Extracting Certain Information from a String

[Go210]

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.

Data	Result	Comments
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

[ChrisNZ · Accepted solution]

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]

That's awesome Chris. I had a number of string functions that were strarting to do my head in. Thanks for your help.

[Go210]

I haven't seen prxmatch function before. Cheers

[ChrisNZ]

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