Hi,
I am using the BACI data on trade flows at a 6 digit product level. i = exporter, j = importer, v = value of trade, k = type of product. I want to find the value of total value of trade per country and trading partner and store it in a new variable. For example, I want to have the total value of trade between country 4 and 8, which means I need to sum v for when i = 4 and j= 8, and when i = 8 and j = 4. I need it in a new variable as I will create weights at a later stage based on the importance of the trading partner.
The dataset contains 150 million observations.
I have written a code that works (below), but is very slow because it has to go through all possibilities of the pairs. Could you help me find a more efficient way?
PS: This is my first post on Statalist after many years of just being a viewer. I apologise in advance if my post lacks any critical information or formating.
I am using the BACI data on trade flows at a 6 digit product level. i = exporter, j = importer, v = value of trade, k = type of product. I want to find the value of total value of trade per country and trading partner and store it in a new variable. For example, I want to have the total value of trade between country 4 and 8, which means I need to sum v for when i = 4 and j= 8, and when i = 8 and j = 4. I need it in a new variable as I will create weights at a later stage based on the importance of the trading partner.
The dataset contains 150 million observations.
I have written a code that works (below), but is very slow because it has to go through all possibilities of the pairs. Could you help me find a more efficient way?
PS: This is my first post on Statalist after many years of just being a viewer. I apologise in advance if my post lacks any critical information or formating.
Code:
gen total_trade_j = .
levelsof i, local(levels) //Total trade with j
foreach x of local levels {
foreach y of local levels {
preserve
keep if i== `x' & j == `y' & year == 1995 | i== `y' & j== `x' & year == 1995
if i[1] == `x' {
sum v
restore
display `x'
display `y'
replace total_trade_j = r(sum) if i == `x' & j == `y'
}
else {
restore
continue
}
}
}
Code:
* Example generated by -dataex-. For more info, type help dataex clear input float(i j v) long k 4 8 .15 851999 4 8 .845 621590 4 8 .018 970110 4 8 .296 630900 4 8 .15 950410 4 8 1.315 570500 4 8 .192 610690 4 8 .188 852810 4 8 .11 630900 4 8 2.952 401519 4 8 .019 852910 4 8 1.69 630900 4 8 2.734 610469 4 40 23.401 853190 4 40 .087 610342 4 40 .1 481830 4 40 .087 731815 4 40 3.889 80420 4 40 .452 330129 4 40 .03 611790 4 40 .787 400829 4 40 .201 848120 4 40 2.134 640411 4 40 .678 940360 4 40 .104 621050 4 40 .208 620311 4 40 4.116 711311 4 40 .123 382000 4 40 163.754 840734 4 40 .788 80420 4 40 2.815 611300 4 40 .072 420229 4 40 .019 520959 4 40 3.029 420222 4 40 .579 442190 4 40 52.583 903180 4 40 .015 940490 4 40 .004 491110 4 40 .421 650590 4 40 .199 570190 4 40 1.065 610910 4 40 .979 691200 4 40 .524 81340 4 40 37.026 847330 4 40 1.607 843991 4 40 3.879 200820 4 40 .637 640320 4 40 4.091 300650 4 40 5.666 200819 4 40 418.627 300490 4 40 18.222 854449 4 40 .227 620433 4 40 .108 611430 4 40 .03 80620 4 40 1.188 711620 4 40 3.419 710310 4 40 .505 570210 4 40 54.428 81340 4 40 .47 401150 4 40 .162 852810 4 40 .328 620422 4 40 88.93 901590 4 40 .131 731815 4 40 .101 491191 4 40 1.452 420232 4 40 .459 620463 4 40 .03 620799 4 40 .222 620292 4 40 .096 620349 4 40 1.161 620463 4 40 .028 620319 4 40 .127 610469 4 40 .031 491199 4 40 .002 600293 4 40 .043 621410 4 40 .008 853610 4 40 1.268 847330 4 40 .185 80720 4 40 .669 640399 4 40 1.398 851790 4 40 .524 611710 4 40 .406 610469 4 40 .023 610719 4 40 7.009 900691 4 40 .246 490199 4 40 1.632 611020 4 40 .083 610443 4 40 1.62 621490 4 40 1.551 81090 4 40 .024 620433 4 40 .08 620444 4 40 .034 640319 4 40 1.094 70990 4 40 .191 711790 4 40 3.175 900890 4 40 1.591 321290 4 40 .559 620342 4 40 4.448 80212 4 40 .652 920600 4 40 .441 392690 end
Comment