calculating a variable that contain startdate* and enddate

[hussein bataineh]

Hi, I need to calculate the Sustainability Committee variable, which is a dummy variable indicating whether a firm had a specialized board committee on sustainability-related issues in a given year (coded as 1) and 0 otherwsie
I have data showing the directors serving on committees within a firm each director have ( IndividualIDdirectorid ) . For each director, I have startdate and enddate shows the period of his service on the committee. If at least one of these directors was present during a given year , then the firm is considered to have a sustainability committee for that year (coded as 1).otherwise 0 My approach:

1. Create a panel dataset for each firm (ticker) covering the years 2004–2022.
2. For each year in this period, check if any director served on a sustainability-related committee during that year (i.e., their start date ≤ year ≤ end date).
3. If at least one director was present, code Sustainability Committee = 1 for that firm-year; otherwise, code it as 0.
4. I'M NOT SURE HOW I CAN CODE THIS IN STATA. CAN YOU HELP ME, PLEASE?

 CODE]
* Example generated by -dataex-. For more info, type help dataex
clear
input str5 ticker str69 CommitteeNamecommitteename str49 IndividualNamedirectorname str10 datestartrole long CompanyIDboardid str10 dateendrole long IndividualIDdirectorid float(startdate startyear startmonth enddate endtyear)
"A"    "Environmental Health and Safety"                       "David Galloway"                         "01/02/2006"     483 "10/29/2007"   58087 16803 2006  1   17468 2007
"A"    "Environmental Health and Safety"                       "Jean Gaulin"                            "04/23/2003"     483 "04/29/2004"  140930 15818 2003  4   16190 2004
"A"    "Environmental Health and Safety"                       "Dong-Kil Cho"                           "01/01/2003"     483 "04/27/2005"  330691 15706 2003  1   16553 2005
"A"    "Environmental Health and Safety"                       "Lise Lachapelle"                        "04/29/2004"     483 "10/29/2007"  330693 16190 2004  4   17468 2007
"A"    "Environmental Health and Safety"                       "Gary Lukassen"                          "04/23/2003"     483 "10/29/2007"  330694 15818 2003  4   17468 2007
"A"    "Environmental Health and Safety"                       "David Ward"                             "01/01/2003"     483 "05/28/2006"  330698 15706 2003  1   16949 2006
"A"    "Environmental Health and Safety"                       "M Bougie"                               "10/20/2004"     483 "12/31/2006"  331030 16364 2004 10   17166 2006
"A"    "Environmental Health and Safety"                       "John Anderson"                          "01/02/2006"     483 "10/29/2007"  489439 16803 2006  1   17468 2007
"AAL"  "Corporate Governance and Public Responsibility"        "Denise O'Leary"                         "07/22/2020" 2021732 "02/22/2021"   33848 22118 2020  7   22333 2021
"AAL"  "Corporate Governance Public Responsibility and Safety" "Jim Albaugh"                            "06/09/2021" 2021732 "05/10/2023"   34603 22440 2021  6   23140 2023
"AAL"  "Corporate Governance and Public Responsibility"        "Jim Albaugh"                            "07/22/2020" 2021732 "06/09/2021"   34603 22118 2020  7   22440 2021
"AAL"  "Corporate Governance and Public Responsibility"        "John Cahill"                            "07/22/2020" 2021732 "06/09/2021"   55426 22118 2020  7   22440 2021
"AAL"  "Corporate Governance Public Responsibility and Safety" "John Cahill"                            "06/09/2021" 2021732 "12/28/2023"   55426 22440 2021  6   23372 2023
"AAL"  "Corporate Governance Public Responsibility and Safety" "Sue Kronick"                            "06/09/2021" 2021732 "12/28/2023"   55687 22440 2021  6   23372 2023
"AAL"  "Corporate Governance and Public Responsibility"        "Sue Kronick"                            "07/22/2020" 2021732 "06/09/2021"   55687 22118 2020  7   22440 2021
"AAL"  "Corporate Governance and Public Responsibility"        "Sue Kronick"                            "01/01/2023" 2021732 "01/01/9000"   55687 23011 2023  1 2571308 9000
"AAL"  "Corporate Governance and Public Responsibility"        "Ray Robinson"                           "07/22/2020" 2021732 "06/09/2021"  106247 22118 2020  7   22440 2021
"AAL"  "Corporate Governance Public Responsibility and Safety" "Ray Robinson"                           "06/09/2021" 2021732 "05/10/2023"  106247 22440 2021  6   23140 2023
"AAL"  "Corporate Governance and Public Responsibility"        "Jeff Benjamin"                          "01/01/2023" 2021732 "06/05/2024"  202241 23011 2023  1   23532 2024
"AAL"  "Corporate Governance and Public Responsibility"        "Adriane Brown"                          "02/22/2021" 2021732 "06/09/2021"  485166 22333 2021  2   22440 2021
"AAL"  "Corporate Governance Public Responsibility and Safety" "Adriane Brown"                          "06/09/2021" 2021732 "12/28/2023"  485166 22440 2021  6   23372 2023
"AAL"  "Corporate Governance and Public Responsibility"        "Marty Nesbitt"                          "01/01/2023" 2021732 "01/01/9000" 1070916 23011 2023  1 2571308 9000
"AAL"  "Corporate Governance Public Responsibility and Safety" "Greg Smith"                             "01/18/2022" 2021732 "12/28/2023" 1072987 22663 2022  1   23372 2023
"AAL"  "Corporate Governance and Public Responsibility"        "Vicente Reynal"                         "01/01/2023" 2021732 "01/01/9000" 1912562 23011 2023  1 2571308 9000
"ABBV" "Public Policy and Sustainability"                      "Glenn Tilton"                           "05/03/2019" 1921326 "05/03/2024"   33296 21672 2019  5   23499 2024
"ABBV" "Public Policy and Sustainability"                      "Rebecca Roberts"                        "05/04/2018" 1921326 "01/01/9000"   42986 21308 2018  5 2571308 9000
"ABBV" "Public Policy and Sustainability"                      "Professor Doctor Robert Alpern"         "01/02/2013" 1921326 "01/01/9000"  755263 19360 2013  1 2571308 9000
"ABBV" "Public Policy and Sustainability"                      "Brett Hart"                             "05/03/2019" 1921326 "05/03/2024"  891538 21672 2019  5   23499 2024
"ABBV" "Public Policy and Sustainability"                      "Melody Meyer"                           "05/05/2017" 1921326 "05/03/2024"  894107 20944 2017  5   23499 2024
"ABBV" "Public Policy and Sustainability"                      "Doctor Sue Quaggin"                     "10/11/2023" 1921326 "01/01/9000" 1866955 23294 2023 10 2571308 9000
"ACN"  "Nominating Governance and Sustainability"              "Arun Sarin"                             "10/01/2021"     573 "01/01/9000"   11523 22554 2021 10 2571308 9000
"ACN"  "Nominating Governance and Sustainability"              "Nancy McKinstry"                        "10/01/2021"     573 "01/01/9000"   25615 22554 2021 10 2571308 9000
"ACN"  "Nominating Governance and Sustainability"              "Gilles Pélisson"                       "10/01/2021"     573 "01/01/9000"   28079 22554 2021 10 2571308 9000
"ACN"  "Nominating Governance and Sustainability"              "Jaime Ardila Gómez"                    "07/12/2021"     573 "01/01/9000"  552615 22473 2021  7 2571308 9000
"ACN"  "Nominating Governance and Sustainability"              "Alan Jope"                              "04/14/2023"     573 "01/01/9000" 1192713 23114 2023  4 2571308 9000
"ADBE" "Governance and Sustainability"                         "Dan Rosensweig"                         "04/20/2021"     808 "01/01/9000"   55271 22390 2021  4 2571308 9000
"ADBE" "Governance and Sustainability"                         "Frank Calderoni"                        "04/20/2021"     808 "01/01/9000"   62702 22390 2021  4 2571308 9000
"ADBE" "Governance and Sustainability"                         "Amy Banse"                              "04/20/2021"     808 "01/01/9000"  454637 22390 2021  4 2571308 9000
"ADBE" "Governance and Sustainability"                         "Leeny Oberg"                            "04/20/2021"     808 "01/01/9000" 1643080 22390 2021  4 2571308 9000
"ADM"  "Corporate Responsibility & Sustainability"             "Alan Boeckmann"                         "05/01/2019"    2490 "11/07/2019"   35823 21670 2019  5   21860 2019
"ADM"  "Corporate Responsibility & Sustainability"             "Terry Crews"                            "05/07/2020"    2490 "05/04/2023"   58822 22042 2020  5   23134 2023
"ADM"  "Corporate Responsibility & Sustainability"             "Pierre Dufour"                          "05/01/2019"    2490 "05/05/2022"  453179 21670 2019  5   22770 2022
"ADM"  "Corporate Responsibility & Sustainability"             "Frank Sanchez"                          "05/01/2019"    2490 "05/04/2023"  999494 21670 2019  5   23134 2023
"ADM"  "Corporate Responsibility & Sustainability"             "Jim Collins Jr"                         "10/03/2022"    2490 "12/28/2024" 1010737 22921 2022 10   23738 2024
"ADM"  "Corporate Responsibility & Sustainability"             "Lei Schlitz"                            "05/01/2019"    2490 "12/28/2024" 1074906 21670 2019  5   23738 2024
"ADM"  "Corporate Responsibility & Sustainability"             "Doctor Ellen de Brabander-van den Berg" "05/04/2023"    2490 "12/28/2024" 1294151 23134 2023  5   23738 2024
"ADM"  "Corporate Responsibility & Sustainability"             "Ted Colbert III"                        "05/06/2021"    2490 "05/04/2023" 1349686 22406 2021  5   23134 2023
"ADM"  "Corporate Responsibility & Sustainability"             "Suzan Harrison"                         "05/01/2019"    2490 "12/28/2024" 1532049 21670 2019  5   23738 2024
"AEE"  "Environmental Nuclear and Safety"                      "Cathy Brune"                            "12/08/2011"    1976 "04/23/2015"   35542 18969 2011 12   20201 2015
"AEE"  "Environmental Nuclear and Safety"                      "Charles Mueller"                        "04/27/2010"    1976 "04/21/2011"   51642 18379 2010  4   18738 2011
"AEE"  "Nuclear Operations and Environmental Sustainability"   "Ellen Fitzsimmons"                      "03/01/2020"    1976 "05/09/2024"   52477 21975 2020  3   23505 2024
"AEE"  "Nuclear Operations and Environmental Sustainability"   "Cindy Brinkley"                         "03/01/2020"    1976 "05/01/2023"  200450 21975 2020  3   23131 2023
"AEE"  "Environmental Nuclear and Safety"                      "Rich Harshman"                          "06/04/2013"    1976 "04/29/2016"  206428 19513 2013  6   20573 2016
"AEE"  "Nuclear Operations and Environmental Sustainability"   "Rich Harshman"                          "03/01/2020"    1976 "05/01/2023"  206428 21975 2020  3   23131 2023
"AEE"  "Environmental Nuclear and Safety"                      "Doctor Gayle Jackson"                   "04/27/2010"    1976 "04/29/2016"  330258 18379 2010  4   20573 2016
"AEE"  "Environmental Nuclear and Safety"                      "Jim Johnson"                            "04/24/2014"    1976 "04/29/2016"  332913 19837 2014  4   20573 2016
"AEE"  "Environmental Nuclear and Safety"                      "Steve Wilson"                           "04/27/2010"    1976 "04/23/2013"  374440 18379 2010  4   19471 2013
"AEE"  "Environmental Nuclear and Safety"                      "Ed Coleman"                             "02/16/2015"    1976 "04/29/2016"  491878 20135 2015  2   20573 2016
"AEE"  "Environmental Nuclear and Safety"                      "Jack Woodard"                           "04/27/2010"    1976 "04/28/2016"  499263 18379 2010  4   20572 2016
"AEE"  "Nuclear Operations and Environmental Sustainability"   "Craig Ivey"                             "03/01/2020"    1976 "01/01/9000" 1013405 21975 2020  3 2571308 9000
"AEE"  "Nuclear Operations and Environmental Sustainability"   "Doctor Leo Mackay Jr"                   "12/14/2020"    1976 "01/01/9000" 1120377 22263 2020 12 2571308 9000
"AEE"  "Nuclear Operations and Environmental Sustainability"   "Ward Dickson"                           "05/01/2023"    1976 "01/01/9000" 1324606 23131 2023  5 2571308 9000
"AEE"  "Nuclear Operations and Environmental Sustainability"   "Noelle Eder"                            "03/01/2020"    1976 "05/06/2021" 1354068 21975 2020  3   22406 2021
"AEE"  "Environmental Nuclear and Safety"                      "Rafael Flores"                          "11/01/2015"    1976 "04/29/2016" 1603402 20393 2015 11   20573 2016
"AEE"  "Nuclear Operations and Environmental Sustainability"   "Rafael Flores"                          "05/01/2023"    1976 "01/01/9000" 1603402 23131 2023  5 2571308 9000
"AEE"  "Nuclear Operations and Environmental Sustainability"   "Rafael Flores"                          "03/01/2020"    1976 "05/01/2023" 1603402 21975 2020  3   23131 2023
"AES"  "Environmental and Safety"                              "Ambassador Phil Lader"                  "04/19/2001"     979 "06/25/2007"   16660 15084 2001  4   17342 2007
"AES"  "Nominating Governance and Corporate Responsibility"    "Ambassador Phil Lader"                  "06/25/2007"     979 "04/20/2017"   16660 17342 2007  6   20929 2017
"AES"  "Nominating Governance and Corporate Responsibility"    "Doctor Sandy Moose"                     "06/25/2007"     979 "04/17/2014"   33126 17342 2007  6   19830 2014
"AES"  "Environmental and Safety"                              "Doctor Tish Emerson"                    "04/25/2002"     979 "12/31/2005"   35056 15455 2002  4   16801 2005
"AES"  "Environmental and Safety"                              "Roger Sant"                             "05/01/2003"     979 "05/10/2006"   38781 15826 2003  5   16931 2006
"AES"  "Nominating Governance and Corporate Responsibility"    "Holly Koeppel"                          "04/20/2017"     979 "12/28/2019"   45386 20929 2017  4   21911 2019
"AES"  "Nominating Governance and Corporate Responsibility"    "Holly Koeppel"                          "04/23/2015"     979 "12/28/2017"   45386 20201 2015  4   21181 2017
"AES"  "Nominating Governance and Corporate Responsibility"    "Sven Sandstrom"                         "06/25/2007"     979 "04/23/2015"   71252 17342 2007  6   20201 2015
"AES"  "Environmental and Safety"                              "Doctor Kristina Johnson"                "04/28/2004"     979 "06/25/2007"  181809 16189 2004  4   17342 2007
"AES"  "Environmental and Safety"                              "John Koskinen"                          "04/28/2004"     979 "05/11/2006"  184074 16189 2004  4   16932 2006
"AES"  "Environmental and Safety"                              "John Koskinen"                          "05/11/2006"     979 "06/25/2007"  184074 16932 2006  5   17342 2007
"AES"  "Nominating Governance and Corporate Responsibility"    "Alain Monié"                           "07/14/2017"     979 "12/28/2019"  377735 21014 2017  7   21911 2019
"AES"  "Nominating Governance and Corporate Responsibility"    "Chuck Harrington"                       "04/19/2018"     979 "04/18/2019"  802940 21293 2018  4   21657 2019
"AES"  "Nominating Governance and Corporate Responsibility"    "Doctor Tarun Khanna"                    "04/23/2009"     979 "12/28/2019"  857496 18010 2009  4   21911 2019
"AES"  "Nominating Governance and Corporate Responsibility"    "Doctor Moisés Naim"                    "04/17/2014"     979 "12/28/2019" 1124040 19830 2014  4   21911 2019
"AFL"  "Sustainability and Corporate Social Responsibility"    "William Bowers"                         "05/06/2013"    1017 "01/01/9000"   36619 19484 2013  5 2571308 9000
"AFL"  "Sustainability and Corporate Social Responsibility"    "Betty Hudson"                           "05/07/2012"    1017 "05/07/2018"   44721 19120 2012  5   21311 2018
"AFL"  "Sustainability and Corporate Social Responsibility"    "Doctor E Purdom"                        "05/07/2012"    1017 "05/05/2014"   44766 19120 2012  5   19848 2014
"AFL"  "Sustainability and Corporate Social Responsibility"    "Doctor Barbara Rimer"                   "05/07/2012"    1017 "05/07/2018"   44771 19120 2012  5   21311 2018
"AFL"  "Sustainability and Corporate Social Responsibility"    "Doctor Barbara Rimer"                   "05/07/2018"    1017 "05/06/2024"   44771 21311 2018  5   23502 2024
"AFL"  "Sustainability and Corporate Social Responsibility"    "Professor Doctor Mel Stith"             "05/07/2018"    1017 "05/02/2022"   57162 21311 2018  5   22767 2022
"AFL"  "Sustainability and Corporate Social Responsibility"    "Tom Kenny"                              "05/06/2019"    1017 "01/01/9000" 1126331 21675 2019  5 2571308 9000
"AFL"  "Sustainability and Corporate Social Responsibility"    "Art Collins"                            "05/01/2023"    1017 "05/28/2024" 1821974 23131 2023  5   23524 2024
"AIG"  "Public Policy and Responsibility"                      "Professor Doctor Marty Feldstein"       "01/21/2005"    1856 "05/17/2006"   32632 16457 2005  1   16938 2006
"AIG"  "Public Policy and Responsibility"                      "Ambassador Richard Holbrooke"           "04/21/2005"    1856 "07/16/2008"   32790 16547 2005  4   17729 2008
"AIG"  "Public Policy and Responsibility"                      "Stephen Hammerman"                      "05/17/2006"    1856 "05/14/2008"   34103 16938 2006  5   17666 2008
"AIG"  "Public Policy and Responsibility"                      "The Hon. William Cohen"                 "04/21/2005"    1856 "05/17/2006"   36311 16547 2005  4   16938 2006
"AIG"  "Public Policy and Responsibility"                      "George Miles Jr"                        "04/21/2005"    1856 "03/25/2009"   36621 16547 2005  4   17981 2009
"AIG"  "Public Policy and Responsibility"                      "Moffit Offit"                           "05/17/2006"    1856 "03/25/2009"  350047 16938 2006  5   17981 2009
"AKAM" "Environmental Social and Governance"                   "Ben Verwaayen"                          "05/20/2020"    1221 "06/03/2021"   17103 22055 2020  5   22434 2021
"AKAM" "Environmental Social and Governance"                   "Ben Verwaayen"                          "06/03/2021"    1221 "01/01/9000"   17103 22434 2021  6 2571308 9000
"AKAM" "Environmental Social and Governance"                   "Fred Salerno"                           "06/20/2020"    1221 "06/03/2021"   25012 22086 2020  6   22434 2021
"AKAM" "Environmental Social and Governance"                   "Jon Miller"                             "05/20/2020"    1221 "01/01/9000"   43631 22055 2020  5 2571308 9000
"AKAM" "Environmental Social and Governance"                   "Dan Hesse"                              "06/03/2021"    1221 "01/01/9000"   54581 22434 2021  6 2571308 9000
end
format %td startdate
format %td enddate
[/CODE]
------------------ copy up to and including the previous line ------------------

Listed 100 out of 2742 observations
Use the count() option to list more

. 

[Clyde Schechter]

This problem was originally posted in another thread, https://www.statalist.org/forums/for...-and-end-dates. In a response there, I said I understood that there were two data files involved, one being a data set of all tickers for the years 2004-2022, and the other a data set of directors with their tenure and committee assignments, and requested data examples from both. In this post, which is in other respects responsive to my original requests, there is no data set of all tickers for those years. I am therefore revising my understanding of the problem and assuming that such a set is to be extracted from the set of directors.

I am also making certain assumptions about the data. One, which is verified in the code below, is that the variables startyear and endtyear are always equal to the year for the variables startdate and enddate, respectively, and that the name endtyear is a typo for endyear. I also assume that the date 1/1/9000 used in the enddate variable is intended to denote that the director is presently still serving. (While this is not, in general, a good way to code this phenomenon, it works out OK here, so I'm not going to tamper with it in the code.) Finally, I assume that a director can be considered to have served on a sustainability committee if and only if the variable CommitteeNamecommitteename contains the word sustainability.

With those assumptions, I believe the following produces the desired result:

//    CREATE A TEMPORARY FILE OF TICKERS AND YEARS, ON WHICH TO BUILD THE PANEL DATA SET
preserve
keep ticker
duplicates drop
expand `=2022-2004+1'
by ticker, sort: gen year = 2003 + _n
tempfile panel
save `panel'
restore

//    VERIFY THAT STARTYEAR AND ENDYEAR ARE ACTUALLY THE YEARS OF
//    STARTDATE AND ENDDATE
assert startyear == year(startdate)
rename endtyear endyear // FIX TYPO IN VARIABLE NAME
assert endyear == year(enddate)

//    IDENTIFY SUSTAINABILITY COMMITTEE ACTIVITY BY STICK AND YEAR-RANGE
keep if strpos(lower(CommitteeNamecommitteename), "sustainability")
keep ticker startyear endyear
duplicates drop

joinby ticker using `panel', unmatched(both)
keep if inrange(year, startyear, endyear)
by ticker year, sort: egen byte sustainability_committee = max(_merge == 3)
drop _merge startyear endyear
by ticker year, sort: keep if _n == 1

I have some concerns that the -joinby- command may attempt to create an intermediate data set that is too large for available memory. That condition would be recognized by Stata halting with an error message something like "Op. sys. refuses to provide memory." If that happens, please post back and I will show a slightly more complicated approach that avoids this problem.

[Hemanshu Kumar]

Here is another way to do it that might be a little less memory-heavy. I am making the same assumptions as Clyde, and just using the startyear and endyear variables as corresponding to the respective dates.

keep ticker CommitteeName startyear endyear
sort ticker

forval yr = 2004/2022 {
    gen byte in_`yr' = strpos(lower(CommitteeName), "sustainability") & inrange(`yr', startyear, endyear)
    egen byte co_in_`yr' = max(in_`yr'), by(ticker)
    drop in_`yr'
}

duplicates drop ticker, force
drop CommitteeName startyear endyear
reshape long co_in_, i(ticker) j(year)
rename co_in_ Sustainability_Committee

To give you a sense of what you get, here are the observations which get coded 1:

. list ticker year if Sustainability_Committee == 1, noobs sepby(ticker)

  +---------------+
  | ticker   year |
  |---------------|
  |   ABBV   2013 |
  |   ABBV   2014 |
  |   ABBV   2015 |
  |   ABBV   2016 |
  |   ABBV   2017 |
  |   ABBV   2018 |
  |   ABBV   2019 |
  |   ABBV   2020 |
  |   ABBV   2021 |
  |   ABBV   2022 |
  |---------------|
  |    ACN   2021 |
  |    ACN   2022 |
  |---------------|
  |   ADBE   2021 |
  |   ADBE   2022 |
  |---------------|
  |    ADM   2019 |
  |    ADM   2020 |
  |    ADM   2021 |
  |    ADM   2022 |
  |---------------|
  |    AEE   2020 |
  |    AEE   2021 |
  |    AEE   2022 |
  |---------------|
  |    AFL   2012 |
  |    AFL   2013 |
  |    AFL   2014 |
  |    AFL   2015 |
  |    AFL   2016 |
  |    AFL   2017 |
  |    AFL   2018 |
  |    AFL   2019 |
  |    AFL   2020 |
  |    AFL   2021 |
  |    AFL   2022 |
  +---------------+

[hussein bataineh]

Thank you, Professor Clude and Hemanshu! Your suggestions worked perfectly.