PROC SQL Where Clause Function within IN

[PeteSAS]

Is there a way to use functions within an IN clause (or am I totally wrecking the syntax)? Example:

proc sql;
create table myExample as
select datepart(myDate) as fmtDate format mmddyy8., sum(myStuff) as sum_stuff
from myTable
where datepart(myDate) in (mdy(11,15,2024), mdy(12,1,2024), mdy(1,1,2025))
group by myDate
;

myDate is a SQL Server DateTime field, hence the datepart addition there. SAS EG seems to want me to put a nested select in that "IN" clause, so it's choking on the MDY functions. Obviously there are a myriad other ways to do this, just wondering if it's a limitation of SAS that functions can't be used within IN clauses.

Thanks in advance!

[Tom · Accepted solution]

SAS wants constants in the IN list.

Which is trivial to do with your example.  Just use date literals.

where datepart(myDate) in ('15NOV2024'd '01DEC2024'd '01JAN2025'd)

Or use macro function %SYSFUNC() to call the MDY() function so that SAS just sees the resulting number of days.

where datepart(myDate) in (%sysfunc(mdy(11,15,2024)) %sysfunc(mdy(12,1,2024)) %sysfunc(mdy(1,1,2025)))

Or like you said you can use the other syntax where it wants a query that generates to the list of values.

[Patrick]

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