BookmarkSubscribeRSS Feed
HashimBasheer
Fluorite | Level 6


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

         )M

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"?

4 REPLIES 4
AhmedAl_Attar
Rhodochrosite | Level 12

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

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

HashimBasheer
Fluorite | Level 6

Thanks Ahmed.

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

AhmedAl_Attar
Rhodochrosite | Level 12

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

TomKari
Onyx | Level 15

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

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 4 replies
  • 1175 views
  • 4 likes
  • 3 in conversation