The IN Operator only allows for a query expression or constants but not for functions.
You can pass SAS date constants in the form 'ddmonyyyy'd
proc sql;
create table myExample as
select datepart(myDate) as fmtDate format mmddyy8.,
sum(myStuff) as sum_stuff
from myTable
where datepart(myDate) in ('15nov2024'd,'01dec2024'd,'01jan2025'd)
group by myDate;
quit;
myDate is a SQL Server DateTime field
Does that mean your source table myTable is a SQL Server table? If so then ensure that SAS can push the where clause to the database for processing so it doesn't first pull all the data from SQL Server before sub-setting. The datepart() function could cause a problem here. Passing SAS Functions to Microsoft SQL Server
Happy to provide additional advice if above is your situation. I would need to know if only the source or also the target table are in SQL Server and the exact where clause you want to use.
As a comment to below syntax provided by @Tom: This will only work for processing on the SAS side because the result of the %sysfunc() portion is a number that expresses a SAS date. SAS won't be able to convert this number to the matching SQL date for in-database processing. If you use SAS date literals like '01jan2024'd then SAS will be able to convert the date to the SQL Server equivalent for in-database processing.
where datepart(myDate) in (%sysfunc(mdy(11,15,2024)) %sysfunc(mdy(12,1,2024)) %sysfunc(mdy(1,1,2025)))
... View more
