How to Proc SQL select top N records?

[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!

[r***@gmail.com]

top 10 results use the following where condition:

WHERE ROWNUM <= 10

[Steven Raimi]

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]

try this:

proc sql;
select *
from file-name
where monotonic()le 10;quit;

[Pardee, Roy]

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]

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

[unknown]

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]

Post by unknown

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;

[unknown]

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]

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]

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]

Try obs, like this:

proc sql;
select *
from work.table1(obs=10)
quit;

[l***@census.gov]

Post by Léon
proc sql;
select *
from work.table1(obs=10)
quit;

This works GREAT for me...

[Léon]

Try this

proc sql;
select * from work.table1(obs=10);
quit;

[dc353]

try the following;

proc sql;
select *
from file_name
where monotonic() le 10;quit;

[l***@census.gov]

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]

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]

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;

[k***@gmail.com]

Post by Ruve
How to Proc SQL select top N records in SAS?

add options obs=10 before select