DATA Step, Macro, Functions and more

PROC SQL.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

PROC SQL.

Can I use ROWNUM Pseudo column in proc sql?  like other database statement?

 

eg.    PROC SQL;

            SELECT * FROM employees WHERE ROWNUM < 11;

         QUIT;


Accepted Solutions
Solution
2 weeks ago
Super User
Super User
Posts: 9,416

Re: PROC SQL.

Posted in reply to katkarparam

No, rownum is a database specific item, not part of ANSI SQL.  You can code it simply in datastep.

View solution in original post


All Replies
Super User
Posts: 9,890

Re: PROC SQL.

[ Edited ]
Posted in reply to katkarparam

Much easier done in a data step:

data want;
set have (obs=10);
run;

SQL either takes much more coding:

proc sql;
create table want (drop=count) as
select *, monotonic() as count
from sashelp.class
having count < 11;
quit;

or will create a WARNING:

proc sql outobs=10;
create table want as select * from sashelp.class;
quit;

 

Edit:

to simply create a row number, use this data step:

data want;
set have;
rownum = _n_;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
2 weeks ago
Super User
Super User
Posts: 9,416

Re: PROC SQL.

Posted in reply to katkarparam

No, rownum is a database specific item, not part of ANSI SQL.  You can code it simply in datastep.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 108 views
  • 0 likes
  • 3 in conversation