Generating cross-sectional information from employment spells

[Evelyn Mare]

Hi all,

I am currently working with a polish data set and would like to generate the professional experience for a person from their reported employment spells. I can only conduct cross-sectional analyses, so I need to generate their cumlated experinece up until the last reported episode.

My main problem is that the spells overlap, as many people were simultaneously working in multiple jobs. I'll give an example below for the Person with the ID "89".

ID    start_y  end_y
89    1964    1967    
89    1967    1968
89    1972    1974
89    1975    1986
89    1985    2013
89    1973    1990
89    1990    2011
89    1999    2001
89    2001    2002
89    1972    1974
89    2013    2015
89    2014    2015
89    2014    2015

I am very new to working with spell data, so I would really appreciate all tipps. What I would ultimately like to do is to generate the cumulated experience, without the overlaps, i.e. in this example from 1964 to 2015 and simulatenously ensure that there aren's any gaps.

Thank you all,

Evelyn

[Clyde Schechter]

What I would ultimately like to do is to generate the cumulated experience, without the overlaps

What does that mean? Can you hand-calculate what you are looking for in the example you show, and show that?

and simulatenously ensure that there aren's any gaps.

Again, I don't know what you mean by that. But if you want to create a variable that tells you whether an ID has a gap, you can do this:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(ID start_y end_y)
89 1964 1967
89 1967 1968
89 1972 1974
89 1975 1986
89 1985 2013
89 1973 1990
89 1990 2011
89 1999 2001
89 2001 2002
89 1972 1974
89 2013 2015
89 2014 2015
89 2014 2015
end

gen long obs_no = _n
reshape long @y, i(obs_no) j(event) string
by ID (y), sort: gen status = sum((event == "start_") - (event == "end_"))
by ID: egen has_gap = max(status == 0)

In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 15.1 or a fully updated version 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use -dataex-.

[Evelyn Mare]

Dear Mr. Schechter,

thank you so much for your help!!

What does that mean? Can you hand-calculate what you are looking for in the example you show, and show that?

What I would like to do is the following: Person 89 entered the labor market in 1964 and reported the last employment for 2015. So per se that would mean 2015-1964 =51 years of work experience. I then 'simply' have to ensure that the person wasn't unemployed in between (e.g. the episodes might not span from 1981-1992), as that should reduce the overall years worked.

Thank you so much for the code!!!!!! I was able to replicate it with my data, but was unable to take ot a step further and link it back to the years that are potentially missing. And thank you for the tipp on dataex, this is from now on what I will use!

Thank you so much,

Evelyn

[Clyde Schechter]

To calculate the total number of years in which each ID was employed:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(ID start_y end_y)
89 1964 1967
89 1967 1968
89 1972 1974
89 1975 1986
89 1985 2013
89 1973 1990
89 1990 2011
89 1999 2001
89 2001 2002
89 1972 1974
89 2013 2015
89 2014 2015
89 2014 2015
end

gen long obs_no = _n
reshape long @y, i(obs_no) j(event) string
gsort ID y -event
by ID (y): gen status = sum((event == "start_") - (event == "end_"))
by ID: egen has_gap = max(status == 0)

//    CALCULATE TOTAL YEARS OF EMPLOYMENT FOR EACH ID
replace status = !!status
gsort ID y -event
by ID (y): gen spell = sum(status != status[_n-1])
by ID spell (y), sort: gen spell_duration = y[_N]-y[1] + 1 if _n == 1
by ID: egen total_years_employed = total(cond(status == 1, spell_duration, .))

Note: Reviewing the code sent earlier, I noticed there was a subtle error. By not specifying that the observations needed to be sorted with start_ preceding end_ within a given ID y combination, the order was left to chance, which could lead to the presence or absence of gaps being calculated incorrectly. The bold faced lines above correct that problem.

The code following the CALCULATE TOTAL YEARS OF EMPLOYMENT comment are responsive to your explanation in #3 as I understand it. Please note that I am assuming that even if a person holds several different jobs in one year, it still counts as only one year. If that is not what you intend, post back with a clarification and I will modify the code accordingly. I am also assuming that when you have an observation where, for example, start_y is 1964 and end_y = 1967, that both 1964 and 1967 are included, so that this is four years.