04-16-2015 10:44 AM
I use a lot of SAS language functionality within my PROC SQL queries. I was stunned recently when I got an error message from SAS 9.4:
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND,
BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.
for the following query:
SELECT MAX(MAX(OF v1 - v4))
This equivalent query works as expected:
SELECT MAX(MAX(v1, v2, v3, v4))
Should the "OF" syntax have worked? If not, is this documented somewhere? If so, any idea what I did wrong?
04-16-2015 11:52 AM
Indeed, I believe SAS uses standard ANSI SQL, which has a function MAX which requires a list of parameters separated by commas. If SAS changed this to use their own variable list setup, it would no longer be ANSI compliant.
04-16-2015 05:27 PM
04-17-2015 03:49 AM
Not sure, you maybe right. My impression was that anything defined in ANSI SQL, of which a function called MAX() is, should be standard. The select into part is SAS specific, i.e. it will only run in SAS SQL, however it is not altering the base functionality of ANSI SQL. If however you mess with the functions which are already implemented, then that would be changing the base. I have been wrong before however :smileylaugh:
04-16-2015 12:40 PM
You cannot use variable lists in SQL.
You also cannot use : modifier in truncated string comparisons. Instead use the mnemonics with T on the end. So use EQT instead of =: .