Using "colsum" and Error Handling

[Nihat Mugurtay]

Dear Friends,

Here, there is my dataset, composed of donors' aid flows to different recipients (rows). I want to represent each recipients' aid as a percent of donors' total aid flows. In order to make it easy, I wrote some simple code but I threw an error. Here, I provide dataex, code and error output. How can I handle the problem without doing it manually.

Best

* Example generated by -dataex-. For more info, type help dataex
clear
input str60 recipient float(belgium2021 nether2021 eu2021 france2021 italy2021 spain2021 sweden2021 germany2021 uk2021)
"Afghanistan"                        2.46 29.69 331.01  42.54 92.13  8.84 104.14    579 239.47
"Albania"                             .03   .16 350.75    6.2 12.91     0  13.34 178.09   4.46
"Algeria"                             .12   .21  34.86  95.68  1.33     0   2.94  28.91   7.87
"Angola"                              .33   .14  48.95  28.41  3.76 17.45   1.84   4.78   1.87
"Antigua and Barbuda"                   0     0    .64      0     0   .12    .03      0    .12
"Azerbaijan"                            0   .17  25.19  42.12   .49   .01    .83      0   2.92
"Argentina"                             0   .16   9.72  12.87     0   7.9    .87  10.86   4.21
"Bahamas"                               0     0      0      0     0     0      0      0      0
"Bahrain"                               0     0      0      0     0     0      0      0      0
"Bangladesh"                         1.13 28.29 221.66 168.24  2.12  1.02  48.93  125.2 110.94
"Armenia"                             .72   .27  83.51  34.47   .22     0   4.89  50.91   5.36
"Barbados"                              0     0      0      0     0     0      0      0      0
"Bermuda"                               0     0      0      0     0     0      0      0      0
"Bhutan"                                0     0   6.31    .04   .05     0    .41   2.49    .25
"Bolivia"                             6.4     0  38.07 144.25   3.9 40.25  28.17  35.25   1.91
"Bosnia and Herzegovina"                0   .12 312.86   2.65  4.73     0  25.84  45.46    6.1
"Botswana"                              0     0   5.92   1.46     0     0    .54   1.22      3
"Brazil"                             1.38   .28 154.39 449.79 12.19  6.57    3.1  29.81  35.94
"Belize"                                0     0   8.71      0     0     0    .03     .1   1.46
"Solomon Islands"                       0     0   2.45      0     0     0    .07     .4    .76
"British Virgin Islands"                0     0      0      0     0     0      0      0      0
"Brunei Darussalam"                     0     0      0      0     0     0      0      0      0
"Myanmar"                             .02  2.15  82.07    7.8  5.82   .01  37.68  38.12  84.34
"Burundi"                           33.94  35.5  64.05   8.54  3.96  1.71   2.55  28.85   1.02
"Belarus"                             .23   .81   4.26   1.29   .27   .01   1.29  14.06   3.35
"Cambodia"                           3.86   .14  99.82 106.64   .27     0  21.27  51.52   2.59
"Cameroon"                           2.46     0 100.09 129.83  7.19     0     12 128.25  10.22
"Cabo Verde"                         1.49     0  19.22   1.14  1.58   .64    .11   1.02    .28
"Cayman Islands"                        0     0      0      0     0     0      0      0      0
"Central African Republic"              0  4.55  83.33   29.2  7.42   .66   17.6  64.78  12.63
"Sri Lanka"                           .38   .17  61.37  13.24  1.69     0   2.36  14.73  10.92
"Chad"                                  0     0  82.84  81.21  5.07  6.74  10.49  35.17   1.53
"Chile"                                 0     0      0      0     0     0      0      0      0
"China (People's Republic of)"        .41  3.16  69.42      0     0     0   2.43 336.58  65.82
"Colombia"                           1.62  3.77  72.87 286.96   3.6 78.59  50.34 268.06  53.71
"Tokelau"                               0     0      0      0     0     0      0      0      0
"Comoros"                               0     0   4.38  44.31     0     0    .13    .02    .19
"Mayotte"                               0     0      0      0     0     0      0      0      0
"Congo"                               .87   .49   8.97  46.69   .79     1    .66    .65    .09
"Democratic Republic of the Congo" 105.38 23.95 203.35  33.69 11.52  5.85 104.15 178.81  92.83
"Cook Islands"                          0     0      0      0     0     0      0      0      0
"Costa Rica"                          .05   .42    4.1   5.34   .08  5.96    .99   2.92   1.05
"Croatia"                               0     0      0      0     0     0      0      0      0
"Cuba"                               4.18   .27     24   16.1 15.01   6.1   3.45   1.83   1.88
"Cyprus"                                0     0      0      0     0     0      0      0      0
"Benin"                             25.45 39.04  52.02     81  5.23   .25    1.2  51.84    .04
"Dominica"                              0     0    1.2      0     0   .01      0    .12     .9
"Dominican Republic"                  .42   .03   19.8 252.77   .08  1.47    .42   2.49   1.12
"Ecuador"                            6.85     0  48.54  38.63  3.05  8.73    .72  40.06   1.35
"El Salvador"                        2.12   .12   4.88  26.21  2.46 31.79      2      0    .89
"Equatorial Guinea"                     0     0    .15   2.56     0  2.83    .07    .01    .04
"Ethiopia"                           9.91  71.2 158.91  42.01 50.38 11.35  77.09 162.94 152.41
"Eritrea"                               0     0   1.42    .61  1.55     0   1.05   3.59   2.08
"Fiji"                                  0     0   8.91    .62     0     0    .31   1.24   1.63
"French Polynesia"                      0     0      0      0     0     0      0      0      0
"Djibouti"                              0     0  21.12    9.6   .46     0     .8    .16    .36
"Gabon"                                 0     0   4.39  51.07   .11   .07    .08    .73      0
"Georgia"                             .01   .56 269.24  186.2   .18   .01   16.4 239.83   6.67
"Gambia"                              .06   .04  30.77    .99   .86   .42   1.01   2.01  22.89
"Ghana"                               .95  8.72  45.29  42.66  1.11     0   3.93  245.4  31.81
"Gibraltar"                             0     0      0      0     0     0      0      0      0
"Kiribati"                              0     0    .87      0     0     0      0      0    .01
"Grenada"                               0     0    .01    .04     0     0      0    .68    .12
"Guatemala"                          2.21     0  39.05   1.78   .69  33.4  35.15  24.71   2.24
"Guinea"                            11.64     0  59.38   73.5  1.53   .27    .95  11.12    .96
"Guyana"                                0     0   9.53    .73     0   .19    .19    .72   1.84
"Haiti"                               5.3   .33  91.01  67.86  2.25     0   5.07   6.75   1.25
"Honduras"                           1.04     0  47.55    .28   .07  9.35   2.98  18.57    .36
"Hong Kong (China)"                     0     0      0      0     0     0      0      0      0
"India"                              1.51   .46 113.89      0 17.84  7.24   9.52 664.69 115.37
"Indonesia"                          2.76     0  14.78  30.79   .71     0    5.8 196.78  37.53
"Iran"                                .21     0  30.71  12.73  3.74   .57   1.51 103.93    .55
"Iraq"                                4.8 27.56 184.73  38.03  11.6   .39  40.93 342.83   64.5
"Israel"                                0     0      0      0     0     0      0      0      0
"Côte d'Ivoire"                      .31   .02  91.57 343.87  4.13 10.89    1.2  86.73   4.39
"Jamaica"                               0     0  13.57      0     0   .69    .11      0    7.8
"Kazakhstan"                            0   .42   1.58   3.38   .23   .05    .07   3.58   2.49
"Jordan"                             2.27 14.19 631.74  59.65 57.82  1.58  10.83 410.07  79.37
"Kenya"                             12.15  9.99 121.65  69.97 11.57   .14  51.69   83.2   91.6
"Korea"                                 0     0      0      0     0     0      0      0      0
"Kuwait"                                0     0      0      0     0     0      0      0      0
"Kyrgyzstan"                            0   .12  71.64   1.62   .05     0    .22  33.99   6.15
"Lao People's Democratic Republic"      0   .03  88.03  12.29   .13     0   1.86  31.42   1.39
"Lebanon"                            9.32 21.64 144.83  88.33 27.57   6.3   9.64 331.25  73.72
"Lesotho"                               0     0  21.38    .02     0     0    .24   1.03   1.14
"Liberia"                               0   .06  34.14   6.06     0   .07  40.66  32.83   3.73
"Libya"                               .29  2.25  86.95   6.44 20.79   .14   5.33  47.57  16.27
"Madagascar"                         4.34     0  79.42 101.64  2.85   .03    4.2  78.56   8.06
"Malawi"                             2.69     0 112.86   2.84  1.58   .51   4.63  87.26  53.82
"Malaysia"                              0     0   1.97   4.89   .02     0     .7  12.45  13.91
"Maldives"                              0     0   2.19    .12     0     0      0    .35   2.07
"Mali"                              20.17 47.51 119.47 114.48  8.46 16.44  47.82 110.69  10.78
"Malta"                                 0     0      0      0     0     0      0      0      0
"Mauritania"                          .39     0  61.25      0  1.18 12.14    .88  20.61    .67
"Mauritius"                             0     0   1.36      0   .01     0    .01   3.68   1.13
"Mexico"                              .06   .11   3.96 337.29   .42  4.75    .97      0  25.91
"Mongolia"                              0   .29  19.72   2.61   .08     0    .13  16.94   2.29
"Moldova"                             .01     0 312.02   3.61   .49   .02  15.09  14.79   2.88
"Montenegro"                            0     0 101.72    .59   .04     0    .26    .19   2.41
"Montserrat"                            0     0   2.24      0     0     0      0      0  34.54
end

------------------ copy up to and including the previous line ------------------

Listed 100 out of 171 observations
Use the count() option to list more

gen total_aid = colsum(of varlist:*)

foreach var of varlist:* { gen `var'_percent = `var' / total_aid * 100 }

Error: ".
. gen total_aid = colsum(of varlist:*)
unknown function colsum()
r(133);"

[Joro Kolev]

The error message says it, there is no function colsum() in Stata.

This should do it:

. foreach var of varlist belgium2021-uk2021 {
  2. summ `var', meanonly
  3. replace `var' = `var'/r(sum)
  4. }

[Clyde Schechter]

Well, the most straightforward way is to start from your existing wide data layout:

foreach v of varlist *2021 {
    summ `v', meanonly
    gen `v'_pct = 100*`v'/r(mean), after(`v')
}

But your existing data layout does not really lend itself to data management and analysis in Stata. Most Stata commands are optimized to work with data in long layout. And if your full data set contains not only multiple donors but multiple years, you will soon find yourself floundering to do anything useful with it in its current form. For that reason, I recommend first reshaping your data into long layout. From there, it is just a 1 line command to get the percentages you want, and nearly everything else you want to do afterwards will be not only possible, but easier.

// COMMANDS LEADING TO LONG LAYOUT DATA
ds recipient, not
rename (`r(varlist)') aid=
reshape long aid, i(recipient) j(donoryear) string
gen int year = real(substr(donoryear, -4, 4))
gen donor = substr(donoryear, 1, strlen(donoryear)-4)
drop donoryear // DATA IS NOW LONG.  SUGGEST SAVING THIS DATASET FOR FUTURE USE

// CALCULATE PERCENTAGE OF DONOR'S AID FOR EACH RECIPIENT EACH YEAR
by donor year (recipient), sort: egen pct_donor_total = pc(aid)

Added: Crossed with #2.

[Nihat Mugurtay]

Dear Joro Kolev,

Thank you so much, I run the command and it worked. Dear Clyde Schechter, it is more visible for me now to handle the issue.

Best,

[Nick Cox]

The problem is solved but there is still a question of why you thought

 
 colsum(of varlist:*)

was Stata code. There is a colsum() function in Mata but it can't be used in Stata.

[Nihat Mugurtay]

Dear Nick Cox,

Actually, I was trying to find a way to solve the problem from various documents, where I encountered "colsum". I just tried to run some options where I find them on such documents.

[Joro Kolev]

Dear Nick Cox,

Actually, I was trying to find a way to solve the problem from various documents, where I encountered "colsum". I just tried to run some options where I find them on such documents.

This looks like Mata help. But it is not clear why it is not clearly identified as such...

[Chen Samulsion]

[M] Mata Matrix Programming

[Nick Cox]

The document linked in #6 is https://www.stata.com/manuals13/m-5sum.pdf and the m-5 is diagnostic of a Mata command. So, Nihat presumably found that with some kind of internet search. The same manual page accessed from within any Stata that supports colsum() does bear the tag [M-5] more explicitly.

You could use colsum() to feed back a sum of a variable to Stata in the following way.

1. Put a copy of a variable in a Mata column.

2. Apply colsum() to the values of that variable, now a column.

3. Pass the result back to Stata as a scalar.

That sounds quite a lot of work, but here is sample code.

. sysuse auto, clear
(1978 automobile data)

. mata : st_numscalar("total", colsum(st_data(., "weight")))

. di total
223440

There are other ways to do that too in Mata.

Only under the aegis of a mata: call within Stata, or if you are in Mata itself, is colsum() going to work. I wouldn't do that myself in this context, or most others. As clearly explained in #2 summarize will find the sum for you. You may use it directly as a saved result r(sum), and not feel compelled to put it in a macro, scalar, or variable.

 
. su weight, meanonly

. di r(sum)
223440

[Nihat Mugurtay]

Thank you so much Nick Cox,

This is an explanatory and enlightening caution about the command.

Best,