02-19-2018 09:30 PM
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.
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.
02-19-2018 11:09 PM - edited 02-19-2018 11:10 PM
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;
02-20-2018 11:56 AM
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.
02-20-2018 12:09 PM
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;