Date:         Sat, 4 Apr 2009 18:55:12 -0400
Reply-To:     "oloolo (dynamicpanel@yahoo.com)" <dynamicpanel@YAHOO.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "oloolo (dynamicpanel@yahoo.com)" <dynamicpanel@YAHOO.COM>
Subject:      Re: PROC SQL: top 5 in each group

I have checked all solutions provided so far, and I particularly like the
PROC UNIVARIATE solution provided by Chang and \sqrt(ADD), which might be
the most efficient one.

here I would like to contribute one more solution, no pre-sort needed,
could be the second efficient one. The techniques were demonstrated before
on SAS-L.
*******************************************;
data test;
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
1995  500
1995  200
1995  700
1995  800
1995  100
1995  200
1995  300
1995  400
1995  752
;
run;

data test;        set test;  u=ranuni(9999);  run;
proc sort data=test; by  u; run;


%let lowyear=1992; * any comfortable lower bound;
%let hiyear=2000;  * any comfortable upper bound;

data out;
     set test  end=eof;

     array d{&lowyear : &hiyear, 0:5} _temporary_;
     if d[YEAR, 0]=. then do;
        d[YEAR, 0]=1;  d[YEAR, 5]=ASSETS;
     end;
     else if ASSETS>d[YEAR, 1] then do;
          if ASSETS>d[YEAR, 5] then i=6;
          else do;
              i=2;
              do while(d[YEAR, i]<ASSETS & i<5); i+1;   end;
          end;
          do j=1 to (i-2);
             d[YEAR, j]=d[YEAR, j+1];
          end;
          d[YEAR, i-1]=ASSETS;
      end;

      if eof then
      do YEAR=&lowyear to &hiyear;
         if d[YEAR, 0]^=. then do;
            do j=1 to 5;
               OrderedAssets=d[Year, j];;
               keep YEAR OrderedAssets;
               output;
            end;
         end;
      end;
run;