turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- ODS and Base Reporting
- /
- Convert Character to Numeric

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-05-2013 03:30 PM

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?

Accepted Solutions

Solution

04-05-2013
03:55 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to djbateman

04-05-2013 03:55 PM

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.

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to djbateman

04-05-2013 03:53 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to djbateman

04-05-2013 03:55 PM

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.