When posting questions, it's helpful to provide example data, so that we can replicate your problem. Based on your description, I was able to replicate your error message with below example data:
data _null_ ;
td=today() ;
We = intnx("week",td-1,1)-1;
mb13 = intnx('month',td-1,-12);
call symputx('start',"'"||put(mb13,yymmddd10.)||"'",'G');
call symputx('end',"'"||put(we,yymmddd10.)||"'",'G');
run ;
%put start = &start.;
%put end = &end.;
data have ;
input ai_dttm datetime. ;
format ai_dttm datetime. ;
cards ;
28FEB2023:00:00:00
05MAR2024:00:00:00
;
run ;
options symbolgen ;
proc sql ;
select *
from have
where (datepart(ai_dttm) between &start and &end)
;
quit ;
You said you have a WHERE clause in your SQL with a clause like:
where (datepart(ai_dttm) between &start and &end)
Note your macro variables START and END result to a text literals, so above will resolve to code like:
where (datepart(ai_dttm) between '2023-03-01' and '2024-03-16' )
That WHERE clause can't work. It's trying to compare a numeric value on the left which results from DATEPART, to a range of character values on the BETWEEN operator. That will generate the error message. Instead of text literals, you need date values for the BETWEEN operator. One way to get these would be to change your CALL SYMPUTX statements to:
call symputx('start',"'"||put(mb13,date9.)||"'d",'G');
call symputx('end',"'"||put(we,date9.)||"'d",'G');
I changed the format to DATE9, and added a d after the quote marks. This will make the START and END resolve to valid date literals:
179 %put start = &start.;
start = '01MAR2023'd
180 %put end = &end.;
end = '16MAR2024'd
Date literals are numeric values, so they will work on your WHERE clause.
... View more