topic Re: How can I solve the error: WHERE clause operator requires compatible variables. in SAS Software for Learning Community

How can I solve the error: WHERE clause operator requires compatible variables.

lunaa — Mon, 28 Nov 2022 12:38:42 GMT

the question is this:

What is the monthly grand total of billing amounts between 1 July 2011 and 31 December 2011? Write statements of one PROC only such that it will report the grand total of billing amounts for each month between 1 July 2011 and 31 December 2011. The report must show each monthly grand total without requiring further manual computations.

My code is this:

proc print data=SAS.ACCOUNTS
grandtotal_label='total';
var amount_topay;
sumby amount_topay;
where 'JUL'<=bill_mth<='DEC';

However it occur the error:WHERE clause operator requires compatible variables.

How to solve this?thank you🙏

Re: How can I solve the error: WHERE clause operator requires compatible variables.

PaigeMiller — Mon, 28 Nov 2022 13:30:59 GMT

Hello

This happens because the variable bill_mth is numeric, and you cannot compare numeric values to character strings such as 'JUL' or 'DEC'.

Most likely, you don't want the character strings in the comparison, but that really depends on the values in variable BILL_MTH. So what are some of the values in variable BILL_MTH?

Also, note that 'DEC' is less than 'JUL' and so

where 'JUL'<=bill_mth<='DEC'

can never be true. Character strings are compared alphabetically, so the first month of the year would be 'APR' and the second month would be 'AUG' and so on using character strings, which is not what most people want.

Re: How can I solve the error: WHERE clause operator requires compatible variables.

lunaa — Mon, 28 Nov 2022 13:30:37 GMT

the value in BILL_MTH is like this : 2011JAN

Re: How can I solve the error: WHERE clause operator requires compatible variables.

PaigeMiller — Mon, 28 Nov 2022 14:46:20 GMT

Great, but that's not a number. It is possible that this is a formatted value, and that unformatted value is something different? Can you remove the format from this variable and tell us some values of this variable?

Also, is it really 2011JAN or is it really JAN2011?

Re: How can I solve the error: WHERE clause operator requires compatible variables.

tom_grant — Mon, 28 Nov 2022 14:40:01 GMT

Here is an example where the dates are stored as character strings - you'll need to create a new numeric date variable & then you can use where statement on the numeric variable:

/* Creating a dataset with a character date variable */
data mons_char;
input dt $7.;
datalines;
jul2022
jun2022
aug2022
jan2022
feb2022
;
run;

/* Creating a numeric date variable using the input function - using formats to show how that new variable can be displayed */
data mons2;
set mons_char;
format dt_num2 monyy7. dt_num3 date9.;
dt_num = input(dt,monyy7.);
dt_num2 = dt_num;
dt_num3 = dt_num;
run;

/* Using numeric date variable in where statement */

proc print data=mons2;
where '01jun2022'd <= dt_num <= '31aug2022'd;
run;