Date:         Tue, 31 Mar 2009 21:06:05 +0200
Reply-To:     karma <dorjetarap@GOOGLEMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         karma <dorjetarap@GOOGLEMAIL.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=windows-1252

Not as simple as the datastep, but if you do a self merge, it can
become quite straightforward

data have ;
input YEAR ASSETS;
cards;
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
;
proc sql ;
   create table want as
      select a.*
      from have as a
      left join have as b on (a.year=b.year and a.assets<=b.assets)
      group by a.year, a.assets
      having count(*) <=5
      order by a.year, a.assets desc ;
   quit;

output:
                                           YEAR    ASSETS
                                        ャャャャャャャャャ
                                            1997       800
                                            1997       700
                                            1997       600
                                            1997       500
                                            1997       400
                                            1998        80
                                            1998        70
                                            1998        60
                                            1998        50
                                            1998        40



2009/3/31 Gilsen, Bruce F. <bruce.gilsen@frb.gov>:
> 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)
>