Is team member present during year/quarter/month x

[Laura Hill]

Dear all,

I am trying to define whether a person was present during year 0- year x / quarter 0 - quarter x / month 0- month x of a company.
I initially used the following code (My database contains firms started between 2005-2020, so it can be max 16y old (2021 was last data retrieval).
Sidenote: I am using STATA/MP version 15.1

gen IncorporationYear = year(DateIncorporation)
gen IncorporationQuarter = quarter(DateIncorporation)
gen IncorporationMonth = month(DateIncorporation)
gen AppointmentYear = year(AppointmentDate)
gen AppointmentQuarter = quarter(AppointmentDate)
gen AppointmentMonth = month(AppointmentDate)
gen ResignationYear = year(ResignationDate)
gen ResignationQuarter = quarter(ResignationDate)
gen ResignationMonth = month(ResignationDate)


forvalues i = 0/16 {
gen year`i'=0

replace year`i'=1 if AppointmentYear <= YearIncorporation & ResignationDate > YearIncorporation + `i'  | AppointmentYear <= YearIncorporation + `i'  & missing(ResignationDate)
}

forvalues i = 0/64 {
gen quarter`i'=0

replace quarter`i'=1 if AppointmentQuarter <= IncorporationQuarter & ResignationQuarter > IncorporationQuarter + `i'  | AppointmentQuarter <= IncorporationQuarter + `i'  & missing(ResignationQuarter)
}

forvalues i = 0/191 {
gen month`i'=0

replace month`i'=1 if AppointmentMonth <= IncorporationMonth & ResignationMonth > IncorporationMonth + `i'  | AppointmentMonth <= IncorporationMonth + `i'  & missing(ResignationMonth)
}

However, this is very wrong, as I did not interpret/ read up enough on the data variables, as it only creates vars between 1-4 for quarters and 1-12 for months.
I only noticed this mistake when trying to find code to determine distinct employees at those time periods, as some employees either have multiple functions or left and then rejoined the team, but I don't want to double count them when calculating diversity measures.

Eventually my goal is to determine at different time periods using DateIncorporation as the starting date of the company when a team member was present (and not double counting them) to then calculate the diversity at said time period.

I am not sure how to solve this issue. Hopefully, someone can guide me in the right direction.

Below a small excerpt of my data using dataex.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str16 BvdIdNumber str10 DMUci float(DateIncorporation AppointmentDate ResignationDate)
"AT9070422953" "P301627755" 21164 21445 21808
"AT9070422953" "P300272480" 21164 21208     .
"BE0501515635" "P144431731" 19318 19318 21774
"BE0501515635" "P238808799" 19318 20121 21774
"BE0501562254" "P123993699" 19318 19814     .
"BE0501562254" "P044740365" 19318 19318 21487
"BE0505804718" "P103923129" 20054 20054 20800
"BE0505804718" "P238328579" 20054 20240 20800
"BE0505804718" "P246173452" 20054 20240 20800
"BE0518917237" "P042037775" 19414 22140     .
"BE0518917237" "P042037775" 19414 19444     .
"BE0518917237" "P044189380" 19414 19414     .
"BE0835023906" "P011838447" 18716 18716 19708
"BE0835023906" "P116393668" 18716 18716 19708
"BE0835023906" "P019045531" 18716 18716 19708
"BE0840019901" "P386103582" 18897 20956 23147
"BE0840019901" "P386103582" 18897 18897 20946
"BE0840019901" "P392366677" 18897 20956 23147
"BE0840019901" "P203870322" 18897 18897 20946
"BE0840019901" "P432388253" 18897 21519 23711
"BE0841847459" "P343584370" 18969 18969 20101
"BE0841847459" "P124055291" 18969 19009 20101
"BE0843384910" "P086802612" 19024 20521 21746
"BE0843384910" "P043425388" 19024 19052 21746
"BE0843384910" "P261765200" 19024 20997 21746
"BE0843384910" "P204339931" 19024 19897 21274
"BE0846014895" "P013967127" 19117 19117 19577
"BE0846014895" "P013967484" 19117 19117 21497
"BE0849315568" "P009769741" 19261 19270 22081
"BE0849315568" "P185375911" 19261 20300 22081
"BE0849315568" "P431997203" 19261 21298 22081
"BE0849315568" "P199588153" 19261 19261 20300
"BE0849315568" "P117812107" 19261 20072 21243
"BE0849315568" "P199588153" 19261 19261 20300
"BE0849315568" "P009408820" 19261 19261 20072
"BE0849315568" "P199588153" 19261 19261 20300
"BE0873448772" "P044695674" 16548 16548 20542
"BE0873448772" "P044695672" 16548 16548 20542
"BE0874694629" "P121304710" 16610 19815 21173
"BE0874694629" "P413295732" 16610 16610 17535
"BE0874694629" "P041569729" 16610 19206     .
"BE0874694629" "P115304012" 16610 19782     .
"BE0874694629" "P039657415" 16610 17888 18675
"BE0874694629" "P354091451" 16610 16610 17535
"BE0874694629" "P039043297" 16610 17435 17983
"BE0874694629" "P389756281" 16610 19206     .
"BE0875897132" "P044598401" 16674 16674     .
"BE0875897132" "P044705036" 16674 16674 17106
"BE0882140467" "P009015146" 16978 16978 17898
"BE0882140467" "P001951660" 16978 21027 21910
"BE0882140467" "P019262999" 16978 17871 20370
"BE0882140467" "P038701290" 16978 19602 21910
"BE0882140467" "P257628378" 16978 20768 20996
"BE0882140467" "P019262997" 16978 17223 17911
"BE0882140467" "P097428664" 16978 19107     .
"BE0882140467" "P010741830" 16978 19107     .
"BE0882140467" "P048619167" 16978 19602     .
"BE0882140467" "P242564356" 16978 19967 20768
"CHCHE112562400" "P048407478" 16723 19452 19956
"CHCHE112562400" "P048407478" 16723 19452 19452
"CHCHE112562400" "P048407478" 16723 16724 19452
"CHCHE112562400" "P111056006" 16723 16724 19956
"CHCHE112562400" "P048407478" 16723 16724 19956
"CHCHE112620049" "P001464122" 16749 16750 20545
"CHCHE112620049" "P048632042" 16749 16750 20545
"CHCHE112620049" "P142952246" 16749 16750     .
"CHCHE112620049" "P142952246" 16749 16750 20074
end
format %td DateIncorporation
format %td AppointmentDate
format %td ResignationDate

Thanks in advance for your help! If more info/variables are needed let me know.
Best regards,
Laura

[Nick Cox]

Years here are, or should be, what you think they are.

There is a difference between month and quarter of each year and a monthly or quarterly date. So, conventionally we are now in quarter 1 of 2023 but the quarterly date is 2023q1 or yq(2023, 1) which to Stata is 252 given Stata's origin, which is yq(1960, 1) as 0.

Shifting to a different origin as a matter of convenience is literally a problem in translation.

Almost all of what you need to know is somewhere in

help datetime 

together with a lot of stuff that you don't need to know right now. It's hard to write documentation that includes only what you need right now and nothing else.

The functions mofd() and qofd() pull monthly dates and quarterly dates out of Stata daily dates.

I am glad that you didn't mention weeks, which are a small nightmare.

[Laura Hill]

Nick Cox : thank you for the info! Indeed this solves the issue

I have adapted my code and it seems to provide the solution I needed

forvalues i = 0/16 {
gen year`i'=0

bysort BvdIdNumber: replace year`i'= 1 if yofd(AppointmentDate) <= yofd(DateIncorporation) +  `i' & yofd(ResignationDate) > yofd(DateIncorporation) + `i' | yofd(AppointmentDate) <= yofd(DateIncorporation) +  `i' & missing(ResignationDate)

}

forvalues i = 0/64 {
gen quarter`i'=0

bysort BvdIdNumber: replace quarter`i'= 1 if qofd(AppointmentDate) <= qofd(DateIncorporation) +  `i' & qofd(ResignationDate) > qofd(DateIncorporation) + `i' | qofd(AppointmentDate) <= qofd(DateIncorporation) +  `i' & missing(ResignationDate)

}

forvalues i = 0/191 {
gen month`i'=0

bysort BvdIdNumber: replace month`i'= 1 if mofd(AppointmentDate) <= mofd(DateIncorporation) +  `i' & mofd(ResignationDate) > mofd(DateIncorporation) + `i' | mofd(AppointmentDate) <= mofd(DateIncorporation) +  `i' & missing(ResignationDate)

}

Thanks again for the guidance!