BookmarkSubscribeRSS Feed
Calcite | Level 5 ysk
Calcite | Level 5

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
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?

Diamond | Level 26

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.

Paige Miller
SAS Employee

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


Super User Tom
Super User

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



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 4 in conversation