topic Re: Converting all character variables to numeric in SAS Programming

Converting all character variables to numeric

ginak — Thu, 15 Nov 2012 02:41:35 GMT

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

Re: Converting all character variables to numeric

Haikuo — Thu, 15 Nov 2012 03:14:41 GMT

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

Re: Converting all character variables to numeric

art297 — Thu, 15 Nov 2012 04:44:26 GMT

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)

Re: Converting all character variables to numeric

Ksharp — Thu, 15 Nov 2012 04:45:35 GMT

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

Re: Converting all character variables to numeric

RichardinOz — Thu, 15 Nov 2012 04:46:15 GMT

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

Re: Converting all character variables to numeric

Haikuo — Thu, 15 Nov 2012 14:00:45 GMT

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

Re: Converting all character variables to numeric

Howles — Thu, 15 Nov 2012 14:13:27 GMT

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

Re: Converting all character variables to numeric

NYSPhil — Thu, 15 Nov 2012 14:28:41 GMT

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.

Re: Converting all character variables to numeric

Howles — Thu, 15 Nov 2012 14:39:05 GMT

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

Re: Converting all character variables to numeric

peterz — Thu, 15 Nov 2012 16:12:19 GMT

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;

Re: Converting all character variables to numeric

ArtC — Thu, 15 Nov 2012 16:24:03 GMT

Looks like you have plenty of choices to do the conversion. To add one more; there is a macro in my macro book that demonstrates how to automate this process. in the sample code search for the macro %ALLCHAR in Section A1.6.4 download the sample code from https://support.sas.com/pubscat/bookdetails.jsp?catid=1&pc=59224

Re: Converting all character variables to numeric

Ksharp — Fri, 16 Nov 2012 03:08:47 GMT

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.

Re: Converting all character variables to numeric

Haikuo — Fri, 16 Nov 2012 04:19:20 GMT

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

Re: Converting all character variables to numeric

Ksharp — Fri, 16 Nov 2012 04:39:05 GMT

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

Re: Converting all character variables to numeric

Tal — Wed, 21 Nov 2012 02:58:39 GMT

hey guys,

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

Re: Converting all character variables to numeric

art297 — Wed, 21 Nov 2012 03:25:45 GMT

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)