topic Re: How to separate out character and numeric from one column in SAS Programming

How to separate out character and numeric from one column

sas_pkc1 — Wed, 26 Apr 2017 10:19:25 GMT

Dear all,

I have one column in that character as well as numeric values are there i need to seperate out those value on base of character and numeric. there is no specific value that we need to seperate out like

ACNB

1980

asjl

8909

8012

dsufjs

Like that i have one column want to seperate the character and numeric.

Please help

Thanks & Regards,

Prashant.

Re: How to separate out character and numeric from one column

Kurt_Bremser — Wed, 26 Apr 2017 10:33:55 GMT

if notdigit(strip(acnb)) > 0
then acnb_char = acnb;
else acnb_num = input(acnb,best.);

notdigit() scans for characters outside 0-9, strip() removes any blanks before notdigit() scans.

Re: How to separate out character and numeric from one column

RW9 — Wed, 26 Apr 2017 10:39:34 GMT

Would it not be safer t keep both, for instance in CDISC models you have a character result - which has everything in as its recorded - and also a numeric result where possible. That way you have best of both worlds, a variable to report as it was, and a numeric to do calculations on? As for how to do that:

new_col=input(old_col,best.);

Re: How to separate out character and numeric from one column

Astounding — Wed, 26 Apr 2017 11:09:58 GMT

The rules for "character" or "numeric" are more complex since a legitimate number can contain a decimal point or a negative sign, but cannot contain embedded blanks. (We'll leave commas out of the mix here.) A robust way would be:

if acnb > ' ' and input(acnb, ??16.) = . then acnb_character=acnb;

else acnb_numeric = input(acnb, 16.);

You are allowed to use a shorter informat, one that matches the length of your variable.

Re: How to separate out character and numeric from one column

sas_pkc1 — Wed, 26 Apr 2017 12:25:39 GMT

Thanks for idea.

but the column ACNB is the character column and number and character are there.

so in strip if my value is 0001 then in final table its coming as 1.

that is the problem.

Please suggest if any

Re: How to separate out character and numeric from one column

RW9 — Wed, 26 Apr 2017 12:45:43 GMT

That output is correct, if you take the string 0001 and set this as a numeric value, then number is 1. there is no such thing as a numeric with preceding zeros. You can apply a format - zX. where X is the number of elements - but what this is doing is displaying the underlying number 1 as 0001, doesen't change the value, and you would need to apply that format to the whole column.

Re: How to separate out character and numeric from one column

AlpanaHansaria — Mon, 11 Jan 2021 21:43:08 GMT

Kurt, your code works fine for digits 0-9. But how about decimals? How would you get the decimal numbers from that same mixed variables also converted to numbers?