Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • egen mean

    Hi folks,

    I am having trouble figuring out how to run the following (I think egen mean should be able to do it):
    year orig dest contig invest
    1 A B 1 11
    1 A C 0 12
    1 A D 1 13
    1 B A 1 14
    1 B C 1 15
    1 B D 0 16
    1 C A 0 17
    1 C B 1 18
    1 C D 1 19
    1 D A 1 20
    1 D B 0 21
    1 D C 1 22
    2 A B 1 61
    2 A C 0 62
    2 A D 1 63
    2 B A 1 64
    2 B C 1 65
    2 B D 0 66
    2 C A 0 67
    2 C B 1 68
    2 C D 1 69
    2 D A 1 70
    2 D B 0 71
    2 D C 1 72
    I am trying to generate a variable IV which will equal to the mean, by year, of invest for dest countries that are contig with a given orig country. For example, the first 3 observations are for year 1 and orig country A. Since A is contig with B and D (but not C), I would like the new variable IV to be the mean of 14 and 20 for the first 3 observations. Another example: observations 4-6 are for year 1 and orig country B. Since B is contig with A and C (but not D), I would like the new variable IV to be the mean of 11 and 18 for observations 4-6.
    Any help would be greatly appreciated.
    Thank you.

  • #2
    Consider the following:

    Code:
    clear
    
    input byte year    str1(orig    dest)    byte contig    int invest
    1    A    B    1    11
    1    A    C    0    12
    1    A    D    1    13
    1    B    A    1    14
    1    B    C    1    15
    1    B    D    0    16
    1    C    A    0    17
    1    C    B    1    18
    1    C    D    1    19
    1    D    A    1    20
    1    D    B    0    21
    1    D    C    1    22
    2    A    B    1    61
    2    A    C    0    62
    2    A    D    1    63
    2    B    A    1    64
    2    B    C    1    65
    2    B    D    0    66
    2    C    A    0    67
    2    C    B    1    68
    2    C    D    1    69
    2    D    A    1    70
    2    D    B    0    71
    2    D    C    1    72
    end
    
    tempfile orig
    save `orig'
    
    rename (orig dest invest) (dest orig dest_invest)
    tempfile dest
    save `dest'
    
    use `orig', clear
    merge 1:1 year orig dest using `dest', assert(match) keepusing(dest_invest) nogen
    egen avg_invest = mean(cond(contig, dest_invest, .)), by(year orig)
    which produces:

    Code:
    . list, noobs sepby(orig)
    
      +------------------------------------------------------------+
      | year   orig   dest   contig   invest   dest_i~t   avg_in~t |
      |------------------------------------------------------------|
      |    1      A      B        1       11         14         17 |
      |    1      A      C        0       12         17         17 |
      |    1      A      D        1       13         20         17 |
      |------------------------------------------------------------|
      |    1      B      A        1       14         11       14.5 |
      |    1      B      C        1       15         18       14.5 |
      |    1      B      D        0       16         21       14.5 |
      |------------------------------------------------------------|
      |    1      C      A        0       17         12       18.5 |
      |    1      C      B        1       18         15       18.5 |
      |    1      C      D        1       19         22       18.5 |
      |------------------------------------------------------------|
      |    1      D      A        1       20         13         16 |
      |    1      D      B        0       21         16         16 |
      |    1      D      C        1       22         19         16 |
      |------------------------------------------------------------|
      |    2      A      B        1       61         64         67 |
      |    2      A      C        0       62         67         67 |
      |    2      A      D        1       63         70         67 |
      |------------------------------------------------------------|
      |    2      B      A        1       64         61       64.5 |
      |    2      B      C        1       65         68       64.5 |
      |    2      B      D        0       66         71       64.5 |
      |------------------------------------------------------------|
      |    2      C      A        0       67         62       68.5 |
      |    2      C      B        1       68         65       68.5 |
      |    2      C      D        1       69         72       68.5 |
      |------------------------------------------------------------|
      |    2      D      A        1       70         63         66 |
      |    2      D      B        0       71         66         66 |
      |    2      D      C        1       72         69         66 |
      +------------------------------------------------------------+

    Comment


    • #3
      Code:
      * Example generated by -dataex-. For more info, type help dataex
      clear
      input byte year str1(orig dest) byte(contig invest)
      1 "A" "B" 1 11
      1 "A" "C" 0 12
      1 "A" "D" 1 13
      1 "B" "A" 1 14
      1 "B" "C" 1 15
      1 "B" "D" 0 16
      1 "C" "A" 0 17
      1 "C" "B" 1 18
      1 "C" "D" 1 19
      1 "D" "A" 1 20
      1 "D" "B" 0 21
      1 "D" "C" 1 22
      2 "A" "B" 1 61
      2 "A" "C" 0 62
      2 "A" "D" 1 63
      2 "B" "A" 1 64
      2 "B" "C" 1 65
      2 "B" "D" 0 66
      2 "C" "A" 0 67
      2 "C" "B" 1 68
      2 "C" "D" 1 69
      2 "D" "A" 1 70
      2 "D" "B" 0 71
      2 "D" "C" 1 72
      end
      
      frame put year orig dest invest if contig, into(join)
      frlink 1:1 year orig dest, frame(join year dest orig)
      frget val =invest, from(join)
      bys year orig: egen wanted= mean(val)
      Res.:

      Code:
      . l, sepby(year orig)
      
           +------------------------------------------------------------+
           | year   orig   dest   contig   invest   join   val   wanted |
           |------------------------------------------------------------|
        1. |    1      A      B        1       11      1    14       17 |
        2. |    1      A      C        0       12      .     .       17 |
        3. |    1      A      D        1       13      2    20       17 |
           |------------------------------------------------------------|
        4. |    1      B      A        1       14      3    11     14.5 |
        5. |    1      B      C        1       15      4    18     14.5 |
        6. |    1      B      D        0       16      .     .     14.5 |
           |------------------------------------------------------------|
        7. |    1      C      A        0       17      .     .     18.5 |
        8. |    1      C      B        1       18      5    15     18.5 |
        9. |    1      C      D        1       19      6    22     18.5 |
           |------------------------------------------------------------|
       10. |    1      D      A        1       20      7    13       16 |
       11. |    1      D      B        0       21      .     .       16 |
       12. |    1      D      C        1       22      8    19       16 |
           |------------------------------------------------------------|
       13. |    2      A      B        1       61      9    64       67 |
       14. |    2      A      C        0       62      .     .       67 |
       15. |    2      A      D        1       63     10    70       67 |
           |------------------------------------------------------------|
       16. |    2      B      A        1       64     11    61     64.5 |
       17. |    2      B      C        1       65     12    68     64.5 |
       18. |    2      B      D        0       66      .     .     64.5 |
           |------------------------------------------------------------|
       19. |    2      C      A        0       67      .     .     68.5 |
       20. |    2      C      B        1       68     13    65     68.5 |
       21. |    2      C      D        1       69     14    72     68.5 |
           |------------------------------------------------------------|
       22. |    2      D      A        1       70     15    63       66 |
       23. |    2      D      B        0       71      .     .       66 |
       24. |    2      D      C        1       72     16    69       66 |
           +------------------------------------------------------------+
      Last edited by Andrew Musau; 07 Apr 2025, 12:53.

      Comment


      • #4
        Code:
        preserve
        by year dest, sort: egen wanted = mean(cond(contig, invest, .))
        clonevar link = dest
        keep link year wanted
        duplicates drop
        tempfile holding
        save `holding'
        
        restore
        clonevar link = orig
        merge m:1 link year using `holding'
        sort year orig
        I wonder if I have understood your question properly. My code produces the answers you proposed for the first 6 observations. But what you are doing is calculating means of groups of observations having a common value of dest, and attributing those means to observations having that value of orig. That is legal to do, but it strikes me as very odd, and I'm having trouble imagining a use case for this.

        Added: Crossed with #2.

        Comment


        • #5
          Thank you, everyone.
          Clyde - I agree that this looks like an odd thing to do, at first glance. But the idea is to create an instrumental variable based on the procedure suggest in Cherif, R., Hasanov, F. and Wang, L., 2018. Sharp instrument: A stab at identifying the causes of economic growth. International Monetary Fund.

          Comment


          • #6
            Got it. Thanks.

            Comment

            Working...
            X