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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.