BookmarkSubscribeRSS Feed
vsl2000
Calcite | Level 5

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.

 

ThanksExAtt.png

4 REPLIES 4
Tom
Super User Tom
Super User

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
Opal | Level 21

Replace

 

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


with

 

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

PG
Reeza
Super User

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
Jade | Level 19

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1740 views
  • 2 likes
  • 5 in conversation