Changes in team composition and familiarity

[Katrin Müller]

Hey there,

I am working with Stata 11.2 and am quite new to the program.

What I try to do is to generate a variable which measures changes in a team from one project to the next project. Hence, I try to include new entries (members, that were not part of the previous project, but are in the new project) and exits (individuals which were part of the previous project, but are not in the current project anymore). In my dataset, the variable "developer" refers to the development firm, i.e. the employing company as often used in other datasets.

So what I did was:

by developer person_id (period_id), sort: gen byte new = _n == 1
by developer person_id (period_id): gen byte out = _n == _N
sort period_id developer 
by developer period_id, sort: egen change = total(new + out)
replace VAR = VAR/title_project_size

The problem I get now, is that the exits count in the period in which the members are still part of the team (so in the period right before exiting). I would like to count these exits in the next project, i.e. in the one in which they are not part of the team for the first time anymore.

I hope I could explain what my problem is.

Can anyone help me? I would really, really appreciate it!

* Example generated by -dataex-. To install: ssc install dataex
clear
input str50 name float person_id str96 title float(project_id period_id) str70 developer int title_project_size byte(new out) float change
"PAVOL LAUKO"          153065 "VAL'GOR: DARK LORD OF MAGIC"   14800 3028 `""SOWHAT!" S.R.O."'            9 1 1 1.2222222
"MAREK MACHACEK"       120800 "VAL'GOR: DARK LORD OF MAGIC"   14800 3028 `""SOWHAT!" S.R.O."'            9 1 0 1.2222222
"KAREL SLABY"           98990 "VAL'GOR: DARK LORD OF MAGIC"   14800 3028 `""SOWHAT!" S.R.O."'            9 1 0 1.2222222
"MARTIN LIPTAK"        124502 "VAL'GOR: DARK LORD OF MAGIC"   14800 3028 `""SOWHAT!" S.R.O."'            9 1 0 1.2222222
"ALZBETKA LIPTAKOVA"     7591 "VAL'GOR: DARK LORD OF MAGIC"   14800 3028 `""SOWHAT!" S.R.O."'            9 1 0 1.2222222
"EGON FRIEBUS"          51134 "VAL'GOR: DARK LORD OF MAGIC"   14800 3028 `""SOWHAT!" S.R.O."'            9 1 0 1.2222222
"LUBOS BIL"            116277 "VAL'GOR: DARK LORD OF MAGIC"   14800 3028 `""SOWHAT!" S.R.O."'            9 1 0 1.2222222
"MILOS KOPRLA"         137604 "VAL'GOR: DARK LORD OF MAGIC"   14800 3028 `""SOWHAT!" S.R.O."'            9 1 1 1.2222222
"JAN HUDAK"             79577 "VAL'GOR: DARK LORD OF MAGIC"   14800 3028 `""SOWHAT!" S.R.O."'            9 1 0 1.2222222
"LUBOS BIL"            116277 "ICE PRINCESS"                   6421 3153 `""SOWHAT!" S.R.O."'            9 0 1  .5555556
"FILIP DZURJOVCIN"      57187 "ICE PRINCESS"                   6421 3153 `""SOWHAT!" S.R.O."'            9 1 0  .5555556
"EGON FRIEBUS"          51134 "ICE PRINCESS"                   6421 3153 `""SOWHAT!" S.R.O."'            9 0 0  .5555556
"MARTIN LIPTAK"        124502 "ICE PRINCESS"                   6421 3153 `""SOWHAT!" S.R.O."'            9 0 0  .5555556
"JAN HUDAK"             79577 "ICE PRINCESS"                   6421 3153 `""SOWHAT!" S.R.O."'            9 0 0  .5555556
"ALZBETKA LIPTAKOVA"     7591 "ICE PRINCESS"                   6421 3153 `""SOWHAT!" S.R.O."'            9 0 0  .5555556
"KAREL SLABY"           98990 "ICE PRINCESS"                   6421 3153 `""SOWHAT!" S.R.O."'            9 0 1  .5555556
"ROBERT HUSAR"         164047 "ICE PRINCESS"                   6421 3153 `""SOWHAT!" S.R.O."'            9 1 1  .5555556
"MAREK MACHACEK"       120800 "ICE PRINCESS"                   6421 3153 `""SOWHAT!" S.R.O."'            9 0 0  .5555556
"JAN HUDAK"             79577 "ART DETECTIVE"                   909 3375 `""SOWHAT!" S.R.O."'            9 0 1 1.3333334
"LUBOS BILL"           116278 "ART DETECTIVE"                   909 3375 `""SOWHAT!" S.R.O."'            9 1 1 1.3333334
"ALZBETKA LIPTAKOVA"     7591 "ART DETECTIVE"                   909 3375 `""SOWHAT!" S.R.O."'            9 0 1 1.3333334
"MARTIN LIPTAK"        124502 "ART DETECTIVE"                   909 3375 `""SOWHAT!" S.R.O."'            9 0 1 1.3333334
"MAREK MACHACEK"       120800 "ART DETECTIVE"                   909 3375 `""SOWHAT!" S.R.O."'            9 0 1 1.3333334
"MARIAN LIPTAK"        121228 "ART DETECTIVE"                   909 3375 `""SOWHAT!" S.R.O."'            9 1 1 1.3333334
"EGON FRIEBUS"          51134 "ART DETECTIVE"                   909 3375 `""SOWHAT!" S.R.O."'            9 0 1 1.3333334
"SIMONA SPENDELOVA"    179883 "ART DETECTIVE"                   909 3375 `""SOWHAT!" S.R.O."'            9 1 1 1.3333334
"FILIP DZURJOVCIN"      57187 "ART DETECTIVE"                   909 3375 `""SOWHAT!" S.R.O."'            9 0 1 1.3333334
"DIERK OHLERICH"        46671 ".KKRIEGER: CHAPTER 1"              8 2220 ".THEPRODUKKT"                  6 1 1         2
"TAMMO HINRICHS"       190646 ".KKRIEGER: CHAPTER 1"              8 2220 ".THEPRODUKKT"                  6 1 1         2
"CHRISTOPH MUTZE"       32323 ".KKRIEGER: CHAPTER 1"              8 2220 ".THEPRODUKKT"                  6 1 1         2
"SEBASTIAN GRILLMAIER" 173890 ".KKRIEGER: CHAPTER 1"              8 2220 ".THEPRODUKKT"                  6 1 1         2
"THOMAS MAHLKE"        194193 ".KKRIEGER: CHAPTER 1"              8 2220 ".THEPRODUKKT"                  6 1 1         2
"FABIAN GIESEN"         56294 ".KKRIEGER: CHAPTER 1"              8 2220 ".THEPRODUKKT"                  6 1 1         2
"DAVE DRIES"            40899 "SPACE BATTLE"                  11963    9 "05.12.2006"                    5 1 1       1.2
"NATHAN STRUM"         142552 "SPACE BATTLE"                  11963    9 "05.12.2006"                    5 1 1       1.2
"DAVE NEUMAN"           41176 "SPACE BATTLE"                  11963    9 "05.12.2006"                    5 1 1       1.2
"STEFAN SIEGERT"       181758 "POWERPUZZLE"                   10006 2391 "1-ABC.NET"                     1 1 0         1
"STEFAN SIEGERT"       181758 "POWERSNAKE"                    10009 2409 "1-ABC.NET"                     1 0 1         1
"SEBASTIEN LAFOUTRY"   174149 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"TIM POLITOV"          195350 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"HUBERT DANIELS"        73198 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"JASON GARBER"          80908 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"JULIEN HAMAIDE"        96254 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"FRANCOIS CNOCKAERT"    58160 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"STEFAAN VAN LEUVEN"   181505 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"ISABELLE HOUZET"       75805 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"LUDOVIC DELBEKE"      116634 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"GILLES DELMOTTE"       63326 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"FRANCO SPADA"          58111 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"CEDRIC VAIDYE"         27393 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"CHRISTOPHE BAUVIR"     32368 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"MARCO PAPPALARDO"     120477 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"BENOIT CLERC"          18623 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"YVES GROLET"          215245 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"BRUNO URBAIN"          24385 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"LIONEL HILLER"        114713 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"LUDWIG BISIAU"        116670 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"KAY KRAMER"           100937 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"ERIC WAIRY"            54349 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"BRUNO GALLET"          24312 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"DENIS HAUMONT"         45219 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"CHRISTOPHE HERMANNS"   32441 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"BANDE ANNONCE"         16765 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"BALA KEILMAN"          16688 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"STEPHANE BURA"        182215 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"ERIC PELZER"           54094 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"YVAN HENDRICK"        215216 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"DIRK MEEUS"            47091 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"MICHAEL DEFROYENNES"  132257 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"JORIS MANS"            93790 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"JEROME EIPPERS"        85537 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"BENJAMIN JOSEPH"       18402 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"FABRICE POISBLAUD"     56511 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"PHILIPPE ZONDACK"     156199 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"MARTIJN PANTLIN"      124071 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37 1 1         2
"VILLE ERIKSSON"       204268 "CRIMSONLAND"                    3044 2112 "10TONS LTD."                   7 1 1 1.8571428
"ION HARDIE"            75424 "CRIMSONLAND"                    3044 2112 "10TONS LTD."                   7 1 1 1.8571428
"PASI HEINONEN"        150182 "CRIMSONLAND"                    3044 2112 "10TONS LTD."                   7 1 1 1.8571428
"VALTTERI PIHLAJAMAKI" 203191 "CRIMSONLAND"                    3044 2112 "10TONS LTD."                   7 1 1 1.8571428
"TIMO PALONEN"         195645 "CRIMSONLAND"                    3044 2112 "10TONS LTD."                   7 1 1 1.8571428
"TERO ALATALO"         192174 "CRIMSONLAND"                    3044 2112 "10TONS LTD."                   7 1 0 1.8571428
"ZACH YOUNG"           215408 "CRIMSONLAND"                    3044 2112 "10TONS LTD."                   7 1 1 1.8571428
"SAMPO TOYSSY"         170334 "AZKEND"                         1084 3070 "10TONS LTD."                   5 1 1       1.8
"TERO ALATALO"         192174 "AZKEND"                         1084 3070 "10TONS LTD."                   5 0 1       1.8
"SARAH BRASS"          171162 "AZKEND"                         1084 3070 "10TONS LTD."                   5 1 1       1.8
"MARK MAGDAMIT"        122933 "AZKEND"                         1084 3070 "10TONS LTD."                   5 1 1       1.8
"JUSSI LEHTINIEMI"      97380 "AZKEND"                         1084 3070 "10TONS LTD."                   5 1 1       1.8
"MATTHEW ENGLE"        129094 "EXCELSIOR PHASE ONE: LYSANDIA"  4613  437 "11TH DIMENSION ENTERTAINMENT"  2 1 0         1
"DANIEL BERKE"          38597 "EXCELSIOR PHASE ONE: LYSANDIA"  4613  437 "11TH DIMENSION ENTERTAINMENT"  2 1 0         1
"MELINDA HSU"          131194 "EXCELSIOR PHASE TWO: ERRONDOR"  4614 1391 "11TH DIMENSION ENTERTAINMENT"  5 1 1       1.6
"JONATHAN P. GWYN"      93055 "EXCELSIOR PHASE TWO: ERRONDOR"  4614 1391 "11TH DIMENSION ENTERTAINMENT"  5 1 1       1.6
"DANIEL BERKE"          38597 "EXCELSIOR PHASE TWO: ERRONDOR"  4614 1391 "11TH DIMENSION ENTERTAINMENT"  5 0 1       1.6
"JIMMY RIMMER"          87429 "EXCELSIOR PHASE TWO: ERRONDOR"  4614 1391 "11TH DIMENSION ENTERTAINMENT"  5 1 1       1.6
"MATTHEW ENGLE"        129094 "EXCELSIOR PHASE TWO: ERRONDOR"  4614 1391 "11TH DIMENSION ENTERTAINMENT"  5 0 1       1.6
"PASCAL POCHOL"        150124 "CRUNCHER IN MAZELAND"           3071 1857 "123-D SOFTWARE, INC."         13 1 0         1
"JOE FALSETTI"          88485 "CRUNCHER IN MAZELAND"           3071 1857 "123-D SOFTWARE, INC."         13 1 0         1
"ARIEL GROSS"           13985 "CRUNCHER IN MAZELAND"           3071 1857 "123-D SOFTWARE, INC."         13 1 0         1
"ROBERT J. DOMINIAK"   164065 "CRUNCHER IN MAZELAND"           3071 1857 "123-D SOFTWARE, INC."         13 1 0         1
"CRISTIAN SOULOS"       35949 "CRUNCHER IN MAZELAND"           3071 1857 "123-D SOFTWARE, INC."         13 1 0         1
"SUSAN SAFRANEK"       186218 "CRUNCHER IN MAZELAND"           3071 1857 "123-D SOFTWARE, INC."         13 1 0         1
end

[Clyde Schechter]

I find the period_id variable confusing. The numbers look arbitrary, yet in your own code you sort on them, which, to me, implies that you think that increasing period_id numbers correspond to chronological order. If that is the case, let us consider Alzbetka Liptakova, who is part of the "SOWHAT!" S.R.O. team in periods 3028, 3153, and 3375. But that person is apparently not part of the time in period 3153, which comes between 3070 and 3375. So why is that person not "out" before 3153 and then "in" again in 3375?

Or, consider developer .THEPRODUKT. This developer is only active in period 2220. So all of the person_ids associated with that developer are both in and out in that period. But if we are to attribute those outs to "the next" period, what period is that next period? Is it 2391, or one of the other later periods in which .THEPRODUKT is still inactive?

I think a clearer explanation is needed. And it would probably help if you hand-worked what you want the solution to actually look like and posted that as well.

[William Lisowski]

A repost of an earlier question; perhaps the explanation there is helpful.

https://www.statalist.org/forums/for...am-composition

[Clyde Schechter]

William, thanks for that link. It does explain a bit more, though the entire picture remains, at least in my mind, pretty confusing.

So here's what I'm going to do. I'm seizing on one sentence that Katrin Muller wrote in that link:

What I want Stata to do, is to tell me how many percent of the team that worked on the developer's previous project are still working on the next project.

and I'm going to ignore all the other description of what is wanted and what was tried. This one sentence makes sense to me, even if it is contradicted elsewhere in the threads. So I'll go with it.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str20 name float person_id str29 title int(project_id period_id) str28 developer byte title_project_size float change
"PAVOL LAUKO"          153065 "VAL'GOR: DARK LORD OF MAGIC"   14800 3028 `""SOWHAT!" S.R.O."'            9 1.2222222
"MAREK MACHACEK"       120800 "VAL'GOR: DARK LORD OF MAGIC"   14800 3028 `""SOWHAT!" S.R.O."'            9 1.2222222
"KAREL SLABY"           98990 "VAL'GOR: DARK LORD OF MAGIC"   14800 3028 `""SOWHAT!" S.R.O."'            9 1.2222222
"MARTIN LIPTAK"        124502 "VAL'GOR: DARK LORD OF MAGIC"   14800 3028 `""SOWHAT!" S.R.O."'            9 1.2222222
"ALZBETKA LIPTAKOVA"     7591 "VAL'GOR: DARK LORD OF MAGIC"   14800 3028 `""SOWHAT!" S.R.O."'            9 1.2222222
"EGON FRIEBUS"          51134 "VAL'GOR: DARK LORD OF MAGIC"   14800 3028 `""SOWHAT!" S.R.O."'            9 1.2222222
"LUBOS BIL"            116277 "VAL'GOR: DARK LORD OF MAGIC"   14800 3028 `""SOWHAT!" S.R.O."'            9 1.2222222
"MILOS KOPRLA"         137604 "VAL'GOR: DARK LORD OF MAGIC"   14800 3028 `""SOWHAT!" S.R.O."'            9 1.2222222
"JAN HUDAK"             79577 "VAL'GOR: DARK LORD OF MAGIC"   14800 3028 `""SOWHAT!" S.R.O."'            9 1.2222222
"LUBOS BIL"            116277 "ICE PRINCESS"                   6421 3153 `""SOWHAT!" S.R.O."'            9  .5555556
"FILIP DZURJOVCIN"      57187 "ICE PRINCESS"                   6421 3153 `""SOWHAT!" S.R.O."'            9  .5555556
"EGON FRIEBUS"          51134 "ICE PRINCESS"                   6421 3153 `""SOWHAT!" S.R.O."'            9  .5555556
"MARTIN LIPTAK"        124502 "ICE PRINCESS"                   6421 3153 `""SOWHAT!" S.R.O."'            9  .5555556
"JAN HUDAK"             79577 "ICE PRINCESS"                   6421 3153 `""SOWHAT!" S.R.O."'            9  .5555556
"ALZBETKA LIPTAKOVA"     7591 "ICE PRINCESS"                   6421 3153 `""SOWHAT!" S.R.O."'            9  .5555556
"KAREL SLABY"           98990 "ICE PRINCESS"                   6421 3153 `""SOWHAT!" S.R.O."'            9  .5555556
"ROBERT HUSAR"         164047 "ICE PRINCESS"                   6421 3153 `""SOWHAT!" S.R.O."'            9  .5555556
"MAREK MACHACEK"       120800 "ICE PRINCESS"                   6421 3153 `""SOWHAT!" S.R.O."'            9  .5555556
"JAN HUDAK"             79577 "ART DETECTIVE"                   909 3375 `""SOWHAT!" S.R.O."'            9 1.3333334
"LUBOS BILL"           116278 "ART DETECTIVE"                   909 3375 `""SOWHAT!" S.R.O."'            9 1.3333334
"ALZBETKA LIPTAKOVA"     7591 "ART DETECTIVE"                   909 3375 `""SOWHAT!" S.R.O."'            9 1.3333334
"MARTIN LIPTAK"        124502 "ART DETECTIVE"                   909 3375 `""SOWHAT!" S.R.O."'            9 1.3333334
"MAREK MACHACEK"       120800 "ART DETECTIVE"                   909 3375 `""SOWHAT!" S.R.O."'            9 1.3333334
"MARIAN LIPTAK"        121228 "ART DETECTIVE"                   909 3375 `""SOWHAT!" S.R.O."'            9 1.3333334
"EGON FRIEBUS"          51134 "ART DETECTIVE"                   909 3375 `""SOWHAT!" S.R.O."'            9 1.3333334
"SIMONA SPENDELOVA"    179883 "ART DETECTIVE"                   909 3375 `""SOWHAT!" S.R.O."'            9 1.3333334
"FILIP DZURJOVCIN"      57187 "ART DETECTIVE"                   909 3375 `""SOWHAT!" S.R.O."'            9 1.3333334
"DIERK OHLERICH"        46671 ".KKRIEGER: CHAPTER 1"              8 2220 ".THEPRODUKKT"                  6         2
"TAMMO HINRICHS"       190646 ".KKRIEGER: CHAPTER 1"              8 2220 ".THEPRODUKKT"                  6         2
"CHRISTOPH MUTZE"       32323 ".KKRIEGER: CHAPTER 1"              8 2220 ".THEPRODUKKT"                  6         2
"SEBASTIAN GRILLMAIER" 173890 ".KKRIEGER: CHAPTER 1"              8 2220 ".THEPRODUKKT"                  6         2
"THOMAS MAHLKE"        194193 ".KKRIEGER: CHAPTER 1"              8 2220 ".THEPRODUKKT"                  6         2
"FABIAN GIESEN"         56294 ".KKRIEGER: CHAPTER 1"              8 2220 ".THEPRODUKKT"                  6         2
"DAVE DRIES"            40899 "SPACE BATTLE"                  11963    9 "05.12.2006"                    5       1.2
"NATHAN STRUM"         142552 "SPACE BATTLE"                  11963    9 "05.12.2006"                    5       1.2
"DAVE NEUMAN"           41176 "SPACE BATTLE"                  11963    9 "05.12.2006"                    5       1.2
"STEFAN SIEGERT"       181758 "POWERPUZZLE"                   10006 2391 "1-ABC.NET"                     1         1
"STEFAN SIEGERT"       181758 "POWERSNAKE"                    10009 2409 "1-ABC.NET"                     1         1
"SEBASTIEN LAFOUTRY"   174149 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"TIM POLITOV"          195350 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"HUBERT DANIELS"        73198 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"JASON GARBER"          80908 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"JULIEN HAMAIDE"        96254 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"FRANCOIS CNOCKAERT"    58160 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"STEFAAN VAN LEUVEN"   181505 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"ISABELLE HOUZET"       75805 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"LUDOVIC DELBEKE"      116634 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"GILLES DELMOTTE"       63326 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"FRANCO SPADA"          58111 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"CEDRIC VAIDYE"         27393 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"CHRISTOPHE BAUVIR"     32368 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"MARCO PAPPALARDO"     120477 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"BENOIT CLERC"          18623 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"YVES GROLET"          215245 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"BRUNO URBAIN"          24385 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"LIONEL HILLER"        114713 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"LUDWIG BISIAU"        116670 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"KAY KRAMER"           100937 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"ERIC WAIRY"            54349 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"BRUNO GALLET"          24312 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"DENIS HAUMONT"         45219 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"CHRISTOPHE HERMANNS"   32441 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"BANDE ANNONCE"         16765 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"BALA KEILMAN"          16688 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"STEPHANE BURA"        182215 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"ERIC PELZER"           54094 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"YVAN HENDRICK"        215216 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"DIRK MEEUS"            47091 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"MICHAEL DEFROYENNES"  132257 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"JORIS MANS"            93790 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"JEROME EIPPERS"        85537 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"BENJAMIN JOSEPH"       18402 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"FABRICE POISBLAUD"     56511 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"PHILIPPE ZONDACK"     156199 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"MARTIJN PANTLIN"      124071 "DAVID DOUILLET JUDO"            3374 2767 "10TACLE STUDIOS BELGIUM"      37         2
"VILLE ERIKSSON"       204268 "CRIMSONLAND"                    3044 2112 "10TONS LTD."                   7 1.8571428
"ION HARDIE"            75424 "CRIMSONLAND"                    3044 2112 "10TONS LTD."                   7 1.8571428
"PASI HEINONEN"        150182 "CRIMSONLAND"                    3044 2112 "10TONS LTD."                   7 1.8571428
"VALTTERI PIHLAJAMAKI" 203191 "CRIMSONLAND"                    3044 2112 "10TONS LTD."                   7 1.8571428
"TIMO PALONEN"         195645 "CRIMSONLAND"                    3044 2112 "10TONS LTD."                   7 1.8571428
"TERO ALATALO"         192174 "CRIMSONLAND"                    3044 2112 "10TONS LTD."                   7 1.8571428
"ZACH YOUNG"           215408 "CRIMSONLAND"                    3044 2112 "10TONS LTD."                   7 1.8571428
"SAMPO TOYSSY"         170334 "AZKEND"                         1084 3070 "10TONS LTD."                   5       1.8
"TERO ALATALO"         192174 "AZKEND"                         1084 3070 "10TONS LTD."                   5       1.8
"SARAH BRASS"          171162 "AZKEND"                         1084 3070 "10TONS LTD."                   5       1.8
"MARK MAGDAMIT"        122933 "AZKEND"                         1084 3070 "10TONS LTD."                   5       1.8
"JUSSI LEHTINIEMI"      97380 "AZKEND"                         1084 3070 "10TONS LTD."                   5       1.8
"MATTHEW ENGLE"        129094 "EXCELSIOR PHASE ONE: LYSANDIA"  4613  437 "11TH DIMENSION ENTERTAINMENT"  2         1
"DANIEL BERKE"          38597 "EXCELSIOR PHASE ONE: LYSANDIA"  4613  437 "11TH DIMENSION ENTERTAINMENT"  2         1
"MELINDA HSU"          131194 "EXCELSIOR PHASE TWO: ERRONDOR"  4614 1391 "11TH DIMENSION ENTERTAINMENT"  5       1.6
"JONATHAN P. GWYN"      93055 "EXCELSIOR PHASE TWO: ERRONDOR"  4614 1391 "11TH DIMENSION ENTERTAINMENT"  5       1.6
"DANIEL BERKE"          38597 "EXCELSIOR PHASE TWO: ERRONDOR"  4614 1391 "11TH DIMENSION ENTERTAINMENT"  5       1.6
"JIMMY RIMMER"          87429 "EXCELSIOR PHASE TWO: ERRONDOR"  4614 1391 "11TH DIMENSION ENTERTAINMENT"  5       1.6
"MATTHEW ENGLE"        129094 "EXCELSIOR PHASE TWO: ERRONDOR"  4614 1391 "11TH DIMENSION ENTERTAINMENT"  5       1.6
"PASCAL POCHOL"        150124 "CRUNCHER IN MAZELAND"           3071 1857 "123-D SOFTWARE, INC."         13         1
"JOE FALSETTI"          88485 "CRUNCHER IN MAZELAND"           3071 1857 "123-D SOFTWARE, INC."         13         1
"ARIEL GROSS"           13985 "CRUNCHER IN MAZELAND"           3071 1857 "123-D SOFTWARE, INC."         13         1
"ROBERT J. DOMINIAK"   164065 "CRUNCHER IN MAZELAND"           3071 1857 "123-D SOFTWARE, INC."         13         1
"CRISTIAN SOULOS"       35949 "CRUNCHER IN MAZELAND"           3071 1857 "123-D SOFTWARE, INC."         13         1
"SUSAN SAFRANEK"       186218 "CRUNCHER IN MAZELAND"           3071 1857 "123-D SOFTWARE, INC."         13         1
end

// VERIFY DISTINCT EMPLOYEES IN OBSERVATION PER DEVELOPER-PERIOD COMBINATION
isid developer period_id name, sort

// GENERATE SEQUENTIAL PROJECT NUMBERS FOR DEVELOPERS
by developer (period_id): gen seq = sum(period_id != period_id[_n-1])

// FOR EACH PROJECT COUNT NUMBER OF EMPLOYEES WORKING
by developer seq, sort: gen total_employees = _N

// NOW COUNT HOW MANY EMPLOYEES OF THIS PROJECT REMAIN IN NEXT PROJECT
preserve
keep person_id seq developer
replace seq = seq - 1
drop if seq < 1
tempfile remaining
save `remaining'
restore
merge 1:1 developer seq person_id using `remaining', keep(match master)
by developer seq, sort: egen remaining_on_next_project = total(_merge == 3)
gen fraction_remaining_next_project = remaining_on_next_project/total_employees

drop _merge

Even if this isn't what is actually wanted, perhaps the code will suggest to her how to get whatever it is she does want.

Note: This code assumes that the variable person_id uniquely identifies employees and that for any developer there is a one-to-one correspondence between project_id and period_id. This enables me to work just with the person_id and period_id variables, ignoring the less convenient string and project_id variables. I have checked that this assumption is true in the example data provided, but did not put a check for it into the code above. I will leave it to the original poster to sort that out.

[Katrin Müller]

Dear Clyde, dear William,

thank you both very much for your time and effort, I really appreciate it.

Clyde, to come to your first remark and example:

I find the period_id variable confusing. The numbers look arbitrary, yet in your own code you sort on them, which, to me, implies that you think that increasing period_id numbers correspond to chronological order. If that is the case, let us consider Alzbetka Liptakova, who is part of the "SOWHAT!" S.R.O. team in periods 3028, 3153, and 3375. But that person is apparently not part of the time in period 3153, which comes between 3070 and 3375. So why is that person not "out" before 3153 and then "in" again in 3375?

Alzbetka Liptakova is part of the team in all of the time periods, otherwise he wouldn't be listed in the period in which he was not a member. What my variable "new" indicates is when a team member is part of the team for the first time, i.e. new=1 in the period in which a member works for the first time on the developer's team. So, for Alzbetka Liptakova in period 3028, n=1 as it's the first time he is on the team. In period 3153 he already has been on the team in the period before (3028) and will still be on the team in the next period (3375), which is why in period 3153 new=0 and out=0. In period 3375, he is part of the team for the last time, which is why new=0 and out=1. And this is exactly what I wanted to change: the "out" variable should equal zero in this last period, but should take on the value 1 in the period afterwards, i.e. the period in which the individual is not a member of the team for the first time.
I am sorry if the prior explanation was unclear.

Clyde, I think I get what your code does, which I think would help me a lot as it measures the fraction of remaining team members. Thank you so much! Unfortunately, I have the problem that project_id and period_id do not correspond one-to-one in every case. Should I work with the project_id instead?

Again, thank you! I really appreciate it as I struggle with this for quite a while now.

[Clyde Schechter]

And this is exactly what I wanted to change: the "out" variable should equal zero in this last period, but should take on the value 1 in the period afterwards, i.e. the period in which the individual is not a member of the team for the first time.

Yes, but what is "the period afterwards" in this case? The values of period_id do not consist of consecutive numbers. Even if I assume that they are in correct order, it isn't clear how you handle gaps. For example, Alzbetka Liptakova is engaged by "SOWHAT!" S.R.O. in periods 3028 3153 and 3375. But she skips 3070, which comes between 3153 and 3375. So why isn't she out for 3070 and then in once again in 3153 (and neither in 3375 which is just the next period after 3153). Also, if you want to attribute that "out" value to the "period afterwards," what period is after 3375 (where everybody goes "out" since it is the last period in the data set?) More subtle, if the developer has no projects after a certain period (as is typically the case), do you want to create new observations for that developer and all of its employees in whatever the next time period is, just so you can record everybody as having gone out then?

I have the problem that project_id and period_id do not correspond one-to-one in every case. Should I work with the project_id instead?

I think so, but I'm not sure and you'll need to think about it carefully. If a given employee is on one project in a period but not the other, are they "out" for the second project? And if two projects fall in the same time period, and somebody did their last work for a developer just before that time period, for which of the two projects is the person "out?" Both? Neither? Somehow pick one?

[Katrin Müller]

Thank you very much, Clyde Schechter. You gave the right hint and pointed me the right direction for creating my variable.