Replace missing values with previous values plus one/forward value minus one

[Natasha Drud Bendsen]

Hello,

How can I replace missing values with previous values + 1 or forward values - 1? I would like to fill out the below data set with the correct ages (age) per individual (id).

If I have data that looks like this:

id	age	year
1	20	2001
1	.	2002
1	22	2003
1	23	2004
2	.	2001
2	56	2002
2	57	2003
3	42	2001
3	.	2002
3	44	2003

I have tried something like:
bysort id: replace age = age[_n-1] + 1 if age=.
bysort id: replace age = age[_n+1] - 1 if age=.

Thank you,
Natasha

[Julia Brown]

Hi Natasha,

You need to make sure the missing values stay in the same place when applying -sort-.

Try:
sort id year
by id : replace age = age[_n-1]+1 if age==. & _n!=1
by id : replace age = age[_n+1]-1 if age==. & _n==1

If that doesn't do what you need, then you'll need to provide us with more information about what is going wrong so we can help you troubleshoot.

Warmly,
Julia

[Nick Cox]

An FAQ at https://www.stata.com/support/faqs/d...issing-values/ applies, except that what you want is just linear interpolation. It's cautious practice to generate a new variable before deciding that you can use it to replace the existing variable.

Please note the use of dataex which is a little more direct than showing a data example as you did.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(id age) int year
1 20 2001
1  . 2002
1 22 2003
1 23 2004
2  . 2001
2 56 2002
2 57 2003
3 42 2001
3  . 2002
3 44 2003
end

ipolate age year, by(id) gen(age2) epolate

list 

     +------------------------+
     | id   age   year   age2 |
     |------------------------|
  1. |  1    20   2001     20 |
  2. |  1     .   2002     21 |
  3. |  1    22   2003     22 |
  4. |  1    23   2004     23 |
  5. |  2     .   2001     55 |
     |------------------------|
  6. |  2    56   2002     56 |
  7. |  2    57   2003     57 |
  8. |  3    42   2001     42 |
  9. |  3     .   2002     43 |
 10. |  3    44   2003     44 |
     +------------------------+

[Carlo Lazzaro]

Natasha:
your -if- qualifier cannot work as you omitted an -=- sign (see Julia's correct code).