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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.