Pooling consecutive spells (spell data)

[Adrij Chakraborty]

In my spell data (panel) setting, I have the following arrangement:

* Example generated by -dataex-. For more info, type help dataex
clear
input float(persnr_siab nspell) int(erwstat begorig endorig)
2  1 101 14976 15340
2  2 109 15341 15705
2  3 101 15706 15867
2  4 109 16071 16436
2  5 101 16437 16736
2  6 101 16737 16801
2  7 101 16802 17163
2  8 101 17164 17166
2  9 101 17167 17531
2 10 101 17532 17897
2 12 109 17898 17952
2 13  11 17953 18205
2 15  11 18206 18210
2 16  11 18211 18227
2 17 109 18228 18250
2 18 101 18251 18262
2 19 109 18263 18627
end
format %tdD_m_CY begorig
format %tdD_m_CY endorig
label values erwstat erwstat_en
label def erwstat_en 11 "11 ALG Unemployment benefits", modify
label def erwstat_en 101 "101 Employees liable to social security without special characteristics", modify
label def erwstat_en 102 "102 Trainees without special characteristics", modify
label def erwstat_en 109 "109 Marginal part-time workers", modify

There are consecutive unemployment (rather UI) spells, highlighted in bold (individual 2, spells 13, 15 and 16). The suggestion given in the literature is the following: if there are consecutive UI spells with a gap between them being less than 14 days, the spells are "pooled and treated as one spell". The words in quotes are verbatim.

Questions:
1. Is pooling the same as merging, or collapsing the three spells in this case? FYI, it's not clear from the literature so I believe I have some room to allow interpretation.
2. How to code the pooling of all such consecutive UI spells (with gap < 14 days)?
3. If I collapse the consecutive spells, is there a way to retain information on the other variables in these consecutive rows?

[Clyde Schechter]

assert begorig <= endorig
by persnr_siab (nspell), sort: assert begorig >= endorig[_n-1] if _n > 1

by persnr_siab (nspell): gen superspell = sum(erwstat != 11 ///
    | ((begorig[_n+1]-endorig < 14) & erwstat[_n-1] != 11))

collapse (min) begorig (max) endorig (first) erwstat , by(persnr_siab superspell)

It's collapsing.

As for your third question, I retained the variable erwstat by including it after -(first)- in -collapse-. If there are other variables in your full data set that need to be retained, you can list them there as well. But, be mindful that this only makes sense to do if those variables are constant within the newly combined spell. If they change, then there is no way to retain all of those values when you reduce the consecutive UI spells to a single spell.

[Adrij Chakraborty]

Thanks a lot for the code Clyde Schechter ! Two things to know from this code:

1. I have missed out on a detail, which may be important here: I have other forms of employment (not just limited to 101) and unemployment (not limited to 11) spells. Eventually, I will boil down my sample to just 101s and 11s, but at the stage where I will collapse the consecutive UI spells, other forms of employment and unemployment spells exist in the dataset. Eg. in my dataset -- the three other forms of unemployment are 12, 13, and 15, and the other forms of employment are 102-115.

Do you have any recommendations on how to tweak the code so that the superspell does not eat up the other forms of employment? Case in point (the two rows - for person 1 - highlighted in bold):

* Example generated by -dataex-. For more info, type help dataex
clear
input long persnr_siab float(nspell superspell) int(erwstat begorig endorig) byte quelle
1  1  1 101  5479  5843 1
1  2  2 101  5844  6209 1
1  3  3 101  6210  6574 1
1  4  4 101  6575  6939 1
1  5  5 101  6940  7304 1
1  6  6 101  7305  7581 1
1  7  7 101  7582  7670 1
1  8  8 101  8766  9131 1
1  9  9 101  9132  9496 1
1 10 10 101  9497  9861 1
1 11 11 101  9862 10226 1
1 12 12 102 10227 10291 1
1 13 12  11 10367 10760 2
2  1  1 101 14976 15340 1
2  2  2 109 15341 15705 1
2  3  3 101 15706 15867 1
2  4  4 109 16071 16436 1
2  5  5 101 16437 16736 1
end
format %tdD_m_CY begorig
format %tdD_m_CY endorig
label values erwstat erwstat_en
label def erwstat_en 11 "11 ALG Unemployment benefits", modify
label def erwstat_en 101 "101 Employees liable to social security without special characteristics", modify
label def erwstat_en 102 "102 Trainees without special characteristics", modify
label def erwstat_en 109 "109 Marginal part-time workers", modify
label values quelle quelle_en
label def quelle_en 1 "1 BeH Employee History", modify
label def quelle_en 2 "2 LEH Benefit Recipient History", modify

2. This may be a stupid question:
In addition to the 2-3 variables that I print out using dataex for the forum, I also have around 70 other observables. While writing the last line of your code, is there a way to include all the variables from the varlist after -(first)-? Because if I don't, then I am only left with the erwstat after the collapse. All the other observable variables are gone.

Thanks again for the help!

[Clyde Schechter]

assert begorig <= endorig
by persnr_siab (nspell), sort: assert begorig >= endorig[_n-1] if _n > 1
assert inlist(erwstat, 11, 12, 13, 15) | inrange(erwstat, 101, 115)
gen byte unemployed = inlist(erwstat, 11, 12, 13, 15)
by persnr_siab (nspell): gen gap = begorig - endorig[_n-1]

by persnr_siab (nspell): gen superspell = sum(!unemployed ///
    | ((gap > 14) & unemployed))
    
ds persnr_siab nspell superspell begorig endorig, not
local other_vars `r(varlist)'

collapse (min) begorig (max) endorig (first) `other_vars' , by(persnr_siab superspell)

Notes:

1. This code will accumulate all of the variables in the data set other than persnr_siab, nspell, superspell, begorig, and endorig and carry them forward through -collapse- by selecting the first value appearing in the superspell.
2. Again, I emphasize caution here: if those other variables can change during a superspell, then information will be lost, and there is no way to avoid that happening.

[Andrew Musau]

Here is a different way that may or may not answer your question in #3.

* Example generated by -dataex-. For more info, type help dataex
clear
input float(persnr_siab nspell) int(erwstat begorig endorig)
2  1 101 14976 15340
2  2 109 15341 15705
2  3 101 15706 15867
2  4 109 16071 16436
2  5 101 16437 16736
2  6 101 16737 16801
2  7 101 16802 17163
2  8 101 17164 17166
2  9 101 17167 17531
2 10 101 17532 17897
2 12 109 17898 17952
2 13  11 17953 18205
2 15  11 18206 18210
2 16  11 18211 18227
2 17 109 18228 18250
2 18 101 18251 18262
2 19 109 18263 18627
end
format %tdD_m_CY begorig
format %tdD_m_CY endorig
label values erwstat erwstat_en
label def erwstat_en 11 "11 ALG Unemployment benefits", modify
label def erwstat_en 101 "101 Employees liable to social security without special characteristics", modify
label def erwstat_en 102 "102 Trainees without special characteristics", modify
label def erwstat_en 109 "109 Marginal part-time workers", modify

bys persnr_siab erwstat (begorig): gen tag= ((endorig <= begorig[_n+1]-14)|(begorig <= endorig[_n+1]+14))*erwstat==11
by persnr_siab: gen pool= sum((!tag[_n-1] & tag)|(_n==1 & tag))*tag
bys pool (begorig): replace begorig= begorig[1] if pool>0
bys pool (begorig): replace endorig = endorig[_N] if pool>0
duplicates drop persnr_siab erwstat begorig endorig, force

Res.:

. sort persnr_siab nspell

. l, sepby(persnr_siab)

     +--------------------------------------------------------------------------------------------------------------------------------------+
     | persnr~b   nspell                                                                   erwstat       begorig       endorig   tag   pool |
     |--------------------------------------------------------------------------------------------------------------------------------------|
  1. |        2        1   101 Employees liable to social security without special characteristics   01 Jan 2001   31 Dec 2001     0      0 |
  2. |        2        2                                            109 Marginal part-time workers   01 Jan 2002   31 Dec 2002     0      0 |
  3. |        2        3   101 Employees liable to social security without special characteristics   01 Jan 2003   11 Jun 2003     0      0 |
  4. |        2        4                                            109 Marginal part-time workers   01 Jan 2004   31 Dec 2004     0      0 |
  5. |        2        5   101 Employees liable to social security without special characteristics   01 Jan 2005   27 Oct 2005     0      0 |
  6. |        2        6   101 Employees liable to social security without special characteristics   28 Oct 2005   31 Dec 2005     0      0 |
  7. |        2        7   101 Employees liable to social security without special characteristics   01 Jan 2006   28 Dec 2006     0      0 |
  8. |        2        8   101 Employees liable to social security without special characteristics   29 Dec 2006   31 Dec 2006     0      0 |
  9. |        2        9   101 Employees liable to social security without special characteristics   01 Jan 2007   31 Dec 2007     0      0 |
 10. |        2       10   101 Employees liable to social security without special characteristics   01 Jan 2008   31 Dec 2008     0      0 |
 11. |        2       12                                            109 Marginal part-time workers   01 Jan 2009   24 Feb 2009     0      0 |
 12. |        2       13                                              11 ALG Unemployment benefits   25 Feb 2009   26 Nov 2009     1      1 |
 13. |        2       17                                            109 Marginal part-time workers   27 Nov 2009   19 Dec 2009     0      0 |
 14. |        2       18   101 Employees liable to social security without special characteristics   20 Dec 2009   31 Dec 2009     0      0 |
 15. |        2       19                                            109 Marginal part-time workers   01 Jan 2010   31 Dec 2010     0      0 |
     +--------------------------------------------------------------------------------------------------------------------------------------+

[Adrij Chakraborty]

Here is a different way that may or may not answer your question in #3.

* Example generated by -dataex-. For more info, type help dataex
clear
input float(persnr_siab nspell) int(erwstat begorig endorig)
2 1 101 14976 15340
2 2 109 15341 15705
2 3 101 15706 15867
2 4 109 16071 16436
2 5 101 16437 16736
2 6 101 16737 16801
2 7 101 16802 17163
2 8 101 17164 17166
2 9 101 17167 17531
2 10 101 17532 17897
2 12 109 17898 17952
2 13 11 17953 18205
2 15 11 18206 18210
2 16 11 18211 18227
2 17 109 18228 18250
2 18 101 18251 18262
2 19 109 18263 18627
end
format %tdD_m_CY begorig
format %tdD_m_CY endorig
label values erwstat erwstat_en
label def erwstat_en 11 "11 ALG Unemployment benefits", modify
label def erwstat_en 101 "101 Employees liable to social security without special characteristics", modify
label def erwstat_en 102 "102 Trainees without special characteristics", modify
label def erwstat_en 109 "109 Marginal part-time workers", modify

bys persnr_siab erwstat (begorig): gen tag= ((endorig <= begorig[_n+1]-14)|(begorig <= endorig[_n+1]+14))*erwstat==11
by persnr_siab: gen pool= sum((!tag[_n-1] & tag)|(_n==1 & tag))*tag
bys pool (begorig): replace begorig= begorig[1] if pool>0
bys pool (begorig): replace endorig = endorig[_N] if pool>0
duplicates drop persnr_siab erwstat begorig endorig, force

Res.:

. sort persnr_siab nspell

. l, sepby(persnr_siab)

+--------------------------------------------------------------------------------------------------------------------------------------+
| persnr~b nspell erwstat begorig endorig tag pool |
|--------------------------------------------------------------------------------------------------------------------------------------|
1. | 2 1 101 Employees liable to social security without special characteristics 01 Jan 2001 31 Dec 2001 0 0 |
2. | 2 2 109 Marginal part-time workers 01 Jan 2002 31 Dec 2002 0 0 |
3. | 2 3 101 Employees liable to social security without special characteristics 01 Jan 2003 11 Jun 2003 0 0 |
4. | 2 4 109 Marginal part-time workers 01 Jan 2004 31 Dec 2004 0 0 |
5. | 2 5 101 Employees liable to social security without special characteristics 01 Jan 2005 27 Oct 2005 0 0 |
6. | 2 6 101 Employees liable to social security without special characteristics 28 Oct 2005 31 Dec 2005 0 0 |
7. | 2 7 101 Employees liable to social security without special characteristics 01 Jan 2006 28 Dec 2006 0 0 |
8. | 2 8 101 Employees liable to social security without special characteristics 29 Dec 2006 31 Dec 2006 0 0 |
9. | 2 9 101 Employees liable to social security without special characteristics 01 Jan 2007 31 Dec 2007 0 0 |
10. | 2 10 101 Employees liable to social security without special characteristics 01 Jan 2008 31 Dec 2008 0 0 |
11. | 2 12 109 Marginal part-time workers 01 Jan 2009 24 Feb 2009 0 0 |
12. | 2 13 11 ALG Unemployment benefits 25 Feb 2009 26 Nov 2009 1 1 |
13. | 2 17 109 Marginal part-time workers 27 Nov 2009 19 Dec 2009 0 0 |
14. | 2 18 101 Employees liable to social security without special characteristics 20 Dec 2009 31 Dec 2009 0 0 |
15. | 2 19 109 Marginal part-time workers 01 Jan 2010 31 Dec 2010 0 0 |
+--------------------------------------------------------------------------------------------------------------------------------------+

* Example generated by -dataex-. For more info, type help dataex

clear

input float(persnr_siab nspell) int(erwstat begorig endorig) float(tag pool)

2  1 101 14976 15340 0 0
2  2 109 15341 15705 0 0
2  3 101 15706 15867 0 0
2  4 109 16071 16436 0 0
2  5 101 16437 16736 0 0
2  6 101 16737 16801 0 0
2  7 101 16802 17163 0 0
2  8 101 17164 17166 0 0
2  9 101 17167 17531 0 0
2 10 101 17532 17897 0 0
2 12 109 17898 17952 0 0

2 13  11  5479 22645 1 1

2 17 109 18228 18250 0 0
2 18 101 18251 18262 0 0
2 19 109 18263 18627 0 0
2 20 101 18876 18992 0 0
2 21 101 18993 19307 0 0
end

format %tdD_m_CY begorig
format %tdD_m_CY endorig
label values erwstat erwstat_en
label def erwstat_en 11 "11 ALG Unemployment benefits", modify
label def erwstat_en 101 "101 Employees liable to social security without special characteristics", modify
label def erwstat_en 102 "102 Trainees without special characteristics", modify
label def erwstat_en 109 "109 Marginal part-time workers", modify

Dear Andrew, thanks a lot for the codes!

While I do see that the code ran well for you, I cannot seem to replicate it. When I copy and paste the codes, the begorig for each erw==11 spells always come out to be 01 January 1975 and the endorig for the erw==11 spells always come to be 31 December 2021 (as you can see, in the bold row from the data sample). I don’t quite understand the code, which is why I don’t know what mistake I’m making. Any ideas?

Thanks again!

[Andrew Musau]

Sorry, I should additionally group by the personal identifier.

bys persnr_siab erwstat (begorig): gen tag= ((endorig <= begorig[_n+1]-14)|(begorig <= endorig[_n+1]+14))*erwstat==11
by persnr_siab: gen pool= sum((!tag[_n-1] & tag)|(_n==1 & tag))*tag
bys persnr_siab pool (begorig): replace begorig= begorig[1] if pool>0
bys persnr_siab pool (begorig): replace endorig = endorig[_N] if pool>0
duplicates drop persnr_siab erwstat begorig endorig, force

[Adrij Chakraborty]

Hi Andrew,

Now I understand how the code essentially works. However, the grouping by personal ID is clustering all the UI spells an individual has in her entire biography into one spell. See for example:

Before the code (code block 1):

* Example generated by -dataex-. For more info, type help dataex
clear
input float(persnr_siab nspell) int(erwstat begorig endorig)
40  1 101 17113 17166
40  2 101 17167 17531
40  3 109 17532 17897
40  4 103 17898 18262
40  5 101 18263 18301

40  6  11 18306 18502
40  7  11 18306 18502
40  8  11 18306 18502
40  9  11 18503 18529
40 10  11 18530 18567

40 11 109 18568 18590
40 12 101 18591 18627
40 13 101 18628 18742
40 14 101 18628 18742

40 15  11 18743 18777
40 16  11 18778 18886
40 17  11 18887 18911

40 18 101 18912 18980

40 19  11 18981 19352

40 20 101 19353 19353
end
format %tdD_m_CY begorig
format %tdD_m_CY endorig
label values erwstat erwstat_en
label def erwstat_en 11 "11 ALG Unemployment benefits", modify
label def erwstat_en 12 "12 ALHI Unemployment assistance", modify
label def erwstat_en 101 "101 Employees liable to social security without special characteristics", modify
label def erwstat_en 102 "102 Trainees without special characteristics", modify
label def erwstat_en 103 "103 Employees in partial retirement", modify
label def erwstat_en 105 "105 Interns", modify
label def erwstat_en 106 "106 Student trainees", modify
label def erwstat_en 109 "109 Marginal part-time workers", modify

After the code (code block 2):

* Example generated by -dataex-. For more info, type help dataex
clear
input float(persnr_siab nspell) int(erwstat begorig endorig) float(tag pool)
40  1 101 17113 17166 0 0
40  2 101 17167 17531 0 0
40  3 109 17532 17897 0 0
40  4 103 17898 18262 0 0
40  5 101 18263 18301 0 0

40  6  11 18306 19352 1 1

40 11 109 18568 18590 0 0
40 12 101 18591 18627 0 0
40 13 101 18628 18742 0 0
40 18 101 18912 18980 0 0
40 20 101 19353 19353 0 0
end
format %tdD_m_CY begorig
format %tdD_m_CY endorig
label values erwstat erwstat_en
label def erwstat_en 11 "11 ALG Unemployment benefits", modify
label def erwstat_en 12 "12 ALHI Unemployment assistance", modify
label def erwstat_en 101 "101 Employees liable to social security without special characteristics", modify
label def erwstat_en 102 "102 Trainees without special characteristics", modify
label def erwstat_en 103 "103 Employees in partial retirement", modify
label def erwstat_en 105 "105 Interns", modify
label def erwstat_en 109 "109 Marginal part-time workers", modify
label def erwstat_en .z ".z No entry", modify

If you follow the highlighted rows, it becomes clearer.

Notice how the end date of the combined UI spell after the merging happens to be the end date of the last UI spell in her recorded biography. So, if an individual has more than one cluster of UI spells (that need clustering/merging independently), the code clusters all the UI spells together and forms one combined super-UI-spell. The resultant super-UI-spell wrongly reports the beginning date of that of the first UI spell and the ending date of the last UI spell. Could you tweak the code to correct for this feature?

Compared with the original dataset (code block 1 above), the output I’m ideally looking for is the following (clubbing the erw==11 spells cluster by cluster) (code block 3):

* Example generated by -dataex-. For more info, type help dataex
clear
input float(persnr_siab nspell) int(erwstat begorig endorig)
40  1 101 17113 17166
40  2 101 17167 17531
40  3 109 17532 17897
40  4 103 17898 18262
40  5 101 18263 18301

40  6  11 18306 18567

40 11 109 18568 18590
40 12 101 18591 18627
40 13 101 18628 18742
40 14 101 18628 18742

40 15  11 18743 18911

40 18 101 18912 18980

40 19  11 18981 19352

40 20 101 19353 19353
end
format %tdD_m_CY begorig
format %tdD_m_CY endorig
label values erwstat erwstat_en
label def erwstat_en 11 "11 ALG Unemployment benefits", modify
label def erwstat_en 12 "12 ALHI Unemployment assistance", modify
label def erwstat_en 101 "101 Employees liable to social security without special characteristics", modify
label def erwstat_en 102 "102 Trainees without special characteristics", modify
label def erwstat_en 103 "103 Employees in partial retirement", modify
label def erwstat_en 105 "105 Interns", modify
label def erwstat_en 106 "106 Student trainees", modify
label def erwstat_en 109 "109 Marginal part-time workers", modify

[Clyde Schechter]

FWIW, if you don't succeed in getting the code in #7 to work, you can get what you are asking for by slightly modifying the code in #4. Just get rid of the -collapse- command and replace it with:

by persnr_siab superspell (begorig), sort: replace begorig = begorig[1]
by persnr_siab superspell (endorig), sort: replace endorig = endorig[_N]

This will leave you with all of the original observations, but the superspells will all have the same start and end dates corresponding to the beginning and end of the superspell. Then you don't have to worry about the other variables, and no information is lost if they are not constant within superspells.

[Andrew Musau]

It's getting a bit late where I am at, so if this or Clyde's suggestion does not solve your problem, I will have to take another look tomorrow.

* Example generated by -dataex-. For more info, type help dataex
clear
input float(persnr_siab nspell) int(erwstat begorig endorig)
40  1 101 17113 17166
40  2 101 17167 17531
40  3 109 17532 17897
40  4 103 17898 18262
40  5 101 18263 18301

40  6  11 18306 18502
40  7  11 18306 18502
40  8  11 18306 18502
40  9  11 18503 18529
40 10  11 18530 18567

40 11 109 18568 18590
40 12 101 18591 18627
40 13 101 18628 18742
40 14 101 18628 18742

40 15  11 18743 18777
40 16  11 18778 18886
40 17  11 18887 18911

40 18 101 18912 18980

40 19  11 18981 19352

40 20 101 19353 19353
end
format %tdD_m_CY begorig
format %tdD_m_CY endorig
label values erwstat erwstat_en
label def erwstat_en 11 "11 ALG Unemployment benefits", modify
label def erwstat_en 12 "12 ALHI Unemployment assistance", modify
label def erwstat_en 101 "101 Employees liable to social security without special characteristics", modify
label def erwstat_en 102 "102 Trainees without special characteristics", modify
label def erwstat_en 103 "103 Employees in partial retirement", modify
label def erwstat_en 105 "105 Interns", modify
label def erwstat_en 106 "106 Student trainees", modify
label def erwstat_en 109 "109 Marginal part-time workers", modify


bys persnr_siab nspell erwstat: gen tag= (((endorig <= begorig[_n+1]-14) & (erwstat==erwstat[_n+1]))|((begorig <= endorig[_n-1]+14) & (erwstat==erwstat[_n-1]))|_n==1)*erwstat==11
by persnr_siab: gen pool= sum((!tag[_n-1] & tag)|(_n==1 & tag))*tag
bys persnr_siab pool (begorig): replace begorig= begorig[1] if pool>0
bys persnr_siab pool (begorig): replace endorig = endorig[_N] if pool>0
duplicates drop persnr_siab erwstat begorig endorig if erwstat==11, force

Res.:

. sort persnr_siab nspell

. l, sepby(persnr_siab)

     +--------------------------------------------------------------------------------------------------------------------------------------+
     | persnr~b   nspell                                                                   erwstat       begorig       endorig   tag   pool |
     |--------------------------------------------------------------------------------------------------------------------------------------|
  1. |       40        1   101 Employees liable to social security without special characteristics   08 Nov 2006   31 Dec 2006     0      0 |
  2. |       40        2   101 Employees liable to social security without special characteristics   01 Jan 2007   31 Dec 2007     0      0 |
  3. |       40        3                                            109 Marginal part-time workers   01 Jan 2008   31 Dec 2008     0      0 |
  4. |       40        4                                       103 Employees in partial retirement   01 Jan 2009   31 Dec 2009     0      0 |
  5. |       40        5   101 Employees liable to social security without special characteristics   01 Jan 2010   08 Feb 2010     0      0 |
  6. |       40        8                                              11 ALG Unemployment benefits   13 Feb 2010   01 Nov 2010     1      1 |
  7. |       40       11                                            109 Marginal part-time workers   02 Nov 2010   24 Nov 2010     0      0 |
  8. |       40       12   101 Employees liable to social security without special characteristics   25 Nov 2010   31 Dec 2010     0      0 |
  9. |       40       13   101 Employees liable to social security without special characteristics   01 Jan 2011   25 Apr 2011     0      0 |
 10. |       40       14   101 Employees liable to social security without special characteristics   01 Jan 2011   25 Apr 2011     0      0 |
 11. |       40       15                                              11 ALG Unemployment benefits   26 Apr 2011   11 Oct 2011     1      2 |
 12. |       40       18   101 Employees liable to social security without special characteristics   12 Oct 2011   19 Dec 2011     0      0 |
 13. |       40       19                                              11 ALG Unemployment benefits   20 Dec 2011   25 Dec 2012     1      3 |
 14. |       40       20   101 Employees liable to social security without special characteristics   26 Dec 2012   26 Dec 2012     0      0 |
     +--------------------------------------------------------------------------------------------------------------------------------------+

.