Long format data: how to copy values from one observation to another

[Sophia Garkov]

Hi,

I have data identified by postcode (string) and year (2002 to 2017). Postcodes are sometimes repeated, as they pertain to multiple years and multiple individuals (string). I have road noise exposure data for 2012 and 2017, which was estimated at postcode level. I want to fill in missing noise data from 2002 to 2011 with the 2012 noise data, and the 2013 to 2016 missing noise data with an average of 2012 and 2017 noise exposure data. How can I do this in Stata?

Thanks so much.

Sophia

[Nick Cox]

For the second problem, I recommend instead linear interpolation using ipolate. The first problem is more contentious still.

[Bruce Weaver]

Hello Sophia Garkov, and welcome to Statalist. What do you intend to do after fill in the missing values? I ask because I wonder if your question is an example of the XY problem that is mentioned in the Statalist FAQ. Thanks for clarifying.

[Sophia Garkov]

Thanks Nick for your suggestion. I found this command, but I am unsure which variables are relevant to be inserted into "ipolate y1 x, generate(y2)". Do you have any suggestions?

Thanks Bruce for your reply. I am conducting a nationwide analysis of the effects of air pollution from 2002-2017 on educational attainment outcomes in children. Noise will be a potential covariate.

Thanks again both!

[Nick Cox]

. clear

. set obs 16
number of observations (_N) was 0, now 16

. gen year = 2001 + _n

. gen postcode = "AB12 99ZZ"

. gen noise = cond(year == 2012, 42, cond(year == 2017, 47, .))
(14 missing values generated)

. ipolate noise year , by(postcode) gen(wanted)
(10 missing values generated)

. list

     +-----------------------------------+
     | year    postcode   noise   wanted |
     |-----------------------------------|
  1. | 2002   AB12 99ZZ       .        . |
  2. | 2003   AB12 99ZZ       .        . |
  3. | 2004   AB12 99ZZ       .        . |
  4. | 2005   AB12 99ZZ       .        . |
  5. | 2006   AB12 99ZZ       .        . |
     |-----------------------------------|
  6. | 2007   AB12 99ZZ       .        . |
  7. | 2008   AB12 99ZZ       .        . |
  8. | 2009   AB12 99ZZ       .        . |
  9. | 2010   AB12 99ZZ       .        . |
 10. | 2011   AB12 99ZZ       .        . |
     |-----------------------------------|
 11. | 2012   AB12 99ZZ      42       42 |
 12. | 2013   AB12 99ZZ       .       43 |
 13. | 2014   AB12 99ZZ       .       44 |
 14. | 2015   AB12 99ZZ       .       45 |
 15. | 2016   AB12 99ZZ       .       46 |
     |-----------------------------------|
 16. | 2017   AB12 99ZZ      47       47 |
     +-----------------------------------+