# RE: RE: st: annual and 3years average

 From "Nick Cox" <[email protected]> To <[email protected]> Subject RE: RE: st: annual and 3years average Date Thu, 18 Sep 2008 12:00:40 +0100

I don't know what "did not work" means if you don't tell us.

In your code, you say -fiveyearaverage- but all calculations are based
on threes.
I guess that's just a typo.

In your code, you use a -while- loop when I would use -forvalues-, but
that's not a bug.

I presume that -year- and -yeara- are references to the same variable.

Otherwise, your code looks correct to me. However, I have not tested it.

Nick
[email protected]

Fabian Brenner

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
}

"Nick Cox" <[email protected]>

> 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
> [email protected]
>
> 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/