Stata sum by group

I got data like this:

tinh huyen xa diaban hoso fee1 fee2 income 1 1 4 12 13 100 200 1 1 4 12 13 120 150 1 1 5 11 14 130 140 1 1 5 11 14 100 100 1 1 6 14 20 130 140 

So I'd like to sum fee1 and fee2 by (tinh, huyen, xa, diaban, hoso) to income column.

Then result supposes like:

tinh huyen xa diaban hoso fee1 fee2 income 1 1 4 12 13 100 200 570(100 + 200 + 120 + 150) 1 1 4 12 13 120 150 570(100 + 200 + 120 + 150) 1 1 5 11 14 130 140 470(130 + 140 + 100 + 100) 1 1 5 11 14 100 100 470(130 + 140 + 100 + 100) 1 1 6 14 20 130 140 270(130 + 140) 

Please help me for this expression. Any solution will be appreciated. Thanks.

1 Answer

I'm sure someone else will have a cleverer solution, but you can combine egen's rowtotal and total functions to achieve this.

clear input tinh huyen xa diaban hoso fee1 fee2 1 1 4 12 13 100 200 1 1 4 12 13 120 150 1 1 5 11 14 130 140 1 1 5 11 14 100 100 1 1 6 14 20 130 140 end egen sum1 = rowtotal(fee1 fee2) egen sum2 = total(sum1), by(tinh huyen xa diaban) li 

yielding

 +---------------------------------------------------------------+ | tinh huyen xa diaban hoso fee1 fee2 sum1 sum2 | |---------------------------------------------------------------| 1. | 1 1 4 12 13 100 200 300 570 | 2. | 1 1 4 12 13 120 150 270 570 | 3. | 1 1 5 11 14 130 140 270 470 | 4. | 1 1 5 11 14 100 100 200 470 | 5. | 1 1 6 14 20 130 140 270 270 | +---------------------------------------------------------------+ 

Note that (per the comments) you can use egen income = total(fee1 + fee2), by(tinh huyen xa diaban). If you have missing values for fee1 or fee2 you'll have to account for those in the fee1 + fee2 expression if you use this syntax.