reformat the data into panel (firm-year) data

[Helen Chang]

Hi,

I would like to merge the following data with panel data (firm/gvkey-year level). Is there a way to extract the earliest year of "facilitystartdate" and latest year of "facilityenddate" within each "packageid" in the data below and change it into panel data at the firm-year level?

For example, I need the original data to be changed to the following format (or something similar):
packageid gvkey dealamount fyear
100 . 50000000 1987
100 . 50000000 1988
100 . 50000000 1989
100 . 50000000 1990
100 . 50000000 1991
100 . 50000000 1992
100 . 50000000 1993
100 . 50000000 1994
100 . 50000000 1995
101 9362 18000000 1987
........
........


Here is the original data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input long packageid str10(facilitystartdate facilityenddate) float gvkey double dealamount
100 "09/12/1987" "09/01/1995"     .   50000000
100 "09/12/1987" "09/01/1995"     .   50000000
100 "09/12/1987" "09/01/1989"     .   50000000
100 "09/12/1987" "09/01/1995"     .   50000000
101 "08/05/1987" "11/05/1987"  9362   18000000
101 "08/05/1987" "11/05/1987"  9362   18000000
101 "08/05/1987" "11/05/1987"  9362   18000000
102 "09/12/1987" "01/12/1988"     .  3.700e+08
102 "09/12/1987" "11/01/1995"     .  3.700e+08
102 "09/12/1987" "11/01/1992"     .  3.700e+08
103 "09/01/1987" "09/01/1988"     .   10000000
104 "09/08/1987" "09/08/1988"     .   10000000
105 "09/01/1987" "09/01/1994" 12826 2.4145e+09
105 "09/01/1987" "09/01/1994" 12826 2.4145e+09
105 "09/01/1987" "09/01/1994" 12826 2.4145e+09
105 "09/01/1987" "09/01/1994" 12826 2.4145e+09
105 "09/01/1987" "09/01/1994" 12826 2.4145e+09
105 "09/01/1987" "09/01/1994" 12826 2.4145e+09
105 "09/01/1987" "09/01/1994" 12826 2.4145e+09
105 "09/01/1987" "09/01/1994" 12826 2.4145e+09
105 "09/01/1987" "09/01/1994" 12826 2.4145e+09
105 "09/01/1987" "09/01/1994" 12826 2.4145e+09
105 "09/01/1987" "09/01/1994" 12826 2.4145e+09
105 "09/01/1987" "09/01/1994" 12826 2.4145e+09
105 "09/01/1987" "09/01/1994" 12826 2.4145e+09
105 "09/01/1987" "09/01/1994" 12826 2.4145e+09
105 "09/01/1987" "09/01/1994" 12826 2.4145e+09
105 "09/01/1987" "09/01/1994" 12826 2.4145e+09
105 "09/01/1987" "09/01/1994" 12826 2.4145e+09
105 "09/01/1987" "09/01/1994" 12826 2.4145e+09
106 "10/01/1987" "10/01/1989"     .   10000000
107 "08/01/1987" "08/01/1988"  6300   10000000
108 "08/01/1987" "02/03/1988"  6300   15400000
109 "01/12/1987" "01/12/1988"     .     600000
110 "09/12/1987" "01/01/1989"  3321    1300000
111 "09/01/1987" "09/01/1990"  6005   80000000
111 "09/01/1987" "03/01/1988"  6005   80000000
112 "09/12/1987" "10/01/1997" 12332  1.900e+08
112 "09/12/1987" "10/01/1997" 12332  1.900e+08
112 "09/12/1987" "10/01/1997" 12332  1.900e+08
112 "09/12/1987" "10/01/1997" 12332  1.900e+08
112 "09/12/1987" "10/01/1997" 12332  1.900e+08
113 "10/01/1987" "10/01/1992" 15242   18000000
113 "10/01/1987" "10/01/1992" 15242   18000000
115 "09/02/1987" "10/01/1988"     .   45000000
115 "09/02/1987" "10/01/1988"     .   45000000
115 "09/02/1987" "10/01/1988"     .   45000000
115 "09/02/1987" "10/01/1988"     .   45000000
115 "09/02/1987" "10/01/1988"     .   45000000
115 "09/02/1987" "10/01/1988"     .   45000000
115 "09/02/1987" "10/01/1988"     .   45000000
115 "09/02/1987" "10/01/1988"     .   45000000
116 "09/01/1987" "04/01/1997"     .   29100000
117 "09/12/1987" "11/01/1992"  4931   52000000
117 "09/12/1987" "11/01/1990"  4931   52000000
118 "09/01/1987" "09/01/1992"  7479   16000000
119 "08/12/1987" "03/01/1989"  8215  2.475e+09
119 "08/12/1987" "03/01/1990"  8215  2.475e+09
119 "08/12/1987" "03/01/1990"  8215  2.475e+09
119 "08/12/1987" "03/01/1990"  8215  2.475e+09
119 "08/12/1987" "03/01/1989"  8215  2.475e+09
119 "08/12/1987" "03/01/1995"  8215  2.475e+09
119 "08/12/1987" "03/01/1995"  8215  2.475e+09
119 "08/12/1987" "03/01/1995"  8215  2.475e+09
119 "08/12/1987" "03/01/1990"  8215  2.475e+09
119 "08/12/1987" "03/01/1995"  8215  2.475e+09
119 "08/12/1987" "03/01/1990"  8215  2.475e+09
119 "08/12/1987" "03/01/1990"  8215  2.475e+09
119 "08/12/1987" "03/01/1990"  8215  2.475e+09
119 "08/12/1987" "03/01/1990"  8215  2.475e+09
119 "08/12/1987" "03/01/1990"  8215  2.475e+09
119 "08/12/1987" "03/01/1995"  8215  2.475e+09
119 "08/12/1987" "03/01/1990"  8215  2.475e+09
119 "08/12/1987" "03/01/1995"  8215  2.475e+09
119 "08/12/1987" "03/01/1990"  8215  2.475e+09
119 "08/12/1987" "03/01/1995"  8215  2.475e+09
119 "08/12/1987" "03/01/1995"  8215  2.475e+09
119 "08/12/1987" "03/01/1995"  8215  2.475e+09
119 "08/12/1987" "03/01/1990"  8215  2.475e+09
119 "08/12/1987" "03/01/1990"  8215  2.475e+09
119 "08/12/1987" "03/01/1995"  8215  2.475e+09
119 "08/12/1987" "03/01/1995"  8215  2.475e+09
119 "08/12/1987" "03/01/1995"  8215  2.475e+09
119 "08/12/1987" "03/01/1995"  8215  2.475e+09
119 "08/12/1987" "03/01/1990"  8215  2.475e+09
119 "08/12/1987" "03/01/1995"  8215  2.475e+09
119 "08/12/1987" "03/01/1990"  8215  2.475e+09
119 "08/12/1987" "03/01/1995"  8215  2.475e+09
119 "08/12/1987" "03/01/1995"  8215  2.475e+09
119 "08/12/1987" "03/01/1989"  8215  2.475e+09
119 "08/12/1987" "03/01/1995"  8215  2.475e+09
119 "08/12/1987" "03/01/1990"  8215  2.475e+09
119 "08/12/1987" "03/01/1990"  8215  2.475e+09
119 "08/12/1987" "03/01/1990"  8215  2.475e+09
119 "08/12/1987" "03/01/1995"  8215  2.475e+09
119 "08/12/1987" "03/01/1990"  8215  2.475e+09
119 "08/12/1987" "03/01/1990"  8215  2.475e+09
119 "08/12/1987" "03/01/1995"  8215  2.475e+09
119 "08/12/1987" "03/01/1990"  8215  2.475e+09
119 "08/12/1987" "03/01/1990"  8215  2.475e+09
end

Thank you very much for your help in advance!!

[Carlo Lazzaro]

Helen:
you may want to try:

.  split facilitystartdate, p(/)

.  split facilityenddate, p(/)

. drop facilitystartdate1 facilitystartdate2 facilityenddate1 facilityenddate2

. destring facilitystartdate3, g(num_start_year)

. destring facilityenddate3, g(num_end_year)

. bysort packageid (num_start_year num_end_year): g flag1=1 if _n==1

. bysort packageid (num_start_year num_end_year): replace flag1=2 if _n==_N & num_start_year!=num_end_year

. g time_var= num_start_year if flag1==1

. replace time_var= num_end_year if flag1==2

. keep if time_var!=.

. tsset packageid time_var

. tsfill

. drop facilitystartdate facilityenddate facilitystartdate3 num_start_year facilityenddate3 num_end_year flag1
. order time_var, after( packageid)

. list, sepby( packageid)

     +-----------------------------------------+
     | packag~d   time_var   gvkey   dealamo~t |
     |-----------------------------------------|
  1. |      100       1987       .    50000000 |
  2. |      100       1988       .           . |
  3. |      100       1989       .           . |
  4. |      100       1990       .           . |
  5. |      100       1991       .           . |
  6. |      100       1992       .           . |
  7. |      100       1993       .           . |
  8. |      100       1994       .           . |
  9. |      100       1995       .    50000000 |
     |-----------------------------------------|
 10. |      101       1987    9362    18000000 |
     |-----------------------------------------|
 11. |      102       1987       .   3.700e+08 |
 12. |      102       1988       .           . |
 13. |      102       1989       .           . |
 14. |      102       1990       .           . |
 15. |      102       1991       .           . |
 16. |      102       1992       .           . |
 17. |      102       1993       .           . |
 18. |      102       1994       .           . |
 19. |      102       1995       .   3.700e+08 |
     |-----------------------------------------|
 20. |      103       1988       .    10000000 |
     |-----------------------------------------|
 21. |      104       1988       .    10000000 |
     |-----------------------------------------|
 22. |      105       1987   12826   2.415e+09 |
 23. |      105       1988       .           . |
 24. |      105       1989       .           . |
 25. |      105       1990       .           . |
 26. |      105       1991       .           . |
 27. |      105       1992       .           . |
 28. |      105       1993       .           . |
 29. |      105       1994   12826   2.415e+09 |
     |-----------------------------------------|
 30. |      106       1989       .    10000000 |
     |-----------------------------------------|
 31. |      107       1988    6300    10000000 |
     |-----------------------------------------|
 32. |      108       1988    6300    15400000 |
     |-----------------------------------------|
 33. |      109       1988       .      600000 |
     |-----------------------------------------|
 34. |      110       1989    3321     1300000 |
     |-----------------------------------------|
 35. |      111       1987    6005    80000000 |
 36. |      111       1988       .           . |
 37. |      111       1989       .           . |
 38. |      111       1990    6005    80000000 |
     |-----------------------------------------|
 39. |      112       1987   12332   1.900e+08 |
 40. |      112       1988       .           . |
 41. |      112       1989       .           . |
 42. |      112       1990       .           . |
 43. |      112       1991       .           . |
 44. |      112       1992       .           . |
 45. |      112       1993       .           . |
 46. |      112       1994       .           . |
 47. |      112       1995       .           . |
 48. |      112       1996       .           . |
 49. |      112       1997   12332   1.900e+08 |
     |-----------------------------------------|
 50. |      113       1987   15242    18000000 |
 51. |      113       1988       .           . |
 52. |      113       1989       .           . |
 53. |      113       1990       .           . |
 54. |      113       1991       .           . |
 55. |      113       1992   15242    18000000 |
     |-----------------------------------------|
 56. |      115       1987       .    45000000 |
 57. |      115       1988       .    45000000 |
     |-----------------------------------------|
 58. |      116       1997       .    29100000 |
     |-----------------------------------------|
 59. |      117       1987    4931    52000000 |
 60. |      117       1988       .           . |
 61. |      117       1989       .           . |
 62. |      117       1990       .           . |
 63. |      117       1991       .           . |
 64. |      117       1992    4931    52000000 |
     |-----------------------------------------|
 65. |      118       1992    7479    16000000 |
     |-----------------------------------------|
 66. |      119       1987    8215   2.475e+09 |
 67. |      119       1988       .           . |
 68. |      119       1989       .           . |
 69. |      119       1990       .           . |
 70. |      119       1991       .           . |
 71. |      119       1992       .           . |
 72. |      119       1993       .           . |
 73. |      119       1994       .           . |
 74. |      119       1995    8215   2.475e+09 |
     +-----------------------------------------+

.

Caveat emptor: missing values are reported for -gvkey- and -dealamount-; perhaps you've planned to fix them via -merge- with the other dataset that you mentioned in your original post.

[William Lisowski]

I have a different approach than Carlo's.

// convert your string dates to SIF dates (see help datetime)
generate fsdate = daily(facilitystartdate,"MDY")
generate fedate = daily(facilityenddate,"MDY")
format fsdate fedate %td
drop facilitystartdate facilityenddate
order packageid fsdate fedate

// for each package, confirm gvkey and dealamount are constant
bysort packageid (gvkey): assert gvkey[_N]==gvkey[1]
bysort packageid (dealamount): assert dealamount[_N]==dealamount[1]

// earliest start and latest end dates
bysort packageid: egen minsdate = min(fsdate)
bysort packageid: egen maxedate = max(fedate)
format minsdate maxedate %td

// just keep one observation for each packageid
bysort packageid: keep if _n==1
drop fsdate fedate

// start year and end year
generate syear = yofd(minsdate)
generate eyear = yofd(maxedate)
list, clean

// expand to one observation per year
expand eyear-syear+1
bysort packageid: generate year = syear+_n-1
drop eyear syear minsdate maxedate

list if inlist(packageid,103,119), clean

. list, clean

       packag~d   gvkey   dealamo~t    minsdate    maxedate   syear   eyear  
  1.        100       .    50000000   12sep1987   01sep1995    1987    1995  
  2.        101    9362    18000000   05aug1987   05nov1987    1987    1987  
  3.        102       .   3.700e+08   12sep1987   01nov1995    1987    1995  
  4.        103       .    10000000   01sep1987   01sep1988    1987    1988  
  5.        104       .    10000000   08sep1987   08sep1988    1987    1988  
  6.        105   12826   2.415e+09   01sep1987   01sep1994    1987    1994  
  7.        106       .    10000000   01oct1987   01oct1989    1987    1989  
  8.        107    6300    10000000   01aug1987   01aug1988    1987    1988  
  9.        108    6300    15400000   01aug1987   03feb1988    1987    1988  
 10.        109       .      600000   12jan1987   12jan1988    1987    1988  
 11.        110    3321     1300000   12sep1987   01jan1989    1987    1989  
 12.        111    6005    80000000   01sep1987   01sep1990    1987    1990  
 13.        112   12332   1.900e+08   12sep1987   01oct1997    1987    1997  
 14.        113   15242    18000000   01oct1987   01oct1992    1987    1992  
 15.        115       .    45000000   02sep1987   01oct1988    1987    1988  
 16.        116       .    29100000   01sep1987   01apr1997    1987    1997  
 17.        117    4931    52000000   12sep1987   01nov1992    1987    1992  
 18.        118    7479    16000000   01sep1987   01sep1992    1987    1992  
 19.        119    8215   2.475e+09   12aug1987   01mar1995    1987    1995  

. 
. // expand to one observation per year
. expand eyear-syear+1
(79 observations created)

. bysort packageid: generate year = syear+_n-1

. drop eyear syear minsdate maxedate

. 
. list if inlist(packageid,103,119), clean

       packag~d   gvkey   dealamo~t   year  
 20.        103       .    10000000   1987  
 21.        103       .    10000000   1988  
 90.        119    8215   2.475e+09   1987  
 91.        119    8215   2.475e+09   1988  
 92.        119    8215   2.475e+09   1989  
 93.        119    8215   2.475e+09   1990  
 94.        119    8215   2.475e+09   1991  
 95.        119    8215   2.475e+09   1992  
 96.        119    8215   2.475e+09   1993  
 97.        119    8215   2.475e+09   1994  
 98.        119    8215   2.475e+09   1995

[Helen Chang]

Thank you very much, Carlo and William. The code you provided works perfectly