creating a variable that takes a value of the difference of the last non-missing value and the first non-missing value in panel data

[Olivia Johns]

i have panel time series.
I want to create a variable that is the difference between the last non-missing value and the first non-missing value.so in this case for Austria, the value would be 1 - 0 = "1".
I want to know the simplest code. I have been creating a variable that is the last value, and another variable that's the first value, and blah blah but i need to do this for so many variables, so i would that'd be a one liner command or something?

thank you!


"Australia" 193 1960 .
"Australia" 193 1961 .
"Australia" 193 1962 .
"Australia" 193 1963 .
"Australia" 193 1964 .
"Australia" 193 1965 .
"Australia" 193 1966 .
"Australia" 193 1967 .
"Australia" 193 1968 .
"Australia" 193 1969 .
"Australia" 193 1970 .
"Australia" 193 1971 .
"Australia" 193 1972 .
"Australia" 193 1973 .
"Australia" 193 1974 .
"Australia" 193 1975 .
"Australia" 193 1976 .
"Australia" 193 1977 .
"Australia" 193 1978 .
"Australia" 193 1979 .
"Australia" 193 1980 .
"Australia" 193 1981 .
"Australia" 193 1982 .
"Australia" 193 1983 .
"Australia" 193 1984 .
"Australia" 193 1985 .
"Australia" 193 1986 .
"Australia" 193 1987 .
"Australia" 193 1988 .
"Australia" 193 1989 .
"Australia" 193 1990 0
"Australia" 193 1991 -1
"Australia" 193 1992 -1
"Australia" 193 1993 -1
"Australia" 193 1994 -1
"Australia" 193 1995 -1
"Australia" 193 1996 0
"Australia" 193 1997 0
"Australia" 193 1998 0
"Australia" 193 1999 0
"Australia" 193 2000 0
"Australia" 193 2001 1
"Australia" 193 2002 0
"Australia" 193 2003 0
"Australia" 193 2004 0
"Australia" 193 2005 1
"Australia" 193 2006 0
"Australia" 193 2007 0
"Australia" 193 2008 0
"Australia" 193 2009 0
"Australia" 193 2010 -1
"Australia" 193 2011 -1
"Australia" 193 2012 -1
"Australia" 193 2013 0
"Australia" 193 2014 1
"Australia" 193 2015 .
"Australia" 193 2016 .
"Australia" 193 2017 .
"Australia" 193 2018 .
"Australia" 193 2019 .
"Australia" 193 2020 .
"Australia" 193 2021 .
"Austria" 122 1960 .
"Austria" 122 1961 .
"Austria" 122 1962 .
"Austria" 122 1963 .
"Austria" 122 1964 .
"Austria" 122 1965 .
"Austria" 122 1966 .
"Austria" 122 1967 .
"Austria" 122 1968 .
"Austria" 122 1969 .
"Austria" 122 1970 .
"Austria" 122 1971 .
"Austria" 122 1972 .
"Austria" 122 1973 .
"Austria" 122 1974 .
"Austria" 122 1975 .
"Austria" 122 1976 .
"Austria" 122 1977 .
"Austria" 122 1978 .
"Austria" 122 1979 .
"Austria" 122 1980 .
"Austria" 122 1981 .
"Austria" 122 1982 .
"Austria" 122 1983 .
"Austria" 122 1984 .
"Austria" 122 1985 .
"Austria" 122 1986 .
"Austria" 122 1987 .
"Austria" 122 1988 .
"Austria" 122 1989 .
"Austria" 122 1990 0
"Austria" 122 1991 0
"Austria" 122 1992 0
"Austria" 122 1993 0
"Austria" 122 1994 0
"Austria" 122 1995 0
"Austria" 122 1996 0
"Austria" 122 1997 0

[Clyde Schechter]

If there is a simpler way to do this than calculating the first and last non-missing values and then subtracting them, I would be delighted to see somebody else post it. But I don't think there is one. If you have to do this for many variables, the solution will be to take your existing code for one variable and putting it in a loop using -foreach-.

Added: to be clear, I can think of a way to get this result without calculating the first and last non-missing values and creating new variables to hold them--but the code is no simpler or shorter than the code I would write that does so.

It would be useful to have a usable example of your data to craft code for this. A screenshot is not helpful--please read the Forum FAQ to see the numerous reasons why Forum members are specifically asked not to use them. The helpful way to show example data is with the -dataex- command.

If you are running version 15.1 or a fully updated version 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Olivia Johns]

I have modified the data!

[David Benson]

Olivia,

It is *a lot* easier for people to help you if you share your data using Stata's dataex command. If you need help using dataex, please take a look at the video tutorial here.

Also, for the other variables for which you want to calculate this (last - first non-missing) will they be missing for the same years for that country, or will you need to calculate different first_year & last_yr for each of those variables. It would seem like one you got the syntax down, it would be fairly straightforward to create a loop to calculate it for each of those variables. (And to use temp_vars for the various first_yr / last_yr combos (so you could easily just delete them at the end)

datex country country_id year amount  // Data shared using -dataex-. To install: ssc install dataex
clear
input str9 country int(country_id year) byte amount
"Australia" 193 1960  .
"Australia" 193 1961  .
"Australia" 193 1962  .
"Australia" 193 1963  .
"Australia" 193 1964  .
"Australia" 193 1965  .
"Australia" 193 1966  .
"Australia" 193 1967  .
"Australia" 193 1968  .
"Australia" 193 1969  .
"Australia" 193 1970  .
"Australia" 193 1971  .
"Australia" 193 1972  .
"Australia" 193 1973  .
"Australia" 193 1974  .
"Australia" 193 1975  .
"Australia" 193 1976  .
"Australia" 193 1977  .
"Australia" 193 1978  .
"Australia" 193 1979  .
"Australia" 193 1980  .
"Australia" 193 1981  .
"Australia" 193 1982  .
"Australia" 193 1983  .
"Australia" 193 1984  .
"Australia" 193 1985  .
"Australia" 193 1986  .
"Australia" 193 1987  .
"Australia" 193 1988  .
"Australia" 193 1989  .
"Australia" 193 1990  0
"Australia" 193 1991 -1
"Australia" 193 1992 -1
"Australia" 193 1993 -1
"Australia" 193 1994 -1
"Australia" 193 1995 -1
"Australia" 193 1996  0
"Australia" 193 1997  0
"Australia" 193 1998  0
"Australia" 193 1999  0
"Australia" 193 2000  0
"Australia" 193 2001  1
"Australia" 193 2002  0
"Australia" 193 2003  0
"Australia" 193 2004  0
"Australia" 193 2005  1
"Australia" 193 2006  0
"Australia" 193 2007  0
"Australia" 193 2008  0
"Australia" 193 2009  0
"Australia" 193 2010 -1
"Australia" 193 2011 -1
"Australia" 193 2012 -1
"Australia" 193 2013  0
"Australia" 193 2014  1
"Australia" 193 2015  .
"Australia" 193 2016  .
"Australia" 193 2017  .
"Australia" 193 2018  .
"Australia" 193 2019  .
"Australia" 193 2020  .
"Australia" 193 2021  .
"Austria"   122 1960  .
"Austria"   122 1961  .
"Austria"   122 1962  .
"Austria"   122 1963  .
"Austria"   122 1964  .
"Austria"   122 1965  .
"Austria"   122 1966  .
"Austria"   122 1967  .
"Austria"   122 1968  .
"Austria"   122 1969  .
"Austria"   122 1970  .
"Austria"   122 1971  .
"Austria"   122 1972  .
"Austria"   122 1973  .
"Austria"   122 1974  .
"Austria"   122 1975  .
"Austria"   122 1976  .
"Austria"   122 1977  .
"Austria"   122 1978  .
"Austria"   122 1979  .
"Austria"   122 1980  .
"Austria"   122 1981  .
"Austria"   122 1982  .
"Austria"   122 1983  .
"Austria"   122 1984  .
"Austria"   122 1985  .
"Austria"   122 1986  .
"Austria"   122 1987  .
"Austria"   122 1988  .
"Austria"   122 1989  .
"Austria"   122 1990  0
"Austria"   122 1991  0
"Austria"   122 1992  0
"Austria"   122 1993  0
"Austria"   122 1994  0
"Austria"   122 1995  0
"Austria"   122 1996  0
"Austria"   122 1997  0
end

------------------ copy up to and including the previous line ------------------

bysort country_id has_data (year): gen n = _n
gen has_data = 1 if amount!=.   // note this leaves has_data==. if amount==.
bysort country_id (country has_data year): gen first_yr = year[1]
gsort country_id country has_data -year
by country_id: gen last_yr = year[1]

* This 1 line may be all you need
bysort country_id has_data (year): gen diff = amount[_N] - amount[1] if _n==1

sort country_id country year
list country_id country year has_data amount diff first_yr last_yr , sepby( country_id has_data )

     +-----------------------------------------------------------------------------+
     | countr~d     country   year   has_data   amount   diff   first_yr   last_yr |
     |-----------------------------------------------------------------------------|
  1. |      122     Austria   1960          .        .      .       1990      1997 |
  2. |      122     Austria   1961          .        .      .       1990      1997 |
  3. |      122     Austria   1962          .        .      .       1990      1997 |
  4. |      122     Austria   1963          .        .      .       1990      1997 |
  5. |      122     Austria   1964          .        .      .       1990      1997 |
  6. |      122     Austria   1965          .        .      .       1990      1997 |
  7. |      122     Austria   1966          .        .      .       1990      1997 |
  8. |      122     Austria   1967          .        .      .       1990      1997 |
  9. |      122     Austria   1968          .        .      .       1990      1997 |
 10. |      122     Austria   1969          .        .      .       1990      1997 |
 11. |      122     Austria   1970          .        .      .       1990      1997 |
 12. |      122     Austria   1971          .        .      .       1990      1997 |
 13. |      122     Austria   1972          .        .      .       1990      1997 |
 14. |      122     Austria   1973          .        .      .       1990      1997 |
 15. |      122     Austria   1974          .        .      .       1990      1997 |
 16. |      122     Austria   1975          .        .      .       1990      1997 |
 17. |      122     Austria   1976          .        .      .       1990      1997 |
 18. |      122     Austria   1977          .        .      .       1990      1997 |
 19. |      122     Austria   1978          .        .      .       1990      1997 |
 20. |      122     Austria   1979          .        .      .       1990      1997 |
 21. |      122     Austria   1980          .        .      .       1990      1997 |
 22. |      122     Austria   1981          .        .      .       1990      1997 |
 23. |      122     Austria   1982          .        .      .       1990      1997 |
 24. |      122     Austria   1983          .        .      .       1990      1997 |
 25. |      122     Austria   1984          .        .      .       1990      1997 |
 26. |      122     Austria   1985          .        .      .       1990      1997 |
 27. |      122     Austria   1986          .        .      .       1990      1997 |
 28. |      122     Austria   1987          .        .      .       1990      1997 |
 29. |      122     Austria   1988          .        .      .       1990      1997 |
 30. |      122     Austria   1989          .        .      .       1990      1997 |
     |-----------------------------------------------------------------------------|
 31. |      122     Austria   1990          1        0      0       1990      1997 |
 32. |      122     Austria   1991          1        0      .       1990      1997 |
 33. |      122     Austria   1992          1        0      .       1990      1997 |
 34. |      122     Austria   1993          1        0      .       1990      1997 |
 35. |      122     Austria   1994          1        0      .       1990      1997 |
 36. |      122     Austria   1995          1        0      .       1990      1997 |
 37. |      122     Austria   1996          1        0      .       1990      1997 |
 38. |      122     Austria   1997          1        0      .       1990      1997 |
     |-----------------------------------------------------------------------------|
 39. |      193   Australia   1960          .        .      .       1990      2014 |
 40. |      193   Australia   1961          .        .      .       1990      2014 |
 41. |      193   Australia   1962          .        .      .       1990      2014 |
 42. |      193   Australia   1963          .        .      .       1990      2014 |
 43. |      193   Australia   1964          .        .      .       1990      2014 |
 44. |      193   Australia   1965          .        .      .       1990      2014 |
 45. |      193   Australia   1966          .        .      .       1990      2014 |
 46. |      193   Australia   1967          .        .      .       1990      2014 |
 47. |      193   Australia   1968          .        .      .       1990      2014 |
 48. |      193   Australia   1969          .        .      .       1990      2014 |
 49. |      193   Australia   1970          .        .      .       1990      2014 |
 50. |      193   Australia   1971          .        .      .       1990      2014 |
 51. |      193   Australia   1972          .        .      .       1990      2014 |
 52. |      193   Australia   1973          .        .      .       1990      2014 |
 53. |      193   Australia   1974          .        .      .       1990      2014 |
 54. |      193   Australia   1975          .        .      .       1990      2014 |
 55. |      193   Australia   1976          .        .      .       1990      2014 |
 56. |      193   Australia   1977          .        .      .       1990      2014 |
 57. |      193   Australia   1978          .        .      .       1990      2014 |
 58. |      193   Australia   1979          .        .      .       1990      2014 |
 59. |      193   Australia   1980          .        .      .       1990      2014 |
 60. |      193   Australia   1981          .        .      .       1990      2014 |
 61. |      193   Australia   1982          .        .      .       1990      2014 |
 62. |      193   Australia   1983          .        .      .       1990      2014 |
 63. |      193   Australia   1984          .        .      .       1990      2014 |
 64. |      193   Australia   1985          .        .      .       1990      2014 |
 65. |      193   Australia   1986          .        .      .       1990      2014 |
 66. |      193   Australia   1987          .        .      .       1990      2014 |
 67. |      193   Australia   1988          .        .      .       1990      2014 |
 68. |      193   Australia   1989          .        .      .       1990      2014 |
     |-----------------------------------------------------------------------------|
 69. |      193   Australia   1990          1        0      1       1990      2014 |
 70. |      193   Australia   1991          1       -1      .       1990      2014 |
 71. |      193   Australia   1992          1       -1      .       1990      2014 |
 72. |      193   Australia   1993          1       -1      .       1990      2014 |
 73. |      193   Australia   1994          1       -1      .       1990      2014 |
 74. |      193   Australia   1995          1       -1      .       1990      2014 |
 75. |      193   Australia   1996          1        0      .       1990      2014 |
 76. |      193   Australia   1997          1        0      .       1990      2014 |
 77. |      193   Australia   1998          1        0      .       1990      2014 |
 78. |      193   Australia   1999          1        0      .       1990      2014 |
 79. |      193   Australia   2000          1        0      .       1990      2014 |
 80. |      193   Australia   2001          1        1      .       1990      2014 |
 81. |      193   Australia   2002          1        0      .       1990      2014 |
 82. |      193   Australia   2003          1        0      .       1990      2014 |
 83. |      193   Australia   2004          1        0      .       1990      2014 |
 84. |      193   Australia   2005          1        1      .       1990      2014 |
 85. |      193   Australia   2006          1        0      .       1990      2014 |
 86. |      193   Australia   2007          1        0      .       1990      2014 |
 87. |      193   Australia   2008          1        0      .       1990      2014 |
 88. |      193   Australia   2009          1        0      .       1990      2014 |
 89. |      193   Australia   2010          1       -1      .       1990      2014 |
 90. |      193   Australia   2011          1       -1      .       1990      2014 |
 91. |      193   Australia   2012          1       -1      .       1990      2014 |
 92. |      193   Australia   2013          1        0      .       1990      2014 |
 93. |      193   Australia   2014          1        1      .       1990      2014 |
     |-----------------------------------------------------------------------------|
 94. |      193   Australia   2015          .        .      .       1990      2014 |
 95. |      193   Australia   2016          .        .      .       1990      2014 |
 96. |      193   Australia   2017          .        .      .       1990      2014 |
 97. |      193   Australia   2018          .        .      .       1990      2014 |
 98. |      193   Australia   2019          .        .      .       1990      2014 |
 99. |      193   Australia   2020          .        .      .       1990      2014 |
100. |      193   Australia   2021          .        .      .       1990      2014 |
     +-----------------------------------------------------------------------------+

Note that I left has_data==. if amount==. That made it easier to sort (has_data==1 comes before has_data==.), but you probably won't want to leave it that way.

[Nick Cox]

I would usually segregate the missings at the highest level, something like this, riffing on the theme:

gen byte OK = !missing(amount) 
bysort OK country_id (year) : gen first = amount[1] if OK 
by OK country_id: gen last = amount[_N] if OK 
by OK country_id: gen when_first = year[1] if OK 
by OK country_id: gen when_last = year[_N] if OK 
gen diff = last - first 
bysort country_id (diff) : replace diff = diff[1]

[Andrew Musau]

You could also utilize the panel identifier

gen tag= country if !missing(amount)
bys tag (year): gen d= amount[_N] - amount[1]
bys country: egen diff= max(d)