topic Re: proc sql list in SAS Programming

proc sql list

SASdevAnneMarie — Sun, 11 Jan 2026 16:37:00 GMT

Hello Experts,

I'm wondering why I got this error using the list :

where datepart(a.ma_date) in (mdy(12,31,year(today())-1), mdy(12,31,year(today())-2), mdy(12,31,year(today())-3),
                                          ___
                                          79
                                          76
ERROR 79-322: Expecting a SELECT.

Thank you for your help !

Re: proc sql list

PaigeMiller — Sun, 11 Jan 2026 16:41:48 GMT

Please show us the FULL COMPLETE log for this PROC SQL.

In the future, please always (that's 100% of the time, don't wait for us to ask) show us the FULL COMPLETE log for any PROC that gives you an error.

Re: proc sql list

Tom — Sun, 11 Jan 2026 17:19:42 GMT

The IN operator in SAS wants a list of VALUES, not function calls.

The IN operator in SQL also allows a subquery, hence the reference to a missing SELECT.

Examples:

 71         proc sql;
 72         select * from sashelp.class
 73         where age in (13)
 74         ;
 75         quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.01 seconds
       cpu time            0.02 seconds
       
 
 76         proc sql;
 77         select * from sashelp.class
 78         where age in (130/10)
                             _
                             22
                             200
 ERROR 22-322: Syntax error, expecting one of the following: a numeric constant, a datetime constant, a missing value, ), ',', -, 
               :.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 79         ;
 NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
 80         quit;
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 
 81         proc sql;
 82         select * from sashelp.class
 83         where age in (select age from sashelp.class where age=130/10)
 84         ;
 85         quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.01 seconds
       cpu time            0.01 seconds

Since your functions are not referencing anything that varies you could use the macro language to generate the date constants.

For example by using %SYSFUNC() to call the SAS functions.

where datepart(a.ma_date) in 
(%sysfunc(intnx(year,%sysfunc(today()),-1,e))
,%sysfunc(intnx(year,%sysfunc(today()),-2,e))
,%sysfunc(intnx(year,%sysfunc(today()),-3,e))
)

Or just put the dates needed into a macro variable.

data _null_;
  length datelist $100;
  do offset=1 to 3 ;
    datelist=catx(' ',datelist,intnx('year',today(),-offset,'e'));
  end;
  call symputx('datelist',datelist);
run;
%put &=datelist;

...
where datepart(a.ma_date) in (&datelist)
...

Re: proc sql list

Amir — Sun, 11 Jan 2026 17:59:46 GMT

Hi,

It appears you are trying to specify date constants via the use of functions; the in operator documentation specifies:

constant

is a number or a quoted character string (or other special notation) that indicates a fixed value. Constants are also called literals.

so that is why there is an error when you use a function, as it is neither a "number or a quoted character string", even though it returns a number.

Thanks & kind regards,

Amir.

Re: proc sql list

SASdevAnneMarie — Sun, 11 Jan 2026 19:33:03 GMT

Thank you Tom!