Cannot change column attribute type

[vsl2000]

I am analysing a survey. Certain columns (questions) have only numeric values, and some are mixed. When I import my excel (xlsx) file, it makes most columns character, not numeric, which does not allow me to format them.

SAS 9.4

CODE
data survey;
set mylib.thesisdata3a;
rename _1=Q1;
run;
proc format;
value typea
other='No Answer'
9='Never'
10='Once a month or less'
11='2-3 times a month'
12='Weekly'
13='2-3 times a week'
14='Daily';
run;
proc freq data=survey;
tables Q1;
	format Q1 typea.;
run;

LOG
314  data survey;
315  set mylib.thesisdata3a;
316  rename _1=Q1;
317  run;

NOTE: There were 253 observations read from the data set MYLIB.THESISDATA3A.
NOTE: The data set WORK.SURVEY has 253 observations and 188 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.04 seconds


318  proc format;
319  value typea
320  other='No Answer'
321  9='Never'
322  10='Once a month or less'
323  11='2-3 times a month'
324  12='Weekly'
325  13='2-3 times a week'
326  14='Daily';
NOTE: Format TYPEA is already on the library WORK.FORMATS.
NOTE: Format TYPEA has been output.
327  run;

NOTE: PROCEDURE FORMAT used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


328  proc freq data=survey;
329  tables Q1;
330      format Q1 typea.;
ERROR: You are trying to use the numeric format TYPEA with the character variable Q1 in data set
       WORK.SURVEY.
331  run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE FREQ used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

Any thoughts? I don't know how to change the column attribute to numeric. The excel file has number as the cell type.

If I run proc means I get

ERROR: Variable Q1 in list does not match type prescribed for this list.

Thanks

[Tom]

Because it is designed to allow any type of value in any cell, Excel is not a good tool for storing your data.

You might have better luck if you export the Excel file into a CSV file.  Then you can write your own data step to read the data and have complete control over how the variables are defined.

That said you can fix the Excel sheet if you are careful.  Make sure not to mix character and numeric values into the same column in the Excel sheet.  Also use valid variable names for your column header and not numbers like the number 10 that appears to have been used as the variable name in your example.

You can convert character variables to numbers and the reverse in SAS, but you will need to make NEW variables.

So if the variable _10 is defined as length $3 and you want instead make a new variable called X10 that is numeric it is a simple as:

data want ;
  set have;
  x10 =input(_10,32.);
run;

[PGStats]

Replace

data survey;
set mylib.thesisdata3a;
rename _1=Q1;
run;

with

data survey;
set mylib.thesisdata3a;
Q1 = input(_1, best.);
drop _1;
run;

[Reeza]

You're correct, you cannot change a column attribute type in place in SAS. The way this is accomplished is to create the new variable with the desired properties and then drop the old variable. You can rename the new variable to have the same name later on. 

Or rename the incoming variable and then the new variable can have the old name. 

But, you cannot change a column type in place. 

[andreas_lds]

Maybe i am missing something, but you could change the numeric format to a char format.

proc format;
value $typea
other='No Answer'
'9'='Never'
'10'='Once a month or less'
'11'='2-3 times a month'
'12'='Weekly'
'13'='2-3 times a week'
'14'='Daily';
run;