Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Expanding 2 dates into a row per year

    Currently, I have a dataset with directors of whom I have their starting date and their end date at their current role. Where .c means that they are still working at the company in their role.

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input double DirectorID long(DateStartRole DateEndRole) str100 RoleName str256 DirectorName
    1310783 21701    .c "Chairman/President/CEO"       "Mike Roman"                       
     534445 19047 19121 "Chairman Elect/President/CEO" "Inge Thulin"                      
      33350 15802 19309 "Independent Director"         "Mike Eskew"                       
     330328 19449    .c "Independent Director"         "Muhtar Kent"                      
    1310783 21366 21701 "CEO"                          "Mike Roman"                                
    1292666 19946 21683 "Independent Director"         "Sondra Barbour"                   
      33719 19309 21312 "Independent Director"         "Doctor Vance Coffman"             
      33154 20486    .c "Independent Director"         "Pat Woertz"                       
     534445 19121 21366 "Chairman/President/CEO"       "Inge Thulin"                      
                 
    end
    format %td DateStartRole
    format %td DateEndRole
    What I want is to expand per DirectorID for every calendar year he or she is employed in their role with a new variable 'year'.
    For example, for the first DirectorID it would look like:
    DirectorID Year
    1310783 2019
    1310783 2020
    1310783 2021

    Of course, the other variables still added to each row.

    Kind regards,
    Hidde van Emmerik

  • #2
    Code:
    gen year = year(DateStartRole)
    gen year_ed = year(DateEndRole)
    * Use the system time to get the current year:
    replace year_ed = real(substr("$S_DATE",8,4)) if DateEndRole == .c
    expand year_ed - year + 1
    bysort DirectorID: replace year = year + _n - 1
    drop year_ed
    Last edited by Ken Chui; 17 May 2021, 07:36.

    Comment

    Working...
    X