I have weekly deaths data by 10 regions and monthly deaths data by 340 local authorities (LA) in each of those regions. I would like to estimate the number of weekly deaths in each local authority by apportioning the monthly death totals over each week. For example, if we know that 10% of all January’s deaths in the region of West Midlands happened in week 1, then we can assume that 10% of January’s deaths occurring in Birmingham (a local authority in WM) would happen in week 1. The calculation looks like this:

Estimated Birmingham deaths in week 1 = Birmingham deaths in January * (West Midlands deaths in week 1 / West Midlands deaths in January)

*Note that some of the weeks start on the 3rd, so I’ll need to apportion 4/7 to month 1 and 3/7 to month 2. I have data for the past 5 years. It comes from two different spreadsheets, with weeks/months as columns and regions/local authorities as rows.

Could you please let me know what is the best way to structure my dataset in Stata and how I can perform the above calculation for each LA? Should I have weekly and monthly deaths as separate variables or is there a smarter way of combining weekly and monthly data?