12-12-2012 03:24 AM
I need to get the below SAS SQL query working.
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?
Below query works fine. We can use SAS functions with any other operators like =, >, <, BETWEEN AND, etc
WHERE INPUT(Calendar_Month, MONYY8.) = INPUT('Dec 2011', MONYY8.);
12-12-2012 03:57 AM
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);
12-12-2012 12:30 PM
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);
12-12-2012 10:28 AM
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);