Total Amount - But Exclude Group

[Hans Schubert]

Hi there,

Looking for a solution for column 4 please

Col 1 - Animal Group
Col 2 - Amount of animals
Col 3 - Total Amount of all animals
Col 4 - Total Amount of all animals excluding the current group

So for Cat, total for Dog and Lion is wanted. For Dog, total for Cat and Lion is wanted. For Lion, total for Dog and Cat is wanted. Basically want to exclude the current group from the calculation.
Thanks

* Example generated by -dataex-. For more info, type help dataex
clear
input str4 animal byte amount int(totalamount excludedamount_wanted)
"Cat"  10 406 336
"Cat"   8 406 336
"Cat"   7 406 336
"Cat"   3 406 336
"Cat"   6 406 336
"Cat"   9 406 336
"Cat"  12 406 336
"Cat"  15 406 336
"Dog"   4 406 317
"Dog"   6 406 317
"Dog"  32 406 317
"Dog"  12 406 317
"Dog"  21 406 317
"Dog"   4 406 317
"Dog"   2 406 317
"Dog"   8 406 317
"Lion"  7 406 159
"Lion" 76 406 159
"Lion" 45 406 159
"Lion" 32 406 159
"Lion" 12 406 159
"Lion" 13 406 159
"Lion"  2 406 159
"Lion"  6 406 159
"Lion"  4 406 159
"Lion" 28 406 159
"Lion" 22 406 159
end

[Nick Cox]

Thanks for very clear question and example!

* Example generated by -dataex-. For more info, type help dataex
clear
input str4 animal byte amount int(totalamount excludedamount_wanted)
"Cat"  10 406 336
"Cat"   8 406 336
"Cat"   7 406 336
"Cat"   3 406 336
"Cat"   6 406 336
"Cat"   9 406 336
"Cat"  12 406 336
"Cat"  15 406 336
"Dog"   4 406 317
"Dog"   6 406 317
"Dog"  32 406 317
"Dog"  12 406 317
"Dog"  21 406 317
"Dog"   4 406 317
"Dog"   2 406 317
"Dog"   8 406 317
"Lion"  7 406 159
"Lion" 76 406 159
"Lion" 45 406 159
"Lion" 32 406 159
"Lion" 12 406 159
"Lion" 13 406 159
"Lion"  2 406 159
"Lion"  6 406 159
"Lion"  4 406 159
"Lion" 28 406 159
"Lion" 22 406 159
end

egen wanted1 = total(amount), by(animal)
egen wanted0 = total(amount)
gen wanted2 = wanted0 - wanted1 

tabdisp animal, c(total excluded wanted?)

----------------------------------------------------------------------------------------------------------
   animal |        totalamount excludedamount_w~d            wanted1            wanted0            wanted2
----------+-----------------------------------------------------------------------------------------------
      Cat |                406                336                 70                406                336
      Dog |                406                317                 89                406                317
     Lion |                406                159                247                406                159
----------------------------------------------------------------------------------------------------------

[Hans Schubert]

Thanks, Nick. Much appreciated.