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

[lunaa]

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🙏

[PaigeMiller]

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.

[lunaa]

the value in BILL_MTH is like this : 2011JAN

[PaigeMiller]

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?

[tom_grant]

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;