SQL RowNumber not working

[yudhishtirb]

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

[Reeza]

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.  

[yudhishtirb]

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

[Astounding · Accepted solution]

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;