How to use Collapse of Panel data for weighted averages

[Ines Valenzuela]

Hi,
I'm pretty new at stata programming and I am trying to figure something out. I have panel data with groups (US regions) across time (twenty years with monthly data) and a variable HDD, which is for every month for every region. I need a variable Wi for region i where its value is the weighted average (by region population, yearly) of HDD for all other regions (it does not include region i in weighted average). I was thinking of using collapse as the command, but I do not know the particular elements I have to compute (what variables, functions, etc.).
Thank you so much for any help, I am sure you know more than I do anyway.
Best wishes.

[Clyde Schechter]

There may be a way to do this with collapse, but it isn't obvious to me. It's also not clear to me if you want a single population-weighted average for each region (same value for all years) or whether you want a value for each region-year combination. I'll assume the latter. I would do this:

by year, sort: egen grand_weighted_sum_HDD = total(population*HDD)
by year: egen grand_total_population = total(population)
by region year, sort: egen region_weighted_sum_HDD = total(population*HDD)
by region year: egen region_total_population = total(population)
gen weighted_mean_other_regions_HDD = ///
    (grand_weighted_sum_HDD - region_weighted_sum_hdd) /(grand_total_population - region_total_population)

Note: As you provided no sample data, this code is untested. It may have typos or other errors, but it shows the general logic.