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?
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);
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
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.