SAS limitation with IN operator

[nitesh_k]

Hi All,

     I need to get the below SAS SQL query working.

Query:

=====

SELECT *

FROM STUDENT_FEE

WHERE INPUT(Calendar_Month, MONYY8.) IN (INPUT('Oct 2011', MONYY8.),INPUT('Dec 2011', MONYY8.)); 

Issue:  Query execution itself fails.

=====

It seems in SAS, IN operator in the where clause doesn't accept any functions like INPUT, CAT etc. Again, this looks like a limitation in SAS.

Any other better way to rewrite the above query?

Note:

====

Below query works fine. We can use SAS functions with any other operators like =, >, <, BETWEEN AND, etc

Query:

===========

SELECT *

FROM STUDENT_FEE

WHERE INPUT(Calendar_Month, MONYY8.) = INPUT('Dec 2011', MONYY8.); 

Please help?

[Keith]

I would be tempted to use the INTNX function against Calendar_Month to return the first of the month, then you can just specify the exact dates in the IN clause.

WHERE INTNX('MONTH',Calendar_Month,0) IN ('01Oct2011'd, '01Dec2011'd);

[PGStats]

Given OP query, Calendar_Month is a string. That's why the INPUT function is called. But the MONYY informat always returns the first of the month. Also, the documentation for the IN operator in SQL is clear: the parentheses must enclose a sequence of CONSTANTS or a query. Thus the condition can be simply written:

WHERE INPUT(Calendar_Month, MONYY8.) IN ('01OCT2011'd, '01DEC2011'd);

PG

[TomKari]

Yes, you've tripped into one of the peculiarities of SQL. The contents of an IN condition can only be a query expression or a constant, whereas your syntax of the IN clause is a sql expression.

If you just have the two conditions, the syntax that Keith gives you is perfect.

If you have a lot of them, or if they change, put your numeric date values into a SAS dataset, and use this syntax:

INPUT(Calendar_Month, MONYY8.) IN (select Condition_Month from Condition_Months);

Tom