Date:         Tue, 31 Mar 2009 12:52:00 -0500
Reply-To:     "./ ADD NAME=Data _null_;" <iebupdte@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "./ ADD NAME=Data _null_;" <iebupdte@GMAIL.COM>
Subject:      Re: PROC SQL: top 5 in each group
Comments: To: "Gilsen, Bruce F." <bruce.gilsen@frb.gov>
In-Reply-To:  <200903311726.n2VAkjgW031903@malibu.cc.uga.edu>
Content-Type: text/plain; charset=ISO-8859-1

proc sql feedback nowarn;
   reset outobs=5;
   create table top5 as
      select * from assets
      order assets desc
      ;
   quit;
   run;


On 3/31/09, 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)
>