Creating a year variable out of two date variables

[Shirzaad Madgan]

I have two date variables in my dataset. They show the beginning and end dates of a person's leadership. My data is like this:

           
person             startdate         enddate
A                  28/12/2000    21/12/2004
B                  27/09/1962    05/11/1967
C                  17/11/1993    08/06/1998
D                  18/11/1958    01/11/1964
E                  02/04/2000    31/12/2004
F                  01/01/1996    31/12/2004

Now I want to create a year variable that shows each year in which a person has the leadership position. For example, for person A, the data should be like this:

person       Year
A             2000
A             2001
A             2002
A             2003
A             2004

I'd appreciate if anyone could help.

[Nick Cox]

Please use dataex to show your data example. As explained in FAQ Advice #12 this is especially important for date variables, as otherwise we have no idea whether your dates are string variables, numeric variables with date formats or even numeric variables with value labels. The code answers vary correspondingly.

[Shirzaad Madgan]

Please use dataex to show your data example. As explained in FAQ Advice #12 this is especially important for date variables, as otherwise we have no idea whether your dates are string variables, numeric variables with date formats or even numeric variables with value labels. The code answers vary correspondingly.

I used dataex here:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str37 leader str10(startdate enddate)
"A. Nastase" "28/12/2000" "21/12/2004"
"AL-Sallal"  "27/09/1962" "05/11/1967"
"Abacha"     "17/11/1993" "08/06/1998"
"Abboud"     "18/11/1958" "01/11/1964"
end

[Nick Cox]

Thanks for the example. Although the year information could be extracted as real(substr(startdate, -4, 4)) and so on, note here the use of daily() which could be taken further to produce numeric daily date variables.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str37 leader str10(startdate enddate)
"A. Nastase" "28/12/2000" "21/12/2004"
"AL-Sallal"  "27/09/1962" "05/11/1967"
"Abacha"     "17/11/1993" "08/06/1998"
"Abboud"     "18/11/1958" "01/11/1964"
end

foreach v in start end { 
    gen `v'year = year(daily(`v'date, "DMY")) 
}

gen toexpand = endyear - startyear + 1
expand toexpand 
bysort leader : gen year = startyear[1] + _n - 1 

list leader *year, sepby(leader)

     +----------------------------------------+
     |     leader   starty~r   endyear   year |
     |----------------------------------------|
  1. | A. Nastase       2000      2004   2000 |
  2. | A. Nastase       2000      2004   2001 |
  3. | A. Nastase       2000      2004   2002 |
  4. | A. Nastase       2000      2004   2003 |
  5. | A. Nastase       2000      2004   2004 |
     |----------------------------------------|
  6. |  AL-Sallal       1962      1967   1962 |
  7. |  AL-Sallal       1962      1967   1963 |
  8. |  AL-Sallal       1962      1967   1964 |
  9. |  AL-Sallal       1962      1967   1965 |
 10. |  AL-Sallal       1962      1967   1966 |
 11. |  AL-Sallal       1962      1967   1967 |
     |----------------------------------------|
 12. |     Abacha       1993      1998   1993 |
 13. |     Abacha       1993      1998   1994 |
 14. |     Abacha       1993      1998   1995 |
 15. |     Abacha       1993      1998   1996 |
 16. |     Abacha       1993      1998   1997 |
 17. |     Abacha       1993      1998   1998 |
     |----------------------------------------|
 18. |     Abboud       1958      1964   1958 |
 19. |     Abboud       1958      1964   1959 |
 20. |     Abboud       1958      1964   1960 |
 21. |     Abboud       1958      1964   1961 |
 22. |     Abboud       1958      1964   1962 |
 23. |     Abboud       1958      1964   1963 |
 24. |     Abboud       1958      1964   1964 |
     +----------------------------------------+

[Shirzaad Madgan]

Thank you very much. It worked perfectly.