topic Re: SQL RowNumber not working in SAS Enterprise Guide

SQL RowNumber not working

yudhishtirb — Thu, 08 Sep 2016 13:11:35 GMT

Hello Team,

Greetings

I am trying to use below query in one of my program

proc sql;
create table Emfuleni.RowNumber as
select wGroup, szAccountNumber, Row_Number () over (partition by szAccountNumber order by dtStartGroup asc) as RowNumber
from db.GroupChange
WHERE wClientCode = 100;
quit;

But it throwing exception as below

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.

Can someone please assist me here ?

Thanks In advance

Re: SQL RowNumber not working

Reeza — Thu, 08 Sep 2016 13:27:11 GMT

This is not valid SAS syntax.

SAS SQL does not have the row_number function. You can try monotonic() in place.

Also, PARTITION is not valid.

You can typically rewrite this using a data step and BY group processing or try a standard group by clause within your SQL.

Re: SQL RowNumber not working

yudhishtirb — Thu, 08 Sep 2016 13:46:52 GMT

HI Reeza,

Thanks for feedback. Can you please assist me here ? Following is my input and output required

Table :

sZaccountNumber Amount

1 100

2 400

3 200

1 90

1 300

3 4000

So output must be

1 100

1 90

2 400

3 200

3 4000

In short I need to group data by szAccountNumber and only need first 2 rows of that group.

Can you please assist me here ?

Thanks In Advance

Re: SQL RowNumber not working

Astounding — Thu, 08 Sep 2016 14:57:12 GMT

Using just the fields you have shown (and ignoring others like wClientCode):

proc sort data=db.GroupChange (keep=sZAccountNumber Amount) out=have;

by sZAccountNumber;

run;

data want;

set have;

by sZAccountNumber;

if first.sZAccountNumber then counter = 1;

else counter + 1;

if counter > 2 then delete;

drop counter;

run;