Mean by block of three years

[Martina Barbaglia]

Hi!
I'm Martina, and I'm using Stata17. I'd like to ask for your help in addressing the following matter.
I have a dataset of investment projects that occurred in a certain country, along with a series of information regarding the country itself (the gdp for the time period 2008-2013). Here are the variables: projectID, projectyear, country, gdp2008, gdp2009, gdp2010, gdp2011, gdp2012, gdp2013. projectID uniquely identifies observations in the dataset (each row is a project), and gdp2008-gdp2013 refer to the gdp of the country stored in the variable country.

Now, I'd like to create a new variable that equals the mean of the gdp for the three years prior the project itself. Put differently: project 1 occured in Italy in 2011 and I want to create a new variable that equals the mean of gdp2008, gdp2009 and gdp2010.
I tried to create a set of new variables for each block of three years (gdp2008_2010, gdp2009_2011 etc.) using rowmean, but for each project I'd like to keep just the one that refers to the three years prior projectyear. How can I do this?

Thank you for your help!

Martina

[George Ford]

stacking the data would make it easier.

but this might work.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte projectid int projectyear str1 country byte(gdp2008 gdp2009 gdp2010 gdp2011 gdp2012 gdp2013) float lag_gdp
1 2011 "A"  5  6  7  8  9 10  6
2 2012 "B" 10 11 12 13 14 15 12
3 2013 "C" 15 16 17 18 19 20 18
end

g lag_gdp = .
forv i = 2011/2013 {
    local a = `i'-1
    local b = `i'-2
    local c = `i'-3
    replace lag_gdp = (gdp`a' + gdp`b' + gdp`c')/3 if projectyear==`i'
}

[Nick Cox]

This picks up george Ford's helpful data example and hint that a long layout would be easier.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte projectid int projectyear str1 country byte(gdp2008 gdp2009 gdp2010 gdp2011 gdp2012 gdp2013) 
1 2011 "A"  5  6  7  8  9 10 
2 2012 "B" 10 11 12 13 14 15 
3 2013 "C" 15 16 17 18 19 20 
end

reshape long gdp, i(projectid) j(year)

bysort projectid : egen wanted = mean(cond(inrange(year, projectyear - 3, projectyear - 1), gdp, .)) 

list, sepby(projectid)

     +-----------------------------------------------------+
     | projec~d   year   projec~r   country   gdp   wanted |
     |-----------------------------------------------------|
  1. |        1   2008       2011         A     5        6 |
  2. |        1   2009       2011         A     6        6 |
  3. |        1   2010       2011         A     7        6 |
  4. |        1   2011       2011         A     8        6 |
  5. |        1   2012       2011         A     9        6 |
  6. |        1   2013       2011         A    10        6 |
     |-----------------------------------------------------|
  7. |        2   2008       2012         B    10       12 |
  8. |        2   2009       2012         B    11       12 |
  9. |        2   2010       2012         B    12       12 |
 10. |        2   2011       2012         B    13       12 |
 11. |        2   2012       2012         B    14       12 |
 12. |        2   2013       2012         B    15       12 |
     |-----------------------------------------------------|
 13. |        3   2008       2013         C    15       18 |
 14. |        3   2009       2013         C    16       18 |
 15. |        3   2010       2013         C    17       18 |
 16. |        3   2011       2013         C    18       18 |
 17. |        3   2012       2013         C    19       18 |
 18. |        3   2013       2013         C    20       18 |
     +-----------------------------------------------------+

.

See also Section 9 in https://journals.sagepub.com/doi/pdf...867X1101100210

[Martina Barbaglia]

Dear George and Nick,
now it works, thank you so much for your help!