How to: get a cumulative sum across years

[Christine Everaerts]

Hi all,

I was wondering whether someone could help me how to get a cumulative sum across the different years for different HS product codes among country pairs.

I want an extra column such that it tells me how many NTM_Tech's have been installed on the product (HS1992) between the country pair in a specific year. The variable of NTM_Tech is a dummy variable which equals 1 if there has been a NTM installed, 0 otherwise. (In the data provided at the end, only the country pair EU-USA is considered, but my dataset also contains other pairs)

So for example:

year	Origin	Destination	HS1992	NTM_Tech
1997	EU	USA	010111	1
1998	EU	USA	010111	1

I want that:

year	Origin	Destination	HS1992	NTM_Tech	Tech_Total
1997	EU	USA	010111	1	1
1998	EU	USA	010111	1	2

Is there someone who could help me?

Thanks in advance!

* Example generated by -dataex-. For more info, type help dataex
clear
input str12 year str25 Origin str32 Destination str6 HS1992 str1 NTM_Tech
"1997" "EU" "USA" "010111" "1"
"1997" "EU" "USA" "010119" "1"
"1998" "EU" "USA" "010111" "1"
"1998" "EU" "USA" "010119" "1"
"1999" "EU" "USA" "010111" "1"
"1999" "EU" "USA" "010119" "1"
"2000" "EU" "USA" "010111" "1"
"2000" "EU" "USA" "010119" "1"
"2001" "EU" "USA" "010111" "1"
"2001" "EU" "USA" "010119" "1"
"2002" "EU" "USA" "010111" "1"
"2002" "EU" "USA" "010119" "1"
"2003" "EU" "USA" "010111" "0"
"2003" "EU" "USA" "010119" "0"
"2004" "EU" "USA" "010111" "0"
"2004" "EU" "USA" "010119" "0"
"2005" "EU" "USA" "010111" "1"
"2005" "EU" "USA" "010119" "1"
"2006" "EU" "USA" "010111" "0"
"2006" "EU" "USA" "010119" "0"
"2007" "EU" "USA" "010111" "1"
"2007" "EU" "USA" "010119" "1"
"2008" "EU" "USA" "010111" "1"
"2008" "EU" "USA" "010119" "1"
"2009" "EU" "USA" "010111" "0"
"2009" "EU" "USA" "010119" "0"
"2010" "EU" "USA" "010111" "0"
"2010" "EU" "USA" "010119" "0"
"2011" "EU" "USA" "010111" "1"
"2011" "EU" "USA" "010119" "1"
"2012" "EU" "USA" "010111" "1"
"2012" "EU" "USA" "010119" "1"
"2013" "EU" "USA" "010111" "1"
"2013" "EU" "USA" "010119" "1"
"2014" "EU" "USA" "010111" "1"
"2014" "EU" "USA" "010119" "1"
"2015" "EU" "USA" "010111" "1"
"2015" "EU" "USA" "010119" "1"
"2016" "EU" "USA" "010111" "0"
"2016" "EU" "USA" "010119" "0"
"2017" "EU" "USA" "010111" "0"
"2017" "EU" "USA" "010119" "0"
"2018" "EU" "USA" "010111" "0"
"2018" "EU" "USA" "010119" "0"
"2019" "EU" "USA" "010111" "0"
"2019" "EU" "USA" "010119" "0"
"2020" "EU" "USA" "010111" "0"
"2020" "EU" "USA" "010119" "0"
"2021" "EU" "USA" "010111" "1"
"2021" "EU" "USA" "010111" "1"
"2021" "EU" "USA" "010119" "1"
"2021" "EU" "USA" "010119" "1"
end

[Nick Cox]

Thanks for the data example. It seems that you would be much better off with two variables made numeric, particularly as you want a cumulative sum. This may give you a start, but I am left unsure what you want to do with multiples for (origin, destination, year).

. destring year NTM_Tech, replace 
year: all characters numeric; replaced as int
NTM_Tech: all characters numeric; replaced as byte

. 
. bysort Origin Destination (year) : gen wanted = sum(NTM_Tech)

[Christine Everaerts]

Thank you Nick!

What do you mean with "multiples for (origign, destination, year)?

[Christine Everaerts]

I tried the code, unfortunately it didn't give me the desired output..

If it would be better, this is another example part of my dataset (filtered for: Origin == "EU" & HS1992 == "010111" & NTM_Tech == 1)

* Example generated by -dataex-. For more info, type help dataex
clear
input int year str25 Origin str32 Destination str6 HS1992 double(Trade1000USD avg_tariff gdp_orig gdp_dest pop_orig pop_dest) byte rta float(dist comlang_off comrelig) str11 NTM byte NTM_Tech str1 NTM_NonTech float(GDP POP)
1997 "EU" "JPN" "010111"  6762.379905700684                  0   9071080448     4324278140.928 388679.65625         126091.003 0 202625.86         0   .1026441 "SPS"     1 "" 3.9225873e+19  49009008640
1997 "EU" "USA" "010111"   5297.76598072052                  0   9071080448     8608500154.368 388679.65625         272657.013 0  143581.6 2.4444444    5.07474 "SPS"     1 ""   7.80884e+19 105976233984
1998 "EU" "AUS" "010111" 104.31999778747559                  0   9363514368       399325102.08  389589.4375              18711 0  353837.7 2.4444444  4.1723742 "SPS"     1 ""  3.739086e+18   7289608192
1998 "EU" "CHE" "010111" 1624.4379737377167                  0   9363514368      294977503.232  389589.4375           7110.001 1  23215.41  5.555555   7.497125 "SPS"     1 ""  2.762026e+18   2769981184
1998 "EU" "PAN" "010111"                  .                  0   9363514368        10932500.48  389589.4375             2933.1 0 206599.33  .9259259   9.334559 "SPS"     1 "" 1.0236663e+17   1142704768
1998 "EU" "SGP" "010111"  163.0570068359375                  .   9363514368       85707554.816  389589.4375 3927.2000000000003 0  218905.4 2.4444444   .6638054 "SPS"     1 ""   8.02524e+17   1529995648
1998 "EU" "USA" "010111" 18842.163103103638                  0   9363514368     9089200422.912  389589.4375         275854.004 0  143581.6 2.4444444    5.07474 "SPS"     1 ""  8.510686e+19 1.074698e+11
1999 "EU" "AUS" "010111"  941.8980102539063                  0   9363586048      388692180.992 390737.28125          18926.001 0  353837.7 2.4444444  4.1723742 "SPS"     1 ""  3.639553e+18   7395094016
1999 "EU" "SGP" "010111"  921.0049941539764                  .   9363586048       86285336.576 390737.28125 3958.7000000000003 0  218905.4 2.4444444   .6638054 "SPS"     1 ""  8.079402e+17   1546811648
1999 "EU" "USA" "010111" 27034.143349170685                  0   9363586048     9660599894.016 390737.28125         279040.009 0  143581.6 2.4444444    5.07474 "SPS"     1 ""  9.045786e+19 109031333888
2000 "EU" "CAN" "010111" 351.70799394499045                  0   8678073344  739455926.2720001   392254.625          30769.699 0  146292.7  5.222222   6.253809 "SPS"     1 ""  6.417053e+18  12069557248
2000 "EU" "CHE" "010111" 1856.9730154275894                  0   8678073344      271659728.896   392254.625            7184.25 1  23215.41  5.555555   7.497125 "SPS"     1 ""  2.357483e+18   2818055168
2000 "EU" "HKG" "010111"  904.1090087890625                  .   8678073344      171668160.512   392254.625               6665 0  196394.5 2.4444444  1.1672584 "SPS"     1 ""  1.489749e+18   2614376960
2000 "EU" "IND" "010111"  25.79400062561035                  .   8678073344      476609150.976   392254.625        1042261.719 0 130902.15 2.4444444   .2192376 "SPS"     1 ""  4.136049e+18  4.08832e+11
2000 "EU" "USA" "010111"  40328.95900440216                  0   8678073344      10284800409.6   392254.625         282162.415 0  143581.6 2.4444444    5.07474 "SPS"     1 ""  8.925225e+19 110679515136
2001 "EU" "HKG" "010111"  650.0709838867188                  .   8741609472      169403236.352   394069.375             6714.3 0  196394.5 2.4444444  1.1672584 "SPS"     1 ""  1.480857e+18   2.6459e+09
2001 "EU" "PAN" "010111"                  .                  0   8741609472       11807500.288   394069.375           3116.409 0 206599.33  .9259259   9.334559 "SPS"     1 "" 1.0321656e+17   1228081408
2001 "EU" "USA" "010111"  34026.34995889664                  0   8741609472    10621800153.088   394069.375         284968.964 0  143581.6 2.4444444    5.07474 "SPS"     1 ""  9.285163e+19 112297541632
2002 "EU" "CHN" "010111"                  .                  0   9518595072     1453827555.328 396147.53125        1280400.024 0  189908.9         0 .006852088 "SPS"     1 "" 1.3838396e+19 5.072273e+11
2002 "EU" "THA" "010111"                 52                  .   9518595072       126876917.76 396147.53125 63797.840000000004 0  191109.1         0  .06249279 "SPS"     1 ""   1.20769e+18  25273356288
2002 "EU" "USA" "010111" 29924.169122695923                  0   9518595072 10977500200.960001 396147.53125         287625.183 0  143581.6 2.4444444    5.07474 "SPS"     1 "" 1.0449038e+20  1.13942e+11
2003 "EU" "CHN" "010111" 180.83799743652344                  0  11615756288     1640958787.584    398362.25        1288400.024 0  189908.9         0 .006852088 "SPS"     1 "" 1.9060977e+19 5.132499e+11
2003 "EU" "PAN" "010111"                  .                  0  11615756288       12933199.872    398362.25 3240.8050000000003 0 206599.33  .9259259   9.334559 "SPS"     1 ""  1.502289e+17   1291014400
2003 "EU" "SGP" "010111"  319.3289966583252                  .  11615756288       97002307.584    398362.25             4114.8 0  218905.4 2.4444444   .6638054 "SPS"     1 "" 1.1267552e+18   1639180928
2004 "EU" "ARE" "010111"   8722.30096244812                  .  14048080896      147824377.856  477719.5625           3658.658 0 100586.22         0    .326569 "SPS"     1 ""  2.076649e+18   1747812480
2005 "EU" "ARE" "010111"  12662.14260397479                  0  14662415360      180617019.392 479942.59375           4148.883 0 100586.22         0    .326569 "SPS"     1 "" 2.6482818e+18   1991225728
2005 "EU" "CHN" "010111"  634.4890289306641                  0  14662415360       2256902553.6 479942.59375 1303719.9710000001 0  189908.9         0 .006852088 "SPS"     1 ""  3.309164e+19 6.257107e+11
2005 "EU" "PAN" "010111"                  .                7.5  14662415360 15464699.904000001 479942.59375           3365.929 0 206599.33  .9259259   9.334559 "SPS"     1 "" 2.2674986e+17   1615452672
2005 "EU" "USA" "010111" 18773.672031402588                  0  14662415360        13093699584 479942.59375         295516.602 0  143581.6 2.4444444    5.07474 "TBT"     1 "" 1.9198527e+20 141831012352
2006 "EU" "CHE" "010111"  2018.978020310402                  0  15624920064      429180747.776       482063           7483.934 1  23215.41  5.555555   7.497125 "SPS"     1 ""  6.705915e+18   3607727616
2007 "EU" "USA" "010111"  49990.48874807358                  0  18253185024     14477599703.04   513951.625         301231.201 0  143581.6 2.4444444    5.07474 "SPS"     1 ""  2.642623e+20 154818265088
2008 "EU" "AUS" "010111"  745.1500244140625                  0  19639851008 1055031623.6800001  515915.1875          21249.201 0  353837.7 2.4444444  4.1723742 "SPS"     1 "" 2.0720664e+19  10962785280
2008 "EU" "CAN" "010111" 1008.3609752655029                  0  19639851008     1542560677.888  515915.1875          33245.773 0  146292.7  5.222222   6.253809 "SPS"     1 ""  3.029566e+19   1.7152e+10
2008 "EU" "USA" "010111"  40174.20063018799                  0  19639851008    14718600216.576  515915.1875         304093.964 0  143581.6 2.4444444    5.07474 "SPS"     1 ""  2.890711e+20 1.568867e+11
2009 "EU" "AUS" "010111"  6810.383785247803                  0  17566554112  926283268.0960001 517459.96875            21691.7 0  353837.7 2.4444444  4.1723742 "SPS"     1 "" 1.6271606e+19  11224586240
2010 "EU" "THA" "010111" 120.40599822998047                  0  17500143616      318907940.864  518740.5625          66402.313 0  191109.1         0  .06249279 "TBT"     1 ""  5.580935e+18  34445574144
2010 "EU" "THA" "010111" 120.40599822998047                  0  17500143616      318907940.864  518740.5625          66402.313 0  191109.1         0  .06249279 "SPS"     1 ""  5.580935e+18  34445574144
2011 "EU" "HKG" "010111" 29.115999221801758                  .  18921826304      248514002.944    520333.75             7071.6 0  196394.5 2.4444444  1.1672584 "SPS"     1 "" 4.7023386e+18   3679592192
2011 "EU" "TUR" "010111"  1403.439011335373                  0  18921826304       774754140.16    520333.75           73058.64 1  39190.85  .6666667  .29392532 "SPS"     1 "" 1.4659763e+19  38014877696
2011 "EU" "USA" "010111" 18057.642471313477                  0  18921826304 15517900341.248001    520333.75          311582.55 0  143581.6 2.4444444    5.07474 "SPS"     1 ""   2.93627e+20 162126921728
2012 "EU" "TUR" "010111" 171.59899997711182                  .  17832341504      788863320.064   519942.125  73997.13100000001 1  39190.85  .6666667  .29392532 "SPS"     1 ""  1.406728e+19  38474223616
2012 "EU" "USA" "010111"  21176.59703063965                  .  17832341504    16163200303.104   519942.125         313873.688 0  143581.6 2.4444444    5.07474 "SPS"     1 ""  2.882277e+20 163196157952
2013 "EU" "ARE" "010111" 18018.733330726624                  .  18623186944      402340118.528   525507.375           9346.129 0 100586.22         0    .326569 "SPS"     1 ""  7.492855e+18   4911459840
2013 "EU" "SGP" "010111"  85.01199913024902                  .  18623186944      297941270.528   525507.375             5399.2 0  218905.4 2.4444444   .6638054 "SPS"     1 ""  5.548616e+18   2837319424
2013 "EU" "USA" "010111"  32304.80571103096                  .  18623186944    16768099680.256   525507.375 316128.84500000003 0  143581.6 2.4444444    5.07474 "SPS"     1 "" 3.1227545e+20 166128041984
2014 "EU" "SGP" "010111" 123.56199836730957                  .  19069810688      308142768.128 506331.53125           5469.724 0  218905.4 2.4444444   .6638054 "SPS"     1 ""  5.876224e+18   2769493760
2014 "EU" "USA" "010111"  72213.33494949341                  .  19069810688    17393103405.056 506331.53125         318563.446 0  143581.6 2.4444444    5.07474 "SPS"     1 "" 3.3168318e+20 161298710528
2014 "EU" "VNM" "010111" .32199999690055847                  .  19069810688       186204651.52 506331.53125          90728.897 0 206086.03         0   .4289102 "SPS"     1 ""  3.550887e+18  4.59389e+10
2015 "EU" "SGP" "010111"  628.9129753112793                  .  16755740672      296840691.712  508031.8125           5535.002 0  218905.4 2.4444444   .6638054 "SPS"     1 "" 4.9737854e+18   2811956992
2015 "EU" "SGP" "010111"  628.9129753112793                  .  16755740672      296840691.712  508031.8125           5535.002 0  218905.4 2.4444444   .6638054 "SPS"     1 "" 4.9737854e+18   2811956992
2015 "EU" "TUR" "010111"  799.1259908676147                  .  16755740672      859383660.544  508031.8125          78271.469 1  39190.85  .6666667  .29392532 "SPS"     1 ""  1.439961e+19  3.97644e+10
2015 "EU" "TUR" "010111"  799.1259908676147                  .  16755740672      859383660.544  508031.8125          78271.469 1  39190.85  .6666667  .29392532 "SPS"     1 ""  1.439961e+19  3.97644e+10
2015 "EU" "USA" "010111"  89276.76456832886                  .  16755740672    18036648050.688  508031.8125         320896.606 0  143581.6 2.4444444    5.07474 "SPS"     1 ""  3.022174e+20 163025682432
2016 "EU" "SAU" "010111" 3477.8400268554688                  .  16811707392      646438387.712   510006.625 32275.688000000002 0  89451.78         0   .2970511 "SPS"     1 "" 1.0867733e+19  16460814336
2016 "EU" "THA" "010111" 134.05399894714355                  .  16811707392      406839689.216   510006.625          68863.518 0  191109.1         0  .06249279 "SPS"     1 ""   6.83967e+18  35120848896
2017 "EU" "AUS" "010111"  3013.121109008789                  0  18079477760     1326882872.011    531217.25           24601.86 0  353837.7 2.4444444  4.1723742 "SPS"     1 ""  2.398935e+19  13068932096
2017 "EU" "SAU" "010111" 1035.2889556884766                  0  18079477760      688586244.293    531217.25          33101.185 0  89451.78         0   .2970511 "SPS"     1 ""  1.244928e+19  17583921152
2017 "EU" "SAU" "010111" 1035.2889556884766                  0  18079477760      688586244.293    531217.25          33101.185 0  89451.78         0   .2970511 "SPS"     1 ""  1.244928e+19  17583921152
2018 "EU" "SAU" "010111"  778.1999969482422                  .  19571306496  786521831.5730001   532389.375          33702.759 0  89451.78         0   .2970511 "SPS"     1 ""  1.539326e+19  17942990848
2018 "EU" "THA" "010111"  205.1310043334961                  .  19571306496      506611070.188   532389.375          69428.452 0  191109.1         0  .06249279 "SPS"     1 ""  9.915041e+18  36962971648
2018 "EU" "VNM" "010111"                  .                  0  19571306496 245213686.36900002   532389.375          95545.959 0 206086.03         0   .4289102 "SPS"     1 ""  4.799152e+18  50867654656
2019 "EU" "SAU" "010111" 1597.5280122756958                  .  19255736320       792966838.16   533071.125          34268.528 0  89451.78         0   .2970511 "SPS"     1 ""  1.526916e+19  18267561984
2020 "EU" "SAU" "010111" 1788.9459915161133                  0  15858302976      700117873.253 464386.65625          34813.866 0  89451.78         0   .2970511 "SPS"     1 ""  1.110268e+19  16167095296
2020 "EU" "THA" "010111" 25.736000061035156                  .  15858302976      501643653.515 464386.65625           69799.98 0  191109.1         0  .06249279 "SPS"     1 ""  7.955217e+18  32414179328
2021 "EU" "AUS" "010111"   727.052001953125                  0 177177419590         1552670000     447199.8          25688.079 0  353837.7 2.4444444  4.1723742 "SPS"     1 "" 2.7509806e+20  11487704064
2021 "EU" "USA" "010111"  92469.90227508545 34.000000953674316 177177419590        2.33151e+10     447199.8         331893.745 0  143581.6 2.4444444    5.07474 "TBT"     1 ""  4.130909e+21 148422819840
2021 "EU" "USA" "010111"  92469.90227508545 34.000000953674316 177177419590        2.33151e+10     447199.8         331893.745 0  143581.6 2.4444444    5.07474 "SPS"     1 ""  4.130909e+21 148422819840
2015 "EU" "VNM" "010111"                  .                  .            .                  .            .                  . .         .         .          . "SPS"     1 ""             .            .
2012 "EU" "VNM" "010111"                  .                  .            .                  .            .                  . .         .         .          . "SPS"     1 ""             .            .
2003 "EU" "EU"  "010111"                  .                  .            .                  .            .                  . .         .         .          . "SPS_STC" 1 ""             .            .
1999 "EU" "EU"  "010111"                  .                  .            .                  .            .                  . .         .         .          . "SPS"     1 ""             .            .
2001 "EU" "EU"  "010111"                  .                  .            .                  .            .                  . .         .         .          . "SPS"     1 ""             .            .
1998 "EU" "EU"  "010111"                  .                  .            .                  .            .                  . .         .         .          . "SPS"     1 ""             .            .
2021 "EU" "HKG" "010111"                  .                  .            .                  .            .                  . .         .         .          . "SPS"     1 ""             .            .
1999 "EU" "THA" "010111"                  .                  .            .                  .            .                  . .         .         .          . "SPS"     1 ""             .            .
2013 "EU" "EU"  "010111"                  .                  .            .                  .            .                  . .         .         .          . "SPS"     1 ""             .            .
2000 "EU" "EU"  "010111"                  .                  .            .                  .            .                  . .         .         .          . "SPS"     1 ""             .            .
1997 "EU" "EU"  "010111"                  .                  .            .                  .            .                  . .         .         .          . "SPS"     1 ""             .            .
2016 "EU" "VNM" "010111"                  .                  .            .                  .            .                  . .         .         .          . "SPS"     1 ""             .            .
2019 "EU" "EU"  "010111"                  .                  .            .                  .            .                  . .         .         .          . "SPS"     1 ""             .            .
end

The former one, was filtered because I got a message that dataex could only provide a certain amount of data.