Mean difference in the same variable across different people and years

[Filippo Colonna]

I am trying to create inequity aversion variables in Stata. I have a panel dataset comprised of 10,000 individuals over 8 years. Say that w_i is the individual's wage and w_k is everyone else's wages in the person's comparison group (people in the same comparison group are the same age and gender), what I need is a variable (let's call it "upward comparison") equal to: [(1/n-1)Summation (w_k-w_i)] if w_k>w_i ("upward" because the person his comparing his/her wage to people with higher wages) and another variable (called "downward comparison") equal to [(1/n-1)Summation (w_i-w_k)] if w_k<w_i. I was thinking something along the lines of bysort year age gender personID: egen upward=mean(income-income[_n-1]) if income[_n-1]>income but it doesn't seem to work.

[Nick Cox]

We don't see any sample data here and n is undefined.

I define the mean of those above as, more precisely, the mean of those at or above a value, because otherwise the measure is undefined for the highest value and I define the mean of those below correspondingly for the corresponding reason.

Here's an analogue with the auto data. If I understand this correctly then such code gets you most of the way there. You just need to subtract the original variable and apply signs of choice.

sysuse auto, clear

bysort foreign (mpg) : gen sum1 = sum(mpg)
by foreign : gen n1 = sum(mpg < .)

* fix ties
bysort foreign mpg (sum1) : replace sum1 = sum1[_N]
bysort foreign mpg (n1) : replace n1 = n1[_N]

gen negmpg = -mpg
bysort foreign (negmpg) : gen sum2 = sum(mpg)
by foreign : gen n2 = sum(mpg < .)

* fix ties
bysort foreign mpg (sum2) : replace sum2 = sum2[_N]
bysort foreign mpg (n2) : replace n2 = n2[_N]

gen belowmean = sum1 / n1
gen abovemean = sum2 / n2

list foreign mpg sum? n? *mean, sepby(foreign)

     +--------------------------------------------------------------+
     |  foreign   mpg   sum1   sum2   n1   n2   belowm~n   abovem~n |
     |--------------------------------------------------------------|
  1. | Domestic    12     24   1031    2   52         12   19.82692 |
  2. | Domestic    12     24   1031    2   52         12   19.82692 |
  3. | Domestic    14     94   1007    7   50   13.42857      20.14 |
  4. | Domestic    14     94   1007    7   50   13.42857      20.14 |
  5. | Domestic    14     94   1007    7   50   13.42857      20.14 |
  6. | Domestic    14     94   1007    7   50   13.42857      20.14 |
  7. | Domestic    14     94   1007    7   50   13.42857      20.14 |
  8. | Domestic    15    124    937    9   45   13.77778   20.82222 |
  9. | Domestic    15    124    937    9   45   13.77778   20.82222 |
 10. | Domestic    16    188    907   13   43   14.46154   21.09302 |
 11. | Domestic    16    188    907   13   43   14.46154   21.09302 |
 12. | Domestic    16    188    907   13   43   14.46154   21.09302 |
 13. | Domestic    16    188    907   13   43   14.46154   21.09302 |
 14. | Domestic    17    222    843   15   39       14.8   21.61539 |
 15. | Domestic    17    222    843   15   39       14.8   21.61539 |
 16. | Domestic    18    348    809   22   37   15.81818   21.86486 |
 17. | Domestic    18    348    809   22   37   15.81818   21.86486 |
 18. | Domestic    18    348    809   22   37   15.81818   21.86486 |
 19. | Domestic    18    348    809   22   37   15.81818   21.86486 |
 20. | Domestic    18    348    809   22   37   15.81818   21.86486 |
 21. | Domestic    18    348    809   22   37   15.81818   21.86486 |
 22. | Domestic    18    348    809   22   37   15.81818   21.86486 |
 23. | Domestic    19    500    683   30   30   16.66667   22.76667 |
 24. | Domestic    19    500    683   30   30   16.66667   22.76667 |
 25. | Domestic    19    500    683   30   30   16.66667   22.76667 |
 26. | Domestic    19    500    683   30   30   16.66667   22.76667 |
 27. | Domestic    19    500    683   30   30   16.66667   22.76667 |
 28. | Domestic    19    500    683   30   30   16.66667   22.76667 |
 29. | Domestic    19    500    683   30   30   16.66667   22.76667 |
 30. | Domestic    19    500    683   30   30   16.66667   22.76667 |
 31. | Domestic    20    560    531   33   22    16.9697   24.13636 |
 32. | Domestic    20    560    531   33   22    16.9697   24.13636 |
 33. | Domestic    20    560    531   33   22    16.9697   24.13636 |
 34. | Domestic    21    623    471   36   19   17.30556   24.78947 |
 35. | Domestic    21    623    471   36   19   17.30556   24.78947 |
 36. | Domestic    21    623    471   36   19   17.30556   24.78947 |
 37. | Domestic    22    733    408   41   16   17.87805       25.5 |
 38. | Domestic    22    733    408   41   16   17.87805       25.5 |
 39. | Domestic    22    733    408   41   16   17.87805       25.5 |
 40. | Domestic    22    733    408   41   16   17.87805       25.5 |
 41. | Domestic    22    733    408   41   16   17.87805       25.5 |
 42. | Domestic    24    805    298   44   11   18.29545   27.09091 |
 43. | Domestic    24    805    298   44   11   18.29545   27.09091 |
 44. | Domestic    24    805    298   44   11   18.29545   27.09091 |
 45. | Domestic    25    830    226   45    8   18.44444      28.25 |
 46. | Domestic    26    882    201   47    7   18.76596   28.71428 |
 47. | Domestic    26    882    201   47    7   18.76596   28.71428 |
 48. | Domestic    28    938    149   49    5   19.14286       29.8 |
 49. | Domestic    28    938    149   49    5   19.14286       29.8 |
 50. | Domestic    29    967     93   50    3      19.34         31 |
 51. | Domestic    30    997     64   51    2   19.54902         32 |
 52. | Domestic    34   1031     34   52    1   19.82692         34 |
     |--------------------------------------------------------------|
 53. |  Foreign    14     14    545    1   22         14   24.77273 |
 54. |  Foreign    17     48    531    3   21         16   25.28572 |
 55. |  Foreign    17     48    531    3   21         16   25.28572 |
 56. |  Foreign    18     84    497    5   19       16.8   26.15789 |
 57. |  Foreign    18     84    497    5   19       16.8   26.15789 |
 58. |  Foreign    21    126    461    7   17         18   27.11765 |
 59. |  Foreign    21    126    461    7   17         18   27.11765 |
 60. |  Foreign    23    195    419   10   15       19.5   27.93333 |
 61. |  Foreign    23    195    419   10   15       19.5   27.93333 |
 62. |  Foreign    23    195    419   10   15       19.5   27.93333 |
 63. |  Foreign    24    219    350   11   12   19.90909   29.16667 |
 64. |  Foreign    25    319    326   15   11   21.26667   29.63636 |
 65. |  Foreign    25    319    326   15   11   21.26667   29.63636 |
 66. |  Foreign    25    319    326   15   11   21.26667   29.63636 |
 67. |  Foreign    25    319    326   15   11   21.26667   29.63636 |
 68. |  Foreign    26    345    226   16    7    21.5625   32.28571 |
 69. |  Foreign    28    373    200   17    6   21.94118   33.33333 |
 70. |  Foreign    30    403    172   18    5   22.38889       34.4 |
 71. |  Foreign    31    434    142   19    4   22.84211       35.5 |
 72. |  Foreign    35    504    111   21    3         24         37 |
 73. |  Foreign    35    504    111   21    3         24         37 |
 74. |  Foreign    41    545     41   22    1   24.77273         41 |
     +--------------------------------------------------------------+

You have a framework

bysort year age gender:

but personID does not belong with that group. Otherwise you will be reduced to groups of one observation each.

[Filippo Colonna]

YEAR	PID	INCOME	AGE
1	1	5	20
2	1	5	21
1	2	2	20
2	2	7	21
1	3	3	20
2	3	6	21
1	4	8	20
2	4	1	21

Thank you, Dr Cox. I can follow most of that code but I thought I'd be more specific given that, as you said, I didn't include any data (and I just want to make sure that I get the correct message across). This table should make it clear what I am trying to do. The dependent variable (not shown) is Y. The table shows my panel data set over 2 years with 4 individuals of the same age. What I am trying to do with Stata is to say that each person's Y depends on their own income and also on how it compares with others' incomes. Take person 1. In Year 1 his income is 5.

So Y= beta₁*5 +...

Next, he compares his income with the others. I would like to define two new variables, one being the mean of the difference between person 1's income and all incomes below his and the other being the mean of the difference of all incomes above Person 1's and Person 1's income. So in this example: PID2 has income 2 in Year 1 (<PID 1's income of 5) and PID3 has income 3 in Year 1 (<PID's 1 income). So the "downward comparison" variable would be given by [(5-2)+(5-3)]/2=2.5 (I divide by two because it's the mean that I'm after). PID 3 has income 8 in Year 1 (>PID 1's income of 5) so here the "upward comparison" variable would be (8-5)/1=3.

So overall, Y = beta₁ (5) + beta₂(2.5) + beta₃(3). I would then do the same thing for Year 2. I hope this makes the question clearer.

[Nick Cox]

Thanks for the extra information. I think I got the gist of what you want, so you should be able to tell from studying my code together with the reproducible example that I have given whether it really is what you want.

As I said, I don't subtract the original variable.

[Filippo Colonna]

So does Income in my model correspond to mpg in the example you provided?

[Nick Cox]

Naturally.

[Filippo Colonna]

Thank you very much!