Desktop productivity for business analysts and programmers

SQL RowNumber not working

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

SQL RowNumber not working

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


Accepted Solutions
Solution
‎09-09-2016 10:17 AM
Respected Advisor
Posts: 4,973

Re: SQL RowNumber not working

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;

View solution in original post


All Replies
Grand Advisor
Posts: 17,338

Re: SQL RowNumber not working

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.  

Contributor
Posts: 25

Re: SQL RowNumber not working

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

 

 

Solution
‎09-09-2016 10:17 AM
Respected Advisor
Posts: 4,973

Re: SQL RowNumber not working

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 432 views
  • 1 like
  • 3 in conversation