DATA Step, Macro, Functions and more

Converting all character variables to numeric

Reply
Contributor
Posts: 67

Converting all character variables to numeric

Hello,


I have a huge data set with 134 variables. Even though everything is a number, they're all listed as character variables. I want to convert everything to numeric variables without having to write 134 input (var name, blah);  statements or 134 newvar=oldcharvar*1 statements. I am guessing the fastest way to do this is either using an array or a macro loop. But with an array, don't the variables have to have a similar variable name? like temp1 temp2 temp3 etc. Mine are all different.


for example, BIRTH_YEAR is a character variable, even though everything listed is a number (1956,1976, etc). and same with AGE.

Help? Thanks,
Gina

Respected Advisor
Posts: 3,156

Re: Converting all character variables to numeric

Your guts are telling the truth: array() is the way to go:

data have;

input (a x1 _3) ($);

cards;

13413 1434 1344

;

/*this sql part is unnecessary if you don't care about the new names being related to the old ones*/

proc sql noprint;

  select cats('new_',name) into :name separated by ' ' from dictionary.columns where libname='WORK' AND MEMNAME='HAVE'; QUIT;

data want;

set have;

array old $ a--_3;

array new &name;

do i=1 to dim(old);

   new(i)=input(old(i),best.);

end;

keep &name;

run;

Haikuo

Super User
Posts: 10,044

Re: Converting all character variables to numeric

Less code with:

data have;
input (a x1 _3) ($);
cards;
13413 1434 1344
;
run;
proc sql noprint;
  select cats('new_',name,'=',name,';') into :name separated by ' ' from dictionary.columns where libname='WORK' AND MEMNAME='HAVE'; QUIT;
data want(keep=new_:); 
 set have;
 &name
run;

Ksharp

Respected Advisor
Posts: 3,156

Re: Converting all character variables to numeric

While your code is slick, but it does not deliver what OP asked for: char to num conversion. Minor tweak is need:

proc sql noprint;

  select cats('new_',name,'=',name,'+0',';') into :name separated by ' ' from dictionary.columns where libname='WORK' AND MEMNAME='HAVE'; QUIT;

of course you can also cats in 'input' or some other ways.

Haikuo

Super User
Posts: 10,044

Re: Converting all character variables to numeric

Hi. Bian Hai Kuo ,

There are no need to add " +0 " . Defaultly ,sas will set a variable be a numeric type if you don't specify it .

So SAS will automatically convert it from character to numeric. You can try my code again.

Respected Advisor
Posts: 3,156

Re: Converting all character variables to numeric

KeShan,

I have tried your code on both 9.2 and 9.3. They both came back with unchanged character. What you have done essentially is to assign  char values to new variable names, those char values, if not converted, will remain char.

Try it for yourself.

Haikuo

Super User
Posts: 10,044

Re: Converting all character variables to numeric

Yes. you are right,I must miss something here. it will still be char type,

I should post my code before checking the result. Anyway, Thank you very much.

Ksharp

Super Contributor
Super Contributor
Posts: 444

Re: Converting all character variables to numeric

hey guys,

what s behind this: input (a x1 _3) ($),the declaration inside the brackets i mean?

PROC Star
Posts: 7,492

Re: Converting all character variables to numeric

That is just a shorthand way of saying "input all 3 variables (the one enclosed in the brackets) as $ (with the $ enclosed in the next set of brackets)

PROC Star
Posts: 7,492

Re: Converting all character variables to numeric

I'd just let proc sql do ALL of the heavy work.  That way you can end up with a file with no name changes.  e.g.:

data have;

  input (a x1 _3) ($);

  cards;

13413 1434 1344

;

proc sql noprint;

  select catt(name,"= c_",name),

         catt(name,"= input(c_",name,",best.);")

    into :rename separated by ' ',

         :convert separated by ' '

      from dictionary.columns

        where libname='WORK' AND

              memname='HAVE'

  ;

quit;

data want (drop=c_Smiley Happy;

  set have (rename=(&rename.));

  &convert.

run;

Originally posted wrong version of code (updated 15nov12 9:17am)

Super Contributor
Posts: 644

Re: Converting all character variables to numeric

Or just export the whole thing as a CSV file and then import it back again - anything that looks like a number will be imported as a number.  Annoying if you do not want this to happen, but in your case just what you want.

Comma is better than Best

Re the input() function - I recommend using the comma informat instead of best.  That will take care of any non numeric symbols in your data.

Richard in Oz

Regular Contributor
Posts: 184

Re: Converting all character variables to numeric

New Contributor
Posts: 3

Re: Converting all character variables to numeric

I am unable to access the webpage that you have entered - I got a message that I had to login.  Then, even after I logged in w/ email & password, I was still blocked.

Regular Contributor
Posts: 184

Re: Converting all character variables to numeric

Strange. Are you at work? Does your IT department block certain sites?

Occasional Contributor
Posts: 8

Re: Converting all character variables to numeric

How about:

data chars;

a='1';

b='2';

c='3';

output;

a='11';

b='21';

c='31';

output;

run;

data _null_;

length varnames $1000;

set chars (obs=1);

array _chars_

  • _character_;
  •      do i = 1 to dim(_chars_);  

              if vname(_chars_) ne 'varnames' then varnames=left(trim(varnames))!!' '!!vname(_chars_);

         end;

         call symput('varnames',varnames);

    run;

    %put &varnames;

    %macro char_num();

    data nums(drop=c_Smiley Happy;

    set chars       (rename=(     

    %do i=1 %to %sysfunc(countw(&varnames));        

         %scan(&varnames,&i)=c_%scan(&varnames,&i)     

    %end;     

    ));  

    %do i=1 %to %sysfunc(countw(&varnames));     

         %scan(&varnames,&i)=input(c_%scan(&varnames,&i),best.);  

    %end;

    run;

    %mend char_num;

    %char_num;

    Ask a Question
    Discussion stats
    • 15 replies
    • 4511 views
    • 2 likes
    • 10 in conversation