Sum of a variable over all observations except one (loop)

[Jean Moulin]

Dear all,

Let's suppose I have a variable displaying the amount of input in a given industry for each country: X_AUSTRALIA, ..., X_JAPAN, ..., X_USA, etc.

My goal is to create a new variable for each country which would be the sum of X excluding the country I am interested in. For instance, for Australia, I want the sum of X over all countries except Australia, and so on for each country.

Ideally, I would like to do that in a loop since I have a lot of countries. I have already created a global containing all the countries.

Anyone has an idea about how to produce theses variables within a loop ? That would be very appreciated. Thank you.

[Carlo Lazzaro]

Jean:
do you mean something along the following lines?

use "C:\Program Files\Stata16\ado\base\a\auto.dta"
g id=_n
. forval id = 1/74 {
2. sum price if id!=`id'
3. }

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6193.562 2959.771 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6184.658 2965.15 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6197.671 2956.606 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6183.74 2965.59 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6142.493 2963.355 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6170.425 2969.57 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6188.712 2962.95 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6178.63 2967.648 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6107.63 2927.661 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6193.795 2959.603 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6093.753 2904.609 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6051.082 2800.411 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6031.822 2735.78 3291 14500

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6204.521 2950.371 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6171.562 2969.406 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6188.014 2963.359 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6179.795 2967.237 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6199.479 2955.077 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6195.534 2958.306 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6195.137 2958.609 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6194.781 2958.877 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6169.082 2969.723 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6162.836 2969.834 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6189.589 2962.42 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6192.356 2960.617 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6092.219 2901.744 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6063.493 2836.085 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6065.247 2840.761 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6197.26 2956.942 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6176.027 2968.442 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6165.26 2969.908 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6187.849 2963.454 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6163.37 2969.863 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6204.63 2950.261 3299 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6128.973 2953.231 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6178.863 2967.569 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6184.877 2965.042 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6182.726 2966.051 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6192.438 2960.561 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6192.247 2960.692 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6107.644 2927.681 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6186.055 2964.439 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6189.096 2962.721 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6188.315 2963.184 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6160.863 2969.664 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6194.096 2959.384 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6170.288 2969.588 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6182.123 2966.312 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6178.178 2967.798 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6185.014 2964.973 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6189.11 2962.712 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6192.562 2960.476 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6116.973 2940.312 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6163.479 2969.868 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6116.356 2939.548 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6164.384 2969.898 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6186.849 2964.013 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6180.137 2967.11 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6138.356 2960.753 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6190.863 2961.614 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6170.274 2969.59 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6188.082 2963.32 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6194.986 2958.723 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6071.767 2857.374 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6196.356 2957.666 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6197.685 2956.595 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6168.904 2969.74 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6198.37 2956.025 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6171.37 2969.436 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6151.904 2967.655 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6175.781 2968.509 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6185.37 2964.794 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6155.877 2968.796 3291 15906

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
price | 73 6085.397 2888.228 3291 15906

[Sami Amaddah]

How would you then create a new variable ? meanonly then gen ?

[Nick Cox]

Ideally, I would like to do that in a loop

That is not ideal at all!

You have a wide layout (some say: format or structure) for panel data. It's best to reshape long as otherwise virtually every calculation from now on will be awkward in Stata.

Interesting name: https://en.wikipedia.org/wiki/Jean_Moulin

[Carlo Lazzaro]

Jean, Sami:

sysuse auto.dta
g id=_n
g double new_var=.
forval id = 1/74  {
quietly sum price if id!=`id'
replace new_var=r(mean) in `id'
}
list new_var in 1/10


     +-----------+
     |   new_var |
     |-----------|
  1. | 6193.5616 |
  2. | 6184.6575 |
  3. | 6197.6712 |
  4. | 6183.7397 |
  5. | 6142.4932 |
     |-----------|
  6. | 6170.4247 |
  7. | 6188.7123 |
  8. | 6178.6301 |
  9. | 6107.6301 |
 10. | 6193.7945 |
     +-----------+

.

As per Nick's wise reminder, this code implies -reshape long- first, ,being the -long- format the best one for most (if not all) Stata calculations.

[Jean Moulin]

Carlo Lazzaro I did not express myself well, sorry. I don't want to sum over all observations but all over all countries (there is one variable per country), except one, at each time. What makes the thing tricky to me is that the variable that has to be dropped changes every time e.g when I want to sum for Australia, I must drop X_Australia; when I want to sum for Italy, I have to drop X_Italy, and so on.

Nick Cox The problem is that I am working on an Input-Output table (Leontief matrix is another way to call it) and, unfortunately, reshaping it does not makes any sense for that particular database.
Yes, easy name to remember for lovers of history.

The main idea of what I am doing is to compute to which extent foreign countries contribute to the national automotive industry of the domestic country. That's why I want to sum the inputs of every country, except the domestic one.

I hope it is a bit clearer now, and thank you all for your answers.

[Nick Cox]

What you want looks like the rowwise sum, MINUS each value. This may help:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str1 id float(X1 X2 X3)
"A" 1 2 3
"B" 4 5 6
"C" 7 8 9
end

egen total = rowtotal(X*)

foreach v of var X* {
     gen `v'2 = total - `v'
 }

list

     +---------------------------------------------+
     | id   X1   X2   X3   total   X12   X22   X32 |
     |---------------------------------------------|
  1. |  A    1    2    3       6     5     4     3 |
  2. |  B    4    5    6      15    11    10     9 |
  3. |  C    7    8    9      24    17    16    15 |
     +---------------------------------------------+

.

[Jean Moulin]

Thanks a lot for your help!

I think Nick's suggestion answers to my question.

Have a nice day.

[Mike Lacy]

I vaguely recalled seeing that there were some user-written modules to facilitate input-output analysis, and a search revealed the following:

ssc describe iot
ssc describe icio

I haven't used either of these and I don't know whether they are relevant to the immediate problem, but I thought I'd note them for whatever use they might be to Jean or others.