Date:         Fri, 3 Jan 2003 15:31:49 -0800
Reply-To:     "Huang, Ya" <yhuang@AMYLIN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Huang, Ya" <yhuang@AMYLIN.COM>
Subject:      Re: Can you do this in one step?
Comments: To: Talbot Katz <topkatz@MSN.COM>
Content-Type: text/plain; charset="iso-8859-1"

Talbot,

Not exactly a one step solution, but it is SQL based
and shorter:

proc sql;
create table junk as
select groupnum,idnum,sum(trans) as tot
from ds1
group by groupnum,idnum
order by groupnum,tot descending
;
select *, monotonic() as n
from junk
group by groupnum
having min(n) <=n<=min(n)+4
;

Kind regards,

Ya Huang

-----Original Message-----
From: Talbot Katz [mailto:topkatz@MSN.COM]
Sent: Friday, January 03, 2003 1:16 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Can you do this in one step?


Hello again and happy new year.

Yet another conundrum.  I have several groups of individuals; each
individual can make several transactions of differing amounts.  I want to
list the five individuals in each group with the highest transaction
totals.  I can do this in two steps, as follows (proc sql followed by data
step) :

proc sql;
  create table gp_ind_sum as select groupnum, idnum, sum(trans) as sumtrans
  from ds1
  group by groupnum, idnum
  order by groupnum, sumtrans desc
  ;
quit;

data gp_sumtrans_top5;
  set gp_ind_sum;
    by groupnum descending sumtrans;
  retain gpct gamtflag opflag 0;
  drop gpct gamtflag opflag;
  if first.sumtrans then do;
    gamtflag = 1;
    if first.groupnum then do;
      gpct = 0;
      opflag = 1;
    end;
  end;
  gpct + 1;
  if last.sumtrans then do;
    gamtflag = 0;
  end;
  if opflag then do;
    output;
    if gamtflag = 0 and gpct ge 5 then do;
      opflag = 0;
    end;
  end;
run;

I thought perhaps there was a way to do this all in the sql step?  Or
perhaps another one step method with proc means or univariate or...?

Thanks!

--  TMK  --