topic Re: Converting character to numeric in SAS Procedures

Converting character to numeric

akberali67 — Tue, 03 Jan 2012 16:19:30 GMT

Hi,

I am trying to convert character fields to numeric. Is there a better way than a regular Input statement to convert multiple variables to numeric from character. I am working with about 300 fields which need to be converted to the same BEST32. format from character. Same data formats too. Please help. This is what I was doing so far:

DATA WORK.SAMPLE;

SET WORK.DETAILS;

FORMAT PRICE1 RENT1 EXPENSE1 BEST32.;

PRICE1 = INPUT(PRICE, BEST32.);

RENT1 = INPUT(RENT, BEST32.);

EXPENSE1 = INPUT(EXPENSE, BEST32.);

RUN;

Thanks & Regards,

Akber.

Converting character to numeric

Tom — Tue, 03 Jan 2012 16:35:35 GMT

Try using arrays.

data want ;

set have ;

array _char price rent expense ;

array _num 8 price1 rent1 expense1;

do i=1 to dim(_char);

_num(i) = input(_char(i),best32.);

end;

run;

Converting character to numeric

art297 — Tue, 03 Jan 2012 17:00:33 GMT

Since you've got over 300 such variables, I'd take Tom's suggestion one step further:

data have;

input (name price rent expense) ($) amount;

cards;

John 1 1 1 14

Joe 2 2 2 15

Mary 3 3 3 16

;

proc sql noprint;

select "num_"||strip(name)

into :vnames

separated by " "

from dictionary.columns

where libname="WORK" and

memname="HAVE" and

type="char"

;

quit;

data want (drop=i);

set have ;

array _char _character_ ;

array _num 8 &vnames.;

do i=1 to dim(_char);

if not missing(input(_char(i),?? best32.)) then

_num(i) = input(_char(i),best32.);

end;

run;

Converting character to numeric

akberali67 — Tue, 03 Jan 2012 18:14:23 GMT

Thanks Tom, that worked great! Just what I wanted to do, thanks again for the time.

Thanks Arthur, the code did work but some variables of the 300 did not need conversion, so need to do some manual work there but a really good suggestion when I need to convert all the character fields. I learn a lot from your answers and truly appreciate the fact that you take time for us. Don't know how I would have managed without your help.

Converting character to numeric

akberali67 — Tue, 03 Jan 2012 21:38:39 GMT

Hi,

I had one more question. How can we sum up variables. I want to add up about 50 variables to create a new one.

I am using a macro statement that has all these variables seperated by a space. Any function which can help me use this statement.

Data:

Name Score1 Score2 Score3

A 10 8 10

B 7 6 5

C 4 10 8

How can I add Score1, Score2 and Score3 to form Total as the sum of these fields.

Thanks,

Akber.

Converting character to numeric

Tom — Tue, 03 Jan 2012 21:42:30 GMT

Use the SUM function.

total=sum(of score1 score2 score3)

If you have the list of variables in a macro variable already then you can use that.

%let vlist=score1 score2 score3 ;

total = sum( of &vlist );

Converting character to numeric

Linlin — Tue, 03 Jan 2012 23:35:36 GMT

Hi Art,

Happy New Year to you!

Can you explain the two "??" in your code?

if not missing(input(_char(i),?? best32.)) then

Converting character to numeric

Ksharp — Wed, 04 Jan 2012 05:51:04 GMT

Variables list is very convenient.

sum( of score1-score4 )

Ksharp

Re: Converting character to numeric

MikeZdeb — Thu, 05 Jan 2012 18:25:11 GMT

hi ...(hey, I'm not Art)

the ?? suppresses error messages in case the character string cannot be converted to a numeric value

also ...

#1 not sure why the NOT MISSING is needed (same for BEST ... I think that's been discussed here via a postingt by Howard Schreier) since that asks a question each time the statement is used versus just converting a missing character to a numeric when encountered

#2 depending on the content of the character variable, you might also consisder using a COMMA informat

try this and look at the LOG ...

data x;

input x : $5. @@;

datalines;

. 1000 999 200 abc 1e6 (200) 1,234 $88 1-2-3

;

run;

data x;

set x;

x1 = input(x,32.);

x2 = input(x, ?? 32.);

x3 = input(x, ?? comma.);

run;

x x1 x2 x3

. . .

1000 1000 1000 1000

999 999 999 999

200 200 200 200

abc . . .

1e6 1000000 1000000 1000000

(200) . . -200

1,234 . . 1234

$88 . . 88

1-2-3 . . 123

Re: Converting character to numeric

Linlin — Thu, 05 Jan 2012 18:32:08 GMT

Hi Mike,

Happy New Year! Thank you for your explanations!

Re: Converting character to numeric

MikeZdeb — Thu, 05 Jan 2012 18:33:37 GMT

hi ... one other idea

if you know that the only variables that start with the string SCORE are in the list SCORE1, SCORE2, etc., you can also use ...

total = sum (of score:);