topic Re: How to change the column type from $12. to a numeric type. in New SAS User

How to change the column type from $12. to a numeric type.

heyyou1 — Fri, 19 Nov 2021 14:48:14 GMT

I am reading in data from a excel sheet I cannot edit. SAS reads in the columns as CHAR and in order to do some calculations with them, I need to turn them into numeric. I tried a SQL modify statement,

/* Changing the columns from characters to numeric */
proc sql;
   alter table race_sjc_mph_data
      modify first_dose_administered format=comma15.;
quit;

but this results in this error.

      
 
 151        
 152        /* Changing the columns from characters to numeric */
 153        proc sql;
 154           alter table race_sjc_mph_data
 155              modify first_dose_administered format=comma15.;
 ERROR: Character column first_dose_administered requires a character format specification.
 NOTE: Table WORK.RACE_SJC_MPH_DATA has been modified, with 17 columns.
 156        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds

This is the current output properties of that table

From what I see in the data, there is not stray characters that would cause an error in parsing from character to numeric.

Thanks you

Re: How to change the column type from $12. to a numeric type.

PaigeMiller — Fri, 19 Nov 2021 14:56:07 GMT

You cannot change a character variable to a numeric variable. Not a feature of SAS.

What you can do is create a new variable that is the numeric equivalent of the character variable, for example in a data step:

first_dose_administered_num = input(first_dose_administered,8.);

This has nothing to do with formats, which are irrelevant to this type of conversion.

Re: How to change the column type from $12. to a numeric type.

Tom — Fri, 19 Nov 2021 15:41:38 GMT

You cannot change the TYPE of an existing variable (changing the format attached to a variable just changes how you want the variable displayed). So make a new numeric variable.

Does the value in the character variable include commas? Is that why you wanted to display the number with the comma format? Try using the COMMA informat when converting the values. That will ignore the commas and dollar signs in the character string when converting the string into a number.

data want;
  set race_sjc_mph_data;
  dose1 = input(first_dose_administered,comma32.);
run;

If you would prefer the use the existing name for the numeric variable then use RENAME statement to change the names.

data want;
  set race_sjc_mph_data;
  dose1 = input(first_dose_administered,comma32.);
  rename first_dose_administered=dose1_char dose1=first_dose_administered;
run;