Creating variables for days lived per financial year

[Anh Trinh]

Hi Stata users,

I have a dataset as shown below, where each household (hh_id) provides the time (start_date and end_date) they lived in a specific location (location) for the period 2015-2019. I want to create a set of variables, named fy*, that represent the number of days each household lived in each financial year. I plan to use these variables to match with financial year income data at a later stage.

Thank you very much in advance.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte hh_id str1 location int(start_date end_date fy_1415 fy_1516 fy_1617 fy_1718 fy_1819 fy_1920)
1 "A" 20166 20454 104 182   0   0   0   0
1 "B" 20455 21914   0 181 366 366 366 185
2 "C" 20166 21486 104 367 366 366 122   0
2 "D" 21487 21914   0   0   0   0 244 185
3 "E" 20166 21914 104 367 366 366 366 185
end
format %td start_date
format %td end_date

[Clyde Schechter]

I cannot exactly reproduce your answers. In part that is because you have not explained what the start and end dates of the fiscal years should be. But the code below uses a standard approach for counting overlaps of intervals. In this code I have assumed that each fy begins on July 1 and ends on the following June 30. I also assume that for the period living in the location we include both the start_date and end_date. With these assumptions:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte hh_id str1 location int(start_date end_date)
1 "A" 20166 20454
1 "B" 20455 21914
2 "C" 20166 21486
2 "D" 21487 21914
3 "E" 20166 21914
end
format %td start_date
format %td end_date

gen `c(obs_t)' obs_no = _n
expand 20-15+1
by obs_no, sort: gen fy_start = mdy(7, 1, 2013+_n)
by obs_no: gen fy_end = mdy(6, 30, 2014+_n)
format fy* %td

gen days_in_fy = max(0, min(end_date, fy_end) - max(start_date, fy_start) + 1)
gen fy = string(mod(year(fy_start), 100)) + string(mod(year(fy_end), 100))
drop fy_start fy_end
reshape wide days_in_fy, i(obs_no) j(fy) string
order hh_id location start_date end_date, first

As you will see, the results this produces are close to the ones you show in #1, but not exactly in agreement. I challenge the accuracy of results. How can the number of days in the fiscal year by 366 if there is not a leap year involved? And I think some of your other numbers are also slightly off.