Help using Base SAS procedures

Converting character to numeric

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

Converting character to numeric

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.


Accepted Solutions
Solution
‎01-03-2012 11:35 AM
Super User
Super User
Posts: 6,502

Converting character to numeric

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;

View solution in original post


All Replies
Solution
‎01-03-2012 11:35 AM
Super User
Super User
Posts: 6,502

Converting character to numeric

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;

PROC Star
Posts: 7,364

Converting character to numeric

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;

Contributor
Posts: 37

Converting character to numeric

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.

Super User
Super User
Posts: 6,502

Converting character to numeric

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

Valued Guide
Posts: 765

Re: Converting character to numeric

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 scoreSmiley Happy;

Super Contributor
Posts: 1,636

Converting character to numeric

Hi Art,

Happy New Year to you!

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

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


Valued Guide
Posts: 765

Re: Converting character to numeric

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

Super Contributor
Posts: 1,636

Re: Converting character to numeric

Hi Mike,

Happy New Year! Thank you for your explanations!

Contributor
Posts: 37

Converting character to numeric

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. Smiley Happy

Super User
Posts: 9,688

Converting character to numeric

Variables list is very convenient.

sum( of  score1-score4 )

Ksharp

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 13235 views
  • 9 likes
  • 6 in conversation