topic Re: Convert Character to Numeric in ODS and Base Reporting

Convert Character to Numeric

djbateman — Fri, 05 Apr 2013 19:30:50 GMT

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?

Re: Convert Character to Numeric

art297 — Fri, 05 Apr 2013 19:53:20 GMT

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;

Re: Convert Character to Numeric

Astounding — Fri, 05 Apr 2013 19:55:06 GMT

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.