Panel data: Calculate the average by state every single year.

[Cal Bryan]

Hey Statalist,

I have a feeling that I am over-thinking how to go about this, but I need to re-calculate the yearly average of a variable every single year. Here is an example of what I want my data to look like after I've performed this operation, where "value" is the variable I'm calculating the average of:

input state year value value_ave
"TX" 2001 8 8
"TX" 2002 6 7
"TX" 2003 10 8
"TX" 2004 0 6
"TX" 2005 6 5
"CA" 2001 7 7
"CA" 2002 2 4.5
"CA" 2003 0 3
"CA" 2004 1 2.5
"CA" 2005 10 4
"FL" 2001 15 15
"FL" 2002 0 7.5
"FL" 2003 15 10
"FL" 2004 2 8
"FL" 2005 3 7

I know calculating rolling averages is fairly intuitive with panel data in Stata, but that's not what I want because I would like this to take into all data I have for each state going back to the beginning of the panel. Any suggestions are appreciated!

[Cal Bryan]

Arithmetic error on my part and missed my window to edit the post. I want the "value_ave" in 2005 in TX to be 6, not 5.

[Andrew Musau]

Assuming that you have a balanced panel, then it is simply the running sum divided by the observation number

bys state (year): g wanted= sum(value)/_n