generate new variable by matching information across rows

[Romil Pandey]

I have a dataset in which observations are uniquely identified by FPrimary (household), wave (survey year), hhmid (household member id).

I want to generate a new variable called dowrygiven_husband such that - If person is female, then dowrygiven_husband should equal dowrygiven reported by her spouse.
The idea is to capture in one observation the dowry info reported by the husband and the wife.

eg, for FPrimary = 101001002, wave = 1: dowrygiven_husband[hhmid = 2] should equal dowrygiven[hhmid = 1] since 1 and 2 are spouses.
Notes: It is possible that a woman does not have a spouse reported in the dataset, so dowrygiven_husband can take a missing value.

I am having trouble coding this.

[Clyde Schechter]

Please read the Forum FAQ for advice about best practices to maximize your chance of getting a timely and helpful response. There you will learn, among other things, that screenshots are discouraged. They are often not readable (as is the case with yours, at least on my setup) and even when readable they are not really helpful to those who want to help you because they do not convey metadata and they cannot be imported into Stata to work with. The helpful way to show example data is with the -dataex- command. 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.

In addition, when you post back with a -dataex- generated example, please explain how you can tell who is the spouse of whom. And also, if you are not using the current version of Stata (17) state what version you are using: there are different approaches to this problem, but the best ones are not available in some earlier versions.

[Romil Pandey]

Thank you for pointing it out.

So there is a variable for spouseid which gives the hhmid of the spouse.

I am using Stata 17.0

* Example generated by -dataex-. For more info, type help dataex
clear
input str14 FPrimary float wave byte(hhmid spouseid gender) double(dowrygiven dowryreceived)
"1008308001" 3 1 . 1   .   .
"1009320001" 3 1 . 2   .  .d
"1009320001" 3 2 . 2   .   .
"1009320001" 3 3 . 1   .   .
"101001002"  1 1 2 1 300  15
"101001002"  1 2 1 2   5  50
"101001002"  1 3 . 2   .   .
"101001002"  1 4 . 2   .   .
"101001002"  1 5 . 1   .   .
"101001002"  2 1 . 1  .d  .d
"101001002"  2 2 . 2   .  .d
"101001002"  2 5 . 1   .   .
"101001002"  2 6 . 1   .   .
"101001002"  3 1 2 1   .   .
"101001002"  3 2 1 2   .   .
"101001002"  3 5 . 1   .   .
"101001002"  3 6 . 1   .   .
"101001002"  3 7 . 1   .   .
"101001003"  1 1 . 1  50   .
"101001003"  2 1 . 1  20   .
"101001003"  2 2 . 2   .  20
"101001003"  2 3 . 1   .   .
"101001003"  2 4 . 1   .   .
"101001003"  3 1 2 1 700   .
"101001003"  3 2 1 2   . 800
"101001003"  3 3 . 1   .   .
"101001003"  3 4 . 1   .   .
"101001003"  3 5 . 2   .   .
"101001004"  1 1 . 1   .   .
"101001004"  2 1 . 1   .   .
"101001004"  2 2 . 2   .   .
"101001004"  2 3 . 2   .   .
end
label values gender gender
label def gender 1 "Male", modify
label def gender 2 "Female", modify

[Clyde Schechter]

Thank you.

isid FPrimary wave hhmid, sort
frame put FPrimary wave hhmid dowrygiven ///
    if !missing(spouseid) & gender == "Male":gender, ///
    into(husbands)
    
frlink m:1 FPrimary wave spouseid, frame(husbands FPrimary wave hhmid)
frget dowrygiven_husband = dowrygiven, from(husbands)
drop husbands
frame drop husbands

[Romil Pandey]

That worked perfectly, thank you!

[Nick Cox]

Here is another way to do it using rangestat from SSC:

* Example generated by -dataex-. For more info, type help dataex
clear
input str14 FPrimary float wave byte(hhmid spouseid gender) double(dowrygiven dowryreceived)
"1008308001" 3 1 . 1   .   .
"1009320001" 3 1 . 2   .  .d
"1009320001" 3 2 . 2   .   .
"1009320001" 3 3 . 1   .   .
"101001002"  1 1 2 1 300  15
"101001002"  1 2 1 2   5  50
"101001002"  1 3 . 2   .   .
"101001002"  1 4 . 2   .   .
"101001002"  1 5 . 1   .   .
"101001002"  2 1 . 1  .d  .d
"101001002"  2 2 . 2   .  .d
"101001002"  2 5 . 1   .   .
"101001002"  2 6 . 1   .   .
"101001002"  3 1 2 1   .   .
"101001002"  3 2 1 2   .   .
"101001002"  3 5 . 1   .   .
"101001002"  3 6 . 1   .   .
"101001002"  3 7 . 1   .   .
"101001003"  1 1 . 1  50   .
"101001003"  2 1 . 1  20   .
"101001003"  2 2 . 2   .  20
"101001003"  2 3 . 1   .   .
"101001003"  2 4 . 1   .   .
"101001003"  3 1 2 1 700   .
"101001003"  3 2 1 2   . 800
"101001003"  3 3 . 1   .   .
"101001003"  3 4 . 1   .   .
"101001003"  3 5 . 2   .   .
"101001004"  1 1 . 1   .   .
"101001004"  2 1 . 1   .   .
"101001004"  2 2 . 2   .   .
"101001004"  2 3 . 2   .   .
end
label values gender gender
label def gender 1 "Male", modify
label def gender 2 "Female", modify

* this post 
gen spouseid2 = cond(missing(spouseid), 0, spouseid)
rangestat wanted=dowrygiven, int(hhmid spouseid2 spouseid2) by(FPrimary wave)
replace wanted = . if gender == 1 

* Clyde's method 
isid FPrimary wave hhmid, sort
frame put FPrimary wave hhmid dowrygiven ///
    if !missing(spouseid) & gender == "Male":gender, ///
    into(husbands)
    
frlink m:1 FPrimary wave spouseid, frame(husbands FPrimary wave hhmid)
frget dowrygiven_husband = dowrygiven, from(husbands)
drop husbands
frame drop husbands

* compare results 
list FPrimary wave hhmid spouseid gender dowrygiven dowryreceived  wanted dowrygiven_husband, sepby(FPrimary wave)

    +-----------------------------------------------------------------------------------------+
     |   FPrimary   wave   hhmid   spouseid   gender   dowryg~n   dowryr~d   wanted   dowryg~d |
     |-----------------------------------------------------------------------------------------|
  1. | 1008308001      3       1          .     Male          .          .        .          . |
     |-----------------------------------------------------------------------------------------|
  2. | 1009320001      3       1          .   Female          .         .d        .          . |
  3. | 1009320001      3       2          .   Female          .          .        .          . |
  4. | 1009320001      3       3          .     Male          .          .        .          . |
     |-----------------------------------------------------------------------------------------|
  5. |  101001002      1       1          2     Male        300         15        .          . |
  6. |  101001002      1       2          1   Female          5         50      300        300 |
  7. |  101001002      1       3          .   Female          .          .        .          . |
  8. |  101001002      1       4          .   Female          .          .        .          . |
  9. |  101001002      1       5          .     Male          .          .        .          . |
     |-----------------------------------------------------------------------------------------|
 10. |  101001002      2       1          .     Male         .d         .d        .          . |
 11. |  101001002      2       2          .   Female          .         .d        .          . |
 12. |  101001002      2       5          .     Male          .          .        .          . |
 13. |  101001002      2       6          .     Male          .          .        .          . |
     |-----------------------------------------------------------------------------------------|
 14. |  101001002      3       1          2     Male          .          .        .          . |
 15. |  101001002      3       2          1   Female          .          .        .          . |
 16. |  101001002      3       5          .     Male          .          .        .          . |
 17. |  101001002      3       6          .     Male          .          .        .          . |
 18. |  101001002      3       7          .     Male          .          .        .          . |
     |-----------------------------------------------------------------------------------------|
 19. |  101001003      1       1          .     Male         50          .        .          . |
     |-----------------------------------------------------------------------------------------|
 20. |  101001003      2       1          .     Male         20          .        .          . |
 21. |  101001003      2       2          .   Female          .         20        .          . |
 22. |  101001003      2       3          .     Male          .          .        .          . |
 23. |  101001003      2       4          .     Male          .          .        .          . |
     |-----------------------------------------------------------------------------------------|
 24. |  101001003      3       1          2     Male        700          .        .          . |
 25. |  101001003      3       2          1   Female          .        800      700        700 |
 26. |  101001003      3       3          .     Male          .          .        .          . |
 27. |  101001003      3       4          .     Male          .          .        .          . |
 28. |  101001003      3       5          .   Female          .          .        .          . |
     |-----------------------------------------------------------------------------------------|
 29. |  101001004      1       1          .     Male          .          .        .          . |
     |-----------------------------------------------------------------------------------------|
 30. |  101001004      2       1          .     Male          .          .        .          . |
 31. |  101001004      2       2          .   Female          .          .        .          . |
 32. |  101001004      2       3          .   Female          .          .        .          . |
     +-----------------------------------------------------------------------------------------+