Count number of days in month covered by spell

Henry Strawforrd

Join Date: Sep 2021

Posts: 228
#1

Count number of days in month covered by spell

26 Jul 2023, 03:39

I have spell data containing beginning and end date of spell at daily level. I am looping through months and would like to count in each month the number of days that are covered by the spell.

For example, if start date is 30.03.2010 and end date is 30.12.2010, then the variable should count 2 days in March 2010 and 30 days in April 2010 etc.
Tags: None
Nick Cox

Join Date: Mar 2014

Posts: 35544
#2

26 Jul 2023, 03:45

Where are you putting the results?
Comment
Henry Strawforrd

Join Date: Sep 2021

Posts: 228
#3

26 Jul 2023, 04:00

I create separate datasets for each month and then append all months
Comment
Nick Cox

Join Date: Mar 2014

Posts: 35544
#4

26 Jul 2023, 04:09

I wouldn't do that. But please post a real or at least realistic data example. What are the earliest and latest dates in your dataset?
Comment

Henry Strawforrd

Join Date: Sep 2021
Posts: 228

28 Jul 2023, 02:44

Dataex

Code:

* Example generated by -dataex-. For more info, type help dataex
clear
input long(persnr betnr) byte(spell quelle) int(begepi endepi erwstat)
988050 2654539  1 1  5479  5843   101
988050 2654539  2 1  5844  6209   101
988050 2654539  3 1  6210  6574   101
988050 2654539  4 1  6575  6939   101
988050 2654539  5 1  6940  7304   101
988050 2654539  6 1  7305  7581   101
988050 2654539  7 1  7582  7670   101
988050 2654539  8 1  8766  9131   101
988050 2654539  9 1  9132  9496   101
988050 2654539 10 1  9497  9861   101
988050 2654539 11 1  9862 10226   101
988050 2654539 12 1 10227 10291   102
988050      .n 13 2 10367 10760    11
988102 2683136  1 1 14976 15340   101
988102 2683136  2 1 15341 15705   109
988102 2683136  3 1 15706 15867   101
988102 2797563  4 1 16071 16436   109
988102 2797563  5 1 16437 16736   101
988102 2026217  6 1 16737 16801   101
988102 2026217  7 1 16802 17163   101
988102 2026217  8 1 17164 17166   101
988102 2026217  9 1 17167 17531   101
988102 2026217 10 1 17532 17673   101
988102 2026217 11 1 17674 17897   101
988102      .n 12 6 17674 17897    33
988102 2026217 13 1 17898 17952   109
988102      .n 14 6 17898 17952    33
988102      .n 15 2 17953 18118    11
988102      .n 16 6 17953 18118    33
988102      .n 17 2 18119 18205    11
988102      .n 18 6 18119 18205    31
988102      .n 19 2 18206 18210    11
988102      .n 20 4 18206 18210 10001
988102      .n 21 6 18206 18210    31
988102      .n 22 2 18211 18227    11
988102      .n 23 6 18211 18227    33
988102 2184204 24 1 18228 18250   109
988102 2184204 25 1 18251 18262   101
988102 2184204 26 1 18263 18627   109
988102 2184204 27 1 18628 18875   101
988102 2184204 28 1 18876 18992   101
988102 2184204 29 1 18993 19307   101
988102 1861604 30 1 19308 19358   101
988102 1861604 31 1 19359 19723   101
988102 1861604 32 1 19724 20088   101
988102 1861604 33 1 20089 20453   101
988102 1861604 34 1 20454 20624   101
988102 1861604 35 1 20625 20673   101
988102      .n 36 6 20625 20673    33
988102 1861604 37 1 20674 20711   101
988196 2482619  1 1  7944  8035   101
988196 2482619  2 1  8036  8400   101
988196 2482619  3 1  8401  8765   101
988196 2482619  4 1  8766  9131   101
988196 2482619  5 1  9132  9496   101
988196 2482619  6 1  9497  9861   101
988196 2482619  7 1  9862 10206   101
988196      .n  8 2 10207 10866    11
988196      .n  9 6 13460 14180    33
988260      .n  1 2 11323 11465    12
988260 1728968  2 1 11466 11660   101
988260      .n  3 2 11466 11660    12
988260      .n  4 2 11661 11667    12
988260 1661662  5 1 11688 11894   101
988260      .n  6 2 11895 11919    11
988260 1917100  7 1 11920 11960   101
988260      .n  8 2 11920 11960    11
988260 1917100  9 1 11961 12053   101
988260 1917100 10 1 12054 12063   101
988260 1298705 11 1 12099 12133   101
988260 2419355 12 1 12134 12183   101
988260 1298705 13 1 12134 12183   101
988260 2419355 14 1 12184 12280   101
988260      .n 15 2 12318 12431    11
988260 2065348 16 1 12434 12450   101
988260      .n 17 2 12501 12506    11
988260 2517749 18 1 12544 12638   101
988260 2738108 19 1 12766 12783   101
988260 2738108 20 1 12784 13148   101
988260 2738108 21 1 13149 13319   101
988260 2738108 22 1 13320 13514   101
988260      .n 23 6 13320 13514    31
988260      .n 24 2 13515 13795    11
988260      .n 25 6 13515 13795    31
988260      .n 26 2 13796 13842    11
988260      .n 27 6 13796 13842    31
988260      .n 28 2 13843 13888    11
988260      .n 29 6 13843 13888    33
988260 2738108 30 1 13889 13959   101
988260      .n 31 2 13889 13959    11
988260 2738108 32 1 13960 13974   101
988260 1896655 33 1 13975 14108   101
988260      .n 34 2 14109 14282    12
988260      .n 35 6 14109 14282    31
988260      .n 36 2 14317 14398    11
988260      .n 37 6 14317 14398    31
988260      .n 38 2 14399 14729    11
988260      .n 39 6 14399 14729    31
988260      .n 40 2 14730 14822    11
988260      .n 41 6 14730 14822    31
end
format %tdD_m_CY begepi
format %tdD_m_CY endepi
label values betnr miss_de
label def miss_de .n ".n Fehlt syst.", modify
label values quelle quelle_de
label def quelle_de 1 "1 BeH Beschaeftigtenhistorik", modify
label def quelle_de 2 "2 LEH Leistungsempfaengerhistorik", modify
label def quelle_de 4 "4 MTH Massnahmeteilnahmehistorik", modify
label def quelle_de 6 "6 ASU Arbeitsuchendenhistorik", modify
label values erwstat erwstat_de
label def erwstat_de 11 "11 ALG Arbeitslosengeld", modify
label def erwstat_de 12 "12 ALHI Arbeitslosenhilfe", modify
label def erwstat_de 31 "31 Arbeitslos (ALO)", modify
label def erwstat_de 33 "33 Nicht arbeitslos arbeitssuchend (NALO)", modify
label def erwstat_de 101 "101 Sozialversicherungspflichtig Beschaeftigte ohne besondere Merkmale", modify
label def erwstat_de 102 "102 Auszubildende ohne besondere Merkmale", modify
label def erwstat_de 109 "109 Geringfuegig Beschaeftigte", modify
label def erwstat_de 10001 "10001 Aktivierung und berufliche Eingliederung", modify

Last edited by Henry Strawforrd; 28 Jul 2023, 02:46.

Comment

Henry Strawforrd

Join Date: Sep 2021

Posts: 228
#6

28 Jul 2023, 02:50

Originally posted by Nick Cox View Post

I wouldn't do that. But please post a real or at least realistic data example. What are the earliest and latest dates in your dataset?

Earliest date is January 1975, end is December 2021
Comment

Henry Strawforrd

Join Date: Sep 2021
Posts: 228

28 Jul 2023, 02:52

Originally posted by Henry Strawforrd View Post

Dataex

Code:

* Example generated by -dataex-. For more info, type help dataex
clear
input long(persnr betnr) byte(spell quelle) int(begepi endepi erwstat)
988050 2654539 1 1 5479 5843 101
988050 2654539 2 1 5844 6209 101
988050 2654539 3 1 6210 6574 101
988050 2654539 4 1 6575 6939 101
988050 2654539 5 1 6940 7304 101
988050 2654539 6 1 7305 7581 101
988050 2654539 7 1 7582 7670 101
988050 2654539 8 1 8766 9131 101
988050 2654539 9 1 9132 9496 101
988050 2654539 10 1 9497 9861 101
988050 2654539 11 1 9862 10226 101
988050 2654539 12 1 10227 10291 102
988050 .n 13 2 10367 10760 11
988102 2683136 1 1 14976 15340 101
988102 2683136 2 1 15341 15705 109
988102 2683136 3 1 15706 15867 101
988102 2797563 4 1 16071 16436 109
988102 2797563 5 1 16437 16736 101
988102 2026217 6 1 16737 16801 101
988102 2026217 7 1 16802 17163 101
988102 2026217 8 1 17164 17166 101
988102 2026217 9 1 17167 17531 101
988102 2026217 10 1 17532 17673 101
988102 2026217 11 1 17674 17897 101
988102 .n 12 6 17674 17897 33
988102 2026217 13 1 17898 17952 109
988102 .n 14 6 17898 17952 33
988102 .n 15 2 17953 18118 11
988102 .n 16 6 17953 18118 33
988102 .n 17 2 18119 18205 11
988102 .n 18 6 18119 18205 31
988102 .n 19 2 18206 18210 11
988102 .n 20 4 18206 18210 10001
988102 .n 21 6 18206 18210 31
988102 .n 22 2 18211 18227 11
988102 .n 23 6 18211 18227 33
988102 2184204 24 1 18228 18250 109
988102 2184204 25 1 18251 18262 101
988102 2184204 26 1 18263 18627 109
988102 2184204 27 1 18628 18875 101
988102 2184204 28 1 18876 18992 101
988102 2184204 29 1 18993 19307 101
988102 1861604 30 1 19308 19358 101
988102 1861604 31 1 19359 19723 101
988102 1861604 32 1 19724 20088 101
988102 1861604 33 1 20089 20453 101
988102 1861604 34 1 20454 20624 101
988102 1861604 35 1 20625 20673 101
988102 .n 36 6 20625 20673 33
988102 1861604 37 1 20674 20711 101
988196 2482619 1 1 7944 8035 101
988196 2482619 2 1 8036 8400 101
988196 2482619 3 1 8401 8765 101
988196 2482619 4 1 8766 9131 101
988196 2482619 5 1 9132 9496 101
988196 2482619 6 1 9497 9861 101
988196 2482619 7 1 9862 10206 101
988196 .n 8 2 10207 10866 11
988196 .n 9 6 13460 14180 33
988260 .n 1 2 11323 11465 12
988260 1728968 2 1 11466 11660 101
988260 .n 3 2 11466 11660 12
988260 .n 4 2 11661 11667 12
988260 1661662 5 1 11688 11894 101
988260 .n 6 2 11895 11919 11
988260 1917100 7 1 11920 11960 101
988260 .n 8 2 11920 11960 11
988260 1917100 9 1 11961 12053 101
988260 1917100 10 1 12054 12063 101
988260 1298705 11 1 12099 12133 101
988260 2419355 12 1 12134 12183 101
988260 1298705 13 1 12134 12183 101
988260 2419355 14 1 12184 12280 101
988260 .n 15 2 12318 12431 11
988260 2065348 16 1 12434 12450 101
988260 .n 17 2 12501 12506 11
988260 2517749 18 1 12544 12638 101
988260 2738108 19 1 12766 12783 101
988260 2738108 20 1 12784 13148 101
988260 2738108 21 1 13149 13319 101
988260 2738108 22 1 13320 13514 101
988260 .n 23 6 13320 13514 31
988260 .n 24 2 13515 13795 11
988260 .n 25 6 13515 13795 31
988260 .n 26 2 13796 13842 11
988260 .n 27 6 13796 13842 31
988260 .n 28 2 13843 13888 11
988260 .n 29 6 13843 13888 33
988260 2738108 30 1 13889 13959 101
988260 .n 31 2 13889 13959 11
988260 2738108 32 1 13960 13974 101
988260 1896655 33 1 13975 14108 101
988260 .n 34 2 14109 14282 12
988260 .n 35 6 14109 14282 31
988260 .n 36 2 14317 14398 11
988260 .n 37 6 14317 14398 31
988260 .n 38 2 14399 14729 11
988260 .n 39 6 14399 14729 31
988260 .n 40 2 14730 14822 11
988260 .n 41 6 14730 14822 31
end
format %tdD_m_CY begepi
format %tdD_m_CY endepi
label values betnr miss_de
label def miss_de .n ".n Fehlt syst.", modify
label values quelle quelle_de
label def quelle_de 1 "1 BeH Beschaeftigtenhistorik", modify
label def quelle_de 2 "2 LEH Leistungsempfaengerhistorik", modify
label def quelle_de 4 "4 MTH Massnahmeteilnahmehistorik", modify
label def quelle_de 6 "6 ASU Arbeitsuchendenhistorik", modify
label values erwstat erwstat_de
label def erwstat_de 11 "11 ALG Arbeitslosengeld", modify
label def erwstat_de 12 "12 ALHI Arbeitslosenhilfe", modify
label def erwstat_de 31 "31 Arbeitslos (ALO)", modify
label def erwstat_de 33 "33 Nicht arbeitslos arbeitssuchend (NALO)", modify
label def erwstat_de 101 "101 Sozialversicherungspflichtig Beschaeftigte ohne besondere Merkmale", modify
label def erwstat_de 102 "102 Auszubildende ohne besondere Merkmale", modify
label def erwstat_de 109 "109 Geringfuegig Beschaeftigte", modify
label def erwstat_de 10001 "10001 Aktivierung und berufliche Eingliederung", modify

The issue that complicates working with this data is that there are parallel spells.

For your interest, I am posting an exceprt of the test data available here: https://fdz.iab.de/en/pd_hd/weakly-a...rsion-7521-v1/

Comment

Hemanshu Kumar

Join Date: Mar 2015

Posts: 1357
#8

28 Jul 2023, 04:02

Sorry, I am not sure what structure you want your data to have and what precisely you want to compute.

I see each observation in your data only has a spell for a specific year -- is this always true?

Are you perhaps looking to create 12 variables, one for each month, each of which will contain how many days in that month are part of the spell?
Or do you want to create a variable for each month-year in the full dataset (so maybe 12 x 46 variables)? Or something else entirely?
Comment

Hemanshu Kumar

Join Date: Mar 2015
Posts: 1357

28 Jul 2023, 04:21

If you are looking to create 12 variables, one for each month, and want them to be filled with the number of days in that month that are contained in the spell, then this code might do the trick.

Code:

gen int year_beg = year(begepi)
gen int year_end = year(endepi)
sum year_beg , meanonly
local min_year = r(min)
sum year_end, meanonly
local max_year = r(max)

forvalues mm = 1/12 {
    gen byte month_`mm' = 0
    forvalues yr = `min_year'/`max_year' {
        local mm_start = date("01-`mm'-`yr'", "DMY")
        local mm_end = date("`=daysinmonth(`mm_start')'-`mm'-`yr'", "DMY")
        replace month_`mm' = month_`mm' + min(`mm_end', endepi) - max(`mm_start', begepi) + 1 if begepi <= `mm_end' & endepi >= `mm_start'
    }
}
drop year_beg year_end

which produces:

Code:

. list persnr spell begepi endepi month_* in 1/20, sepby(persnr) noobs

  +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | persnr   spell        begepi        endepi   month_1   month_2   month_3   month_4   month_5   month_6   month_7   month_8   month_9   month_10   month_11   month_12 |
  |-----------------------------------------------------------------------------------------------------------------------------------------------------------------------|
  | 988050       1   01 Jan 1975   31 Dec 1975        31        28        31        30        31        30        31        31        30         31         30         31 |
  | 988050       2   01 Jan 1976   31 Dec 1976        31        29        31        30        31        30        31        31        30         31         30         31 |
  | 988050       3   01 Jan 1977   31 Dec 1977        31        28        31        30        31        30        31        31        30         31         30         31 |
  | 988050       4   01 Jan 1978   31 Dec 1978        31        28        31        30        31        30        31        31        30         31         30         31 |
  | 988050       5   01 Jan 1979   31 Dec 1979        31        28        31        30        31        30        31        31        30         31         30         31 |
  | 988050       6   01 Jan 1980   03 Oct 1980        31        29        31        30        31        30        31        31        30          3          0          0 |
  | 988050       7   04 Oct 1980   31 Dec 1980         0         0         0         0         0         0         0         0         0         28         30         31 |
  | 988050       8   01 Jan 1984   31 Dec 1984        31        29        31        30        31        30        31        31        30         31         30         31 |
  | 988050       9   01 Jan 1985   31 Dec 1985        31        28        31        30        31        30        31        31        30         31         30         31 |
  | 988050      10   01 Jan 1986   31 Dec 1986        31        28        31        30        31        30        31        31        30         31         30         31 |
  | 988050      11   01 Jan 1987   31 Dec 1987        31        28        31        30        31        30        31        31        30         31         30         31 |
  | 988050      12   01 Jan 1988   05 Mar 1988        31        29         5         0         0         0         0         0         0          0          0          0 |
  | 988050      13   20 May 1988   17 Jun 1989        31        28        31        30        43        47        31        31        30         31         30         31 |
  |-----------------------------------------------------------------------------------------------------------------------------------------------------------------------|
  | 988102       1   01 Jan 2001   31 Dec 2001        31        28        31        30        31        30        31        31        30         31         30         31 |
  | 988102       2   01 Jan 2002   31 Dec 2002        31        28        31        30        31        30        31        31        30         31         30         31 |
  | 988102       3   01 Jan 2003   11 Jun 2003        31        28        31        30        31        11         0         0         0          0          0          0 |
  | 988102       4   01 Jan 2004   31 Dec 2004        31        29        31        30        31        30        31        31        30         31         30         31 |
  | 988102       5   01 Jan 2005   27 Oct 2005        31        28        31        30        31        30        31        31        30         27          0          0 |
  | 988102       6   28 Oct 2005   31 Dec 2005         0         0         0         0         0         0         0         0         0          4         30         31 |
  | 988102       7   01 Jan 2006   28 Dec 2006        31        28        31        30        31        30        31        31        30         31         30         28 |
  +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+

When a spell contains the same month from more than one year, e.g. for May and June of 1988 and 1989 in observation #13, the code will add up the days of that month from both years.

Last edited by Hemanshu Kumar; 28 Jul 2023, 04:53.

Comment

Henry Strawforrd

Join Date: Sep 2021

Posts: 228
#10

28 Jul 2023, 05:14

Sorry, I am not sure what structure you want your data to have and what precisely you want to compute.

I see each observation in your data only has a spell for a specific year -- is this always true?

No, unfortunately this is not generally true. Even in one month we may have multiple spells, and we also have parallel spells (with different status).

But note that spells end at the end of the year. Splitting spells covering multiple years was done in a previous data preparation step.

Are you perhaps looking to create 12 variables, one for each month, each of which will contain how many days in that month are part of the spell?
Or do you want to create a variable for each month-year in the full dataset (so maybe 12 x 46 variables)? Or something else entirely?

No, I want to create variables that count whether somebody had a certain status in that month, eg. quelle==1 for employed and then count the number of days that this is true in the month. The same for quelle=3 etc.

The end result should be a year-month-id panel with indicators for labor market status in each month (e.g. any quelle==1) and continuous variables for days spent in each state in that month (e.g. days in month that quelle==1).

For the example, let's focus on quelle==1, I can expand to other states by myself later. The issue is more how to count the days, because even for quelle==1 there may be consecutive and/or parallel spells in a month.

Last edited by Henry Strawforrd; 28 Jul 2023, 05:26.
Comment

Hemanshu Kumar

Join Date: Mar 2015
Posts: 1357

#11

28 Jul 2023, 05:36

See if this looks a bit more like it:

Code:

sum begepi, meanonly
local min_month = mofd(`=r(min)')
sum endepi, meanonly
local max_month = mofd(`=r(max)')

forvalues mmyy = `min_month' / `max_month' {
    gen byte count`mmyy' = 0
    local mm = month(dofm(`mmyy'))
    local yy = year(dofm(`mmyy'))
    local mm_start = date("01-`mm'-`yy'", "DMY")
    local mm_end = date("`=daysinmonth(`mm_start')'-`mm'-`yy'", "DMY")
    replace count`mmyy' = min(`mm_end', endepi) - max(`mm_start', begepi) + 1 if begepi <= `mm_end' & endepi >= `mm_start'
}

reshape long count, i(persnr betnr spell quelle begepi endepi erwstat) j(month)
format month %tm
collapse (sum) count , by(persnr month quelle)
reshape wide count, i(persnr month) j(quelle)
foreach var of varlist count* {
    replace `var' = 0 if missing(`var')
}

where the count variables are the count of number of days for the respective quelle value.

The dataset now looks like this:

Code:

. list in 1/20, noobs sep(0)

  +------------------------------------------------------+
  | persnr     month   count1   count2   count4   count6 |
  |------------------------------------------------------|
  | 988050    1975m1       31        0        0        0 |
  | 988050    1975m2       28        0        0        0 |
  | 988050    1975m3       31        0        0        0 |
  | 988050    1975m4       30        0        0        0 |
  | 988050    1975m5       31        0        0        0 |
  | 988050    1975m6       30        0        0        0 |
  | 988050    1975m7       31        0        0        0 |
  | 988050    1975m8       31        0        0        0 |
  | 988050    1975m9       30        0        0        0 |
  | 988050   1975m10       31        0        0        0 |
  | 988050   1975m11       30        0        0        0 |
  | 988050   1975m12       31        0        0        0 |
  | 988050    1976m1       31        0        0        0 |
  | 988050    1976m2       29        0        0        0 |
  | 988050    1976m3       31        0        0        0 |
  | 988050    1976m4       30        0        0        0 |
  | 988050    1976m5       31        0        0        0 |
  | 988050    1976m6       30        0        0        0 |
  | 988050    1976m7       31        0        0        0 |
  | 988050    1976m8       31        0        0        0 |
  +------------------------------------------------------+

Last edited by Hemanshu Kumar; 28 Jul 2023, 05:52.

Comment

Henry Strawforrd

Join Date: Sep 2021

Posts: 228
#12

01 Aug 2023, 02:07

Thank you!
Comment

Announcement