I have a character field that has values as found in RESULT. I want to convert numbers to a numeric variable (if there is an inequality, then I just truncate the inequality sign).
RESULT | NEW_RSLT |
---|---|
172 | 172 |
1.83 | 1.83 |
NEGATIVE | . |
< 2 | 2 |
+++ | . |
> 25 | 25 |
This is a VERY small sample of values. There are all kinds of alpha/numeric/punctuation results (some are even mixed). Here is what I have attempted:
if anyalpha(result)=0 & anypunct(result)=0 then new_rslt=input(compress(result,' <>',best.);
This does not work because '<' and '>' are excluded in the ANYPUNT() function. Second, a period is also excluded, so it never converts 1.83 to a number. Any suggestions on how to work around this?
Well, a starting point would be this:
new_rslt = input(result, ?? 12.);
It will find all the values that are numeric already, including scientific notation. To get the rest, you're probably looking at the COMPRESS function first. Here's an initial attempt:
if new_rslt = . then new_rslt = (input(compress(result,,'kd'), ??12.);
By omitting the second parameter and specifying the third, COMPRESS is K=Keeping the D=Digits. That's obviously not good enough since you will want decimal points, possibly a minus sign as well. There may be ways to add to the third parameter of the COMPRESS function to get exactly what you want, or you may be able to specify your own list:
if new_rslt = . then new_rslt = input( compress(result, '-.+', 'kd'), ??12.);
I suspect this is where you will end up. It keeps digits, as well as a decimal point, plus sign, or minus sign. You could start here, and forget about "if new_rslt = ." since the only reason to go through the first test is to capture scientific notation.
Note that this will reject a range. 2 - 5 will not come out as 25, but will come out as missing.
Good luck.
How about?:
data have;
input RESULT & $20.;
cards;
172
1.83
NEGATIVE
< 2
+++
> 25
;
data want;
set have;
NEW_RSLT=input(compress(result," <>"), ?? 12.);
run;
Well, a starting point would be this:
new_rslt = input(result, ?? 12.);
It will find all the values that are numeric already, including scientific notation. To get the rest, you're probably looking at the COMPRESS function first. Here's an initial attempt:
if new_rslt = . then new_rslt = (input(compress(result,,'kd'), ??12.);
By omitting the second parameter and specifying the third, COMPRESS is K=Keeping the D=Digits. That's obviously not good enough since you will want decimal points, possibly a minus sign as well. There may be ways to add to the third parameter of the COMPRESS function to get exactly what you want, or you may be able to specify your own list:
if new_rslt = . then new_rslt = input( compress(result, '-.+', 'kd'), ??12.);
I suspect this is where you will end up. It keeps digits, as well as a decimal point, plus sign, or minus sign. You could start here, and forget about "if new_rslt = ." since the only reason to go through the first test is to capture scientific notation.
Note that this will reject a range. 2 - 5 will not come out as 25, but will come out as missing.
Good luck.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.