How to separate out character and numeric from one column

Reply
Occasional Contributor
Posts: 11

How to separate out character and numeric from one column

 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.

Super User
Posts: 6,972

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 11

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

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

Super User
Super User
Posts: 7,430

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

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.

Super User
Super User
Posts: 7,430

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

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.);
Super User
Posts: 5,099

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

 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.

Ask a Question
Discussion stats
  • 5 replies
  • 153 views
  • 1 like
  • 4 in conversation