Hi,

I am trying to create a new column where it shows the average value of another column for each year, for example I want to create a column called average_benefit and I want it to show the average for each year. I have ran this code first:


gen ben_times_wieght = 0
gen sum_wieght = 0
gen average_benefit = 0

sort mainyear
forvalue i =2001/2017 {
replace ben_times_wieght = sum(attend_lcf * GS_NEWHH) if attend_lcf>0 & attend_lcf=. & mainyear == `i'
replace sum_wieght = sum(GS_NEWHH) if attend_lcf>0 & attend_lcf=. & mainyear == `i'
replace average_benefit = ben_times_wieght/sum_wieght if attend_lcf>0 & attend_lcf=. & mainyear == `i'
}

after running this code I expected that every "average_benefit" column with the same year will have the same value but instead it shows the average value of each row and the above row, as you can see in the pic below "capture".

but when I run this command I get the values I want as it shows in pic capture 2:

egen ben_times_wieght = sum($benefit * GS_NEWHH) if attend_lcf>0 & attend_lcf=. & & mainyear ==2001
egen sum_wieght = sum(GS_NEWHH) if attend_lcf>0 & attend_lcf=. & & mainyear ==2001
gen average_benefit = ben_times_wieght/sum_wieght if attend_lcf>0 & attend_lcf=. & mainyear ==2001


also I would like the average_benefit column to display the value of the average for every row with the same value, for example there are 1000 rows in the data i want them all to be filled in with the average value of each year, even if they dont follow (attend_lcf>0 & attend_lcf=.) if attend_lcf is 0 I want the column average_benefit to display the average value of that year,
so overall I would like there to be 17 average values for the 17 years

is there a way to do this or will I have to create new columns for each year average_benefit?

thanks