Keeping all spells in spEmp and merge with other data files

Dear Colleagues , I am using SC1 and interested in respondent’s employment biography. Therefore, I would like to merge spEmp with other data files such as CohortProfil, but keeping all the employment spells in spEmp.
Could you please let me know the best way to merge different data files in SC1 where I can keep all the spells in spEmp?
Many thanks in advance.


Hello Saikat,

There are several possible approaches to your concern and it all depends on how you want to link and analyze the employment spells with the other variables.
As you know, the spells were collected retrospectively in order to have a complete life history of the person. However, this also means that within a wave/survey (particularly in the case of initial surveys or the first use of a spell instrument), life episodes were queried that lie in the person’s past. Especially when it comes to linking the spells with other variables, you have to keep that in mind. Because now you basically have three options:

  1. From a purely technical point of view, you can use the wave variable contained in the data for merging. In the vast majority of cases, however, this procedure is not recommended because it means that the spells collected retrospectively and from the past are also linked to information that was valid at the time of the interview, and in most cases this makes no sense. However, there may be analysis scenarios for which this could make sense.
  2. If you are mainly interested in the spell information, you can also view the additional information from pTarget, pParent or other data sets as time-constant information and then merge this to ALL employment spells. That would be an m:1 merge based on ID_t. To do this, however, you would first have to limit the information to be merged from additional files to one line per person.
  3. Then you can also establish a direct temporal relationship between the time of the interview and the actual start and end times of the spells. This means that the information from the panel datasets is only matched to those employment spells that were current at the time of the respective interview. In this case, you would have to generate a single variable for the time of the interview in the dataset to be merged (e.g. CohortProfile) (generate interviewdate = ym(tx8620y,tx8620m)). In order to then merge this data from CohortProfile to the corresponding spells, you have to carry out a so-called monthly splitting. Take a look at this forum post (SC5: spVoctrain, spSchool in wide-Format). Unfortunately it’s in German, but I’m sure your German is good enough by now :wink:

Best regards and good luck,