How to do the arithmetic operation within a group

[Jenna Kerry]

Hello All,

Currently, I am doing a data analysis, where I am required to calculate the wives' earnings relative to their husbands' earnings. I am showing my data set in simple form:

 list, sepby(Family)

     +------------------------------------------------------------+
     | Family   Sex   Edu   expense   income   wife_inc   hus_inc |
     |------------------------------------------------------------|
  1. |      1     1     2       100       20          .        20 |
  2. |      1     2     5       100       25         25         . |
     |------------------------------------------------------------|
  3. |      2     1     4       200       40          .        40 |
  4. |      2     2     1       200       65         65         . |
     |------------------------------------------------------------|
  5. |      3     1     0       300       60          .        60 |
  6. |      3     2     5       300      100        100         . |
     |------------------------------------------------------------|
  7. |      4     1     4       150       35          .        35 |
  8. |      4     2     3       150       25         25         . |
     |------------------------------------------------------------|
  9. |      5     1     2       650      110          .       110 |
 10. |      5     2     1       650      220        220         . |
     |------------------------------------------------------------|
 11. |      6     1     1       789      150          .       150 |
 12. |      6     2     2       789      300        300         . |
     |------------------------------------------------------------|
 13. |      7     1     3        78      200          .       200 |
 14. |      7     2     4        78        0          0         . |
     |------------------------------------------------------------|
 15. |      8     1     5        65      300          .       300 |
 16. |      8     2     6        65       15         15         . |
     |------------------------------------------------------------|
 17. |      9     1     5        25       90          .        90 |
 18. |      9     2     4        25        0          0         . |
     |------------------------------------------------------------|
 19. |     10     1     3        10      100          .       100 |
 20. |     10     2     2        10        0          0         . |
     +------------------------------------------------------------+

.

Now for every family, I want calculate wife_inc/hus_inc.

How can I do this for a more extensive data set?


Thank you very much!

[Clyde Schechter]

by Family (Sex), sort: gen income_ratio = income[_N]/income[1]

Note: Because the example data was not shown in a way that could be imported to Stata for use, this code is not tested.

In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Nick Cox]

This works for your dataset example, or at least for the dataset example produced from your listing.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(Family Sex Edu) int(expense income wife_inc hus_inc)
 1 1 2 100  20   .  20
 1 2 5 100  25  25   .
 2 1 4 200  40   .  40
 2 2 1 200  65  65   .
 3 1 0 300  60   .  60
 3 2 5 300 100 100   .
 4 1 4 150  35   .  35
 4 2 3 150  25  25   .
 5 1 2 650 110   . 110
 5 2 1 650 220 220   .
 6 1 1 789 150   . 150
 6 2 2 789 300 300   .
 7 1 3  78 200   . 200
 7 2 4  78   0   0   .
 8 1 5  65 300   . 300
 8 2 6  65  15  15   .
 9 1 5  25  90   .  90
 9 2 4  25   0   0   .
10 1 3  10 100   . 100
10 2 2  10   0   0   .
end

bysort Family (Sex) : gen wanted = income[2]/income[1] if Sex[1] == 1 & Sex[2] == 2

tabdisp Family, c(wanted)

----------------------
   Family |     wanted
----------+-----------
        1 |       1.25
        2 |      1.625
        3 |   1.666667
        4 |   .7142857
        5 |          2
        6 |          2
        7 |          0
        8 |        .05
        9 |          0
       10 |          0
----------------------

Note that the ratio is indeterminate if the husband's income is zero.

[Jenna Kerry]

My sincere thanks go out to you for your response and help! It worked both ways and I learned a lot!