Weighted Sum in Collapse

[Dhruv Jain DSE]

Hi,

I am working with firm-level data (cross-section data). I am trying to calculate the weighted sum of product Gross Value by (i) Product Code (given by productASICC) and (ii) State

I am using collapse command as follows:

(i) For Weighted Sum by Product Code
collapse (sum) sum_output=productGrossValue [aw=emult2] , by(productASICC)

(ii) For Weighted Sum by State

collapse (sum) sum_output=productGrossValue [aw=emult2], by(state)


However, when I collapse the two (separately) and then calculate the total of sum_output, I get a different answer in each case!

Total Sum (when doing (i))= 7956141480645.23

Total Sum (when doing(ii))=5334065631590.61


Please let me know if I am doing anything wrong! My gut is that, the two sums should be equal. But I dont know if my code is incorrect or not.

Thanks
Dhruv

[William Lisowski]

You do not describe to us what your weights represent. You will see from the output of help collapse that there are four types of weights, and that when collapse is used with the by() option, the analytic weights that you are using are rescaled separately for each by-group, That is the cause of your inconsistent results. You should review the discussion in the output of help weights to better understand which type of weights your emult2 values represent, and change your collapse commands to reflect that.

[Joro Kolev]

Here is another concise source on weights:

https://www.stata.com/support/faqs/s...ar-regression/

[Dhruv Jain DSE]

Thank You both! The weight in intend to use was probability weights. The answers in both cases (by state and by ASICC) are now consistent.