Creating a rate grouped by another variable

[Malia Skjefte]

Hello,


I have a large dataset (27 vars, 1,048,575 obs) that contains data on malaria insecticide treated nets (ITNs) distributed at antenatal care (ANC) visits per facility over a 3 year period (2019-2021). I am looking to create a new variable of “ANC Issuing Rate” that includes the numerator [sum of ITNs distributed between 2019-2021] divided by the denominator [sum ANC registrants in 2019-2021] grouped per facility. Overall, I am looking to get one issuing rate per facility for the total time period.

I have tried to use this code below. It is creating a rate for each row (ie a rate for each month) but it is not grouping this rate by facility and by the 3 years. I have included a reduced dataset and how I wish the final data to appear. I very much appreciate any and all guidance on this topic!


bysort facility: gen anc_grouped= ITN_Distributed/ ANC_Registrants


(This is my first time here so I also appreciate feedback on posting etiquette for future posts)

[Andrew Musau]

Refer to FAQ Advice #12 on details of how to present data examples using the dataex command. You probably want to do the division after aggregating the data, something like:

bys facility: egen ITN_tot= total(ITN_Distributed)
bys facility: egen ANC_tot= total(ANC_Registrants)
gen wanted=  ITN_tot/ ANC_tot

or alternatively:

preserve
collapse (sum) ITN_Distributed ANC_Registrants, by(facility)
gen wanted= ITN_Distributed/ ANC_Registrants
list
restore

[Malia Skjefte]

Thank you, Andrew! This is extremely helpful and the code has worked perfectly. I appreciate your help.