Collapse by groups : add a total row

[Baptiste Roux]

Hi!

I'm trying to add a "total" row to a data frame such that it corresponds to the sum of all my groups' observations (similar issue to https://www.statalist.org/forums/for...sting-variable).
I would like to use the method that is shown in this already solved topic, but for now I don't see how using either tabdisp or tag will help me to generate this new total row.

In short, considering this code :

sysuse auto, clear
collapse(sum) price, by(foreign)

How do I add a third row that shows "total" for the foreign variable and the sum of the two figures above for the price variable ?

Many thanks !
Baptiste

[Karl Tjensvoll]

First of all, I would not recommend doing this, as it makes little sense outside of Excel, and I would say is bad practice. A better way is to make a new column, and dont worry about duplicate values.
If you still want to do it, this is a way, but not very elegant.

sysuse auto, clear
collapse(sum) price, by(foreign)
expand 2 if _n==_N
replace foreign = 9 if _n==_N
label define total 0 "Domestic" 1 "Foreign" 9 "Total"
label values foreign total
replace price = price[1] + price[2] if _n==_N
br

[Baptiste Roux]

Thanks a lot !

[Flavia Hug]

I would appraoch this differently. Expand the data set before collapsing, by duplicating every row and call it foreign=Total.

This way you can scale it easily with more categories (e.g., North American, South American, EU, Asian) and also create other summary statistics (e.g., means, sd)

sysuse auto, clear
expand 2
replace foreign = 9 if _n>_N/2
label define total 0 "Domestic" 1 "Foreign" 9 "Total"
label values foreign total
collapse(sum) price, by(foreign)
br