Table by country: Percentages of the total sum

[Budu Gulo]

My data set has four variables: year, company_name, country, and net_income. I want to generate a table like the following--not necessarily with tabstat-- with an additional column: percent of the total sum (net_income). For example, how much Canada represents of the total net_income.

In addition, given the display format, can I improve the aesthetics of the following table? For instance, stats (N sum) for United States are very closely located

72,513,077,424 

, and 72(N) is separated by a comma.

. tabstat net_income if year==2011 , by(country) stat(mean, n,  sum) format(%13.0fc)

Summary for variables: net_income
     by categories of: country (NATION)

         country |      mean         N       sum
-----------------+------------------------------
          CANADA |   -1,166,507            3   -3,499,520
           CHILE |  -21,313,845            1  -21,313,845
           CHINA |   26,537,806            2   53,075,612
          FRANCE |   26,652,916            1   26,652,916
          GREECE | -108,836,563            1 -108,836,563
        HONGKONG |   14,753,076            1   14,753,076
           INDIA |    2,279,532            3    6,838,596
       INDONESIA |    3,665,358            1    3,665,358
           JAPAN |    7,467,490            1    7,467,490
     SWITZERLAND |    4,345,756            1    4,345,756
          TAIWAN |    1,775,617            1    1,775,617
          TURKEY |    1,567,966            1    1,567,966
  UNITED KINGDOM |    1,310,858            1    1,310,858
   UNITED STATES |  359,011,061            72,513,077,424
-----------------+------------------------------
           Total |  100,035,230           252,500,880,743
------------------------------------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input double year str244(company_name country) double net_income
2010 "BEST FRIEND TECHNOLOGY COMPANY LIMITED"               "TAIWAN"              -8483785.8
2010 "TECHPRECISION CORPORATION"                            "UNITED STATES"          2044735
2010 "TONNA ELECTRONIQUE"                                   "FRANCE"               780456.27
2010 "CMI FPE LTD"                                          "INDIA"               6087595.84
2010 "HERO MOTOCORP LIMITED"                                "INDIA"                497921273
2010 "CHINNEY INVESTMENTS, LTD."                            "HONG KONG"          35580754.98
2010 "TATA SPONGE IRON LIMITED"                             "INDIA"                 18856412
2010 "MARATHON RESOURCES LTD"                               "AUSTRALIA"       -3433256.96938
2010 "PANGOLIN DIAMONDS CORP"                               "CANADA"           -881562.54017
2010 "SOLAR ENERGY LIMITED"                                 "UNITED STATES"            -7761
2010 "KARULA COMPANY LIMITED"                               "JAPAN"                   798750
2010 "GRAND KOREA LEISURE CO LIMITED"                       "KOREA (SOUTH)"     63797673.952
2010 "MCGRATH RENTCORP"                                     "UNITED STATES"         36479000
2010 "NORTH CAIRO FLOUR MILLS"                              "EGYPT"            3191310.71952
2010 "IRC LIMITED"                                          "HONG KONG"      -82313207.13096
2010 "HUNNU COAL PTY LTD"                                   "AUSTRALIA"       -7373466.54915
2010 "PROSPECT REIT INVESTMENT CORPORATION"                 "JAPAN"                  4007340
2010 "ORIENT PRESS LIMITED"                                 "INDIA"            2345739.19219
2010 "CHINA SINGYES SOLAR TECHNOLOGIES HOLDINGS LIMITED"    "HONG KONG"          32835090.68
2010 "STONESOFT OYJ"                                        "FINLAND"            -3599737.41
2010 "HUNAS FALLS HOTELS PLC"                               "SRI LANKA"             50348.57
2010 "COMBINED GROUP CONTRACTING COMPANY KSCP"              "KUWAIT"           31651131.1248
2010 "SINTESI SOCIETA DI INVESTIMENTI E PARTECIPAZIONI SPA" "ITALY"             -12657313.95
2010 "INFORTREND TECHNOLOGY INCORPORATION"                  "TAIWAN"             18858319.28
2010 "ORION EQUITIES LIMITED"                               "AUSTRALIA"         935639.59804
2011 "CONTINENTAL PRECIOUS MINERALS INC"                    "CANADA"           -1403568.3194
2011 "HEBEI CHENGDE LOLO COMPANY LIMITED"                   "CHINA"              30729457.44
2011 "VIVUS, INC."                                          "UNITED STATES"        -46140000
2011 "TELEVERBIER"                                          "SWITZERLAND"      4345756.35158
2011 "RARE ELEMENT RESOURCES LTD"                           "UNITED STATES"  -17257829.63075
2011 "TEK ART INSAAT TICARET TUR SAN VE YAT AS"             "TURKEY"            1567966.4895
2011 "GRUPO EMPRESAS NAVIERAS SA"                           "CHILE"          -21313844.78976
2011 "THERMADOR GROUPE"                                     "FRANCE"             26652915.84
2011 "SPYR INC"                                             "UNITED STATES"          -152909
2011 "ANTHEM INC"                                           "UNITED STATES"       2.6467e+09
2011 "DAGGER FORST TOOLS LIMITED"                           "INDIA"               -81660.977
2011 "FORELAND FABRICTECH HOLDINGS LIMITED"                 "CHINA"              22346154.24
2011 "COWELL DEVELOPMENT TBK PT"                            "INDONESIA"        3665357.62934
2011 "UNITED FIBER OPTIC COMMUNICATION INC."                "TAIWAN"              1775617.48
2011 "ALLOCATE SOFTWARE PLC"                                "UNITED KINGDOM"      1310857.78
2011 "IRF EUROPEAN FINANCE INVESTMENTS LTD."                "GREECE"            -108836562.9
2011 "OIL COUNTRY TUBULAR LIMITED"                          "INDIA"               6841390.55
2011 "GOLDTRAIN RESOURCES INC"                              "CANADA"           -670489.70139
2011 "THERALASE TECHNOLOGIES INC."                          "CANADA"          -1425461.56986
2011 "GEPCO LTD"                                            "UNITED STATES"          -480837
2011 "PHARMASSET INC"                                       "UNITED STATES"        -91185000
2011 "BLUCORA INCORPORATION"                                "UNITED STATES"         21594000
2011 "COMPUTER POINT LIMITED"                               "INDIA"                 78866.85
2011 "KAM HING INTERNATIONAL HOLDINGS LTD"                  "HONGKONG"           14753076.25
2011 "NOMURA MICRO SCIENCE CO., LTD."                       "JAPAN"               7467490.17
end
format %ty year

[Marcos Almeida]

You may wish to left-align, this way:

tabstat net_income if year==2011 , by(country) stat(mean, n,  sum) format(%-13.0fc)

Hopefully that helps.

[Budu Gulo]

Marcos Almeida thank you. Now how do I find the percentages?

I want to generate a table like the following--not necessarily with tabstat-- with an additional column: percent of the total sum (net_income). For example, how much Canada represents of the total net_income.

[Carole J. Wilson]

I don't think you can add the column you want with -tabstat-, but -table- will let you do it:

*create percent 
egen total=total(net_income)
bysort country: egen country_total=total( net_income)
gen pc_total= (country_total/ total)*100

table country, cont(mean net_income n net_income sum net_income mean pc_total )

You don't get the bottom total, but you can get that with -collapse- & -list- (without the -preserve-/-restore- commands, you will lose your data)

preserve
collapse (mean) net_income (count) N_net_income=net_income (sum) sum_net_income=net_income (mean) pc_total, by(country)
list, clean noobs sum
restore

[Budu Gulo]

Many thanks Carole J. Wilson! Now if I want to do table by year, is the following code efficacious?

*create percent by year
bysort year:egen total=total(net_income)
bysort year country: egen country_total=total( net_income)
bysort year:gen pc_total= (country_total/ total)*100
*table
bysort year: table country, cont(mean net_income n net_income sum net_income mean pc_total ) format(%-13.0fc)

In addition, how do I apply the " -collapse- & -list- " code to get the bottom totals by year?

[Amir Moayeri]

Hello Everyone,
I have a list of individuals and their incomes. I have divided them into 5 quintiles of income. I use the command below in Stata-17 to find the summation on incomes in each quintile.
table (Q)(), statistic (sum income)
How can I add another column containing the percentages? I mean, what command should I use to calculate the share of each quintile in the total income of the sample?
I appreciate your help.