Converting all character variables to numeric

[ginak]

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

[Haikuo]

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

[Ksharp]

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

[Haikuo]

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

[Ksharp]

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.

[Haikuo]

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

[Ksharp]

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

[Tal]

hey guys,

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

[art297]

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)

[art297]

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

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

  &convert.

run;

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

[RichardinOz]

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

[Howles]

http://www.sascommunity.org/wiki/Convert_Character_Variables_to_Numeric

[NYSPhil]

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.

[Howles]

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

[peterz]

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

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;