Converting character to numeric

[akberali67]

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.

[Tom · Accepted solution]

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;

[art297]

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;

[akberali67]

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.

[Tom]

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

[MikeZdeb]

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:);

[Linlin]

Hi Art,

Happy New Year to you!

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

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

[MikeZdeb]

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

[Linlin]

Hi Mike,

Happy New Year! Thank you for your explanations!

[akberali67]

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.

[Ksharp]

Variables list is very convenient.

sum( of  score1-score4 )

Ksharp