BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
akberali67
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

10 REPLIES 10
Tom
Super User Tom
Super User

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
Opal | Level 21

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
Calcite | Level 5

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
Super User Tom
Super User

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
Rhodochrosite | Level 12

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
Lapis Lazuli | Level 10

Hi Art,

Happy New Year to you!

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

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


MikeZdeb
Rhodochrosite | Level 12

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
Lapis Lazuli | Level 10

Hi Mike,

Happy New Year! Thank you for your explanations!

akberali67
Calcite | Level 5

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

Ksharp
Super User

Variables list is very convenient.

sum( of  score1-score4 )

Ksharp

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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