```Date: Fri, 3 Jan 2003 15:31:49 -0800 Reply-To: "Huang, Ya" Sender: "SAS(r) Discussion" From: "Huang, Ya" Subject: Re: Can you do this in one step? Comments: To: Talbot Katz 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 -- ```

Back to: Top of message | Previous page | Main SAS-L page