topic Re: SAS limitation with IN operator in SAS Procedures

SAS limitation with IN operator

nitesh_k — Wed, 12 Dec 2012 08:24:17 GMT

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?

Re: SAS limitation with IN operator

Keith — Wed, 12 Dec 2012 08:57:25 GMT

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

Re: SAS limitation with IN operator

TomKari — Wed, 12 Dec 2012 15:28:56 GMT

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

Re: SAS limitation with IN operator

PGStats — Wed, 12 Dec 2012 17:30:42 GMT

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