Creating change variable with unbalanced panel

[Josh Rodrigues]

Hi everyone,

I have a variable called MHI which takes on a value between 0 and 100, it was only recorded in the survey every two years from 2000 to 2010 and then again in 2015. There are also some missing observations for some individuals where for example they have only answered in years 2000, 2004 and 2010.

I'm trying to create a variable dMHI that measures the change in MHI from it's last value, but I can't seem to figure out how to do it.

The years run from 1997 to 2016 and the panel is in long format.

Does anyone have any tips?

Apologies if this isn't clear enough - my first post.

Thank you in advance

[Nick Cox]

bysort id (time) : gen change = MHI -  MHI[_n-1]

should get you most of the way.

If there are further problems, then tell us what they are with a data example.

[Josh Rodrigues]

Hi Nick,

Thanks for the help, but for some reason it just created the change variable with all observations == .

I did something slightly long-winded, but it seems to be working now:

gen newmhi = mhi
replace newmhi = newmhi[_n-1] if newmhi == .

gen change = newmhi - newmhi[_n-1] if mhi != .

[Nick Cox]

That looks quite wrong as your code pays no attention to panel identifier and so some previous observations belong to different panels will be used in the calculation.

As said, post a data example to get better advice. If you don't know what that means, you haven't read the FAQ Advice yet.

[Josh Rodrigues]

You're quite right, I didn't take note of that.

Here's a data example using the code in your previous post:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(id year) byte(num_jobs wks_worked) int hours_worked long income int job byte(health nervous calm down happy depressed emo_limit level_emo_limit) float(wage ln_wage mhi change) byte(_est_fe _est_re)
1 1997 1 26  544      . 4760 1 . . . . . . .         .          .  . . 0 0
1 1998 1 52 1100    475 4760 1 . . . . . . .  .4318182  -.8397506  . . 0 0
1 1999 2 52  682      . 9990 1 . . . . . . .         .          .  . . 0 0
1 2000 1 53 1090   8000 4110 1 3 2 3 2 3 . .  7.339449   1.993264 65 . 1 1
1 2001 3 52 3045   7000 4110 2 . . . . . . .  .7662835 -.26620305  . . 0 0
1 2002 .  .    .   8000 4110 1 1 1 2 2 2 . .         .          . 40 . 0 0
1 2003 1 42 1050  15000 4760 2 . . . . . . . 14.285714    2.65926  . . 0 0
1 2004 1 52 2660      . 4760 1 1 1 1 1 1 . .         .          . 25 . 0 0
1 2005 1 53 3710  10000 4760 2 . . . . . . .  2.695418   .9915532  . . 0 0
1 2006 2 52 3765  80471 4760 1 1 1 1 1 1 . .  10.68672  2.3690019 25 . 1 1
1 2007 2 52 1875 112215  800 1 . . . . . . .    29.924   3.398661  . . 0 0
1 2008 1 52 1820  42500  800 2 3 2 3 2 3 . .  23.35165   3.150668 65 . 1 1
1 2009 1 52 1885  43500  800 1 . . . . . . . 23.076923   3.138833  . . 0 0
1 2010 1 52 2060  47000  800 1 3 2 3 2 3 3 3 22.815535   3.127442 65 . 1 1
1 2011 1 53 1935  50000  800 1 . . . . . . . 25.839794   3.251916  . . 0 0
1 2012 1 52 2080      .    . . . . . . . . .         .          .  . . 0 0
1 2013 1 52 2080  70000  800 2 . . . . . . . 33.653847   3.516127  . . 0 0
1 2014 1 52 2080      .    . . . . . . . . .         .          .  . . 0 0
1 2015 1 52 2080 120000  800 3 4 3 4 3 4 . .  57.69231   4.055124 90 . 1 1
1 2016 1  2   80      .    . . . . . . . . .         .          .  . . 0 0
end

[Nick Cox]

Thanks for the example. This may help:

keep id year mhi
bysort id (year) : gen previous = mhi[_n-1]
by id : replace previous = previous[_n-1] if missing(previous)
by id : gen change = mhi - previous

list

     +-------------------------------------+
     | id   year   mhi   previous   change |
     |-------------------------------------|
  1. |  1   1997     .          .        . |
  2. |  1   1998     .          .        . |
  3. |  1   1999     .          .        . |
  4. |  1   2000    65          .        . |
  5. |  1   2001     .         65        . |
     |-------------------------------------|
  6. |  1   2002    40         65      -25 |
  7. |  1   2003     .         40        . |
  8. |  1   2004    25         40      -15 |
  9. |  1   2005     .         25        . |
 10. |  1   2006    25         25        0 |
     |-------------------------------------|
 11. |  1   2007     .         25        . |
 12. |  1   2008    65         25       40 |
 13. |  1   2009     .         65        . |
 14. |  1   2010    65         65        0 |
 15. |  1   2011     .         65        . |
     |-------------------------------------|
 16. |  1   2012     .         65        . |
 17. |  1   2013     .         65        . |
 18. |  1   2014     .         65        . |
 19. |  1   2015    90         65       25 |
 20. |  1   2016     .         90        . |
     +-------------------------------------+

You meant missing values in #1, not missing observations. Also in Stata terms your example is balanced; there being missing values is not fatal to that.

[Josh Rodrigues]

Thanks Nick, that worked.

Really appreciate it!