BookmarkSubscribeRSS Feed
TMKAIG1
Fluorite | Level 6

Hi!

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:

PROC SQL;

SELECT MAX(MAX(OF v1 - v4))

INTO :max_v

FROM ds1

;

QUIT;

This equivalent query works as expected:

PROC SQL;

SELECT MAX(MAX(v1, v2, v3, v4))

INTO :max_v

FROM ds1

;

QUIT;

Should the "OF" syntax have worked?  If not, is this documented somewhere?  If so, any idea what I did wrong?

Thanks!

5 REPLIES 5
ballardw
Super User

I believe the issue is a more generic restriction on not allowing any variable lists in Proc sql.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

PGStats
Opal | Level 21

Not sure this is true. The standard describes what is legal syntax but not what should be done with illegal queries. For example, I don't think the SELECT myVar INTO :myMacro syntax makes SAS/SQL non compliant. IMHO, variable lists are not implemented in SAS/SQL for other reasons.

PG

PG
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi PGStats,

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:

Tom
Super User Tom
Super User

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 =: .

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1688 views
  • 0 likes
  • 5 in conversation