Date:         Fri, 31 Jul 2009 14:09:39 -0500
Reply-To:     "Data _null_;" <iebupdte@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Data _null_;" <iebupdte@GMAIL.COM>
Subject:      Re: request help with SQL and summary stats
Comments: To: jd <doylejm@jmu.edu>
In-Reply-To:  <200907311902.n6VHP7qO011852@malibu.cc.uga.edu>
Content-Type: text/plain; charset=ISO-8859-1

data have;
   input ID  year X method;
   cards;
1   2000  0.9  1
1   2000  0.4  2
1   2001  0.5  1
1   2001  0.7  2
2   2000  1.2  1
2   2000  1.1  2
2   2001  0.3  1
2   2001  0.2  2
;;;;
   run;
proc summary data=have nway;
   class id year;
   output out=need(drop=_:)
      idgroup(max(x) out(x method)=);
   run;
proc print;
   run;

On 7/31/09, jd <doylejm@jmu.edu> wrote:
> I am trying to select observations based on the maximum value within a by
> group.  I have data such as:
>
> ID  year  X    method
> 1   2000  0.9  1
> 1   2000  0.4  2
> 1   2001  0.5  1
> 1   2001  0.7  2
> 2   2000  1.2  1
> 2   2000  1.1  2
> 2   2001  0.3  1
> 2   2001  0.2  2
>
> and I want to collapse it by picking out the largest X for each ID and
> year, but I want to carry along the value of "method" so it becomes
> ID  year maxX method
> 1   2000 0.9  1
> 1   2001 0.7  2
> 2   2000 1.2  1
> 2   2001 0.3  1
>
> I tried the following, but of course it remerges and doesn't collapse the
> data.
>
> proc sql ;
> create table tmp1
> as select ID, year, max(X) as maxX, method
> from mydata
> group by ID, year;
> quit;
>
> I also thought maybe I could use "method" to format the variable X, but
> got a bit lost in the sas help on proc format.
>
> I greatly appreciate any help. Thank you in advance.
> jd
>