Weighted Average in Stata's collapse command

Question

I couldn't find a Stata command on the following issue, so I solved it manually:

According to the official manual, Stata doesn't do weights with averages in the collapse command (p. 6 of the Collapse chapter):

It means that I am not able to get weighted average prices paid in my sales data set at a week/product level where the weight is the units sold. The data set is a collection of single transactions with # of purchases and prices per unit paid at week/store/product level:

clear
set more off
week,store,product,price,units
1,1,cheer,1,10
1,1,tide,2,6
1,2,cheer,2,7
1,2,tide,3,5
2,1,cheer,1,15
2,1,tide,1,20
2,1,mrclean,2,8
2,2,cheer,2,8
2,2,tide,4,5
2,2,mrclean,3,4

Clearly, taking simple averages when collapsing ignores number of units purchased, resulting in a wrong average price estimate.

So what I did is I manually calculated a new variable price x units in the original data set. Then, I collapsed the dataset by summing up the price x units and units variables:

collapse (sum) pricexunits units, by(week product)

And finally, I created a new variable where I divided pricexunits by units. Do you think I could have done something differently?

Solution

You're misreading that documentation.

The point is whether normalization makes a difference to the results for different kinds of weights, not whether weights are allowed at all. In fact, weights are fairly generally allowed, as the help and manual entry explain.

Thus, the frequency-weighted sum or count differs from the analytically-weighted sum or count, and so forth.

Weights are allowed with collapse (mean). Try it, e.g.

collapse (mean) price [w=units] , by(week product)