Date: Tue, 31 Mar 2009 14:28:56 -0400
Reply-To: Akshaya <akshaya.nathilvar@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Akshaya <akshaya.nathilvar@GMAIL.COM>
Subject: Re: PROC SQL: top 5 in each group
In-Reply-To: <200903311726.n2VAkjgW031903@malibu.cc.uga.edu>
Content-Type: text/plain; charset=ISO-8859-1
As far as I know, there's no direct way to do this under SQL. However a view
can be created using SQL with the order intended and use a Data step
afterward to get the top 5 rows.
Proc sql;
create view yd as
select *
from have
order by 1,assets desc;
Quit;
Data want;
do _n_=1 by 1 until (last.year);
set yd;
by year descending assets;
if _n_<6 then output;
end;
Run;
Proc datasets memtype=view nolist;
delete yd;
Quit;
On Tue, Mar 31, 2009 at 1:26 PM, Gilsen, Bruce F. <bruce.gilsen@frb.gov>wrote:
> A user wants to create a data set with the top 5 values of ASSETS for each
> YEAR.
>
> Data set ONE (enough records to give a sense of the question):
>
> YEAR ASSETS
> 1998 10
> 1998 20
> 1998 30
> 1998 40
> 1998 50
> 1998 60
> 1998 70
> 1998 80
> 1997 500
> 1997 600
> 1997 700
> 1997 800
> 1997 100
> 1997 200
> 1997 300
> 1997 400
>
> I typically do this with PROC SORT and a DATA step.
>
> proc sort data=one out=two;
> by year descending assets;
> run;
> data three;
> set two;
> by year descending assets;
> retain n 0; /* count observations for current year */
> drop n; /* not part of output data set */
> if first.year
> then n=1;
> else n+1;
> if n lt 6; /* write only top 5 for each YEAR to output data set */
> run;
>
>
> I assumed that there was a fairly obvious way to do this in PROC SQL, but
> I couldn't think of one and didn't see one when I searched a bit. SAS
> Institute recommended PROC SORT+DATA step, not PROC SQL.
>
> Anyone have an idea? Thanks in advance.
>
> Bruce Gilsen
> speaking only for myself (and my fantasy baseball team, which tries
> for its 5th league title in 21 years in 2009)
>
--
AkshayA!
| 
