HH to individual level

[Eve Alexander]

Hello,
In the survey I'm using, I have information on time taken to collect water by women in a HH at the HH level.
However I need to bring that number to the individual women level in each household.

Suppose I count the eligible women (El_women) in the HH by.

 egen El_women=count(EWELIGIBLE), by( STATEID DISTID PSUID HHID HHSPLITID )

and WA6A is the daily walking time to water by women in a HH, the data is as follows.


[CODE]----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(STATEID DISTID HHID HHSPLITID WA6A) float DILsize double ID_PERSON
1 2   2 0   . 1 10201002009
1 2  16 0   . 1 10201016004
1 2  18 0   . 1 10201018005
1 2  30 1   . 1 10201030102
1 2  50 1   . 1 10201050102
1 2 160 1   . 1 10201160104
1 2 170 1   . 1 10201170102
1 2 180 1   . 1 10201180105
1 2 200 1   . 1 10201200104
1 2   6 0   . 1 10202006004
1 2   7 0   . 1 10202007004
1 2  14 0   . 1 10202014002
1 2  15 0   . 1 10202015002
1 2  17 0   . 1 10202017002
1 2  40 1   5 1 10202040102
1 2  60 1   . 1 10202060102
1 2  70 1 160 1 10202070104
1 2 150 1   5 1 10202150102
1 2 160 1  20 1 10202160102
1 2 170 1  10 1 10202170102
1 2 200 1  30 1 10202200102
1 2   6 0   . 1 10203006009
1 2  11 0   . 1 10203011002
1 2  14 0   . 1 10203014004
1 2  15 0   . 1 10203015003
1 2  19 0   . 1 10203019004
1 2  50 1   . 1 10203050102
1 2  60 1   . 1 10203060109
1 2  70 1   . 1 10203070102
1 2 140 1   . 1 10203140104
1 2 160 1  20 1 10203160102
1 2 190 1   . 1 10203190102
1 2   1 0   . 1 10204001002
1 2   8 0   . 1 10204008004
1 2  17 0   . 1 10204017011
1 2  40 1  20 1 10204040102
1 2  90 1 120 1 10204090102
1 2 130 3   . 1 10204130302
1 2 150 1   . 1 10204150102
1 2   7 0   . 1 10205007004
1 2  10 1   . 1 10205010102
1 2  12 0   . 1 10205012003
1 2  15 0   . 1 10205015002
1 2  20 1   . 1 10205020104
1 2  40 1   . 1 10205040104
1 2  70 1   . 1 10205070104
1 2 120 1   . 1 10205120103
1 2 150 1 150 1 10205150102
1 2 170 1  30 1 10205170102
1 2 180 1  30 1 10205180102
1 2 200 1   . 1 10205200104
1 2   3 0   . 1 10206003002
1 2   6 0   . 1 10206006002
1 2  12 0   . 1 10206012004
1 2  15 0   . 1 10206015004
1 2  30 1  15 1 10206030102
1 2  40 1   . 1 10206040102
1 2 110 1   . 1 10206110102
1 2 120 1   . 1 10206120104
1 2 150 1   . 1 10206150104
1 2   1 0   . 1 10207001002
1 2   2 0   . 1 10207002002
1 2   5 0   . 1 10207005002
1 2   6 0   . 1 10207006004
1 2  10 1   . 1 10207010102
1 2  60 1   . 1 10207060104
1 2  70 1  20 1 10207070102
1 2 100 1   . 1 10207100102
1 2 110 1   . 1 10207110102
1 2 120 1   . 1 10207120102
1 2  12 0   . 1 10208012002
1 2  30 1   . 1 10208030103
1 2  60 1   . 1 10208060102
1 2  70 1   5 1 10208070102
1 2  90 1   . 1 10208090102
1 2 120 1   . 1 10208120102
1 2 130 1  20 1 10208130102
1 2 140 1   . 1 10208140102
1 3  13 0   . 1 10301013004
1 3  20 1   . 1 10301020102
1 3  40 1  60 1 10301040102
1 3  50 1   . 1 10301050103
1 3  90 1  60 1 10301090104
1 3   1 0   . 1 10302001004
1 3   5 0   . 1 10302005004
1 3  10 1   . 1 10302010106
1 3  14 0   . 1 10302014002
1 3  20 1   . 1 10302020102
1 3  70 1   . 1 10302070102
1 3 130 1   . 1 10302130102
1 3 140 1   . 1 10302140102
1 3  11 0   . 1 10303011004
1 3  15 0   . 1 10303015004
1 3 110 1   . 1 10303110104
1 3 150 1   . 1 10303150102
1 3   9 0   . 1 10304009002
1 3  14 0   . 1 10304014002
1 3  80 1   . 1 10304080102
1 3 110 1   . 1 10304110102
1 3 120 1   . 1 10304120102
end
label values STATEID STATEID
label def STATEID 1 "Jammu & Kashmir 01", modify

------------------ copy up to and including the previous line ------------------

Suppose if I tab El_women I get 1 and 2
so maximum there are two eligible women in the household.

How do I construct a variable such that time taken to collect water at the HH level is replicated for all the ID_person( which is the ID for the eligible women).
And for HH with two women maybe I can avg the total time.
How will I be able to do that?

[Nick Cox]

count() doesn't do what you may think it does. This came up only yesterday. count() counts non-missing values and for example 0 counts as non-missing.

count() is often used when people need total(). See #2 at https://www.statalist.org/forums/for...ther-variables

EWELIGIBLE is not in your dataset. Suppose it is an indicator 1 or 0. At its simplest what you want is something like

egen total = total(WA6A), bySTATEID DISTID PSUID HHID HHSPLITID) 

egen  count = total(EWELIGIBLE == 1), bySTATEID DISTID PSUID HHID HHSPLITID)  

gen wanted = total / count if EWELIGIBLE == 1

[Eve Alexander]

Thank you very much for your help.