Date:         Sun, 16 May 2010 17:02:33 +0000
Reply-To:     toby dunn <tobydunn@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         toby dunn <tobydunn@HOTMAIL.COM>
Subject:      Re: Average the Dups
Comments: To: stats112@gmail.com
In-Reply-To:  <AANLkTilLUWMDFrRhc_mh7hXquVsTBq88zVNm7wBAxMwc@mail.gmail.com>
Content-Type: text/plain; charset="iso-8859-1"

A DoW loop seems to do the trick at least for me and has the added benefit that one doesn't have to worry about reseting the cnt and sum vars for each value of ID:

 

Data Need ( Drop = Cnt Sum ) ;

Do I = 1 By 1 Until( Last.ID ) ;

  Set W ;

  By ID ;

  

  Cnt = Sum( Cnt , 1 ) ;

  Sum = Sum( Sum , X , 0 ) ;

 

  If ( Last.ID And Cnt = 1 ) Then Output ;

  Else If Last.Id Then Do ;

    X = Sum / Cnt ;

    Output ;

  End ;

End ;

 

Run ;



Toby Dunn 

"Don't bail. The best gold is at the bottom of barrels of crap." 
Randy Pausch
 
"Be prepared. Luck is where preparation meets opportunity." 
Randy Pausch



 
> Date: Sun, 16 May 2010 08:57:52 -0400
> From: stats112@GMAIL.COM
> Subject: Average the Dups
> To: SAS-L@LISTSERV.UGA.EDU
> 
> Hello:
> 
> I have a dataset that is
> 
> id x y
> 
> Every once in a while in the dataset there would be two consecutive rows
> with the same unique id. In cases of these duplicates, I would like to take
> the average of the two rows. How can I do this in SAS? For example the
> data would like this
> 
> 1 x1 y1
> 2 x2 y2
> 3 x3a y3a
> 3 x3b y3b
> 4 x4 y4
> .
> .
> .
> 74 x74a y74a
> 74 x74b y74b
> 
> .
> .
> .
> The final table would look like
> 1 x1 y1
> 2 x2 y2
> 3 Average(x3a,x3b) Average(y3a,y3b)
> 4 x4 y4
> .
> .
> .
> 74 Average(x74a,x74b) Average(y74a,y74b)
> 
> .
> .
> .
> Can we do this in a single datastep or PROC SQL? Which is faster?
                                          
_________________________________________________________________
Hotmail is redefining busy with tools for the New Busy. Get more from your inbox.
http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_2