topic Re: sas sql in SAS Programming

sas sql

georgel — Tue, 07 Sep 2021 21:51:14 GMT

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

Re: sas sql

Reeza — Tue, 07 Sep 2021 22:06:30 GMT

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

Re: sas sql

ChrisNZ — Tue, 07 Sep 2021 23:03:28 GMT

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.