BookmarkSubscribeRSS Feed
ysk
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
-------
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?

3 REPLIES 3
PaigeMiller
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
russt_sas
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

 

Tom
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

sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 3 replies
  • 301 views
  • 0 likes
  • 4 in conversation