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
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 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.
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.