Identifying overlapping time period

[Mia Pham]

Dear Statalisters;

I have the following dataset, including firm_name, manager_name, the time the managers start working in the firm and the time they leave the firm. One firm can be managed by several managers at one time. I would like to identify the period where the manager is the single manager of the firm

For example, for firm A, A Spencer managed the firm from 28 Feb 2003 to 14 May 2006 while S Tarca managed the firm from 28 Feb 2003 to 17 Nov 2008, so the period that Spencer is a single manager is None, while S Tarca is the single manager of Firm A from 14 May 2006 to 17 Nov 2008.

Things will get more complicated when firms have more managers during the study period.

Can you please help me with this?
Thank you very much in advance.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str42 firm_name str31 manager_name float(begin end)
"A" " A Spencer" 15764 16935
"A" " S Tarca"   15764 17853
"C" " R Weller"  16162 18567
"C" " J Alonzo"  16741 22128
"C" " C Blum"    17556 19084
"C" " D Ruhl"    19084 21854
"C" " G Fish"    19206 21489
"C" " W Choi"    21854 22128
"B" " A Spencer" 15792 16935
"B" " S Tarca"   15792 17853
"B" " R Weller"  16162 18567
"B" " J Alonzo"  16755 22128
"B" " C Blum"    17556 19084
"B" " D Ruhl"    19084 21854
"B" " S Tarca"   15792 17853
end
format %td begin
format %td end

[Clyde Schechter]

I believe the following will do it:

* Example generated by -dataex-. To install: ssc install dataex
clear*
input str42 firm_name str31 manager_name float(begin end)
"A" " A Spencer" 15764 16935
"A" " S Tarca"   15764 17853
"C" " R Weller"  16162 18567
"C" " J Alonzo"  16741 22128
"C" " C Blum"    17556 19084
"C" " D Ruhl"    19084 21854
"C" " G Fish"    19206 21489
"C" " W Choi"    21854 22128
"B" " A Spencer" 15792 16935
"B" " S Tarca"   15792 17853
"B" " R Weller"  16162 18567
"B" " J Alonzo"  16755 22128
"B" " C Blum"    17556 19084
"B" " D Ruhl"    19084 21854
"B" " S Tarca"   15792 17853
end
format %td begin
format %td end

duplicates drop

rename (begin end) date=
reshape long date, i(firm_name manager_name) j(event) string
by firm_name (date event), sort: gen n_managers = ///
    sum((event == "begin") - (event == "end"))
  
frame create alone_intervals str42 firm_name str31 manager_name begin end

capture program drop one_firm
program define one_firm
    local firm = firm_name[1]
    forvalues i = 1/`=_N' {
     if n_managers[`i'] == 1 {
         local begin = date[`i']
            local end = date[`=`i'+1']
            if event[`i'] == "begin" {
             local manager = manager[`i']
            }
            else {
             local manager = manager[`=`i'+1']
            }
            frame post alone_intervals (`"`firm'"') (`"`manager'"') ///
                (`begin') (`end')
        }
    }
    exit
end

runby one_firm, by(firm)

frame change alone_intervals
drop if begin == end
format begin end %td
list, noobs clean abbrev(24)

-runby- is written by Robert Picard and me, and is available from SSC.

If you are running a version of Stata older than 16, then you cannot use frames. In that case, create a -postfile- to capture the single manager intervals and post them there instead, -use- the -postfile- after -runby-, and everything else is the same.

By the way, there was a duplicate observation in your data example. That will break the code. The same manager and firm can only appear once if this code is to work.

[Mia Pham]

Thank you very much Clyde.

The code works perfectly.

I would like to ask how to modify the code so that it can work when there is duplication in terms of firm name and manager name. For example, the dataset below the same manager (S Tarca) work at firm B for two different periods.
I have tried several ways but came up with an error. Can you please help me?

Thank you very much

* Example generated by -dataex-. To install: ssc install dataex
clear
input str42 firm_name str31 manager_name float(begin end)
"A" " A Spencer" 15764 16935
"A" " S Tarca"   15764 17853
"C" " R Weller"  16162 18567
"C" " J Alonzo"  16741 22128
"C" " C Blum"    17556 19084
"C" " D Ruhl"    19084 21854
"C" " G Fish"    19206 21489
"C" " W Choi"    21854 22128
"B" " A Spencer" 15792 16935
"B" " S Tarca"   15792 17853
"B" " R Weller"  16162 18567
"B" " J Alonzo"  16755 22128
"B" " C Blum"    17556 19084
"B" " D Ruhl"    19084 21854
"B" " S Tarca"   17984 18948
end
format %td begin
format %td end

[Clyde Schechter]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str42 firm_name str31 manager_name float(datebegin dateend)
"A" " A Spencer" 15764 16935
"A" " S Tarca"   15764 17853
"C" " R Weller"  16162 18567
"C" " J Alonzo"  16741 22128
"C" " C Blum"    17556 19084
"C" " D Ruhl"    19084 21854
"C" " G Fish"    19206 21489
"C" " W Choi"    21854 22128
"B" " A Spencer" 15792 16935
"B" " S Tarca"   15792 17853
"B" " R Weller"  16162 18567
"B" " J Alonzo"  16755 22128
"B" " C Blum"    17556 19084
"B" " D Ruhl"    19084 21854
"B" " S Tarca"   15792 17853
end
format %td datebegin
format %td dateend

duplicates drop

gen long obs_no = _n

reshape long date, i(obs_no) j(event) string
by firm_name (date event), sort: gen n_managers = ///
    sum((event == "begin") - (event == "end"))
 
frame create alone_intervals str42 firm_name str31 manager_name begin end

capture program drop one_firm
program define one_firm
    local firm = firm_name[1]
    forvalues i = 1/`=_N' {
     if n_managers[`i'] == 1 {
         local begin = date[`i']
            local end = date[`=`i'+1']
            if event[`i'] == "begin" {
             local manager = manager[`i']
            }
            else {
             local manager = manager[`=`i'+1']
            }
            frame post alone_intervals (`"`firm'"') (`"`manager'"') ///
                (`begin') (`end')
        }
    }
    exit
end

runby one_firm, by(firm)

frame change alone_intervals
drop if begin == end
format begin end %td
list, noobs clean abbrev(24)

Changes in bold face. As you can see, not much has to change. Note that I still retained the -duplicates drop- command. This code is able to handle the situation where the same manager works two different spells at the same firm. But without -duplicates drop-, it would fail if the data mistakenly listed the same manager working the same spell (same dates) at the same firm twice.

[Diana Yoko]

Dear Clyde,

I am sorry for interupting.

Your solution in #4 works well for the given example, but it seems to me that, for nested situation (like firm x in below example), it does not solve the problem. In finding his intersting puzzle quite similar to my own issue, I would very much appreciate if you could kindly suggest a solution for that.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str1 firm_name str10 manager_name int(datebegin dateend)
"A" " A Spencer" 15764 16935
"A" " S Tarca"   15764 17853
"C" " R Weller"  16162 18567
"C" " J Alonzo"  16741 22128
"C" " C Blum"    17556 19084
"C" " D Ruhl"    19084 21854
"C" " G Fish"    19206 21489
"C" " W Choi"    21854 22128
"B" " A Spencer" 15792 16935
"B" " S Tarca"   15792 17853
"B" " R Weller"  16162 18567
"B" " J Alonzo"  16755 22128
"B" " C Blum"    17556 19084
"B" " D Ruhl"    19084 21854
"B" " S Tarca"   15792 17853
"X" "AAA"        15341 18263
"X" "BBB"        15737 16376
"X" "CCC"        16496 17075
"X" "DDD"        17257 17776
"x" "EEE"        16953 18628
end
format %td datebegin
format %td dateend

[Clyde Schechter]

Interesting! OK, this will work:

* Example generated by -dataex-. To install: ssc install dataex
clear*
input str1 firm_name str10 manager_name int(datebegin dateend)
"A" " A Spencer" 15764 16935
"A" " S Tarca"   15764 17853
"C" " R Weller"  16162 18567
"C" " J Alonzo"  16741 22128
"C" " C Blum"    17556 19084
"C" " D Ruhl"    19084 21854
"C" " G Fish"    19206 21489
"C" " W Choi"    21854 22128
"B" " A Spencer" 15792 16935
"B" " S Tarca"   15792 17853
"B" " R Weller"  16162 18567
"B" " J Alonzo"  16755 22128
"B" " C Blum"    17556 19084
"B" " D Ruhl"    19084 21854
"B" " S Tarca"   15792 17853
"X" "AAA"        15341 18263
"X" "BBB"        15737 16376
"X" "CCC"        16496 17075
"X" "DDD"        17257 17776
"X" "EEE"        16953 18628
end
format %td datebegin
format %td dateend

duplicates drop

gen long obs_no = _n

reshape long date, i(obs_no) j(event) string
replace manager_name = strtoname(trim(manager_name))

capture program drop one_firm
program define one_firm
    local firm = firm_name[1]
    gen managers = manager_name in 1
    forvalues i = 2/`=_N' {
     if event[`i'] == "begin" {
         replace managers = managers[`=`i'-1'] + " " ///
                + manager_name[`i'] in `i'
        }
        else if event[`i'] == "end" {
         replace managers = ///
                subinstr(managers[`=`i'-1'], manager_name[`i'], "", 1) in `i'
        }
        replace managers = trim(managers) in `i'
        if wordcount(managers[`i']) == 1 {
            frame post alone_intervals (`"`firm'"') (managers[`i']) (date[`i']) ///
                (date[`=`i'+1'])
        }
    }
    exit
end

sort firm_name date
frame create alone_intervals str1 firm_name str10 manager_name datebegin dateend

runby one_firm, by(firm_name) verbose

frame change alone_intervals
drop if datebegin == dateend
format date* %td
replace manager_name = trim(manager_name)
replace manager_name = subinstr(manager_name, "_", " ", .)
quietly compress
list, noobs clean abbrev(24)

Notes:
1. I believe there was a mistake in your example data. The firm_name value in the final observation was shown as lower case x, not upper case X. So at first my code treated it as a separate firm. But I imagine that is not what you meant. So in my response I have changed lower case x to upper case X there.
2. This code relies on tracking the ensemble of managers at each time point, rather than just counting how many, to avoid the pitfall of attributing a solo-manager interval to the wrong person. To do that, I had to transform the managers' names into single "words" in the sense that Stata's wordcount() function defines them. So I had to replace blanks with underscores. For display purposes at the very end, before listing out the alone intervals, they are transformed back to a more ordinary representation.

[Mia Pham]

Thank you very much Clyde
The issue raised by Diana is very interesting.
I have another issue running the latest code when the firm name is in different formats. The output file only keep the first letter of the firm name instead of the original firm name.
And if I change the manager name format (adding a "." in their middle name) there is an error in the code.
If you could help to have a look, that would be highly appreciated Clyde.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str11 firm_name str10 manager_name int(datebegin dateend)
"Aaa"         " A Spencer" 15764 16935
"Aaa"         " S Tarca"   15764 17853
"Ccc Ddd Eee" " R Weller"  16162 18567
"Ccc Ddd Eee" " J Alonzo"  16741 22128
"Ccc Ddd Eee" " C Blum"    17556 19084
"Ccc Ddd Eee" " D Ruhl"    19084 21854
"Ccc Ddd Eee" " G Fish"    19206 21489
"Ccc Ddd Eee" " W Choi"    21854 22128
"B"           " A J. Spencer" 15792 16935
"B"           " S D. Tarca"   15792 17853
"B"           " R Weller"  16162 18567
"B"           " J Alonzo"  16755 22128
"B"           " C Blum"    17556 19084
"B"           " D Ruhl"    19084 21854
"B"           " S D. Tarca"   15792 17853
"X"           "AAA"        15341 18263
"X"           "BBB"        15737 16376
"X"           "CCC"        16496 17075
"X"           "DDD"        17257 17776
"X"           "EEE"        16953 18628
end
format %td datebegin
format %td dateend

[Clyde Schechter]

OK. Just a few little tweaks to make it work with this data.

* Example generated by -dataex-. To install: ssc install dataex
clear*
input str11 firm_name str10 manager_name int(datebegin dateend)
"Aaa"         " A Spencer" 15764 16935
"Aaa"         " S Tarca"   15764 17853
"Ccc Ddd Eee" " R Weller"  16162 18567
"Ccc Ddd Eee" " J Alonzo"  16741 22128
"Ccc Ddd Eee" " C Blum"    17556 19084
"Ccc Ddd Eee" " D Ruhl"    19084 21854
"Ccc Ddd Eee" " G Fish"    19206 21489
"Ccc Ddd Eee" " W Choi"    21854 22128
"B"           " A J. Spencer" 15792 16935
"B"           " S D. Tarca"   15792 17853
"B"           " R Weller"  16162 18567
"B"           " J Alonzo"  16755 22128
"B"           " C Blum"    17556 19084
"B"           " D Ruhl"    19084 21854
"B"           " S D. Tarca"   15792 17853
"X"           "AAA"        15341 18263
"X"           "BBB"        15737 16376
"X"           "CCC"        16496 17075
"X"           "DDD"        17257 17776
"X"           "EEE"        16953 18628
end
format %td datebegin
format %td dateend

duplicates drop

gen long obs_no = _n

reshape long date, i(obs_no) j(event) string
replace manager_name = subinstr((trim(manager_name)), " ", "_", .)

capture program drop one_firm
program define one_firm
    local firm = firm_name[1]
    gen managers = manager_name in 1
    forvalues i = 2/`=_N' {
     if event[`i'] == "begin" {
         replace managers = managers[`=`i'-1'] + " " ///
                + manager_name[`i'] in `i'
        }
        else if event[`i'] == "end" {
         replace managers = ///
                subinstr(managers[`=`i'-1'], manager_name[`i'], "", 1) in `i'
        }
        replace managers = trim(managers) in `i'
        if wordcount(managers[`i']) == 1 {
            frame post alone_intervals (`"`firm'"') (managers[`i']) (date[`i']) ///
                (date[`=`i'+1'])
        }
    }
    exit
end

sort firm_name date
frame create alone_intervals str240 firm_name str240 manager_name datebegin dateend

runby one_firm, by(firm_name) verbose

frame change alone_intervals
drop if datebegin == dateend
format date* %td
replace manager_name = trim(manager_name)
replace manager_name = subinstr(manager_name, "_", " ", .)
quietly compress
list, noobs clean abbrev(24)

The truncation of firm_name to one character arose because in your data example you provided one-character firm names, and I, perhaps foolishly, took that to be representative of your data. So I only allocated one character for the firmname in the alone_intervals frame. The code above allows up to 240 characters each for the firm_name and manager_name. If even those aren't long enough, you can allocate still more.

The use of the period after a middle initial was problematic because I used the handy -strtoname()- function to reduce each manager name to a single "word" for the purposes of the -wordcount()- function. -strtoname()- replaces periods with underscores, because periods aren't allowed in a variable name in Stata. The solution here is to just more directly replace all the blanks by underscores and leave everything else alone. That said: do be cautious. Names are very tricky when working with computer programs. If in the same firm you refer to the same person once as S Tarka and on another occasion as S D. Tarka, Stata is not going to recognize those as the same person, and the code will fail. You must be sure that you consistently use exactly the same character string as the name each time you refer to the same person within a firm. (In this problem it doesn't matter if you use different names for the same person when they appear in different firms--but it's still a bad practice because it will get you in trouble in most situations.) A similar consideration applies to the firm name's. You must use exactly the same character string to refer to a given firm each time it appears in the data. They must agree on punctuation, upper vs lower case, spacing, full-words vs abbreviations, everything. There isn't any satisfactory way to code around that restriction: you have to clean the data to conform to that before you try doing anything with it.

[Mia Pham]

Dear Clyde

Thank you very much.

The dataset in my example is the simpler version of the original dataset. I will look at your code and try to make some adjustments when dealing with different name formats.
I have learned so many things by reading your code.
Thank you again

Kind regards;
Mia

[Diana Yoko]

Dear Clyde, many thanks for your help. Please also accept my apology for mistakenly giving firm_name as "x" for the last obs, not "X" as correctly noticed by you.

I have checked your code, finding it almost provided the desire. But my expectation is a little bit further than that: to list ALL available period(s) under Single management. Then, as shown below, manager AAA should be listed in two observations, along with the fact that his single management are observed in 2 separated periods. The code in #8 does not capture the first period of AAA.

     +--------------------------------------------------+
     | firm_name   manager_name   datebegin     dateend |
     |--------------------------------------------------|
  1. |       Aaa        S Tarca   14may2006   17nov2008 |
  2. |         B       J Alonzo   01nov2019   01aug2020 |
  3. |         X            AAA   01jan2002   01feb2003 |
  4. |         X            AAA   01nov2004   01mar2005 |
  5. |         X            EEE   01jan2010   01jan2011 |
     +--------------------------------------------------+

I wonder whether any direct adjustment in the code could deal with that? In any circumstance, please receive my apprection and respect for all of your help.

[Clyde Schechter]

Good pickup! I see where it went wrong. Here's corrected code:

* Example generated by -dataex-. To install: ssc install dataex
clear*
input str11 firm_name str10 manager_name int(datebegin dateend)
"Aaa"         " A Spencer" 15764 16935
"Aaa"         " S Tarca"   15764 17853
"Ccc Ddd Eee" " R Weller"  16162 18567
"Ccc Ddd Eee" " J Alonzo"  16741 22128
"Ccc Ddd Eee" " C Blum"    17556 19084
"Ccc Ddd Eee" " D Ruhl"    19084 21854
"Ccc Ddd Eee" " G Fish"    19206 21489
"Ccc Ddd Eee" " W Choi"    21854 22128
"B"           " A J. Spencer" 15792 16935
"B"           " S D. Tarca"   15792 17853
"B"           " R Weller"  16162 18567
"B"           " J Alonzo"  16755 22128
"B"           " C Blum"    17556 19084
"B"           " D Ruhl"    19084 21854
"B"           " S D. Tarca"   15792 17853
"X"           "AAA"        15341 18263
"X"           "BBB"        15737 16376
"X"           "CCC"        16496 17075
"X"           "DDD"        17257 17776
"X"           "EEE"        16953 18628
end
format %td datebegin
format %td dateend

duplicates drop

gen long obs_no = _n

reshape long date, i(obs_no) j(event) string
replace manager_name = subinstr((trim(manager_name)), " ", "_", .)

capture program drop one_firm
program define one_firm
    local firm = firm_name[1]
    gen managers = ""
    forvalues i = 1/`=_N' {
     if event[`i'] == "begin" {
         replace managers = managers[`=`i'-1'] + " " ///
                + manager_name[`i'] in `i'
        }
        else if event[`i'] == "end"  {
         replace managers = ///
                subinstr(managers[`=`i'-1'], manager_name[`i'], "", 1) in `i'
        }
        replace managers = trim(managers) in `i'
        if wordcount(managers[`i']) == 1 {
            frame post alone_intervals (`"`firm'"') (managers[`i']) (date[`i']) ///
                (date[`=`i'+1'])
        }
    }
    exit
end

sort firm_name date
frame create alone_intervals str240 firm_name str240 manager_name datebegin dateend

runby one_firm, by(firm_name) verbose

frame change alone_intervals
drop if datebegin == dateend
format date* %td
replace manager_name = trim(manager_name)
replace manager_name = subinstr(manager_name, "_", " ", .)
quietly compress
list, noobs clean abbrev(24)

The fix is pretty simple, shown above in bold italics.

[Mia Pham]

Dear Clyde

Thank you very much

I have work on the code on the following dataset and find some issues.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str42 firm_name str31 manager_name float(begin end)
"A" " Douglas W. Case"        18049 19568
"A" " Jon E. Quigley"         18049 22128
"A" " John D. Bright"         19933 22128
"B" " Ophelia Barsketis"      19539 20298
"B" " Edward J. Calkins"      19264 22128
"B" " Wells L. Frice"         19264 22128
"B" " Gary A. Lenhoff"        21029 21763
"B" " Huong M. Le"            19264 22128
"B" " Steven G. Wittwer"      19539 20819
"B" " Benjamin Kim"           22035 22128
"B" " Raymond O. Wicklander"  22035 22128
"B" " Scott W. Schoelzel"     15846 17653
"B" " Paul Cloonan"           19602 22128
"B" " Andrew Acheson"         19602 22128
"B" " Giri K Devulapally"     19602 22128
"B" " Christopher Mark Jones" 19602 19844
"B" " A. Douglas Rao"         19513 19602
"B" " Ron Sachs"              17532 19513
"C" " Scott W. Schoelzel"     15846 17653
"C" " Paul Cloonan"           19602 22128
"C" " Andrew Acheson"         19602 22128
"C" " Giri K Devulapally"     19602 22128
"C" " Christopher Mark Jones" 19602 19844
"C" " A. Douglas Rao"         19513 19602
"C" " Ron Sachs"              17532 19513
end
format %td begin
format %td end

If I use the code in #11, the output file is as follows: Looking at A. Douglas Rao, for example, I don't think he has any period where he is a single manager.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str1 firm_name str18 manager_name int(datebegin dateend)
"A" "Jon E. Quigley"     19568 19933
"B" "Scott W. Schoelzel" 15846 17532
"B" "Ron Sachs"          17653 19264
"C" "Scott W. Schoelzel" 15846 17532
"C" "Ron Sachs"          17653 19513
"C" "A. Douglas Rao"     19513 19602
end
format %td datebegin
format %td dateend

If I use the code in #4 then the output file has no observation at all. I didn't do the manual check for all obs but I can see at least Jon E. Quigley has a period when he was a single manager (from 29Jul2013 to 29Jul2014).

Can you have a look, please. Thank you very much for your patience, Clyde.

[Diana Yoko]

Many thanks, Clyde. I believe the code in #11 gives the correct output.

I also find no error at all in the Mia's example in #12, including the case of Jon E. Quigley or A. Douglas Rao.

[Clyde Schechter]

Mia Pham Several corrections have been made to the code since #4. The correct version is in #11.

[Mia Pham]

Clyde Schechter : Thank you. Yes I know that #11 is the most updated one. I just want to try several codes to see where the problem is.

Diana Yoko : May I ask what is the output file when you tried code #11 with my data sample in #12? I have tried several times and still see the name of A. Douglas Rao in the list of single managers. I don't think he has any period where he managed firm B solely by himself. In the sample data, he managed firm B from 04Jun2013 to 01Sep2013. I can see his time period overlapped with (at least) that of Edward J. Calkins (who managed the firm from 28sep2012 to 01aug2020).
I don't know where I got it wrong. If you can point it out that would be highly appreciated.