Problem with Date Macro

[ysk]

Hi there,

I have set up a date macro to set a range of report period 

%let report_start = %sysfunc(intnx(day,%sysfunc(today()),-8,b),date9.);
%let report_end = %sysfunc(intnx(day,%sysfunc(today()),-2,b),date9.);

I am trying to use them in my sql query below but getting an Error.

Service_Date1 is in datetime format originally. 

proc sql;
create table Test as
select * from Table 
where date(Service_date1) >= %bquote(&report_start)
and date(Service_date1) <= %bquote(&report_end)

1 13MAR2023
-------
22
-------
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=,
<>, =, >, >=, AND, EQ, EQT, GE, GET, GROUP, GT, GTT, HAVING, LE, LET, LT, LTT,
NE, NET, OR, ORDER, ^=, |, ||, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

Any thoughts?

[PaigeMiller]

Macro variables should not be formatted (unless you are using them in titles, labels or file name in which case a human has to understand them, and then you should format them). For arithmetic and logical operations (which is what you are doing), macro variables should not be formatted.

%let report_start = %sysfunc(intnx(day,%sysfunc(today()),-8,b));
%let report_end = %sysfunc(intnx(day,%sysfunc(today()),-2,b));

Why doesn't it work if it is formatted? Because you have written code that (after the macro variable resolves) is not valid legal working SAS code.

where date(Service_date1) >= %bquote(&report_start)

resolves to 

where date(Service_date1) >= 13MAR2023

and this is not legal SAS code, so you get an error. Do you understand why this is not legal SAS code?

Also, %bquote not needed in this situation.

[russt_sas]

If you leave the macro variable formatted and you are trying to compare it to a SAS date value then you can use a date constant, for example:

where date(Service_date1) >= "&report_start"d
and date(Service_date1) <= "&report_end"d

[Tom]

Your WHERE clause is really messed up.

First of all the DATE() function does NOT take any inputs.  It just returns today's date.  (NOTE you can use the alias TODAY() for it if you want to make that clearer in your code.).

Second a date constant must be either the actual number of days since 1960 that SAS uses to store dates, or it must be a quoted string that the DATE informat can recognize immediately followed by the letter D.

Perhaps you meant to use the DATEPART() function?  That function does take an input, a datetime value.  It will convert the number of seconds since 1960 that SAS uses to represent datetime values into a number of days instead. Basically dividing by the number of seconds in a day.  datepart(datetime_value) is equivalent to int(datetime_value/'24:00:00't) 

where datepart(Service_date1) between "&report_start"d and "&report_end"d