Dear All,

I am working a study on sick leave using register data. From the register, I got only starting dates and end dates of sick leaves for each individual. But the dates are not broken down year by year. For instance , for person A, there are only data for start date (1-may-2016) and end date (14-feb-2018).
So, I would like to know how I can split out the dates year by year (ie. 1/5/16 to14/2/18 will be divided into 1/5/16-31/12/16, 1/1/2017-31/12/17, 1/1/18-14/2/18) in order to calculate total number of sick leaves for each year.

The example data created for the question is as follow;

Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id float(from to)
1 21185 22191
2 20454 22281
3 21186 21337
3 21367 21549
4 21459 21582
4 21914 22281
5 21094 21324
6 22341 22645
7 20454 20610
end
format %tdCCYY-NN-DD from
format %tdCCYY-NN-DD to
Desired data (created manually) is as follow;

Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id float(from to f_16 t_16 f_17 t_17 f_18 t_18 f_19 t_19 f_20 t_20 f_21 t_21)
1 21185 22191     .     .     .     . 21185 21550 21550 21915 21915 22191     .     .
2 20454 22281 20454 20820 20820 21185 21185 21550 21550 21915 21915 22281     .     .
3 21186 21337     .     .     .     . 21186 21337     .     .     .     .     .     .
3 21367 21549     .     .     .     . 21367 21549     .     .     .     .     .     .
4 21459 21582     .     .     .     . 21459 21550 21550 21582     .     .     .     .
4 21914 22281     .     .     .     .     .     . 21914 21915 21915 22281     .     .
5 21094 21324     .     . 21094 21185 21185 21324     .     .     .     .     .     .
6 22341 22645     .     .     .     .     .     .     .     .     .     . 22341 22645
7 20454 20610 20454 20610     .     .     .     .     .     .     .     .     .     .
end
format %tdCCYY-NN-DD from
format %tdCCYY-NN-DD to
format %tdCCYY-NN-DD f_16
format %tdCCYY-NN-DD t_16
format %tdCCYY-NN-DD f_17
format %tdCCYY-NN-DD t_17
format %tdCCYY-NN-DD f_18
format %tdCCYY-NN-DD t_18
format %tdCCYY-NN-DD f_19
format %tdCCYY-NN-DD t_19
format %tdCCYY-NN-DD f_20
format %tdCCYY-NN-DD t_20
format %tdCCYY-NN-DD f_21
format %tdCCYY-NN-DD t_21
Kind Regards,
Moon Lu