Date:         Tue, 31 Mar 2009 14:43:05 -0400
Reply-To:     msz03@albany.edu
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mike Zdeb <msz03@ALBANY.EDU>
Subject:      Re: PROC SQL: top 5 in each group
Content-Type: text/plain;charset=iso-8859-1

hi ... OK, it's not SQL, but how about PROC RANK

good in that it would take care of situations where there are multiple observations
with the same ASSETS and possibly more that 5 observations that
would qualify as 'top 5'


* added a few new observations;
data bg;
input year assets @@;
datalines;
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
1997  400
1996  100  1996  100
1996  100  1996  100
1996  100  1996  100
;
run;

proc sort data=bg;
by year;
run;

proc rank data=bg out=bg5 (where=(rank le 5)) ties=low descending;
by year;
var assets;
ranks rank;
run;

* 17 observations as 'top 5' ... not 15 for 3 years;
proc print data=bg5;
by year;
run;

--
Mike Zdeb
U@Albany School of Public Health
One University Place
Rensselaer, New York 12144-3456
P/518-402-6479 F/630-604-1475

> 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)
>
>