Count months based on multiples columns (R or Stata)-CodePudding

I would like to count the numbers of months a person has worked for. Separation_month refers to the calendar month of dismissal if there was one and is equal to 0 if the person was not dismissed in the current year (2017). I want to count the months from hire date to dismissal date (if the person was dismissed). If he was not it means he worked until the end of the current year. So I want to count all months of 2017, that is 12 months for 2017 plus the months from other years.

structure(list(id = 1:5, current_year = c(2017L, 2017L, 2017L, 
2017L, 2017L), hire_month = c(2L, 9L, 10L, 3L, 2L), hire_year = c(2016L, 
2014L, 1980L, 2017L, 2017L), separation_month = c(0L, 3L, 4L, 
4L, 0L)), class = "data.frame", row.names = c(NA, -5L))

  id current_year hire_month hire_year separation_month
1  1         2017          2      2016                0
2  2         2017          9      2014                3
3  3         2017         10      1980                4
4  4         2017          3      2017                4
5  5         2017          2      2017                0

E.g. for the first observation, I expect there to be 23 months (he worked for 11 months in 2016 and for 12 months in 2017 since he was not separated from his job).

CodePudding user response：

Stata:

gen months_worked = separation_month  (separation_month==0)*12
replace months_worked = months_worked   (current_year-hire_year)*12-hire_month 1

df %>% 
  mutate(months_worked = separation_month   (separation_month<1)*12,
         months_worked = months_worked   (current_year-hire_year)*12-hire_month 1
  )

CodePudding user response：

Another Stata solution:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id int current_year byte hire_month int hire_year byte separation_month
1 2017  2 2016 0
2 2017  9 2014 3
3 2017 10 1980 4
4 2017  3 2017 4
5 2017  2 2017 0
end

gen wanted = 1   cond(separation_month == 0, ym(2017, 12) - ym(hire_year, hire_month), ym(2017, separation_month) - ym(hire_year, hire_month))