sas sql

[georgel]

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

[Reeza]

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

---

[ChrisNZ]

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.