The SAS Output Delivery System and reporting techniques

Convert Character to Numeric

Accepted Solution Solved
Reply
Regular Contributor
Posts: 223
Accepted Solution

Convert Character to Numeric

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

RESULTNEW_RSLT
172172
1.831.83
NEGATIVE.
< 22
+++.
> 2525

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?


Accepted Solutions
Solution
‎04-05-2013 03:55 PM
Super User
Posts: 5,361

Re: Convert Character to Numeric

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.

View solution in original post


All Replies
PROC Star
Posts: 7,432

Re: Convert Character to Numeric

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;

Solution
‎04-05-2013 03:55 PM
Super User
Posts: 5,361

Re: Convert Character to Numeric

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 350 views
  • 3 likes
  • 3 in conversation