Date:         Tue, 31 Mar 2009 13:26:13 -0400
Reply-To:     "Gilsen, Bruce F." <bruce.gilsen@FRB.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Gilsen, Bruce F." <bruce.gilsen@FRB.GOV>
Subject:      PROC SQL: top 5 in each group

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)