Hi all,
I would like to keep the first two observations ordered by date by group using sql in one step
proc sql;
select *, ROW_NUMBER()
from test_sql
where ROW_NUMBER<=2
order by cusip date;
quit;
The above does not work
Many thanks in advance
Regards
George
ROW_NUMBER() isn't valid in SAS SQL, I think that's MS SQL?
All DBs do this one a bit differently, Oracle is ROWNUM for example.
Have you tried using the MONOTONIC() function or using the OBS= data set option?
Note that you were also missing a comma in the ORDER BY clause.
proc sql;
create table want(obs=2) as
select *
from test_sql
order by cusip, date;
quit;
proc sql;
select * , monotonic() as rowNum
from test_sql
where calculated rowNum <=2
order by cusip, date;
quit;
@georgel wrote:
Hi all,
I would like to keep the first two observations ordered by date by group using sql in one step
proc sql; select *, ROW_NUMBER() from test_sql where ROW_NUMBER<=2 order by cusip date; quit;
The above does not work
Many thanks in advance
Regards
George
Note that SQL does not guarantee that rows will be processed in the table order.
If you read a single table and use option proc sql nothreads, it should still happen though.
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.