LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (March 2009, week 5)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Tue, 31 Mar 2009 12:52:00 -0500
Reply-To:     "./ ADD NAME=Data _null_;" <iebupdte@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "./ ADD NAME=Data _null_;" <iebupdte@GMAIL.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=ISO-8859-1

proc sql feedback nowarn; reset outobs=5; create table top5 as select * from assets order assets desc ; quit; run;

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


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