Creating a new variable with the first observation

[Abdan Syakura]

Hi all,

I have panel data for the year 2000-2014. I want to create a new variable county2000 which takes the value of year 2000 from county x. If the observation in 2000 is missing, I want to use the next available observation (e.g. 2001). Can you help me with how to do it? The following is the data sample. Thank you for your help.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long id int year byte(state county)
 1758 2000 11  3
 1758 2001  .  .
 1758 2002  .  .
 1758 2003  .  .
 1758 2004  .  .
 1758 2005  .  .
 1758 2006  .  .
 1758 2007  .  .
 1758 2008  .  .
 1758 2009  .  .
 1758 2010  .  .
 1758 2011  .  .
 1758 2012  .  .
 1758 2013  .  .
 1758 2014  .  .
 1761 2000 11  5
 1761 2001 11  7
 1761 2002 11  7
 1761 2003 11  7
 1761 2004 11  7
 1761 2005 11 15
 1761 2006 11 15
 1761 2007 11 15
 1761 2008 11 15
 1761 2009 11 15
 1761 2010 11 15
 1761 2011 11 15
 1761 2012 11 15
 1761 2013 11 15
 1761 2014 11 15
49002 2000  .  .
49002 2001 12  7
49002 2002 12  7
49002 2003  .  .
49002 2004 12  7
49002 2005 12  7
49002 2006 12  7
49002 2007 12  7
49002 2008 12  7
49002 2009 12  7
49002 2010  .  .
49002 2011  .  .
49002 2012  .  .
49002 2013  .  .
49002 2014  .  .
end

Best regards,

Abdan

[Andrew Musau]

* Example generated by -dataex-. For more info, type help dataex
clear
input long id int year byte(state county)
 1758 2000 11  3
 1758 2001  .  .
 1758 2002  .  .
 1758 2003  .  .
 1758 2004  .  .
 1758 2005  .  .
 1758 2006  .  .
 1758 2007  .  .
 1758 2008  .  .
 1758 2009  .  .
 1758 2010  .  .
 1758 2011  .  .
 1758 2012  .  .
 1758 2013  .  .
 1758 2014  .  .
 1761 2000 11  5
 1761 2001 11  7
 1761 2002 11  7
 1761 2003 11  7
 1761 2004 11  7
 1761 2005 11 15
 1761 2006 11 15
 1761 2007 11 15
 1761 2008 11 15
 1761 2009 11 15
 1761 2010 11 15
 1761 2011 11 15
 1761 2012 11 15
 1761 2013 11 15
 1761 2014 11 15
49002 2000  .  .
49002 2001 12  7
49002 2002 12  7
49002 2003  .  .
49002 2004 12  7
49002 2005 12  7
49002 2006 12  7
49002 2007 12  7
49002 2008 12  7
49002 2009 12  7
49002 2010  .  .
49002 2011  .  .
49002 2012  .  .
49002 2013  .  .
49002 2014  .  .
end

bys id (year): gen tag= sum(sum(!missing(county)))==1 & year>=2000
by id: egen wanted= max(cond(tag, county, .))

Res.:

. l, sepby(id)

     +----------------------------------------------+
     |    id   year   state   county   tag   wanted |
     |----------------------------------------------|
  1. |  1758   2000      11        3     1        3 |
  2. |  1758   2001       .        .     0        3 |
  3. |  1758   2002       .        .     0        3 |
  4. |  1758   2003       .        .     0        3 |
  5. |  1758   2004       .        .     0        3 |
  6. |  1758   2005       .        .     0        3 |
  7. |  1758   2006       .        .     0        3 |
  8. |  1758   2007       .        .     0        3 |
  9. |  1758   2008       .        .     0        3 |
 10. |  1758   2009       .        .     0        3 |
 11. |  1758   2010       .        .     0        3 |
 12. |  1758   2011       .        .     0        3 |
 13. |  1758   2012       .        .     0        3 |
 14. |  1758   2013       .        .     0        3 |
 15. |  1758   2014       .        .     0        3 |
     |----------------------------------------------|
 16. |  1761   2000      11        5     1        5 |
 17. |  1761   2001      11        7     0        5 |
 18. |  1761   2002      11        7     0        5 |
 19. |  1761   2003      11        7     0        5 |
 20. |  1761   2004      11        7     0        5 |
 21. |  1761   2005      11       15     0        5 |
 22. |  1761   2006      11       15     0        5 |
 23. |  1761   2007      11       15     0        5 |
 24. |  1761   2008      11       15     0        5 |
 25. |  1761   2009      11       15     0        5 |
 26. |  1761   2010      11       15     0        5 |
 27. |  1761   2011      11       15     0        5 |
 28. |  1761   2012      11       15     0        5 |
 29. |  1761   2013      11       15     0        5 |
 30. |  1761   2014      11       15     0        5 |
     |----------------------------------------------|
 31. | 49002   2000       .        .     0        7 |
 32. | 49002   2001      12        7     1        7 |
 33. | 49002   2002      12        7     0        7 |
 34. | 49002   2003       .        .     0        7 |
 35. | 49002   2004      12        7     0        7 |
 36. | 49002   2005      12        7     0        7 |
 37. | 49002   2006      12        7     0        7 |
 38. | 49002   2007      12        7     0        7 |
 39. | 49002   2008      12        7     0        7 |
 40. | 49002   2009      12        7     0        7 |
 41. | 49002   2010       .        .     0        7 |
 42. | 49002   2011       .        .     0        7 |
 43. | 49002   2012       .        .     0        7 |
 44. | 49002   2013       .        .     0        7 |
 45. | 49002   2014       .        .     0        7 |
     +----------------------------------------------+