Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Creating bilateral indicators for a pair of countries in a Panel data

    Hi all,

    I am looking to create the following ratio t_ij= Xii*Xjj / Xij*Xji in STATA, where Xij is the amount of trade flows from country i to partner country j.
    Basically, what I want is to produce the above ratio for every country pair and by years.
    Taking for example in 1986 and country pair EGY FRA, thus t_(EGY,FRA)= t_(FRA,EGY)=(21513.73*331483.529) / (64.21 * 712.93)
    kindly find below a sample of my dataset (using dataex).
    Thank you so much.

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input str3(exporter importer) float year double trade
"EGY" "EGY" 1986 21513.732490437775
"EGY" "FRA" 1986  64.21695140701533
"EGY" "JOR" 1986  8.102257941186428
"EGY" "USA" 1986  36.60202864223719
"FRA" "EGY" 1986  712.9363456668854
"FRA" "FRA" 1986  331483.5292166333
"FRA" "JOR" 1986   87.8217879538536
"FRA" "USA" 1986  8971.946022914886
"JOR" "EGY" 1986 12.781617261639118
"JOR" "FRA" 1986 12.256044158697128
"JOR" "JOR" 1986  4103.510239855207
"JOR" "USA" 1986  5.215929063171148
"USA" "EGY" 1986  856.1221194445491
"USA" "FRA" 1986  7209.906632585526
"USA" "JOR" 1986 161.66331162691117
"USA" "USA" 1986 2095390.4279831976
"EGY" "EGY" 2006 25694.889538964944
"EGY" "FRA" 2006  549.1717557053418
"EGY" "JOR" 2006 168.16246060848235
"EGY" "USA" 2006  888.4000080750436
"FRA" "EGY" 2006  916.5363648258672
"FRA" "FRA" 2006  685143.9163277873
"FRA" "JOR" 2006  345.7737723813057
"FRA" "USA" 2006 32968.143155763624
"JOR" "EGY" 2006 50.585540661748496
"JOR" "FRA" 2006  8.101419810827256
"JOR" "JOR" 2006 24843.785552740606
"JOR" "USA" 2006 1066.4934238657802
"USA" "EGY" 2006 1738.9365705337525
"USA" "FRA" 2006 25712.636164726257
"USA" "JOR" 2006   449.262461796999
"USA" "USA" 2006  4233436.103399765
end
    ------------------ copy up to and including the previous line ------------------

    Listed 32 out of 32 observations

    Tags: None
  • #2
    Thanks for the data example. Here is one way.

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input str3(exporter importer) float year double trade
"EGY" "EGY" 1986 21513.732490437775
"EGY" "FRA" 1986  64.21695140701533
"EGY" "JOR" 1986  8.102257941186428
"EGY" "USA" 1986  36.60202864223719
"FRA" "EGY" 1986  712.9363456668854
"FRA" "FRA" 1986  331483.5292166333
"FRA" "JOR" 1986   87.8217879538536
"FRA" "USA" 1986  8971.946022914886
"JOR" "EGY" 1986 12.781617261639118
"JOR" "FRA" 1986 12.256044158697128
"JOR" "JOR" 1986  4103.510239855207
"JOR" "USA" 1986  5.215929063171148
"USA" "EGY" 1986  856.1221194445491
"USA" "FRA" 1986  7209.906632585526
"USA" "JOR" 1986 161.66331162691117
"USA" "USA" 1986 2095390.4279831976
"EGY" "EGY" 2006 25694.889538964944
"EGY" "FRA" 2006  549.1717557053418
"EGY" "JOR" 2006 168.16246060848235
"EGY" "USA" 2006  888.4000080750436
"FRA" "EGY" 2006  916.5363648258672
"FRA" "FRA" 2006  685143.9163277873
"FRA" "JOR" 2006  345.7737723813057
"FRA" "USA" 2006 32968.143155763624
"JOR" "EGY" 2006 50.585540661748496
"JOR" "FRA" 2006  8.101419810827256
"JOR" "JOR" 2006 24843.785552740606
"JOR" "USA" 2006 1066.4934238657802
"USA" "EGY" 2006 1738.9365705337525
"USA" "FRA" 2006 25712.636164726257
"USA" "JOR" 2006   449.262461796999
"USA" "USA" 2006  4233436.103399765
end

preserve
rename (exporter importer trade) (importer exporter trade2)
tempfile file1
save `file1'
keep if exporter==importer
keep exporter year trade2
rename trade2 trade3
tempfile file2
save `file2'
rename (exporter trade3) (importer trade4)
tempfile file3
save `file3'
restore
merge 1:1 exporter importer year using `file1'
drop _merge
merge m:1 exporter year using `file2'
drop _merge
merge m:1 importer year using `file3'
drop _merge
gen wanted= (trade3* trade4)/(trade*trade2)
    Result:

    Code:
    . l, sepby(year)

     +---------------------------------------------------------------------------------------+
     | exporter   importer   year       trade      trade2      trade3      trade4     wanted |
     |---------------------------------------------------------------------------------------|
  1. |      USA        EGY   1986   856.12212   36.602029   2095390.4   21513.732    1438599 |
  2. |      EGY        EGY   1986   21513.732   21513.732   21513.732   21513.732          1 |
  3. |      JOR        EGY   1986   12.781617   8.1022579   4103.5102   21513.732   852470.6 |
  4. |      FRA        EGY   1986   712.93635   64.216951   331483.53   21513.732   155767.7 |
     |---------------------------------------------------------------------------------------|
  5. |      FRA        EGY   2006   916.53636   549.17176   685143.92    25694.89   34976.04 |
  6. |      JOR        EGY   2006   50.585541   168.16246   24843.786    25694.89    75042.8 |
  7. |      EGY        EGY   2006    25694.89    25694.89    25694.89    25694.89          1 |
  8. |      USA        EGY   2006   1738.9366   888.40001   4233436.1    25694.89   70412.13 |
     |---------------------------------------------------------------------------------------|
  9. |      USA        FRA   1986   7209.9066    8971.946   2095390.4   331483.53   10737.68 |
 10. |      JOR        FRA   1986   12.256044   87.821788   4103.5102   331483.53    1263761 |
 11. |      FRA        FRA   1986   331483.53   331483.53   331483.53   331483.53          1 |
 12. |      EGY        FRA   1986   64.216951   712.93635   21513.732   331483.53   155767.7 |
     |---------------------------------------------------------------------------------------|
 13. |      JOR        FRA   2006   8.1014198   345.77377   24843.786   685143.92    6076401 |
 14. |      EGY        FRA   2006   549.17176   916.53636    25694.89   685143.92   34976.04 |
 15. |      FRA        FRA   2006   685143.92   685143.92   685143.92   685143.92          1 |
 16. |      USA        FRA   2006   25712.636   32968.143   4233436.1   685143.92   3421.635 |
     |---------------------------------------------------------------------------------------|
 17. |      JOR        JOR   1986   4103.5102   4103.5102   4103.5102   4103.5102          1 |
 18. |      EGY        JOR   1986   8.1022579   12.781617   21513.732   4103.5102   852470.6 |
 19. |      USA        JOR   1986   161.66331   5.2159291   2095390.4   4103.5102   1.02e+07 |
 20. |      FRA        JOR   1986   87.821788   12.256044   331483.53   4103.5102    1263761 |
     |---------------------------------------------------------------------------------------|
 21. |      JOR        JOR   2006   24843.786   24843.786   24843.786   24843.786          1 |
 22. |      USA        JOR   2006   449.26246   1066.4934   4233436.1   24843.786   219509.1 |
 23. |      FRA        JOR   2006   345.77377   8.1014198   685143.92   24843.786    6076401 |
 24. |      EGY        JOR   2006   168.16246   50.585541    25694.89   24843.786    75042.8 |
     |---------------------------------------------------------------------------------------|
 25. |      USA        USA   1986   2095390.4   2095390.4   2095390.4   2095390.4          1 |
 26. |      FRA        USA   1986    8971.946   7209.9066   331483.53   2095390.4   10737.68 |
 27. |      JOR        USA   1986   5.2159291   161.66331   4103.5102   2095390.4   1.02e+07 |
 28. |      EGY        USA   1986   36.602029   856.12212   21513.732   2095390.4    1438599 |
     |---------------------------------------------------------------------------------------|
 29. |      USA        USA   2006   4233436.1   4233436.1   4233436.1   4233436.1          1 |
 30. |      EGY        USA   2006   888.40001   1738.9366    25694.89   4233436.1   70412.13 |
 31. |      FRA        USA   2006   32968.143   25712.636   685143.92   4233436.1   3421.635 |
 32. |      JOR        USA   2006   1066.4934   449.26246   24843.786   4233436.1   219509.1 |
     +---------------------------------------------------------------------------------------+
    • 1 like

    Comment

    Previous Next
    Working...
    X