The main bit you're missing is that when using intnx with SAS datetime values the name of the directives need to start with DT... (like: DTweek). The other option would be to first convert the datetime to a date value using datepart().
Given that your main table contains datetime values I would stick to working with datetime values using a.ai_dttm between &start. and &end. because:
- Should there be an index on ai_dttm then using the datepart() function will not allow to use this index
- Should your table reside in a database then using the datepart() function could cause SAS to pull the data to the SAS side for sub-setting.
Below some sample code how you could go about this.
Option 2 and 3 are preferable because they populate &start and &end with a string in the datetime20 format. The conversion to a datetime value happens in the SQL itself via "<datetime string"DT In doing so SAS "knows" that this is a SAS datetime value and if pushing the query to a database will convert the SAS Datetime value to the matching representation in the database (like: timestamp). This wouldn't happen with Option 1 where SAS wouldn't know that this is a SAS Datetime value but "think" it's just a number and push it as a number to the DB. Option 1 works only for SAS tables.
data have;
format ai_dttm datetime20.;
do ai_dttm=intnx('dtday',datetime(),-40,'b') to intnx('dtday',datetime(),40,'b') by 100012;
var+1;
output;
end;
run;
/* option 1 */
data _null_;
start1=intnx('dtweek',datetime(),-1,'b');
end1 =intnx('dtmonth',datetime(),0,'e');
call symputx('start1',start1,'g');
call symputx('end1',end1,'g');
run;
proc sql;
select
ai_dttm,
count(*) as n_rows,
&start1 as start format=datetime20.,
&end1 as stop format=datetime20.
from have
where ai_dttm between &start1 and &end1;
;
quit;
/* option 2 */
data _null_;
start2=intnx('dtweek',datetime(),-1,'b');
end2 =intnx('dtmonth',datetime(),0,'e');
call symputx('start2',put(start2,datetime20.),'g');
call symputx('end2',put(end2,datetime20.),'g');
run;
proc sql;
select
ai_dttm,
count(*) as n_rows,
"&start2"dt as start format=datetime20.,
"&end2"dt as stop format=datetime20.
from have
where ai_dttm between "&start2"dt and "&end2"dt;
;
quit;
/* option 3 */
%let start3=%sysfunc(intnx(dtweek,%sysfunc(datetime()),-1,b),datetime20.);
%let end3 =%sysfunc(intnx(dtmonth,%sysfunc(datetime()),0,e),datetime20.);
proc sql;
select
ai_dttm,
count(*) as n_rows,
"&start3"dt as start format=datetime30.,
"&end3"dt as stop format=datetime30.
from have
where ai_dttm between "&start3"dt and "&end3"dt;
;
quit;
And last but not least: If using intnx() and aligning SAS datetime value to the end of the directive intnx('dtmonth',datetime(),0,'e') you get the last second of the boundary - like 31MAR2024:23:59:59
If in your source you've also got fractional seconds then using the between operator you risk to miss the last last second before midnight. Below option 4 avoids this potential issue.
/* option 4 */
data _null_;
start4=intnx('dtweek',datetime(),-1,'b');
end4 =intnx('dtmonth',datetime(),0,'e');
end4=end4+1;
call symputx('start4',put(start4,datetime20.),'g');
call symputx('end4',put(end4,datetime20.),'g');
run;
proc sql;
select
ai_dttm,
count(*) as n_rows,
"&start4"dt as start format=datetime20.,
"&end4"dt as stop format=datetime20.
from have
where "&start4"dt <= ai_dttm < "&end4"dt;
;
quit;
... View more
