Generating sums of values of a variable for certain values of a commodity code

[Tony Ekere]

Hi all,

I'm using stata 18 on windows and I'm working on bilateral trade data for 21 years, for about 200 countries. The trade commodities are coded and stringed in 2, 4 and 6 digits respectively. The 4 digits are a further specification of the 2 digits and the 6 digits also further specify the 4 digits. I'm interested in working at the 4 digit level. However, I have a list of green products given at the 6 digit code. I will like to calculate the green intensities of each 4 digits code. That is, the sum of trade values of green 6 digits within each 4 digits divided by the total trade values of their respective four digits (green + nongreen). Not all 4 digits contain at least 1 green 6 digit code, so these will be 0.

Below is an exmple dataset;

* Example generated by -dataex-. For more info, type help dataex
clear
input int year str24 reporter int reportercode str32 partner int partnercode str6 commoditycode double tradevalueus
1988 "Australia" 36 "World"                       0 "01"     252106823
1988 "Australia" 36 "Argentina"                  32 "01"        323513
1988 "Australia" 36 "Austria"                    40 "01"          1172
1988 "Australia" 36 "Bahrain"                    48 "01"       6944755
1988 "Australia" 36 "Solomon Isds"               90 "01"          4964
1988 "Australia" 36 "Brunei Darussalam"          96 "01"       4427812
1988 "Australia" 36 "Canada"                    124 "01"        277546
1988 "Australia" 36 "China"                     156 "01"         61070
1988 "Australia" 36 "Denmark"                   208 "01"          1172
1988 "Australia" 36 "Dominican Rep."            214 "01"          1563
1988 "Australia" 36 "Fiji"                      242 "01"         11695
1988 "Australia" 36 "France"                    251 "01"        275017
1988 "Australia" 36 "Fmr Fed. Rep. of Germany"  280 "01"         19862
1988 "Australia" 36 "Kiribati"                  296 "01"           633
1988 "Australia" 36 "China, Hong Kong SAR"      344 "01"       3032658
1988 "Australia" 36 "Indonesia"                 360 "01"       5101323
1988 "Australia" 36 "Ireland"                   372 "01"          1563
1988 "Australia" 36 "Israel"                    376 "01"         55305
1988 "Australia" 36 "Japan"                     392 "01"      16467797
1988 "Australia" 36 "Jordan"                    400 "01"         37364
1988 "Australia" 36 "Rep. of Korea"             410 "01"        600145
1988 "Australia" 36 "Kuwait"                    414 "01"      37574736
1988 "Australia" 36 "China, Macao SAR"          446 "01"         42795
1988 "Australia" 36 "Malaysia"                  458 "01"       6890297
1988 "Australia" 36 "Mexico"                    484 "01"          2657
1988 "Australia" 36 "Other Asia, nes"           490 "01"        630368
1988 "Australia" 36 "Oman"                      512 "01"       6366192
1988 "Australia" 36 "Netherlands"               528 "01"          6801
1988 "Australia" 36 "New Caledonia"             540 "01"        288838
1988 "Australia" 36 "Vanuatu"                   548 "01"          2345
1988 "Australia" 36 "New Zealand"               554 "01"      15600787
1988 "Australia" 36 "Norfolk Isds"              574 "01"          1252
1988 "Australia" 36 "Pakistan"                  586 "01"         10480
1988 "Australia" 36 "Papua New Guinea"          598 "01"         77847
1988 "Australia" 36 "Peru"                      604 "01"       1027226
1988 "Australia" 36 "Philippines"               608 "01"       3278097
1988 "Australia" 36 "Qatar"                     634 "01"      10762174
1988 "Australia" 36 "Saudi Arabia"              682 "01"      86932919
1988 "Australia" 36 "India"                     699 "01"        150780
1988 "Australia" 36 "Singapore"                 702 "01"       3119681
1988 "Australia" 36 "So. African Customs Union" 711 "01"       7859049
1988 "Australia" 36 "Spain"                     724 "01"         37524
1988 "Australia" 36 "Switzerland"               757 "01"         73348
1988 "Australia" 36 "Syria"                     760 "01"           798
1988 "Australia" 36 "Thailand"                  764 "01"       2145489
1988 "Australia" 36 "United Arab Emirates"      784 "01"      19618043
1988 "Australia" 36 "Fmr USSR"                  810 "01"        503330
1988 "Australia" 36 "Egypt"                     818 "01"       3554932
1988 "Australia" 36 "United Kingdom"            826 "01"         45992
1988 "Australia" 36 "USA"                       842 "01"       1044357
1988 "Australia" 36 "US Misc. Pacific Isds"     849 "01"         13554
1988 "Australia" 36 "Uruguay"                   858 "01"         12320
1988 "Australia" 36 "Venezuela"                 862 "01"        979740
1988 "Australia" 36 "Fmr Arab Rep. of Yemen"    886 "01"       5787665
1988 "Australia" 36 "Areas, nes"                899 "01"         17481
1988 "Australia" 36 "World"                       0 "0101"    22182487
1988 "Australia" 36 "Brunei Darussalam"          96 "0101"       10943
1988 "Australia" 36 "Fiji"                      242 "0101"        3126
1988 "Australia" 36 "Fmr Fed. Rep. of Germany"  280 "0101"        7817
1988 "Australia" 36 "China, Hong Kong SAR"      344 "0101"     2741293
1988 "Australia" 36 "Indonesia"                 360 "0101"      261799
1988 "Australia" 36 "Japan"                     392 "0101"      557610
1988 "Australia" 36 "Rep. of Korea"             410 "0101"      600145
1988 "Australia" 36 "Malaysia"                  458 "0101"      146255
1988 "Australia" 36 "Other Asia, nes"           490 "0101"       48893
1988 "Australia" 36 "New Caledonia"             540 "0101"       20169
1988 "Australia" 36 "New Zealand"               554 "0101"    14500923
1988 "Australia" 36 "Papua New Guinea"          598 "0101"       25797
1988 "Australia" 36 "Philippines"               608 "0101"       81838
1988 "Australia" 36 "Singapore"                 702 "0101"     1179131
1988 "Australia" 36 "Spain"                     724 "0101"       37524
1988 "Australia" 36 "Thailand"                  764 "0101"     1031317
1988 "Australia" 36 "United Kingdom"            826 "0101"        2345
1988 "Australia" 36 "USA"                       842 "0101"      925172
1988 "Australia" 36 "Areas, nes"                899 "0101"         390
1988 "Australia" 36 "World"                       0 "010111"  12886335
1988 "Australia" 36 "Brunei Darussalam"          96 "010111"      6253
1988 "Australia" 36 "Fiji"                      242 "010111"      3126
1988 "Australia" 36 "Indonesia"                 360 "010111"     88598
1988 "Australia" 36 "Japan"                     392 "010111"     30754
1988 "Australia" 36 "Rep. of Korea"             410 "010111"    156930
1988 "Australia" 36 "Malaysia"                  458 "010111"     24312
1988 "Australia" 36 "New Caledonia"             540 "010111"     20169
1988 "Australia" 36 "New Zealand"               554 "010111"  12058621
1988 "Australia" 36 "Philippines"               608 "010111"     81838
1988 "Australia" 36 "Singapore"                 702 "010111"    174330
1988 "Australia" 36 "Spain"                     724 "010111"     37524
1988 "Australia" 36 "Thailand"                  764 "010111"    203880
1988 "Australia" 36 "World"                       0 "010119"   9287476
1988 "Australia" 36 "Fmr Fed. Rep. of Germany"  280 "010119"      7817
1988 "Australia" 36 "China, Hong Kong SAR"      344 "010119"   2741293
1988 "Australia" 36 "Indonesia"                 360 "010119"    173201
1988 "Australia" 36 "Japan"                     392 "010119"    526856
1988 "Australia" 36 "Rep. of Korea"             410 "010119"    443215
1988 "Australia" 36 "Malaysia"                  458 "010119"    121943
1988 "Australia" 36 "Other Asia, nes"           490 "010119"     48893
1988 "Australia" 36 "New Zealand"               554 "010119"   2442302
1988 "Australia" 36 "Papua New Guinea"          598 "010119"     25797
1988 "Australia" 36 "Singapore"                 702 "010119"   1004801
1988 "Australia" 36 "Thailand"                  764 "010119"    823451
end

Please any ideas on how to proceed?

[Nick Cox]

help egen

and use its total() function.

[Clyde Schechter]

gen commodity4 = substr(commoditycode, 1, 4)
by commodity4 (reportercode year partnercode), sort: egen total_trade = total(tradevalueus)
by commodity4: egen green_trade = total(cond(strlen(commoditycode) == 6, tradevalueus, .))
gen green_intensity = green_trade/total_trade

Added: Crossed with #2.

[Tony Ekere]

Thanks Nick and Clyde. I think I was not so explicit. Permit me to explain further.

1. Not all the 6 digit codes are green. Only about 168 out of almost 5,000 "6 digit" codes are green and these cut across 78 out of around 2,200 "4 digit" codes. I have a list of green products.
2. I will like to calculate the green trade intensity of each bilateral pair, per year, for each four digits code. For those codes that do not have a green product their green intensity should equate zero.
3. A "4 digit" code can have one, two or several "6 digit" green codes.
4. A "4 digit" code can also have no "6 digit" breakdown at all, where the classification stops at 4 digits.

For example, in the dataex in #1, 0101 is a "4 digit" code that has two "6 digit" codes; 010111 & 010119. Assuming only the first is a green product, I will like to get its' trade value divided by the sum of both trade values for each country pair, per year, wherever it appears.

[Clyde Schechter]

As stated, and with your example data, this cannot be done because there is nothing in the data that identifies which 6 digit codes are green and which are not.

To do the summation by bilateral pairs per year is a relatively simple modification to the code, but there is no point in showing that when the fundamental problem is not solvable.

[Tony Ekere]

Please find below a data example with green identifiers.

* Example generated by -dataex-. For more info, type help dataex
clear
input int year str32 reporter int reportercode str44 partner int partnercode str6 commoditycode double tradevalueus float Green_tag
2016 "France"                           251 "Benin"                   204 "840490"      2877 0
2016 "Indonesia"                        360 "China"                   156 "840590"        80 0
2016 "Rep. of Korea"                    410 "Russian Federation"      643 "840590"      1200 0
2016 "Panama"                           591 "Dominican Rep."          214 "840590"      5878 0
2016 "Belgium"                           56 "United Rep. of Tanzania" 834 "841410"      2149 2
2016 "China"                            156 "China, Hong Kong SAR"    344 "841410"   3789086 2
2016 "Germany"                          276 "Iraq"                    368 "841410"     85484 2
2016 "Italy"                            381 "Botswana"                 72 "841410"      4679 2
2016 "United Kingdom"                   826 "Poland"                  616 "841410"    746314 2
2016 "Austria"                           40 "Latvia"                  428 "841420"       875 0
2016 "Belgium"                           56 "Namibia"                 516 "841430"       472 2
2016 "Czechia"                          203 "Kuwait"                  414 "841430"    220983 2
2016 "Germany"                          276 "Serbia"                  688 "841430"   1349155 2
2016 "Viet Nam"                         704 "Canada"                  124 "841430"       500 2
2016 "Spain"                            724 "Benin"                   204 "841430"      2781 2
2016 "USA"                              842 "Hungary"                 348 "841430"     35691 2
2016 "Bolivia (Plurinational State of)"  68 "Chile"                   152 "841440"    370156 2
2016 "Czechia"                          203 "United Arab Emirates"    784 "841440"    928494 2
2016 "Bosnia Herzegovina"                70 "Croatia"                 191 "841451"       165 0
2016 "Croatia"                          191 "Italy"                   381 "841451"     40042 0
2016 "Germany"                          276 "Portugal"                620 "841451"    243473 0
2016 "Japan"                            392 "Russian Federation"      643 "841451"    472469 0
2016 "Singapore"                        702 "Estonia"                 233 "841451"      4094 0
2016 "Slovakia"                         703 "Belarus"                 112 "841451"       716 0
2016 "Thailand"                         764 "Japan"                   392 "841451"  11929128 0
2016 "USA"                              842 "Curaçao"                531 "841451"    207064 0
2016 "Canada"                           124 "Nepal"                   524 "841459"       298 2
2016 "Pakistan"                         586 "Saudi Arabia"            682 "841459"        54 2
2016 "Portugal"                         620 "Hungary"                 348 "841459"      1425 2
2016 "Portugal"                         620 "India"                   699 "841459"     13293 2
2016 "India"                            699 "Grenada"                 308 "841459"       651 2
2016 "Thailand"                         764 "Israel"                  376 "841459"    853824 2
2016 "United Kingdom"                   826 "Bulgaria"                100 "841459"    235512 2
2016 "USA"                              842 "Estonia"                 233 "841459"    218886 2
2016 "France"                           251 "Romania"                 642 "841460"      6564 0
2016 "Poland"                           616 "Oman"                    512 "841460"    271010 0
2016 "Belgium"                           56 "Ethiopia"                231 "841490"   1182251 2
2016 "Belgium"                           56 "Germany"                 276 "841490"  75108372 2
2016 "China"                            156 "Br. Virgin Isds"          92 "841490"     19353 2
2016 "Croatia"                          191 "Switzerland"             757 "841490"     45435 2
2016 "Croatia"                          191 "North Macedonia"         807 "841490"     10981 2
2016 "India"                            699 "Cameroon"                120 "841490"     36288 2
2016 "Switzerland"                      757 "Kuwait"                  414 "841490"    844766 2
2016 "Switzerland"                      757 "Mali"                    466 "841490"     38263 2
2016 "Turkey"                           792 "New Zealand"             554 "841490"     19123 2
2017 "Hungary"                          348 "Germany"                 276 "840410"     68941 2
2017 "Lithuania"                        440 "United Kingdom"          826 "840410"     28283 2
2017 "Switzerland"                      757 "Hungary"                 348 "840410"       287 2
2017 "Belgium"                           56 "Switzerland"             757 "840420"      1627 2
2017 "Estonia"                          233 "Austria"                  40 "840490"        62 0
2017 "Spain"                            724 "Brazil"                   76 "840490"     78828 0
2017 "Estonia"                          233 "Ukraine"                 804 "840510"     58839 2
2017 "France"                           251 "Indonesia"               360 "840510"     14278 2
2017 "Colombia"                         170 "Panama"                  591 "841410"      4332 2
2017 "Czechia"                          203 "Serbia"                  688 "841410"     78207 2
2017 "France"                           251 "French Polynesia"        258 "841410"     75315 2
2017 "Rep. of Korea"                    410 "Mauritius"               480 "841410"      5300 2
2017 "Norway"                           579 "Azerbaijan"               31 "841410"     40222 2
2017 "Spain"                            724 "Qatar"                   634 "841410"    103953 2
2017 "USA"                              842 "Russian Federation"      643 "841410"    634840 2
2017 "Bulgaria"                         100 "Romania"                 642 "841420"       826 0
2017 "Mongolia"                         496 "Sweden"                  752 "841420"        28 0
2017 "Poland"                           616 "Iceland"                 352 "841420"      1548 0
2017 "Sweden"                           752 "Spain"                   724 "841420"     14062 0
2017 "Australia"                         36 "Canada"                  124 "841430"       384 2
2017 "Bosnia Herzegovina"                70 "Slovenia"                705 "841430"       176 2
2017 "Hungary"                          348 "Belgium"                  56 "841430"   1995968 2
2017 "Thailand"                         764 "Norway"                  579 "841430"     18749 2
2017 "China, Hong Kong SAR"             344 "Viet Nam"                704 "841440"    301077 2
2017 "Belgium"                           56 "Slovenia"                705 "841451"       196 0
2017 "China"                            156 "Maldives"                462 "841451"    336332 0
2017 "Denmark"                          208 "Austria"                  40 "841451"     10785 0
2017 "Poland"                           616 "Germany"                 276 "841451"   2367742 0
2017 "Denmark"                          208 "Latvia"                  428 "841459"     74551 2
2017 "Germany"                          276 "Finland"                 246 "841459"  41187882 2
2017 "Germany"                          276 "France"                  251 "841459" 155645839 2
2017 "Iran"                             364 "United Arab Emirates"    784 "841459"     23511 2
2017 "Israel"                           376 "Peru"                    604 "841459"     40000 2
2017 "Italy"                            381 "Spain"                   724 "841459"  23609272 2
2017 "Lithuania"                        440 "Italy"                   381 "841459"     76642 2
2017 "Netherlands"                      528 "Libya"                   434 "841459"      1638 2
2017 "Romania"                          642 "Montenegro"              499 "841459"       806 2
2017 "Guatemala"                        320 "Cuba"                    192 "841460"        35 0
2017 "Belgium"                           56 "Italy"                   381 "841480"   9877859 2
2017 "China"                            156 "Singapore"               702 "841480"  11458613 2
2017 "France"                           251 "United Kingdom"          826 "841480"  76924394 2
2017 "India"                            699 "Senegal"                 686 "841480"     72140 2
2017 "Slovakia"                         703 "Greece"                  300 "841480"      3925 2
2017 "Switzerland"                      757 "Haiti"                   332 "841480"    142414 2
2017 "Malaysia"                         458 "Viet Nam"                704 "841490"   5995751 2
2017 "Norway"                           579 "Dominican Rep."          214 "841490"     40102 2
2017 "Romania"                          642 "Spain"                   724 "841490"     40509 2
2017 "South Africa"                     710 "Czechia"                 203 "841490"        76 2
2017 "United Arab Emirates"             784 "Kuwait"                  414 "840420"      1498 2
2017 "United Arab Emirates"             784 "Uganda"                  800 "841420"      1450 0
2018 "Netherlands"                      528 "Sweden"                  752 "840410"   1145946 2
2018 "Belarus"                          112 "Ukraine"                 804 "840420"       200 2
2018 "Canada"                           124 "Australia"                36 "840490"      1474 0
2018 "Germany"                          276 "Poland"                  616 "840490"   2693747 0
2018 "Ireland"                          372 "United Kingdom"          826 "840490"     25006 0
end

For green goods, Green_tag =2 & for non-green, Green_tag=0

[Clyde Schechter]

OK, the code is now modified to do the calculations for each trading pair and year, and to select out as green, those items for which Green_tag == 2.

Now, I will point out that in this example data, each four-digit level either has all observations with Green_tag == 2, or none within any pair of countries and year. So it works out that the green_intensity is always either 0 or 1. But presumably in your real data set there are some four-digit level commodity codes that exhibit green/non-green variability at least for some trading pairs and years.

gen commodity4 = substr(commoditycode, 1, 4)
by commodity4 reportercode year partnercode, sort: egen total_trade = total(tradevalueus)
by commodity4 reportercode year partnercode: egen green_trade = ///
    total(cond(Green_tag == 2, tradevalueus, .))
gen green_intensity = green_trade/total_trade

[Tony Ekere]

Thanks Clyde, It works perfectly. Yes, in my original data I have some four-digit level commodity codes that exhibit both green and non-green variability for some trading pairs. Many thanks.