Convert start/end variables into years

[carla Oth]

Hi all,

I am working with some data that is structured as follows:

Person	Company	Role	Role start date	Role end date
John	A	Manager	2014	2016
John	B	Director	2013	2015
Luke	B	Manager	2014	2015
Lucy	C	Advisor	2014	2015

What I am interested in obtaining is the following: I want to see, for each person, how many simultaneous roles they had, and in which years. For instance, John worked simultaneously in company A and B for the years 2014-2015. I would like to end up with something like this:

Person	Year	Simultaneous roles
John	2013	1
John	2014	2
John	2015	2
John	2016	1
Luke	2014	1
Luke	2015	1
Lucy	2014	1
Lucy	2015	1

As you can see, for simplicity I deleted the company and role variables (because I do not care about the company or role role, but whether a person has 2 or more roles at once), and of course the start and end dates, as I want to have panel data.

The new variable simultaneous roles indicates the number of roles that an individual has in a given year. It can be 1, 2, 3... Besides, I would like to include the whole sample period (2000-2019), so in the years when the individuals were not employed, the variable simultaneous roles would simply indicate 0.


Thank you very much in advance!

Best regards,

Carla

[Andrew Musau]

* Example generated by -dataex-. For more info, type help dataex
clear
input str4 person str1 company str8 role int(rolestartdate roleenddate)
"John" "A" "Manager"  2014 2016
"John" "B" "Director" 2013 2015
"Luke" "B" "Manager"  2014 2015
"Lucy" "C" "Advisor"  2014 2015
end

gen duration= roleenddate -rolestartdate +1
expand duration, g(new)
bys person company role (new): replace rolestartdate = rolestartdate[_n-1]+ 1 if new
rename rolestartdate year
contract person year, freq(simultaneous_roles)

Res.:

. l, sepby(person)

     +--------------------------+
     | person   year   simult~s |
     |--------------------------|
  1. |   John   2013          1 |
  2. |   John   2014          2 |
  3. |   John   2015          2 |
  4. |   John   2016          1 |
     |--------------------------|
  5. |   Lucy   2014          1 |
  6. |   Lucy   2015          1 |
     |--------------------------|
  7. |   Luke   2014          1 |
  8. |   Luke   2015          1 |
     +--------------------------+