creating couple level data from individual level household data

[caleb gwaindepi]

Dear Stata users

i am relatively new to Stata and i am trying to create couple level data from individual household data.

* Example generated by -dataex-. For more info, type help dataex
clear
input long pid byte w5_a_gen long w5_a_parhpid byte(w5_a_mar w5_a_curmarst)
501102 1 301027 2 .
737559 1 301064 1 .
310050 1 301067 1 .
301077 2 301073 1 .
301073 1 301077 1 .
301255 1 301079 1 .
301081 2 301080 1 .
301703 2 301095 1 .
501197 1 301102 1 .
302664 1 301104 2 .
301106 1 301105 1 .
301105 2 301106 1 .
779302 2 301114 2 .
301125 2 301124 1 .
301124 1 301125 1 .
783563 2 301133 1 .
788116 1 301135 1 .
752883 1 301141 1 .
301147 1 301142 1 .
301139 2 301151 1 .
301167 1 301157 2 1
301158 2 301164 1 .
769677 2 301165 2 .
301157 2 301167 1 .
792726 1 301178 2 3
301128 2 301181 1 .
301858 2 301189 1 .
301154 2 301192 1 .
301198 2 301197 1 .
301197 1 301198 1 .
753451 1 301214 1 .
301248 2 301235 1 .
301239 1 301238 1 .
301238 2 301239 1 .
301245 1 301244 1 .
301244 2 301245 1 .
501555 1 301253 1 .
301079 2 301255 1 .
727959 1 301256 2 .
765344 2 301277 1 .
301961 1 301280 1 .
725984 2 301295 1 .
581845 1 301316 1 .
301318 2 301317 1 .
301317 1 301318 1 .
316676 1 301329 1 .
301337 1 301334 1 .
301334 2 301337 1 .
720668 2 301340 1 .
301352 1 301351 1 .
301351 2 301352 1 .
308500 2 301367 1 .
309813 1 301373 1 .
301673 2 301376 1 .
311222 2 301399 2 .
301434 2 301401 1 .
735013 2 301408 2 .
301413 2 301410 1 .
301410 1 301413 1 .
770643 2 301416 2 .
732272 2 301420 2 .
302076 1 301424 1 .
311317 1 301426 1 .
704387 1 301427 1 .
501185 1 301429 2 3
301401 1 301434 1 .
705636 2 301445 2 .
776724 2 301460 2 .
301465 2 301464 1 .
301468 2 301467 1 .
301467 1 301468 1 .
302123 1 301470 1 .
720688 2 301479 1 .
735024 2 301482 1 .
301478 2 301488 1 .
301491 1 301490 1 .
301490 2 301491 1 .
301496 1 301497 1 .
301498 2 301500 1 .
501506 1 301501 1 .
768010 2 301502 2 .
720224 1 301509 1 .
785195 1 301511 2 .
311806 1 301516 1 .
788620 2 301522 2 .
734856 1 301527 2 .
301537 2 301534 1 .
301534 1 301537 1 .
301530 2 301563 1 .
758967 2 301589 2 1
724051 2 301595 1 .
302218 2 301600 1 .
309085 1 301608 1 .
754940 2 301609 1 .
303011 1 301610 1 .
301636 2 301619 1 .
301634 1 301633 1 .
301633 2 301634 1 .
301619 1 301636 1 .
301650 2 301647 1 .
end
label values w5_a_gen w5_gen
label def w5_gen 1 "Male", modify
label def w5_gen 2 "Female", modify
label values w5_a_parhpid w5_parhpid
label values w5_a_mar w5_mar
label def w5_mar 1 "Formally married", modify
label def w5_mar 2 "Living together", modify
label values w5_a_curmarst w5_curmarst
label def w5_curmarst 1 "Married", modify
label def w5_curmarst 3 "Divorced or separated", modify
label var pid "Person identifier" 
label var w5_a_gen "b2 - Gender" 
label var w5_a_parhpid "b4_1 - Spouse or partner part of this HH" 
label var w5_a_mar "b4_2 - Formally married or living with a partner" 
label var w5_a_curmarst "b6_2 - Currently married, widowed, divorced or separated?"

[Nick Cox]

See https://journals.sagepub.com/doi/pdf...867X0800800414

* Example generated by -dataex-. For more info, type help dataex
clear
input long pid byte w5_a_gen long w5_a_parhpid byte(w5_a_mar w5_a_curmarst)
501102 1 301027 2 .
737559 1 301064 1 .
310050 1 301067 1 .
301077 2 301073 1 .
301073 1 301077 1 .
301255 1 301079 1 .
301081 2 301080 1 .
301703 2 301095 1 .
501197 1 301102 1 .
302664 1 301104 2 .
301106 1 301105 1 .
301105 2 301106 1 .
779302 2 301114 2 .
301125 2 301124 1 .
301124 1 301125 1 .
783563 2 301133 1 .
788116 1 301135 1 .
752883 1 301141 1 .
301147 1 301142 1 .
301139 2 301151 1 .
301167 1 301157 2 1
301158 2 301164 1 .
769677 2 301165 2 .
301157 2 301167 1 .
792726 1 301178 2 3
301128 2 301181 1 .
301858 2 301189 1 .
301154 2 301192 1 .
301198 2 301197 1 .
301197 1 301198 1 .
753451 1 301214 1 .
301248 2 301235 1 .
301239 1 301238 1 .
301238 2 301239 1 .
301245 1 301244 1 .
301244 2 301245 1 .
501555 1 301253 1 .
301079 2 301255 1 .
727959 1 301256 2 .
765344 2 301277 1 .
301961 1 301280 1 .
725984 2 301295 1 .
581845 1 301316 1 .
301318 2 301317 1 .
301317 1 301318 1 .
316676 1 301329 1 .
301337 1 301334 1 .
301334 2 301337 1 .
720668 2 301340 1 .
301352 1 301351 1 .
301351 2 301352 1 .
308500 2 301367 1 .
309813 1 301373 1 .
301673 2 301376 1 .
311222 2 301399 2 .
301434 2 301401 1 .
735013 2 301408 2 .
301413 2 301410 1 .
301410 1 301413 1 .
770643 2 301416 2 .
732272 2 301420 2 .
302076 1 301424 1 .
311317 1 301426 1 .
704387 1 301427 1 .
501185 1 301429 2 3
301401 1 301434 1 .
705636 2 301445 2 .
776724 2 301460 2 .
301465 2 301464 1 .
301468 2 301467 1 .
301467 1 301468 1 .
302123 1 301470 1 .
720688 2 301479 1 .
735024 2 301482 1 .
301478 2 301488 1 .
301491 1 301490 1 .
301490 2 301491 1 .
301496 1 301497 1 .
301498 2 301500 1 .
501506 1 301501 1 .
768010 2 301502 2 .
720224 1 301509 1 .
785195 1 301511 2 .
311806 1 301516 1 .
788620 2 301522 2 .
734856 1 301527 2 .
301537 2 301534 1 .
301534 1 301537 1 .
301530 2 301563 1 .
758967 2 301589 2 1
724051 2 301595 1 .
302218 2 301600 1 .
309085 1 301608 1 .
754940 2 301609 1 .
303011 1 301610 1 .
301636 2 301619 1 .
301634 1 301633 1 .
301633 2 301634 1 .
301619 1 301636 1 .
301650 2 301647 1 .
end
label values w5_a_gen w5_gen
label def w5_gen 1 "Male", modify
label def w5_gen 2 "Female", modify
label values w5_a_parhpid w5_parhpid
label values w5_a_mar w5_mar
label def w5_mar 1 "Formally married", modify
label def w5_mar 2 "Living together", modify
label values w5_a_curmarst w5_curmarst
label def w5_curmarst 1 "Married", modify
label def w5_curmarst 3 "Divorced or separated", modify
label var pid "Person identifier" 
label var w5_a_gen "b2 - Gender" 
label var w5_a_parhpid "b4_1 - Spouse or partner part of this HH" 
label var w5_a_mar "b4_2 - Formally married or living with a partner" 
label var w5_a_curmarst "b6_2 - Currently married, widowed, divorced or separated?"

gen first = cond(pid <  w5_a_parhpid, pid, w5_a_parhpid) 
gen second = cond(pid < w5_a_parhpid, w5_a_parhpid, pid) 

egen couples = group(first second), label 

sort couples pid

list, sepby(couples)

Note: labelling your couples will fail with a really big dataset. Omit the label option if so. That isn't fatal as the pair (first, second) or (*id) carries the information.

[caleb gwaindepi]

Thank you Nick