topic Re: SAS EG 4.2 sql error in SAS Enterprise Guide

SAS EG 4.2 sql error

HashimBasheer — Fri, 04 Jan 2013 08:42:58 GMT

Hello,

I received an query from user as below.

Proc sql;

Create table test1 as

(select M.*

from

(select A.*,

count(distinct po)

over (partition by system, id, key, cd)as cnt

from

test A

where cnt>1

group BY

1 ,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17)

;

quit

;

When running the query user is getting an error

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, ^=, |, ||, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

Is this because of the "over partition by system"?

Re: SAS EG 4.2 sql error

AhmedAl_Attar — Fri, 04 Jan 2013 13:09:31 GMT

Yes, Your SQL looks like Oracle's SQL syntax.

As far as I know, SAS SQL does not support the "Over" syntax.

Re: SAS EG 4.2 sql error

HashimBasheer — Fri, 04 Jan 2013 13:18:07 GMT

Thanks Ahmed.

Do you think there is a better way to modify the query?

Re: SAS EG 4.2 sql error

AhmedAl_Attar — Fri, 04 Jan 2013 13:47:06 GMT

You probably would have to break the query into multiple queries/tables

Re: SAS EG 4.2 sql error

TomKari — Fri, 04 Jan 2013 20:00:52 GMT

OVER is a feature of SQL 2003 that SAS hasn't implemented at this point, that allows for "Determining the partitioning and ordering of a rowset before the associated window function is applied." (I've never used it myself).

You'll have to either restructure your query for SAS, or, if your query is to an exernal database, use pass-through to pass the OVER clause to the database.

Tom