Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Track team changes (turnover + newcomers) within firms (per year)

    Hi everyone,

    I had a question regarding tracking changes within a team, namely if someone new joined the team or someone left the team.
    However, I have no idea how to start. It might be that I'm overlooking a straightforward solution, so apologies upfront.

    Below is an excerpt of my dataset using dataex.
    I know when a firm started (DateIncorporation) (and failed if it failed (dummy)), when a person joined and left (appointment and resignation date, if the resignation date is missing the person is still part of the team), a unique company and employee identifier (BvdIdNumber & DMUci) and some diversity variables (gender, age, nationality).

    My goal with this variable is to look at the influence of diversity in the team (blau/coefficient of variation per year) on firm failure (0/1) BUT use the average team changes per year (as some firms exist for 10 vs 2 years, so absolute numbers might not be too valuable) as an interaction effect.
    In my dataset, there are currently multiple observations per team member (due to other variables), but if necessary I can delete any duplicates on the above-mentioned variables

    So I would need a way to calculate the total number of changes that occurred in the firm lifetime and I would then divide it by the firm age.
    Thank you in advance for your advice and help!

    Best regards,
    Laura

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input str16 BvdIdNumber float(DateIncorporation FirmFailure DateFailure) str10 DMUci float(AppointmentDate ResignationDate) str1 DMGender str10 DMBirthdate str27 Nationality
"AT9070350951" 20454 0     . "P039706490" 20578     . "M" "1978"       "Austria"
"AT9070350951" 20454 0     . "P206368021" 20578     . "M" "1961"       "Austria"
"AT9070350951" 20454 1 22410 "P039706490" 20578     . "M" "1978"       "Austria"
"AT9070350951" 20454 0     . "P039706490" 20578     . "M" "1978"       "Austria"
"AT9070350951" 20454 1 22410 "P206368021" 20578     . "M" "1961"       "Austria"
"AT9070350951" 20454 1 22410 "P039706490" 20578     . "M" "1978"       "Austria"
"AT9070422953" 21164 1 21844 "P301627755" 21445 21808 "M" "10/03/1995" "Austria"
"AT9070422953" 21164 1 21844 "P301627755" 21208     . "M" "10/03/1995" "Austria"
"AT9070422953" 21164 1 21844 "P300272480" 21208     . "M" "17/04/1993" "Austria"
"AT9110749953" 18263 1 21122 "P098788430" 19886     . "M" "27/03/1985" "Bulgaria"
"AT9110939024" 20461 1 21825 "P001968694" 20488     . "M" "30/03/1962" "Germany"
"AT9110939024" 20461 1 21825 "P117216063" 20488     . "M" "1968"       "Germany"
"BE0501515635" 19318 1 21774 "P238808799" 20121 21774 "M" "20/06/1984" "Belgium"
"BE0501515635" 19318 1 21774 "P144431731" 19318 21774 "M" "21/12/1983" "Belgium"
"BE0501562254" 19318 0     . "P123993699" 19814     . "M" "17/08/1988" "Belgium"
"BE0501562254" 19318 1 19907 "P044740365" 19318 21487 "M" "25/03/1972" "Belgium"
"BE0501562254" 19318 1 19907 "P123993699" 19814     . "M" "17/08/1988" "Belgium"
"BE0501562254" 19318 0     . "P044740365" 19318 21487 "M" "25/03/1972" "Belgium"
"BE0501562254" 19318 1 19907 "P123993699" 19814     . "M" "17/08/1988" "Belgium"
"BE0501562254" 19318 1 19907 "P044740365" 19318 21487 "M" "25/03/1972" "Belgium"
"BE0505804718" 20054 0     . "P246173452" 20240 20800 "M" "1974"       "Japan"  
"BE0505804718" 20054 0     . "P238328579" 20240 20800 "M" "1968"       "Japan"  
"BE0505804718" 20054 1 20800 "P246173452" 20240 20800 "M" "1974"       "Japan"  
"BE0505804718" 20054 1 20800 "P238328579" 20240 20800 "M" "1968"       "Japan"  
"BE0505804718" 20054 0     . "P103923129" 20054 20800 "M" "1973"       "Germany"
"BE0505804718" 20054 1 20800 "P103923129" 20054 20800 "M" "1973"       "Germany"
"BE0518917237" 19414 0     . "P042037775" 22140     . "M" "08/03/1946" "Belgium"
"BE0518917237" 19414 0     . "P042037775" 19414     . "M" "08/03/1946" "Belgium"
"BE0518917237" 19414 0     . "P044189380" 19414     . "F" "05/03/1946" "Belgium"
"BE0518917237" 19414 0     . "P044189380" 19414     . "F" "05/03/1946" "Belgium"
end
format %td DateIncorporation
format %td DateFailure
format %td AppointmentDate
format %td ResignationDate
    Last edited by Laura Hill; 10 Feb 2023, 03:28.
    Tags: formatting, panel data, syntax, team
  • #2
    I realised that in my dataex example there are not many firms with large teams, so here I have added another dataex example


    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input str16 BvdIdNumber float(DateIncorporation FirmFailure DateFailure) str10 DMUci float(AppointmentDate ResignationDate) str1 DMGender str10 DMBirthdate str27 Nationality
"BE0822663730" 18284 1 22064 "P415739933" 21285 21428 "M" "21/07/1973" "United States" 
"BE0822663730" 18284 1 22064 "P469602315" 21633 22280 "M" "14/03/1972" "United States" 
"BE0822663730" 18284 1 22064 "P415739933" 21285 21428 "M" "21/07/1973" "United States" 
"BE0822663730" 18284 1 22064 "P210520024" 20852     . "F" "06/03/1964" "United States" 
"BE0822663730" 18284 1 22064 "P300158435" 20453 21299 "F" "07/02/1980" "United Kingdom"
"BE0822663730" 18284 1 22064 "P010015992" 18284 21550 "M" "15/09/1957" "United States" 
"BE0822663730" 18284 1 22064 "P010015992" 18284 21550 "M" "15/09/1957" "United States" 
"BE0822663730" 18284 1 22064 "P210520024" 20852     . "F" "06/03/1964" "United States" 
"BE0822663730" 18284 1 22064 "P469602315" 21633 22280 "M" "14/03/1972" "United States" 
"BE0822663730" 18284 1 22064 "P469602315" 21633 22280 "M" "14/03/1972" "United States" 
"BE0822663730" 18284 1 22064 "P469602315" 21633 22280 "M" "14/03/1972" "United States" 
"BE0822663730" 18284 1 22064 "P300158435" 20453 21299 "F" "07/02/1980" "United Kingdom"
"BE0822663730" 18284 1 22064 "P194256668" 18284 20452 "M" "14/10/1970" "United States" 
"BE0822663730" 18284 1 22064 "P194256668" 18284 20452 "M" "14/10/1970" "United States" 
"BE0824978862" 18364 0     . "P009938840" 18364 19648 "M" "1943"       "Italy"         
"BE0824978862" 18364 1 19648 "P086778852" 18364 19648 "M" "01/02/1944" "Italy"         
"BE0824978862" 18364 1 19648 "P009938840" 18364 19648 "M" "1943"       "Italy"         
"BE0824978862" 18364 0     . "P086778852" 18364 19648 "M" "01/02/1944" "Italy"         
"BE0826292520" 18409 0     . "P004132400" 18409     . "M" "23/07/1963" "United Kingdom"
"BE0840019901" 18897 1 21901 "P386103582" 18897 20946 "M" "12/1962"    "Japan"         
"BE0840019901" 18897 0     . "P386103582" 18897 20946 "M" "12/1962"    "Japan"         
"BE0840019901" 18897 1 21901 "P392366677" 20956 23147 "M" "01/11/1957" "Japan"         
"BE0840019901" 18897 0     . "P432388253" 21519 23711 "M" "18/04/1963" "Japan"         
"BE0840019901" 18897 1 21901 "P386711907" 18897 20946 "M" "07/01/1977" "Japan"         
"BE0840019901" 18897 1 21901 "P203870322" 18897 20946 "M" "03/03/1956" "Japan"         
"BE0840019901" 18897 0     . "P203870322" 18897 20946 "M" "03/03/1956" "Japan"         
"BE0840019901" 18897 0     . "P386711907" 18897 20946 "M" "07/01/1977" "Japan"         
"BE0840019901" 18897 1 21901 "P386103582" 20956 23147 "M" "12/1962"    "Japan"         
"BE0840019901" 18897 0     . "P386103582" 20956 23147 "M" "12/1962"    "Japan"         
"BE0840019901" 18897 1 21901 "P432388253" 21519 23711 "M" "18/04/1963" "Japan"         
"BE0840019901" 18897 0     . "P392366677" 20956 23147 "M" "01/11/1957" "Japan"         
"BE0843384910" 19024 1 21746 "P074478801" 19052 20892 "M" "15/05/1967" "Germany"       
"BE0843384910" 19024 1 21746 "P086802612" 19024 22370 "M" "05/10/1963" "Denmark"       
"BE0843384910" 19024 1 21746 "P261765200" 20997 22735 "M" "18/01/1964" "Japan"         
"BE0843384910" 19024 1 21746 "P204339931" 19897 21433 "M" "28/09/1962" "Japan"         
"BE0843384910" 19024 1 21746 "P043425388" 19052 22735 "M" "1963"       "Denmark"       
"BE0849315568" 19261 1 22081 "P199588153" 19261 20300 "M" "1953"       "United States" 
"BE0849315568" 19261 1 22081 "P199588153" 19261 20300 "M" "1953"       "United States" 
"BE0849315568" 19261 1 22081 "P009769741" 19270 22081 "F" "1963"       "Ireland"       
"BE0849315568" 19261 1 22081 "P431997203" 21298 22081 "F" "1975"       "Ireland"       
"BE0849315568" 19261 1 22081 "P117812107" 20072 21243 "M" "1969"       "United States" 
"BE0849315568" 19261 1 22081 "P199588153" 19261 20300 "M" "1953"       "United States" 
"BE0849315568" 19261 1 22081 "P185375911" 20300 22081 "M" "1981"       "Ireland"       
"BE0849315568" 19261 1 22081 "P009408820" 19261 20072 "M" "19/06/1963" "United States" 
"BE0849315568" 19261 1 22081 "P199588153" 19261 20300 "M" "1953"       "United States" 
"BE0874694629" 16610 1 21173 "P039043297" 17435 17983 "M" "17/12/1973" "United Kingdom"
"BE0874694629" 16610 1 21173 "P115304012" 19782     . "M" "1966"       "Sweden"        
"BE0874694629" 16610 1 21173 "P354091451" 16610 17535 "M" "09/05/1961" "Sweden"        
"BE0874694629" 16610 1 21173 "P121304710" 19815 21173 "M" "1976"       "Germany"       
"BE0874694629" 16610 1 21173 "P389756281" 19206     . "M" "03/07/1972" "United Kingdom"
end
format %td DateIncorporation
format %td DateFailure
format %td AppointmentDate
format %td ResignationDate

    Comment

    • #3
      Do you just want to count the number of appointments and resignations? Additionally, if the same individual is appointed, resigns, and is reappointed again, should we double-count them? Consider:

      Code:
      bys DMBirthdate DMUci (AppointmentDate):gen reappointed= AppointmentDate[1]!=AppointmentDate[_N]
list  BvdIdNumber DateIncorporation DMUci AppointmentDate ResignationDate DMGender DMBirthdate if reappointed
      Res.:

      Res.:

      Code:
           +-------------------------------------------------------------------------------------+
     |  BvdIdNumber   DateInc~n        DMUci   Appoint~e   Resigna~e   DMGender   DMBirt~e |
     |-------------------------------------------------------------------------------------|
 16. | BE0840019901   27sep2011   P386103582   27sep2011   07may2017          M    12/1962 |
 17. | BE0840019901   27sep2011   P386103582   27sep2011   07may2017          M    12/1962 |
 18. | BE0840019901   27sep2011   P386103582   17may2017   17may2023          M    12/1962 |
 19. | BE0840019901   27sep2011   P386103582   17may2017   17may2023          M    12/1962 |
     +-------------------------------------------------------------------------------------+
      • 1 like

      Comment

      • #4
        Hi Andrew Musau, thank you for your reply. Yes indeed counting the number of appointments and resignations might actually be the easiest solution.
        I did not think of this!
        However, I do not want to count the original team being a change (only start counting team changes as of 1y after the creation of the firm for example).

        So perhaps I could do something like the following

        Code:
        bys BvdIdNumber: gen newappointments= count(AppointmentDate) if AppointmentDate > DateIncorporation+365.25
bys BvdIdNumber: gen resignations= count(ResignationDate) 
bys BvdIdNumber: gen changesteam = newappointments + resignations
        I could the potentially divide the variable by the firm age so I could account for older companies having more absolute changes but perhaps not relative changes (for example a 2y company that has had 4 team changes, vs 10y company with 5 changes. In absolute figures they had more team changes, but relative by year they had way less).
        Or could there be potentially be a better (more complex) way to account for firm age. Is there perhaps a way I could create a variable by year (t0= start company, t1= 1 years old...) that shows how many changes occurred in that year?

        And thank you for bringing this to my attention, I believe it would make sense to double count them if they get re-appointed again.
        But it is definitely interesting to see if people are re-appointed (perhaps in the same or in another role).

        Thanks for your help and advice!!

        Comment

        • #5
          If you want to count on a year-to-year basis starting from 1 year after incorporation, I think the following should do it. But do check for any inconsistencies. If using reshape proves difficult, install tolong from SSC, which is faster. The syntax is the same, you just replace "reshape long" with "tolong" in the code.

          Code:
          ssc install tolong, replace
          Code:
          * Example generated by -dataex-. To install: ssc install dataex
clear
input str16 BvdIdNumber float(DateIncorporation FirmFailure DateFailure) str10 DMUci float(AppointmentDate ResignationDate) str1 DMGender str10 DMBirthdate str27 Nationality
"BE0822663730" 18284 1 22064 "P415739933" 21285 21428 "M" "21/07/1973" "United States"
"BE0822663730" 18284 1 22064 "P469602315" 21633 22280 "M" "14/03/1972" "United States"
"BE0822663730" 18284 1 22064 "P415739933" 21285 21428 "M" "21/07/1973" "United States"
"BE0822663730" 18284 1 22064 "P210520024" 20852     . "F" "06/03/1964" "United States"
"BE0822663730" 18284 1 22064 "P300158435" 20453 21299 "F" "07/02/1980" "United Kingdom"
"BE0822663730" 18284 1 22064 "P010015992" 18284 21550 "M" "15/09/1957" "United States"
"BE0822663730" 18284 1 22064 "P010015992" 18284 21550 "M" "15/09/1957" "United States"
"BE0822663730" 18284 1 22064 "P210520024" 20852     . "F" "06/03/1964" "United States"
"BE0822663730" 18284 1 22064 "P469602315" 21633 22280 "M" "14/03/1972" "United States"
"BE0822663730" 18284 1 22064 "P469602315" 21633 22280 "M" "14/03/1972" "United States"
"BE0822663730" 18284 1 22064 "P469602315" 21633 22280 "M" "14/03/1972" "United States"
"BE0822663730" 18284 1 22064 "P300158435" 20453 21299 "F" "07/02/1980" "United Kingdom"
"BE0822663730" 18284 1 22064 "P194256668" 18284 20452 "M" "14/10/1970" "United States"
"BE0822663730" 18284 1 22064 "P194256668" 18284 20452 "M" "14/10/1970" "United States"
"BE0824978862" 18364 0     . "P009938840" 18364 19648 "M" "1943"       "Italy"        
"BE0824978862" 18364 1 19648 "P086778852" 18364 19648 "M" "01/02/1944" "Italy"        
"BE0824978862" 18364 1 19648 "P009938840" 18364 19648 "M" "1943"       "Italy"        
"BE0824978862" 18364 0     . "P086778852" 18364 19648 "M" "01/02/1944" "Italy"        
"BE0826292520" 18409 0     . "P004132400" 18409     . "M" "23/07/1963" "United Kingdom"
"BE0840019901" 18897 1 21901 "P386103582" 18897 20946 "M" "12/1962"    "Japan"        
"BE0840019901" 18897 0     . "P386103582" 18897 20946 "M" "12/1962"    "Japan"        
"BE0840019901" 18897 1 21901 "P392366677" 20956 23147 "M" "01/11/1957" "Japan"        
"BE0840019901" 18897 0     . "P432388253" 21519 23711 "M" "18/04/1963" "Japan"        
"BE0840019901" 18897 1 21901 "P386711907" 18897 20946 "M" "07/01/1977" "Japan"        
"BE0840019901" 18897 1 21901 "P203870322" 18897 20946 "M" "03/03/1956" "Japan"        
"BE0840019901" 18897 0     . "P203870322" 18897 20946 "M" "03/03/1956" "Japan"        
"BE0840019901" 18897 0     . "P386711907" 18897 20946 "M" "07/01/1977" "Japan"        
"BE0840019901" 18897 1 21901 "P386103582" 20956 23147 "M" "12/1962"    "Japan"        
"BE0840019901" 18897 0     . "P386103582" 20956 23147 "M" "12/1962"    "Japan"        
"BE0840019901" 18897 1 21901 "P432388253" 21519 23711 "M" "18/04/1963" "Japan"        
"BE0840019901" 18897 0     . "P392366677" 20956 23147 "M" "01/11/1957" "Japan"        
"BE0843384910" 19024 1 21746 "P074478801" 19052 20892 "M" "15/05/1967" "Germany"      
"BE0843384910" 19024 1 21746 "P086802612" 19024 22370 "M" "05/10/1963" "Denmark"      
"BE0843384910" 19024 1 21746 "P261765200" 20997 22735 "M" "18/01/1964" "Japan"        
"BE0843384910" 19024 1 21746 "P204339931" 19897 21433 "M" "28/09/1962" "Japan"        
"BE0843384910" 19024 1 21746 "P043425388" 19052 22735 "M" "1963"       "Denmark"      
"BE0849315568" 19261 1 22081 "P199588153" 19261 20300 "M" "1953"       "United States"
"BE0849315568" 19261 1 22081 "P199588153" 19261 20300 "M" "1953"       "United States"
"BE0849315568" 19261 1 22081 "P009769741" 19270 22081 "F" "1963"       "Ireland"      
"BE0849315568" 19261 1 22081 "P431997203" 21298 22081 "F" "1975"       "Ireland"      
"BE0849315568" 19261 1 22081 "P117812107" 20072 21243 "M" "1969"       "United States"
"BE0849315568" 19261 1 22081 "P199588153" 19261 20300 "M" "1953"       "United States"
"BE0849315568" 19261 1 22081 "P185375911" 20300 22081 "M" "1981"       "Ireland"      
"BE0849315568" 19261 1 22081 "P009408820" 19261 20072 "M" "19/06/1963" "United States"
"BE0849315568" 19261 1 22081 "P199588153" 19261 20300 "M" "1953"       "United States"
"BE0874694629" 16610 1 21173 "P039043297" 17435 17983 "M" "17/12/1973" "United Kingdom"
"BE0874694629" 16610 1 21173 "P115304012" 19782     . "M" "1966"       "Sweden"        
"BE0874694629" 16610 1 21173 "P354091451" 16610 17535 "M" "09/05/1961" "Sweden"        
"BE0874694629" 16610 1 21173 "P121304710" 19815 21173 "M" "1976"       "Germany"      
"BE0874694629" 16610 1 21173 "P389756281" 19206     . "M" "03/07/1972" "United Kingdom"
end
format %td DateIncorporation
format %td DateFailure
format %td AppointmentDate
format %td ResignationDate

frame put  BvdIdNumber DMUci AppointmentDate ResignationDate DateIncorporation, into(Count)
frame change Count
duplicates drop *, force
gen age= 2023- year(DateIncorporation) + 1
bys BvdIdNumber DMUci (AppointmentDate): gen seq=_n
rename (AppointmentDate ResignationDate) event=
reshape long event, i(BvdIdNumber DMUci seq) j(which) string
local j 0
qui sum age
forval i= 2/`r(max)'{
    local ++j
    bys BvdIdNumber: egen year`i'_APP= total(which=="AppointmentDate" & inrange(event,DateIncorporation+(365*`j'), DateIncorporation+(365*`=`j'+1'))) if `i'<= age-1  
    bys BvdIdNumber: egen year`i'_RES= total(which=="ResignationDate" & inrange(event,DateIncorporation+(365*`j'), DateIncorporation+(365*`=`j'+1'))) if `i'<= age-1
    bys BvdIdNumber: egen year`i'_CHG= total(inrange(event,DateIncorporation+(365*`j'), DateIncorporation+(365*`=`j'+1'))) if `i'<= age-1    
}
          Res.:

          Code:
          . l BvdIdNumber DMUci DateIncorporation age year5_APP-year6_CHG, sepby(BvdIdNumber)

     +---------------------------------------------------------------------------------------------------------------+
     |  BvdIdNumber        DMUci   DateInc~n   age   year5_~P   year5_~S   year5_~G   year6_~P   year6_~S   year6_~G |
     |---------------------------------------------------------------------------------------------------------------|
  1. | BE0822663730   P010015992   22jan2010    14          0          0          0          1          1          2 |
  2. | BE0822663730   P010015992   22jan2010    14          0          0          0          1          1          2 |
  3. | BE0822663730   P194256668   22jan2010    14          0          0          0          1          1          2 |
  4. | BE0822663730   P194256668   22jan2010    14          0          0          0          1          1          2 |
  5. | BE0822663730   P210520024   22jan2010    14          0          0          0          1          1          2 |
  6. | BE0822663730   P210520024   22jan2010    14          0          0          0          1          1          2 |
  7. | BE0822663730   P300158435   22jan2010    14          0          0          0          1          1          2 |
  8. | BE0822663730   P300158435   22jan2010    14          0          0          0          1          1          2 |
  9. | BE0822663730   P415739933   22jan2010    14          0          0          0          1          1          2 |
 10. | BE0822663730   P415739933   22jan2010    14          0          0          0          1          1          2 |
 11. | BE0822663730   P469602315   22jan2010    14          0          0          0          1          1          2 |
 12. | BE0822663730   P469602315   22jan2010    14          0          0          0          1          1          2 |
     |---------------------------------------------------------------------------------------------------------------|
 13. | BE0824978862   P009938840   12apr2010    14          0          0          0          0          0          0 |
 14. | BE0824978862   P009938840   12apr2010    14          0          0          0          0          0          0 |
 15. | BE0824978862   P086778852   12apr2010    14          0          0          0          0          0          0 |
 16. | BE0824978862   P086778852   12apr2010    14          0          0          0          0          0          0 |
     |---------------------------------------------------------------------------------------------------------------|
 17. | BE0826292520   P004132400   27may2010    14          0          0          0          0          0          0 |
 18. | BE0826292520   P004132400   27may2010    14          0          0          0          0          0          0 |
     |---------------------------------------------------------------------------------------------------------------|
 19. | BE0840019901   P203870322   27sep2011    13          0          0          0          2          3          5 |
 20. | BE0840019901   P203870322   27sep2011    13          0          0          0          2          3          5 |
 21. | BE0840019901   P386103582   27sep2011    13          0          0          0          2          3          5 |
 22. | BE0840019901   P386103582   27sep2011    13          0          0          0          2          3          5 |
 23. | BE0840019901   P386103582   27sep2011    13          0          0          0          2          3          5 |
 24. | BE0840019901   P386103582   27sep2011    13          0          0          0          2          3          5 |
 25. | BE0840019901   P386711907   27sep2011    13          0          0          0          2          3          5 |
 26. | BE0840019901   P386711907   27sep2011    13          0          0          0          2          3          5 |
 27. | BE0840019901   P392366677   27sep2011    13          0          0          0          2          3          5 |
 28. | BE0840019901   P392366677   27sep2011    13          0          0          0          2          3          5 |
 29. | BE0840019901   P432388253   27sep2011    13          0          0          0          2          3          5 |
 30. | BE0840019901   P432388253   27sep2011    13          0          0          0          2          3          5 |
     |---------------------------------------------------------------------------------------------------------------|
 31. | BE0843384910   P043425388   01feb2012    12          0          0          0          1          1          2 |
 32. | BE0843384910   P043425388   01feb2012    12          0          0          0          1          1          2 |
 33. | BE0843384910   P074478801   01feb2012    12          0          0          0          1          1          2 |
 34. | BE0843384910   P074478801   01feb2012    12          0          0          0          1          1          2 |
 35. | BE0843384910   P086802612   01feb2012    12          0          0          0          1          1          2 |
 36. | BE0843384910   P086802612   01feb2012    12          0          0          0          1          1          2 |
 37. | BE0843384910   P204339931   01feb2012    12          0          0          0          1          1          2 |
 38. | BE0843384910   P204339931   01feb2012    12          0          0          0          1          1          2 |
 39. | BE0843384910   P261765200   01feb2012    12          0          0          0          1          1          2 |
 40. | BE0843384910   P261765200   01feb2012    12          0          0          0          1          1          2 |
     |---------------------------------------------------------------------------------------------------------------|
 41. | BE0849315568   P009408820   25sep2012    12          0          0          0          1          1          2 |
 42. | BE0849315568   P009408820   25sep2012    12          0          0          0          1          1          2 |
 43. | BE0849315568   P009769741   25sep2012    12          0          0          0          1          1          2 |
 44. | BE0849315568   P009769741   25sep2012    12          0          0          0          1          1          2 |
 45. | BE0849315568   P117812107   25sep2012    12          0          0          0          1          1          2 |
 46. | BE0849315568   P117812107   25sep2012    12          0          0          0          1          1          2 |
 47. | BE0849315568   P185375911   25sep2012    12          0          0          0          1          1          2 |
 48. | BE0849315568   P185375911   25sep2012    12          0          0          0          1          1          2 |
 49. | BE0849315568   P199588153   25sep2012    12          0          0          0          1          1          2 |
 50. | BE0849315568   P199588153   25sep2012    12          0          0          0          1          1          2 |
 51. | BE0849315568   P431997203   25sep2012    12          0          0          0          1          1          2 |
 52. | BE0849315568   P431997203   25sep2012    12          0          0          0          1          1          2 |
     |---------------------------------------------------------------------------------------------------------------|
 53. | BE0874694629   P039043297   23jun2005    19          0          0          0          0          0          0 |
 54. | BE0874694629   P039043297   23jun2005    19          0          0          0          0          0          0 |
 55. | BE0874694629   P115304012   23jun2005    19          0          0          0          0          0          0 |
 56. | BE0874694629   P115304012   23jun2005    19          0          0          0          0          0          0 |
 57. | BE0874694629   P121304710   23jun2005    19          0          0          0          0          0          0 |
 58. | BE0874694629   P121304710   23jun2005    19          0          0          0          0          0          0 |
 59. | BE0874694629   P354091451   23jun2005    19          0          0          0          0          0          0 |
 60. | BE0874694629   P354091451   23jun2005    19          0          0          0          0          0          0 |
 61. | BE0874694629   P389756281   23jun2005    19          0          0          0          0          0          0 |
 62. | BE0874694629   P389756281   23jun2005    19          0          0          0          0          0          0 |
     +---------------------------------------------------------------------------------------------------------------+

.
          or more compactly, appending to the code

          Code:
          contract BvdIdNumber age year*
drop _freq
          Res.:

          Code:
          . l  BvdIdNumber- year4_CHG, sepby(BvdIdNumber)

     +-----------------------------------------------------------------------------------------------------------------------+
     |  BvdIdNumber   age   year2_~P   year2_~S   year2_~G   year3_~P   year3_~S   year3_~G   year4_~P   year4_~S   year4_~G |
     |-----------------------------------------------------------------------------------------------------------------------|
  1. | BE0822663730    14          0          0          0          0          0          0          0          0          0 |
     |-----------------------------------------------------------------------------------------------------------------------|
  2. | BE0824978862    14          0          0          0          0          0          0          0          2          2 |
     |-----------------------------------------------------------------------------------------------------------------------|
  3. | BE0826292520    14          0          0          0          0          0          0          0          0          0 |
     |-----------------------------------------------------------------------------------------------------------------------|
  4. | BE0840019901    13          0          0          0          0          0          0          0          0          0 |
     |-----------------------------------------------------------------------------------------------------------------------|
  5. | BE0843384910    12          0          0          0          1          0          1          0          0          0 |
     |-----------------------------------------------------------------------------------------------------------------------|
  6. | BE0849315568    12          0          0          0          2          2          4          0          0          0 |
     |-----------------------------------------------------------------------------------------------------------------------|
  7. | BE0874694629    19          0          0          0          1          1          2          0          1          1 |
     +-----------------------------------------------------------------------------------------------------------------------+
          Last edited by Andrew Musau; 10 Feb 2023, 10:32.
          • 1 like

          Comment

          • #6
            Andrew Musau , thank you very much for your help and sorry for my late reply.
            The code works perfectly!

            Comment

            Previous Next
            Working...
            X