DATA Step, Macro, Functions and more

Convert multiple character variables to numeric

Reply
Frequent Contributor
Posts: 98

Convert multiple character variables to numeric

I have a bunch of variables imported from an Excel file that are erroneously coded as character rather than numeric. I am trying to convert them all at once to numeric. I don't understand why the following code fragments don't work; everything I can find searching these communities and online indicates that this should work. But the variables are still character!

 

 

/* Using DO OVER */
DATA month3;
	set month3;
	array cha $ 8 SRA13--SRA21;
	array num SRA13--SRA21;
	do over cha;
		num = input(cha,best8.);
	end;
run;

/* Using indexed arrays */
DATA month3;
	set month3;
	array cha{*} $ 8 SRA13--SRA21;
	array num{*} SRA13--SRA21;
	do i = 1 to dim(cha);
		num(i) = input(cha(i),best8.);
	end;
run;

I have even tried converting the variables one by one instead of using an array, and I've tried multiple methods of conversion (multiplying by 1, input, etc.). No matter what I do, the following message appears in the log:

 

 

 

NOTE: Character values have been converted to numeric values at the places given by:
      (Line):(Column).
      754:13
NOTE: Numeric values have been converted to character values at the places given by:
      (Line):(Column).
      754:18

For some reason, it's converting them from character to numeric, and then re-converting them back to character variables? Does anybody have any idea how to fix this, or what is causing this behavior? I apologize I can't provide the data for this.

 

Super User
Posts: 5,498

Re: Convert multiple character variables to numeric

[ Edited ]
Posted in reply to RyanSimmons

While you have defined the array NUM,  it contains all character variables.  Once SRA13, for example, is defined as character, it remains character forever.  This statement would not change it:

 

SRA13 = input(SRA13, best8.);

 

Yes, the INPUT function returns a number.  But that number gets stored in a character variable forcing SAS to make a numeric to character conversion.

 

You will need a new set of variable names in the NUM array.  If you want to keep the old names, you would have to use some combination of drop and rename, equivalent to:

 

drop SRA13;

rename NewSRA13 = SRA13;

Super User
Posts: 11,343

Re: Convert multiple character variables to numeric

Posted in reply to RyanSimmons

And since the data orginated in Excel if you are going to deal with multiple files I suggest researching in this community for solutions tha tlet you assign/control the data type instead of letting Excel tell SAS what type it thinks things are.

Also the next problem you are likely to have it you need to process multiple similar files is that the lengths of your character variables are going to vary from import to import which may cause either errors or data truncation depending on what you are doing.

Respected Advisor
Posts: 3,799

Re: Convert multiple character variables to numeric

Posted in reply to RyanSimmons

You might consider a technique similar to this, where character variables are transpose to observations and then read into a numeric variable and transposed back.  This has the advantage of keeping the original names and labels.  This technique will work well enough with smallish sample sizes.  

 

data example;
   array SRA $8 SRA13-SRA21;
   do id = 1 to 5;
      do i = 1 to dim(sra);
         sra[i] = put(ranuni(1),best8.);
         end;
      output;
      end;
   attrib sra: label='This is the label';
   drop i;
   run;
proc print;
   run;
proc transpose data=example out=tall;
   by id;
   var sra:;
   run;
data tallV / view=tallv;
   set tall;
   num = input(col1,f8.);
   run;
proc transpose data=tallv out=want(drop=_name_);
   by id;
   run;
proc contents varnum;
   ods select position;
   run;
proc print;
   run;
      

Capture.PNG

 

 

 

Ask a Question
Discussion stats
  • 3 replies
  • 1752 views
  • 3 likes
  • 4 in conversation