Statalist


[Date Prev][Date Next][Thread Prev][Thread Next][Date index][Thread index]

Re: RE: st: annual and 3years average


From   "Fabian Brenner" <Fabian.Brenner@gmx.de>
To   statalist@hsphsun2.harvard.edu
Subject   Re: RE: st: annual and 3years average
Date   Wed, 17 Sep 2008 21:42:50 +0200

Hi,

thank you very much your for your help, Nick. Besides "year" (1979-2006) there is a variable "group" (from 1-38) in my dataset. I want to create a three-year-average across groups and years. Variable "fiveyearaverage" should be the average across years (the three years before) for the group this observation belongs to.

My data look like the following:

"year"     "ROE"     "group"     "threeyearaverage" (for this group in the last three years)
1979         3             29                     ?
1979         4             17                     ?
1979         1             29                     ?
1980         4             9                       ?
...

I tried it like this but it did not work.

generate fiveyearaverage = .

local k = 1
while `k' <38 {
quietly forvalues y = 1979/2006 {
summarize ROE if yeara == `= `y' - 3' & group == `k', meanonly
local mean3 = return(mean)
summarize ROE if yeara == `= `y' - 2' & group == `k', meanonly
local mean2 = return(mean)
summarize ROE if yeara == `= `y' - 1' & group == `k', meanonly
replace fiveyearaverage = (`mean3' + `mean2' + r(mean)) / 3 if yeara == `y' & group == `k'
}
local k = `k' + 1
}


Fabian Brenner

-------- Original-Nachricht --------
> Datum: Wed, 17 Sep 2008 15:21:02 +0100
> Von: "Nick Cox" <n.j.cox@durham.ac.uk>
> An: statalist@hsphsun2.harvard.edu
> Betreff: RE: st: annual and 3years average

> I agree with Neil's suggestion of -egen, mean() by(year)- for yearly
> averages. 
> 
> For the average of the previous three years, there are several ways to
> do it. I don't see that Neil's solution takes into consideration that
> the periods should overlap. 
> 
> Here is one way to do it: 
> 
> gen threeyearaverage = . 
> 
> qui forval y = 1982/2006 { 
> 	local y1 = `y' - 3 
> 	local y2 = `y' - 1 	
> 	su ROE if inrange(year, `y1', `y2'), meanonly 
> 	replace threeyearaverage = r(mean) if year == `y' 
> } 
> 
> This is an average across observations, not years. If you want the
> latter, it would be 
> 
> gen threeyearaverage = . 
> 
> qui forval y = 1982/2006 { 
> 	su ROE if year == `= `y' - 3', meanonly  
> 	local mean3 = r(mean) 
> 	su ROE if year == `= `y' - 2', meanonly  
> 	local mean2 - r(mean) 
> 	su ROE if year == `= `y' - 1', meanonly 
> 	replace threeyearaverage = (`mean3' + `mean2' + r(mean)) / 3 if
> year == `y' 
> }
> 
> Another different way to do it would be to -collapse-, work on the
> collapsed dataset, and -merge- back in again. 
> 
> I'll put in here that -round(,)- can be useful in similar problems. 
> 
> Nick 
> n.j.cox@durham.ac.uk 
> 
> Neil Shephard
> 
> Fabian Brenner wrote:
> >
> > I have several observations called "ROE" for the "years" (from 1979 to
> 2006). There is a different number of observations for each year. 
> >
> > My data look like this:
> > "year"   "ROE"      "Average"   "threeyearaverage"
> >
> > 79           12               ?                       ?      
> > 79            9                ?                       ?
> > 79            2                ?                       ?
> > 80            3                ?                       ?
> > 81            20              ?                       ?
> > 81            5                ?                       ?
> > 82            3                ?                       ?
> > 82            6                ?                       ?
> > 82            9                ?                       ?
> > 82            8                ?                       ?
> > .               .                 .
> > .               .                 .
> > .               .                 .
> >
> > I want to compute the average of the observations for each year, e.g.
> for 1979: (12+9+2)/3 (I tried to sort the observations and to divide the
> sum by _n but it didn't work...)
> >   
> 
> bysort year : egen average = mean(ROE)
> > In a second step I want to get the average ROE for the past three
> years ("threeyearaverage") (beginning in 1982), e.g. for 1982 it should
> be the average of the ROE in 1979 plus the average ROE in 1980 plus
> average ROE in 1981, divided by 3.
> >   
> 
> Thats an inappropriate way of calculating the three year average, as it 
> fails to account for the fact that there are different numbers 
> observations from each year, thus the weights aren't equal.  This is 
> covered in most basic statistics books.  You therefore have two options,
> 
> a) use weights; b) use the raw data.  Since -egen newvar = mean()- 
> doesn't allow weights I'd be inclined to go with b).
> 
> You therefore need to generate a variable that bins your data...
> 
> gen year3 = .
> replace year3 = 1 if(year >= 79 & year <= 82)
> replace year3 = 2 if(year >= 83 & year <= 85)
> replace year3 = 3 if(year >= 86 & year <= 88)
> ....
> bysort year3 : egen threeyearaverage = mean(ROE)
> 
> 
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/statalist/faq
> *   http://www.ats.ucla.edu/stat/stata/

-- 
Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! 
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/



© Copyright 1996–2014 StataCorp LP   |   Terms of use   |   Privacy   |   Contact us   |   What's new   |   Site index