Discussion:
How to Proc SQL select top N records?
(too old to reply)
Ruve
2008-03-11 18:40:29 UTC
Permalink
Raw Message
How to Proc SQL select top N records in SAS?

Just like "Select Top 10" in SQL Server? I just want to see the top
10 results without having to saving all query results into a dataset.

Thanks!
r***@gmail.com
2008-03-11 18:48:10 UTC
Permalink
Raw Message
top 10 results use the following where condition:

WHERE ROWNUM <= 10
Steven Raimi
2008-03-11 19:04:01 UTC
Permalink
Raw Message
Post by Ruve
How to Proc SQL select top N records in SAS?
Just like "Select Top 10" in SQL Server? I just want to see the top
10 results without having to saving all query results into a dataset.
Thanks!
proc sql outobs=10; /* limit to first 10 results */
select groupvar, count(*)
from table
group by groupvar
order by 2 desc; /* this is how you make them the TOP 10 */
run;


Steve Raimi
dc353
2011-12-21 15:24:10 UTC
Permalink
Raw Message
try this:

proc sql;
select *
from file-name
where monotonic()le 10;quit;
Pardee, Roy
2008-03-11 19:33:24 UTC
Permalink
Raw Message
Couple of notes here:

1) You may want to add the option NOWARN after the outobs= spec to
suppress WARNINGs in your log due to th outobs spec.
2) If you have a WHERE clause, note that SAS will process your entire
dataset in order to apply it--so this isn't something to do for
performance reasons.

HTH,

-Roy

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of
Steven Raimi
Sent: Tuesday, March 11, 2008 12:04 PM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: How to Proc SQL select top N records?
Post by Ruve
How to Proc SQL select top N records in SAS?
Just like "Select Top 10" in SQL Server? I just want to see the top 10
results without having to saving all query results into a dataset.
Thanks!
proc sql outobs=10; /* limit to first 10 results */
select groupvar, count(*)
from table
group by groupvar
order by 2 desc; /* this is how you make them the TOP 10 */ run;


Steve Raimi
b***@gmail.com
2013-09-27 21:30:10 UTC
Permalink
Raw Message
This doesnt work for me
Post by Pardee, Roy
1) You may want to add the option NOWARN after the outobs= spec to
suppress WARNINGs in your log due to th outobs spec.
2) If you have a WHERE clause, note that SAS will process your entire
dataset in order to apply it--so this isn't something to do for
performance reasons.
HTH,
-Roy
-----Original Message-----
Steven Raimi
Sent: Tuesday, March 11, 2008 12:04 PM
Subject: Re: How to Proc SQL select top N records?
Post by Ruve
How to Proc SQL select top N records in SAS?
Just like "Select Top 10" in SQL Server? I just want to see the top 10
results without having to saving all query results into a dataset.
Thanks!
proc sql outobs=10; /* limit to first 10 results */
select groupvar, count(*)
from table
group by groupvar
order by 2 desc; /* this is how you make them the TOP 10 */ run;
Steve Raimi
Howard Schreier)
2008-03-12 01:54:30 UTC
Permalink
Raw Message
Post by r***@gmail.com
WHERE ROWNUM <= 10
I think that more or less begs the question. How do you derive the ROWNUM
column?
n***@gmail.com
2013-01-18 07:56:32 UTC
Permalink
Raw Message
Post by Howard Schreier)
Post by r***@gmail.com
WHERE ROWNUM <= 10
I think that more or less begs the question. How do you derive the ROWNUM
column?
By default sql generates rownum(). you can use this to retrieve specified cols
like

where rownum between 10 and 20;
Howard Schreier)
2008-03-12 02:00:58 UTC
Permalink
Raw Message
Post by Pardee, Roy
1) You may want to add the option NOWARN after the outobs= spec to
suppress WARNINGs in your log due to th outobs spec.
2) If you have a WHERE clause, note that SAS will process your entire
dataset in order to apply it--so this isn't something to do for
performance reasons.
3. If there is a tie for 10th (or a 3-way tie for 9th, etc.) this method
will arbitrarily report some but not all of the tied rows.
Post by Pardee, Roy
HTH,
-Roy
-----Original Message-----
Steven Raimi
Sent: Tuesday, March 11, 2008 12:04 PM
Subject: Re: How to Proc SQL select top N records?
Post by Ruve
How to Proc SQL select top N records in SAS?
Just like "Select Top 10" in SQL Server? I just want to see the top 10
results without having to saving all query results into a dataset.
Thanks!
proc sql outobs=10; /* limit to first 10 results */
select groupvar, count(*)
from table
group by groupvar
order by 2 desc; /* this is how you make them the TOP 10 */ run;
Steve Raimi
Pardee, Roy
2008-03-12 16:33:25 UTC
Permalink
Raw Message
Yeah, this won't work in SAS. Our correspondent here may be used to
working with oracle, which adds that 'pseudocolumn' automatically (tho
even on oracle I think doing top N is a tad bit more complicated).

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of
Howard Schreier <hs AT dc-sug DOT org>
Sent: Tuesday, March 11, 2008 6:54 PM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: How to Proc SQL select top N records?
Post by r***@gmail.com
WHERE ROWNUM <= 10
I think that more or less begs the question. How do you derive the
ROWNUM column?
Sigurd Hermansen
2008-03-12 17:26:42 UTC
Permalink
Raw Message
In a much earlier post I recommended creating a row index variable such
as ROWNUM in a Data step view using the automatic _N_ variable.
S

-----Original Message-----
From: owner-sas-***@listserv.uga.edu [mailto:owner-sas-***@listserv.uga.edu]
On Behalf Of Pardee, Roy
Sent: Wednesday, March 12, 2008 12:33 PM
To: Howard Schreier <hs AT dc-sug DOT org>; SAS-***@LISTSERV.UGA.EDU
Subject: RE: Re: How to Proc SQL select top N records?


Yeah, this won't work in SAS. Our correspondent here may be used to
working with oracle, which adds that 'pseudocolumn' automatically (tho
even on oracle I think doing top N is a tad bit more complicated).

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of
Howard Schreier <hs AT dc-sug DOT org>
Sent: Tuesday, March 11, 2008 6:54 PM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: How to Proc SQL select top N records?
Post by r***@gmail.com
WHERE ROWNUM <= 10
I think that more or less begs the question. How do you derive the
ROWNUM column?
Léon
2011-12-21 09:13:57 UTC
Permalink
Raw Message
Try obs, like this:

proc sql;
select *
from work.table1(obs=10)
quit;
l***@census.gov
2017-07-11 15:20:41 UTC
Permalink
Raw Message
Post by Léon
proc sql;
select *
from work.table1(obs=10)
quit;
This works GREAT for me...

Léon
2011-12-21 09:16:16 UTC
Permalink
Raw Message
Try this

proc sql;
select * from work.table1(obs=10);
quit;
dc353
2011-12-21 15:20:50 UTC
Permalink
Raw Message
try the following;

proc sql;
select *
from file_name
where monotonic() le 10;quit;
l***@census.gov
2017-07-11 15:19:14 UTC
Permalink
Raw Message
Post by dc353
try the following;
proc sql;
select *
from file_name
where monotonic() le 10;quit;
This actually work for outputting only 10 obs, which is what I was looking for.... GREAT!! THNAKS!!
g***@gmail.com
2013-10-06 05:14:33 UTC
Permalink
Raw Message
Post by Ruve
How to Proc SQL select top N records in SAS?
Just like "Select Top 10" in SQL Server? I just want to see the top
10 results without having to saving all query results into a dataset.
Thanks!
proc sql inobs=10;
create table temp as
select 8 from work.final;
quit;
g***@gmail.com
2017-04-20 02:02:52 UTC
Permalink
Raw Message
Post by Ruve
How to Proc SQL select top N records in SAS?
Just like "Select Top 10" in SQL Server? I just want to see the top
10 results without having to saving all query results into a dataset.
Thanks!
I use the OUTOBS option, as in...

proc sql outobs=1;
create table foo as
select
cluster,
count(*) as n
from student_cluster
group by cluster
order by calculated n desc
;
quit;
Loading...