topic Re: WHERE clause operator requires compatible variables in SAS Programming

WHERE clause operator requires compatible variables

ybz12003 — Wed, 24 Apr 2024 17:50:29 GMT

Hello,

My code didn't work in Where statement. Please help.

proc sql noprint;
   select min(weekcode), max(weekcode) into :minweek, :maxweek
   from ND_Preval;
quit;
data MMWR_NREVSS_weekcode (keep= WeekCode );
	set lookup.MMWR;
	rename WEEK=WeekCode; *Renaming to match name in SAS program; 
run;
Proc sort data=MMWR_NREVSS_weekcode; by weekcode; run;

data ND_Preval_1;
	merge MMWR_NREVSS_weekcode ND_Preval (where=(&minweek. le WeekCode le &maxweek.));
	by weekcode;
run;

60   data ND_Preval_1;
61       merge MMWR_NREVSS_weekcode ND_Preval (where=(&minweek. le WeekCode le &maxweek.));
ERROR: WHERE clause operator requires compatible variables.
62       by weekcode;
63   run;

Re: WHERE clause operator requires compatible variables

ballardw — Wed, 24 Apr 2024 17:54:34 GMT

That means one of your Weekcode variables is character and the other numeric. Which one?

The ideal solution would be to go back in your process, pick a variable type and be consistent.

Or at use fix by converting numeric to character with a PUT or character to numeric with an Input. Your choice.

Re: WHERE clause operator requires compatible variables

Tom — Wed, 24 Apr 2024 18:20:48 GMT

What type (numeric or character) of variable is weekcode in ND_Preval? Does it have a format attached? What format?

If it is numeric variable with a date type format (DATE, YYMMDD, etc) attached then your macro variables should have normal date values because of the use of MIN() and MAX() operators will remove the formatting. So your code should work.

1    data test;
2      today=date();
3      format today date9.;
4    run;

NOTE: The data set WORK.TEST has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


5    proc sql noprint;
6    select min(today) into :today
7    from test;
8    quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


9    %put &=today;
TODAY=   23490

But if it is character then the whole premise of your code can only work if the variable has strings like, 2024-04-24, that will sort in the same order as the date values that humans would see them as having. Because when represented as string "01JUL2023" comes before "12SEP2000" because "0" comes before "1".

But if the character variable does have strings in that YYYY-MM-DD (or YYYYMMDD) style then the macro variables will also and so your where condition will look something like:

2023-01-01 le WeekCode le 2024-12-31

Which SAS will interpret as comparing the numbers 2021 and 1981 to a character string, hence the type mismatch.