panel sum by differnt columsn

[Cansu Oymak]

Dear statalist users,

I have a question about creating a variable that corresponds to the total of different columns.

The data spans from 1997 to 2000. It shows each country's total armed conflict events in a given year. My aim is to create a variable such that:

The variable battle is the sum of armed conflicts in a country in a given year. I want a new variable, say battle_total_neighbours" which corresponds to the sum of each country's border neighbour's battle value in a given year. To be more precise, in Angola, for 1997, I want my new variable "battle_total_neighbours" take the total of battle in "Namibia" "Zambia" "Democratic Republic of the Congo" "Republic of the Congo" in 1997.

I would be very happy if someone can help me.

Here is the example of my data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str28 ctr_n int yr double battle str7 border_angola1 str6 border_angola2    str32 border_angola3 str21 border_angola4 str7 border_benin1 str4    border_benin2    str7    border_botswana1    str12    border_botswana2    str8    border_botswana3
"Angola"       1997  126 "Namibia" "Zambia" "Democratic Republic of the Congo"    "Republic of the Congo" ""        ""     ""        ""    ""        
"Angola"       1998  299 "Namibia" "Zambia" "Democratic Republic of the Congo"    "Republic of the Congo" ""        ""     ""        ""    ""        
"Angola"       1999 1103 "Namibia" "Zambia" "Democratic Republic of the Congo"    "Republic of the Congo" ""        ""     ""        ""    ""        
"Angola"       2000  198 "Namibia" "Zambia" "Democratic Republic of the Congo"    "Republic of the Congo" ""        ""     ""        ""    ""        
"Angola"       2001  130 "Namibia" "Zambia" "Democratic Republic of the Congo"    "Republic of the Congo" ""        ""     ""        ""    ""        
"Angola"       2002   10 "Namibia" "Zambia" "Democratic Republic of the Congo"    "Republic of the Congo" ""        ""     ""        ""    ""        
"Angola"       2003    2 "Namibia" "Zambia" "Democratic Republic of the Congo"    "Republic of the Congo" ""        ""     ""        ""    ""        
"Angola"       2004    1 "Namibia" "Zambia" "Democratic Republic of the Congo"    "Republic of the Congo" ""        ""     ""        ""    ""        
"Angola"       2005    2 "Namibia" "Zambia" "Democratic Republic of the Congo"    "Republic of the Congo" ""        ""     ""        ""    ""        
"Angola"       2006    1 "Namibia" "Zambia" "Democratic Republic of the Congo"    "Republic of the Congo" ""        ""     ""        ""    ""        
"Angola"       2007    4 "Namibia" "Zambia" "Democratic Republic of the Congo"    "Republic of the Congo" ""        ""     ""        ""    ""        
"Angola"       2008    0 "Namibia" "Zambia" "Democratic Republic of the Congo"    "Republic of the Congo" ""        ""     ""        ""    ""        
"Angola"       2009    1 "Namibia" "Zambia" "Democratic Republic of the Congo"    "Republic of the Congo" ""        ""     ""        ""    ""        
"Angola"       2010    1 "Namibia" "Zambia" "Democratic Republic of the Congo"    "Republic of the Congo" ""        ""     ""        ""    ""        
"Angola"       2011    2 "Namibia" "Zambia" "Democratic Republic of the Congo"    "Republic of the Congo" ""        ""     ""        ""    ""        
"Angola"       2012    0 "Namibia" "Zambia" "Democratic Republic of the Congo"    "Republic of the Congo" ""        ""     ""        ""    ""        
"Angola"       2013    0 "Namibia" "Zambia" "Democratic Republic of the Congo"    "Republic of the Congo" ""        ""     ""        ""    ""        
"Angola"       2014    0 "Namibia" "Zambia" "Democratic Republic of the Congo"    "Republic of the Congo" ""        ""     ""        ""    ""        
"Angola"       2015    1 "Namibia" "Zambia" "Democratic Republic of the Congo"    "Republic of the Congo" ""        ""     ""        ""    ""        
"Angola"       2016   11 "Namibia" "Zambia" "Democratic Republic of the Congo"    "Republic of the Congo" ""        ""     ""        ""    ""        
"Benin"        1997    0 ""        ""       ""    ""                      "Nigeria" "Togo" ""        ""    ""        
"Benin"        1998    0 ""        ""       ""    ""                      "Nigeria" "Togo" ""        ""    ""        
"Benin"        1999    0 ""        ""       ""    ""                      "Nigeria" "Togo" ""        ""    ""        
"Benin"        2000    0 ""        ""       ""    ""                      "Nigeria" "Togo" ""        ""    ""        
"Benin"        2001    0 ""        ""       ""    ""                      "Nigeria" "Togo" ""        ""    ""        
"Benin"        2002    0 ""        ""       ""    ""                      "Nigeria" "Togo" ""        ""    ""        
"Benin"        2003    0 ""        ""       ""    ""                      "Nigeria" "Togo" ""        ""    ""        
"Benin"        2004    1 ""        ""       ""    ""                      "Nigeria" "Togo" ""        ""    ""        
"Benin"        2005    0 ""        ""       ""    ""                      "Nigeria" "Togo" ""        ""    ""        
"Benin"        2006    0 ""        ""       ""    ""                      "Nigeria" "Togo" ""        ""    ""        
"Benin"        2007    1 ""        ""       ""    ""                      "Nigeria" "Togo" ""        ""    ""        
"Benin"        2008    0 ""        ""       ""    ""                      "Nigeria" "Togo" ""        ""    ""        
"Benin"        2009    0 ""        ""       ""    ""                      "Nigeria" "Togo" ""        ""    ""        
"Benin"        2010    0 ""        ""       ""    ""                      "Nigeria" "Togo" ""        ""    ""        
"Benin"        2011    1 ""        ""       ""    ""                      "Nigeria" "Togo" ""        ""    ""        
"Benin"        2012    0 ""        ""       ""    ""                      "Nigeria" "Togo" ""        ""    ""        
"Benin"        2013    0 ""        ""       ""    ""                      "Nigeria" "Togo" ""        ""    ""        
"Benin"        2014    0 ""        ""       ""    ""                      "Nigeria" "Togo" ""        ""    ""        
"Benin"        2015    2 ""        ""       ""    ""                      "Nigeria" "Togo" ""        ""    ""        
"Benin"        2016    3 ""        ""       ""    ""                      "Nigeria" "Togo" ""        ""    ""        
"Botswana"     1997    0 ""        ""       ""    ""                      ""        ""     "Namibia" "South Africa"    "Zimbabwe"
"Botswana"     1998    0 ""        ""       ""    ""                      ""        ""     "Namibia" "South Africa"    "Zimbabwe"
"Botswana"     2000    0 ""        ""       ""    ""                      ""        ""     "Namibia" "South Africa"    "Zimbabwe"
"Botswana"     2003    0 ""        ""       ""    ""                      ""        ""     "Namibia" "South Africa"    "Zimbabwe"
"Botswana"     2004    0 ""        ""       ""    ""                      ""        ""     "Namibia" "South Africa"    "Zimbabwe"
"Botswana"     2005    0 ""        ""       ""    ""                      ""        ""     "Namibia" "South Africa"    "Zimbabwe"
"Botswana"     2006    0 ""        ""       ""    ""                      ""        ""     "Namibia" "South Africa"    "Zimbabwe"
"Botswana"     2007    0 ""        ""       ""    ""                      ""        ""     "Namibia" "South Africa"    "Zimbabwe"
"Botswana"     2008    0 ""        ""       ""    ""                      ""        ""     "Namibia" "South Africa"    "Zimbabwe"
"Botswana"     2009    1 ""        ""       ""    ""                      ""        ""     "Namibia" "South Africa"    "Zimbabwe"
"Botswana"     2010    0 ""        ""       ""    ""                      ""        ""     "Namibia" "South Africa"    "Zimbabwe"
"Botswana"     2011    0 ""        ""       ""    ""                      ""        ""     "Namibia" "South Africa"    "Zimbabwe"
"Botswana"     2013    0 ""        ""       ""    ""                      ""        ""     "Namibia" "South Africa"    "Zimbabwe"
"Botswana"     2014    0 ""        ""       ""    ""                      ""        ""     "Namibia" "South Africa"    "Zimbabwe"
"Botswana"     2015    0 ""        ""       ""    ""                      ""        ""     "Namibia" "South Africa"    "Zimbabwe"
"Botswana"     2016    0 ""        ""       ""    ""                      ""        ""     "Namibia" "South Africa"    "Zimbabwe"
"Burkina Faso" 1997    0 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Burkina Faso" 1998    0 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Burkina Faso" 1999    2 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Burkina Faso" 2000    1 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Burkina Faso" 2001    0 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Burkina Faso" 2002    0 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Burkina Faso" 2003    0 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Burkina Faso" 2004    2 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Burkina Faso" 2005    0 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Burkina Faso" 2006    4 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Burkina Faso" 2007    0 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Burkina Faso" 2008    5 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Burkina Faso" 2009    0 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Burkina Faso" 2010    0 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Burkina Faso" 2011    3 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Burkina Faso" 2012    3 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Burkina Faso" 2013    2 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Burkina Faso" 2014    5 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Burkina Faso" 2015    7 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Burkina Faso" 2016   14 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Cameroon"     1997   11 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Cameroon"     1998    2 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Cameroon"     1999    1 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Cameroon"     2000    0 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Cameroon"     2001    0 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Cameroon"     2002    0 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Cameroon"     2003    0 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Cameroon"     2004    0 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Cameroon"     2005   12 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Cameroon"     2006    1 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Cameroon"     2007   12 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Cameroon"     2008    9 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Cameroon"     2009    5 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Cameroon"     2010    2 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Cameroon"     2011    4 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Cameroon"     2012    1 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Cameroon"     2013   13 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Cameroon"     2014   66 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Cameroon"     2015   39 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Cameroon"     2016   88 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Chad"         1997    3 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Chad"         1998    3 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Chad"         1999    8 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
"Chad"         2000   14 ""        ""       ""    ""                      ""        ""     ""        ""    ""        
end

[Seungmin Lee]

Try the code below to see if this is what you want.
I tested with only 2 border countries, but it seems working from my perspective.
(I manually changed the first two border countries of Angola, to test if the code works properly)

replace    border_angola1="Cameroon" if ctr_n=="Angola"
replace    border_angola2="Burkina Faso" if ctr_n=="Angola"

tempfile original
save `original'

keep ctr_n yr battle

clonevar border_angola1=ctr_n
clonevar border_angola2=ctr_n

clonevar    battle_neighbor1    =    battle
clonevar    battle_neighbor2    =    battle
tempfile border
save    `border'

use    `original', clear


merge    m:1    border_angola1    yr    using `border', keepusing(battle_neighbor1) gen(merge_border1) keep(1 3)
merge    m:1    border_angola2    yr    using `border', keepusing(battle_neighbor2) gen(merge_border2) keep(1 3)

egen    battle_total_neighbours    =    rowtotal(battle_neighbor1    battle_neighbor2)

sort    ctr_n yr

I suggest having 4 variables - border_neighbor1, border_neighbor2, border_neighbor3, border_neight4 - that has neighbor country information for all countries, rather than having 4 columns for each country.