How to add values of a variable together

[Ned Malone]

I'm currently working with panel data on the UN member states covering the years 1995 to 2012. One of the variables is the number of nationals from each state who are working as UN staff members.

I want to calculate the total number of staff working at the UN across all countries for each year. In other words, total staff at the UN by year.

I've had a lot of trouble figuring out how to do this in Stata. Can someone please help?

[Nick Cox]

You don't give a data example (please do read FAQ Advice #12), but compare this silly example for technique.

. sysuse auto, clear
(1978 Automobile Data)

. tabstat weight, s(n sum) by(rep78)

Summary for variables: weight
     by categories of: rep78 (Repair Record 1978)

   rep78 |         N       sum
---------+--------------------
       1 |         2      6200
       2 |         8     26830
       3 |        30     98970
       4 |        18     51660
       5 |        11     25550
---------+--------------------
   Total |        69    209210
------------------------------

. egen total = total(weight), by(rep78)

. tabdisp rep78, c(total)

----------------------
Repair    |
Record    |
1978      |      total
----------+-----------
        1 |       6200
        2 |      26830
        3 |      98970
        4 |      51660
        5 |      25550
        . |      14230
----------------------

[Clyde Schechter]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str1 state int year float employees
"A" 1996 19
"A" 1997 12
"A" 1998 20
"A" 1999 19
"A" 2000 13
"B" 1996 19
"B" 1997  7
"B" 1998 19
"B" 1999 12
"B" 2000  8
"C" 1996 19
"C" 1997 12
"C" 1998 10
"C" 1999 20
"C" 2000 13
"D" 1996  8
"D" 1997 12
"D" 1998 12
"D" 1999 13
"D" 2000  9
end

by year, sort: egen total_employees = total(employees)

Do familiarize youself with the various -egen- functions. Start with -help egen- and then pursue the most relevant ones in the online user manual. They will come in very handy over and over again.

Crossed with Nick's response. Note that Nick's code will give you a table of years and the corresponding totals. My code will create a new variable in your data set with those totals in the corresponding observations. You didn't actually say in your post whether you wanted a tabulation or a new variable.

[Nick Cox]

My code does both: creates a variable and shows you a corresponding table.

[Ned Malone]

Thank you both. Sorry for not posting a data example. It's my first time posting on this forum. Definitely will read through the FAQ before next time posting.

I wanted a tabulation but it's also useful to have the new variable. Thanks again.

[Meesha Iqbal]

The discussion is very helpful. I have a followup question. We can sum up variable (e.g allocation_total), by another variable (e.g Year)

tabstat Allocation_Total, s (n sum) by ( Year2019)

If we want to sum up yet by another variable e.g tabstat Allocation_Total, s (n sum) by ( Year2019) by (Infrastructure).
This command doesn't work. Is there a way to solve this?

[Clyde Schechter]

You can't do it with -tabstat-. You can do it with the old version of the -table- command:

version 16: table Year2019 Infrastructure, c(N Allocation_Total sum Allocation_total)

Note: if you are using version 16 or older, omit the version 16: prefix from the command.

[Dr. Iqra Yaseen]

Clyde Schechter #3 helped this is what i got

* Example generated by -dataex-. To install: ssc install dataex
clear
input int year str33 iso3_o float total_ProductSector
2010 "AUS" 1258795648
2010 "AUS" 1258795648
2010 "AUS" 1258795648
2010 "AUS" 1258795648
2010 "AUS" 1258795648
2010 "AUS" 1258795648
2010 "AUS" 1258795648
2010 "AUS" 1258795648
2010 "AUS" 1258795648
2010 "AUS" 1258795648
2010 "AUS" 1258795648
2010 "AUS" 1258795648
2010 "AUS" 1258795648
2010 "AUS" 1258795648
2010 "AUS" 1258795648
2010 "AUS" 1258795648
2010 "AUS" 1258795648
2010 "AUS" 1258795648
2010 "AUS" 1258795648
2010 "AUS" 1258795648
2010 "AUS" 1258795648
2010 "AUS" 1258795648
2010 "CAN"   50814176
2010 "CAN"   50814176
2010 "CAN"   50814176
2010 "CAN"   50814176
2010 "CAN"   50814176
2010 "CAN"   50814176
2010 "CAN"   50814176
2010 "CAN"   50814176
2010 "CAN"   50814176
2010 "CAN"   50814176
2010 "CAN"   50814176
2010 "CAN"   50814176
2010 "CAN"   50814176
2010 "CAN"   50814176
2010 "CAN"   50814176
2010 "CAN"   50814176
2010 "CAN"   50814176
2010 "CAN"   50814176
2010 "CAN"   50814176
2010 "CAN"   50814176
2010 "CAN"   50814176
2010 "CAN"   50814176
2010 "DEU"   11147379
2010 "DEU"   11147379
2010 "DEU"   11147379
2010 "DEU"   11147379
2010 "DEU"   11147379
2010 "DEU"   11147379
2010 "DEU"   11147379
2010 "DEU"   11147379
2010 "DEU"   11147379
2010 "DEU"   11147379
2010 "DEU"   11147379
2010 "DEU"   11147379
2010 "DEU"   11147379
2010 "DEU"   11147379
2010 "DEU"   11147379
2010 "DEU"   11147379
2010 "DEU"   11147379
2010 "DEU"   11147379
2010 "DEU"   11147379
2010 "DEU"   11147379
2010 "DEU"   11147379
2010 "DEU"   11147379
2010 "DEU"   11147379
2010 "DEU"   11147379
2010 "DEU"   11147379
2010 "DEU"   11147379
2010 "FIN"     465706
2010 "FIN"     465706
2010 "FIN"     465706
2010 "FIN"     465706
2010 "FIN"     465706
2010 "FIN"     465706
2010 "FIN"     465706
2010 "FIN"     465706
2010 "FIN"     465706
2010 "FIN"     465706
2010 "FRA"    5142900
2010 "FRA"    5142900
2010 "FRA"    5142900
2010 "FRA"    5142900
2010 "FRA"    5142900
2010 "FRA"    5142900
2010 "FRA"    5142900
2010 "FRA"    5142900
2010 "FRA"    5142900
2010 "FRA"    5142900
2010 "FRA"    5142900
2010 "FRA"    5142900
2010 "FRA"    5142900
2010 "FRA"    5142900
2010 "FRA"    5142900
2010 "FRA"    5142900
2010 "FRA"    5142900
2010 "FRA"    5142900
2010 "FRA"    5142900
2010 "FRA"    5142900
end

now the problem is i want one single value for 2010 AUS , 2010 CAN and alike. but i get multiple observations containing same value for 2010 AUS , 2010 CAN. how can i delete the duplicate values

[Nick Cox]

#8

help duplicates

[Dr. Iqra Yaseen]

* Example generated by -dataex-. To install: ssc install dataex
clear
input int year str212 ProductSector str33 iso3_o
2010 "HS - 2602 - Manganese ores and concentrates, including ferruginous manganese ores and concentrates with a manganese content of 20 % or more, calculated on the dry weight."                                           "Albania"                        
2010 "HS - 2610 - Chromium ores and concentrates."                                                                                                                                                                          "Albania"                        
2010 "HS - 282560 - Germanium oxides and zirconium dioxide"                                                                                                                                                                 "Algeria"                        
2010 "HS - 2602 - Manganese ores and concentrates, including ferruginous manganese ores and concentrates with a manganese content of 20 % or more, calculated on the dry weight."                                           "Angola"                        
2010 "HS - 283691 - Lithium carbonates"                                                                                                                                                                                     "Argentina"                      
2010 "HS - 2602 - Manganese ores and concentrates, including ferruginous manganese ores and concentrates with a manganese content of 20 % or more, calculated on the dry weight."                                           "Argentina"                      
2010 "HS - 2603 - Copper ores and concentrates."                                                                                                                                                                            "Argentina"                      
2010 "HS - 282530 - Vanadium oxides and hydroxides"                                                                                                                                                                         "Argentina"                      
2010 "HS - 2835 - Phosphinates (hypophosphites), phosphonates (phosphites) and phosphates; polyphosphates, whether or not chemically defined."                                                                              "Argentina"                      
2010 "HS - 2810 - Oxides of boron; boric acids."                                                                                                                                                                            "Argentina"                      
2010 "HS - 2603 - Copper ores and concentrates."                                                                                                                                                                            "Armenia"                        
2010 "HS - 282540 - Nickel oxides and hydroxides"                                                                                                                                                                           "Australia"                      
2010 "HS - 2605 - Cobalt ores and concentrates."                                                                                                                                                                            "Australia"                      
2010 "HS - 2616 - Precious metal ores and concentrates."                                                                                                                                                                    "Australia"                      
2010 "HS - 2819 - Chromium oxides and hydroxides."                                                                                                                                                                          "Australia"                      
2010 "HS - 2820 - Manganese oxides."                                                                                                                                                                                        "Australia"                      
2010 "HS - 261710 - Antimony ores and concentrates"                                                                                                                                                                         "Australia"                      
2010 "HS - 8112 - Beryllium, chromium, germanium, vanadium, gallium, hafnium, indium, niobium (columbium), rhenium and thallium, and articles of these metals, including waste and scrap."                                  "Australia"                      
2010 "HS - 2615 - Niobium, tantalum, vanadium or zirconium ores and concentrates."                                                                                                                                          "Australia"                      
2010 "HS - 282520 - Lithium oxide and hydroxide"                                                                                                                                                                            "Australia"                      
2010 "HS - 2835 - Phosphinates (hypophosphites), phosphonates (phosphites) and phosphates; polyphosphates, whether or not chemically defined."                                                                              "Australia"                      
2010 "HS - 2848 - Phosphides, whether or not chemically defined, excluding ferrophosphorus."                                                                                                                                "Australia"                      
2010 "HS - 2602 - Manganese ores and concentrates, including ferruginous manganese ores and concentrates with a manganese content of 20 % or more, calculated on the dry weight."                                           "Australia"                      
2010 "HS - 2611 - Tungsten ores and concentrates."                                                                                                                                                                          "Australia"                      
2010 "HS - 282560 - Germanium oxides and zirconium dioxide"                                                                                                                                                                 "Australia"                      
2010 "HS - 2816 - Hydroxide and peroxide of magnesium; oxides, hydroxides and peroxides, of strontium or barium."                                                                                                           "Australia"                      
2010 "HS - 2504 - Natural graphite."                                                                                                                                                                                        "Australia"                      
2010 "HS - 2810 - Oxides of boron; boric acids."                                                                                                                                                                            "Australia"                      
2010 "HS - 2604 - Nickel ores and concentrates."                                                                                                                                                                            "Australia"                      
2010 "HS - 282580 - Antimony oxides"                                                                                                                                                                                        "Australia"                      
2010 "HS - 2603 - Copper ores and concentrates."                                                                                                                                                                            "Australia"                      
2010 "HS - 2610 - Chromium ores and concentrates."                                                                                                                                                                          "Australia"                      
2010 "HS - 2519 - Natural magnesium carbonate (magnesite); fused magnesia; deadburned (sintered) magnesia, whether or not containing small quantities of other oxides added before sintering; other magnesium oxide, wheth" "Australia"                      
2010 "HS - 282560 - Germanium oxides and zirconium dioxide"                                                                                                                                                                 "Austria"                        
2010 "HS - 283691 - Lithium carbonates"                                                                                                                                                                                     "Austria"                        
2010 "HS - 2610 - Chromium ores and concentrates."                                                                                                                                                                          "Austria"                        
2010 "HS - 2615 - Niobium, tantalum, vanadium or zirconium ores and concentrates."                                                                                                                                          "Austria"                        
2010 "HS - 2603 - Copper ores and concentrates."                                                                                                                                                                            "Austria"                        
2010 "HS - 2519 - Natural magnesium carbonate (magnesite); fused magnesia; deadburned (sintered) magnesia, whether or not containing small quantities of other oxides added before sintering; other magnesium oxide, wheth" "Austria"                        
2010 "HS - 2822 - Cobalt oxides and hydroxides; commercial cobalt oxides."                                                                                                                                                  "Austria"                        
2010 "HS - 2604 - Nickel ores and concentrates."                                                                                                                                                                            "Austria"                        
2010 "HS - 261710 - Antimony ores and concentrates"                                                                                                                                                                         "Austria"                        
2010 "HS - 2504 - Natural graphite."                                                                                                                                                                                        "Austria"                        
2010 "HS - 282530 - Vanadium oxides and hydroxides"                                                                                                                                                                         "Austria"                        
2010 "HS - 2816 - Hydroxide and peroxide of magnesium; oxides, hydroxides and peroxides, of strontium or barium."                                                                                                           "Austria"                        
2010 "HS - 8112 - Beryllium, chromium, germanium, vanadium, gallium, hafnium, indium, niobium (columbium), rhenium and thallium, and articles of these metals, including waste and scrap."                                  "Austria"                        
2010 "HS - 2835 - Phosphinates (hypophosphites), phosphonates (phosphites) and phosphates; polyphosphates, whether or not chemically defined."                                                                              "Austria"                        
2010 "HS - 2602 - Manganese ores and concentrates, including ferruginous manganese ores and concentrates with a manganese content of 20 % or more, calculated on the dry weight."                                           "Bahrain, Kingdom of"            
2010 "HS - 2504 - Natural graphite."                                                                                                                                                                                        "Bahrain, Kingdom of"            
2010 "HS - 2835 - Phosphinates (hypophosphites), phosphonates (phosphites) and phosphates; polyphosphates, whether or not chemically defined."                                                                              "Bahrain, Kingdom of"            
2010 "HS - 282560 - Germanium oxides and zirconium dioxide"                                                                                                                                                                 "Bahrain, Kingdom of"            
2010 "HS - 2615 - Niobium, tantalum, vanadium or zirconium ores and concentrates."                                                                                                                                          "Bahrain, Kingdom of"            
2010 "HS - 2816 - Hydroxide and peroxide of magnesium; oxides, hydroxides and peroxides, of strontium or barium."                                                                                                           "Bangladesh"                    
2010 "HS - 2848 - Phosphides, whether or not chemically defined, excluding ferrophosphorus."                                                                                                                                "Bangladesh"                    
2010 "HS - 282530 - Vanadium oxides and hydroxides"                                                                                                                                                                         "Barbados"                      
2010 "HS - 261710 - Antimony ores and concentrates"                                                                                                                                                                         "Belgium"                        
2010 "HS - 2816 - Hydroxide and peroxide of magnesium; oxides, hydroxides and peroxides, of strontium or barium."                                                                                                           "Belgium"                        
2010 "HS - 2835 - Phosphinates (hypophosphites), phosphonates (phosphites) and phosphates; polyphosphates, whether or not chemically defined."                                                                              "Belgium"                        
2010 "HS - 2819 - Chromium oxides and hydroxides."                                                                                                                                                                          "Belgium"                        
2010 "HS - 2610 - Chromium ores and concentrates."                                                                                                                                                                          "Belgium"                        
2010 "HS - 2602 - Manganese ores and concentrates, including ferruginous manganese ores and concentrates with a manganese content of 20 % or more, calculated on the dry weight."                                           "Belgium"                        
2010 "HS - 2504 - Natural graphite."                                                                                                                                                                                        "Belgium"                        
2010 "HS - 282530 - Vanadium oxides and hydroxides"                                                                                                                                                                         "Belgium"                        
2010 "HS - 283691 - Lithium carbonates"                                                                                                                                                                                     "Belgium"                        
2010 "HS - 282520 - Lithium oxide and hydroxide"                                                                                                                                                                            "Belgium"                        
2010 "HS - 282580 - Antimony oxides"                                                                                                                                                                                        "Belgium"                        
2010 "HS - 8112 - Beryllium, chromium, germanium, vanadium, gallium, hafnium, indium, niobium (columbium), rhenium and thallium, and articles of these metals, including waste and scrap."                                  "Belgium"                        
2010 "HS - 2604 - Nickel ores and concentrates."                                                                                                                                                                            "Belgium"                        
2010 "HS - 2615 - Niobium, tantalum, vanadium or zirconium ores and concentrates."                                                                                                                                          "Belgium"                        
2010 "HS - 2611 - Tungsten ores and concentrates."                                                                                                                                                                          "Belgium"                        
2010 "HS - 2519 - Natural magnesium carbonate (magnesite); fused magnesia; deadburned (sintered) magnesia, whether or not containing small quantities of other oxides added before sintering; other magnesium oxide, wheth" "Belgium"                        
2010 "HS - 282560 - Germanium oxides and zirconium dioxide"                                                                                                                                                                 "Belgium"                        
2010 "HS - 2810 - Oxides of boron; boric acids."                                                                                                                                                                            "Belgium"                        
2010 "HS - 2820 - Manganese oxides."                                                                                                                                                                                        "Belgium"                        
2010 "HS - 283692 - Strontium carbonate"                                                                                                                                                                                    "Belgium"                        
2010 "HS - 282540 - Nickel oxides and hydroxides"                                                                                                                                                                           "Belgium"                        
2010 "HS - 2822 - Cobalt oxides and hydroxides; commercial cobalt oxides."                                                                                                                                                  "Belgium"                        
2010 "HS - 2819 - Chromium oxides and hydroxides."                                                                                                                                                                          "Bhutan"                        
2010 "HS - 2504 - Natural graphite."                                                                                                                                                                                        "Bhutan"                        
2010 "HS - 261710 - Antimony ores and concentrates"                                                                                                                                                                         "Bolivia, Plurinational State of"
2010 "HS - 282580 - Antimony oxides"                                                                                                                                                                                        "Bolivia, Plurinational State of"
2010 "HS - 2615 - Niobium, tantalum, vanadium or zirconium ores and concentrates."                                                                                                                                          "Bolivia, Plurinational State of"
2010 "HS - 2603 - Copper ores and concentrates."                                                                                                                                                                            "Bosnia and Herzegovina"        
2010 "HS - 2616 - Precious metal ores and concentrates."                                                                                                                                                                    "Botswana"                      
2010 "HS - 2603 - Copper ores and concentrates."                                                                                                                                                                            "Botswana"                      
2010 "HS - 8112 - Beryllium, chromium, germanium, vanadium, gallium, hafnium, indium, niobium (columbium), rhenium and thallium, and articles of these metals, including waste and scrap."                                  "Brazil"                        
2010 "HS - 2610 - Chromium ores and concentrates."                                                                                                                                                                          "Brazil"                        
2010 "HS - 2519 - Natural magnesium carbonate (magnesite); fused magnesia; deadburned (sintered) magnesia, whether or not containing small quantities of other oxides added before sintering; other magnesium oxide, wheth" "Brazil"                        
2010 "HS - 2835 - Phosphinates (hypophosphites), phosphonates (phosphites) and phosphates; polyphosphates, whether or not chemically defined."                                                                              "Brazil"                        
2010 "HS - 2616 - Precious metal ores and concentrates."                                                                                                                                                                    "Brazil"                        
2010 "HS - 2602 - Manganese ores and concentrates, including ferruginous manganese ores and concentrates with a manganese content of 20 % or more, calculated on the dry weight."                                           "Brazil"                        
2010 "HS - 2603 - Copper ores and concentrates."                                                                                                                                                                            "Brazil"                        
2010 "HS - 2504 - Natural graphite."                                                                                                                                                                                        "Brazil"                        
2010 "HS - 2820 - Manganese oxides."                                                                                                                                                                                        "Brazil"                        
2010 "HS - 2611 - Tungsten ores and concentrates."                                                                                                                                                                          "Brazil"                        
2010 "HS - 282530 - Vanadium oxides and hydroxides"                                                                                                                                                                         "Brazil"                        
2010 "HS - 2519 - Natural magnesium carbonate (magnesite); fused magnesia; deadburned (sintered) magnesia, whether or not containing small quantities of other oxides added before sintering; other magnesium oxide, wheth" "Brunei Darussalam"              
2010 "HS - 8112 - Beryllium, chromium, germanium, vanadium, gallium, hafnium, indium, niobium (columbium), rhenium and thallium, and articles of these metals, including waste and scrap."                                  "Bulgaria"                      
2010 "HS - 2835 - Phosphinates (hypophosphites), phosphonates (phosphites) and phosphates; polyphosphates, whether or not chemically defined."                                                                              "Bulgaria"                      
2010 "HS - 283692 - Strontium carbonate"                                                                                                                                                                                    "Bulgaria"                      
end

i want to create a new variable naming it as "minerals". then i want to add values of all observations that include (e.g) cobalt for years 2010, 2011,..........2021 seperately for different countries with cobalt as value under variable "minerals". i want to do the same for other minerals like copper, magnesium etc. like this

year minerals iso3_o tradeflow_wto_d
2010 cobalt    AUS      558587878
2010 cobalt    CAN       88758875
2010 cobalt     FRA       774757747
2010 boron    AUS      558587878
2010 boron   CAN       88758875
2010 boron     FRA       774757747

please help with the code

[Dr. Iqra Yaseen]

Nick Cox please help with #10

[Nick Cox]

I did not understand #10 when I first read it. Still true. Sorry to disappoint.

[Dr. Iqra Yaseen]

Nick Cox thanks for your response. somehow, I managed to add trade data according to the minerals given in data example #10, but in the form of variables. now I have variable₁ as graphite, var₂ as magnesium, var₃ as copper...........v_n as gallium. now i want to create a new variable "minerals" with the data of all these variables under the single "minerals" variable. i tried reshaping the data from wide to long but i receive "variable minerals contains all missing values r(498) error". please help and if i am still not understandable to you, notify the same. i will try to rephrase my query.

[Nick Cox]

Sorry, but answering you once doesn't mean that I committed myself to giving support until you're done. More importantly, why did no-one else answer #10? It just wasn't very clear, I guess.

Statalist can be helpful when you ask a question that is clear and simple enough for someone to understand. If your question isn't clear to anyone, no one is obliged even to say so. That may seem brutal, but it's actually everyone's rules. People who ask questions often never answer any themselves and that's allowed too.

[Dr. Iqra Yaseen]

Nick Cox thank you so much for your time and effort