Error: Variable has been defined as both character and numeric

[adzoyi]

Hi!

  I am running a written SAS codes for a report and I received errors in my log. Below is the error message and the procedure steps. Any help or suggestions will be appreciated. Thank you.

355 data combined;
356 set std_prevyr2 (Drop=patient_id morb_id address_id mmwr_week) report_dz;
ERROR: Variable morb_age has been defined as both character and numeric.
ERROR: Variable SURVWEB_CONDITION_ID has been defined as both character and numeric.
ERROR: Variable NETSS_YEAR has been defined as both character and numeric.
ERROR: Variable COUNTED_JURISDICTION_LOC_ZIP has been defined as both character and numeric.
357 format gen_rept_dt mmddyy10.;
358 gen_rept_dt=mdy(rept_month, 1,input(cats("20",mmwr_year),4.));
359 run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.COMBINED may be incomplete. When this step was stopped there were 0
observations and 131 variables.
NOTE: DATA statement used (Total process time):
real time 0.05 seconds
cpu time 0.00 seconds

*Importing data for previous year;
PROC IMPORT OUT= WORK.std_prevyr
DATAFILE= "R:\morb&prevyr..xls"
DBMS=EXCEL REPLACE;
RANGE="MORB$";
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

*Reformatting month names for previous year's data;
data std_prevyr2 (drop=mmwr_year);
set std_prevyr;
if mmwr_month='JANUARY' then rept_month=1;
else if mmwr_month='FEBRUARY' then rept_month=2;
else if mmwr_month='MARCH' then rept_month=3;
else if mmwr_month='APRIL' then rept_month=4;
else if mmwr_month='MAY' then rept_month=5;
else if mmwr_month='JUNE' then rept_month=6;
else if mmwr_month='JULY' then rept_month=7;
else if mmwr_month='AUGUST' then rept_month=8;
else if mmwr_month='SEPTEMBER' then rept_month=9;
else if mmwr_month='OCTOBER' then rept_month=10;
else if mmwr_month='NOVEMBER' then rept_month=11;
else if mmwr_month='DECEMBER' then rept_month=12;
else rept_month=.;

length mmwr_year2 $4;
mmwr_year2=substr(put(mmwr_year,4.),3,2);
run;

proc datasets lib=work nolist;
modify std_prevyr2;
rename mmwr_year2=mmwr_year;
quit;

data combined;
set std_prevyr2 (Drop=patient_id morb_id address_id mmwr_week) report_dz;
format gen_rept_dt mmddyy10.;
gen_rept_dt=mdy(rept_month, 1,input(cats("20",mmwr_year),4.));
run;

[Reeza]

How did you import your data? Did you verify that the variable types and formats were correct for the data?

ERROR: Variable morb_age has been defined as both character and numeric.
ERROR: Variable SURVWEB_CONDITION_ID has been defined as both character and numeric.
ERROR: Variable NETSS_YEAR has been defined as both character and numeric.
ERROR: Variable COUNTED_JURISDICTION_LOC_ZIP has been defined as both character and numeric.

You check your types for the above variables in each data set and then decide type should they be, ie age should likely be numeric so why is it character in one data set. You can then convert your variables using PUT/INPUT() as needed. Note that the new variable needs a new name since you cannot change names in place. One option is to rename your variable before you covert it, then save the new variable. In an ideal scenario you'll read the data in correctly instead, and this isn't necessary.

*convert numeric to character;
survweb_condition_id_char = put(survweb_condition_id, 8.);

*convert character to numeric;
morb_age_num = input(morb_age, 8.);

[adzoyi]

Below is how I imported the data. The data was imported successfully. I am sorry but I do not understand the suggestions you made to me. I am new to SAS and I will appreciate it if you can break things further for me. Thanks

PROC IMPORT OUT= WORK.std_prevyr
322 DATAFILE= "R:\morb&prevyr..xls"
323 DBMS=EXCEL REPLACE;
324 RANGE="MORB$";
325 GETNAMES=YES;
326 MIXED=YES;
327 SCANTEXT=YES;
328 USEDATE=YES;
329 SCANTIME=YES;
330 RUN;

NOTE: Data source is connected in READ ONLY mode.
NOTE: WORK.STD_PREVYR data set was successfully created.
NOTE: The data set WORK.STD_PREVYR has 52191 observations and 129 variables.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 1:13.35
cpu time 33.79 seconds

[Reeza]

When you import data using PROC IMPORT it guesses at your variable types. Any automatic guessing system can guess wrong. If you take a look at the log, there is code generated there that has the full import code. If you take a look at that code you can see what type your variables are being read in as. You can copy that code from your log, paste it into your program to replace the PROC IMPORT and fix the portions needed to import your data.

[Reeza]

You basically need to use the approach here to read in the data:
https://stats.idre.ucla.edu/sas/modules/inputting-data-into-sas/

[Kurt_Bremser]

Save the data to csv files and import those with a fixed data step; that way, you always get the same dataset structure, and a change in the spreadsheet structure will be easier to detect.

[PaigeMiller]

You have to change the four variables so they have the same type (character or numeric) in both data sets. But since you can't really change the type of a variable, you can do something like this:

data class;
	set sashelp.class(rename=(height=height1));
	height = put(height1,2.); /* Height is now character */
	drop height1;
run;

Now, you may be thinking: what good is height as a character variable? Good question! It really isn't any use as a character variable. Which leads to @Reeza's advice — better to modify the process so that however these two data sets were created, the variables automatically get the same type.