topic Re: Do Loop to change variables from character to numeric in SAS Programming

Do Loop to change variables from character to numeric

kmardinian — Thu, 11 Oct 2018 16:36:25 GMT

Hi I would like to convert 32 different variables q1-q32 from character to numeric using a do-loop but I cannot seem to find the right code to do that. Any help is much appreciated. Below is the code I have so far, but is not working correctly.

data t1_2;
set t1;
array i {32} q1-q32;
do i=1 to 32;
i_num=input(i,best5.);
drop i;
rename i_num=i;
end;
run;

Re: Do Loop to change variables from character to numeric

ballardw — Thu, 11 Oct 2018 16:56:44 GMT

Once a variable is created you cannot change its type from character to numeric or the other way. You need to create new variables.

Best practice is to insure that data is read from external sources as needed.

This adds numeric variables to your data set named NUMQ1 to NUMQ32.

data t1_2;
   set t1;
   array q{32} q1-q32;
   array numQ{32};
   do i = 1 to dim(q);
      numq[i] = input(q[i],best5.);
   end;
run;

You could drop the Q variables if desired. Or go through some renaming if you really want the variables to be named Q1 to Q32, though I would have ensured my data was read correctly.

Re: Do Loop to change variables from character to numeric

kmardinian — Thu, 11 Oct 2018 17:02:40 GMT

Thank you! How would I ensure that my data is read in correctly, if SAS imports it as a character variable?

Re: Do Loop to change variables from character to numeric

ballardw — Thu, 11 Oct 2018 20:31:05 GMT

@kmardinian wrote:

Thank you! How would I ensure that my data is read in correctly, if SAS imports it as a character variable?

How did you read the data to begin with?

We might need to see some example from the source file.

Hint: if the data originated as a spreadsheet (Excel) then you may be better of using Excel to save the file as CSV. You can then write a data step to read things as needed. Proc Import on a CSV file will generate data step code to read the file. Add the GUESSINGROWS option to be large (close to the number of rows) to ensure that most of the data is examined before assigning types.

You could then copy the code from the log and paste into the editor and modify the code as needed.

Depending on the data source and how read things that can affect importing data are 2 or more rows of text headers (with a CSV or other delimited file, you can tell Proc Import to start on the 2nd , 3rd or what ever row to examine data with the datarow= statement) , or many missing values for a variable in the first few records. Also the lengths of your character variables may be "short" if the first few records do not happen to hold the longest values.