How to repeat values in a variable as per the subgroups in a variable?

[Amruta Bagwe]

I have Household data as follows. I want to create two variables- the proportion of girls below 12 and boys below 12 in the Household. When I use bysort and egen, I get the proportion as per household only for the selected female/male below 12; the rest is missing. But I want the proportions to be repeated for all the members of the same household and the households that don't have to be zero.

DATA:

HHID	memsex	age
1	F	11
1	M	8
1	M	37
1	F	34
2	F	5
2	F	6
2	M	11
2	M	40
2	F	38
3	M	13
3	F	40

My code and output I get:

bysort HHID memsex: egen girlsbelow12 = count(memsex) if age<12 & memsex==2
bysort HHID memsex: egen boysbelow12 = count(memsex) if age<12 & memsex==1

HHID	memsex	age	girlsbelow12	boysbelow12
1	F	11	1	.
1	M	8	.	1
1	M	37	.	.
1	F	34	.	.
2	F	5	2	.
2	F	6	2	.
2	M	11	.	1
2	M	40	.	.
2	F	38	.	.
3	M	13	.	.
3	F	40	.	.

What I want as an output:

HHID	memsex	age	girlsbelow12	boysbelow12
1	F	11	1	1
1	M	8	1	1
1	M	37	1	1
1	F	34	1	1
2	F	5	2	1
2	F	6	2	1
2	M	11	2	1
2	M	40	2	1
2	F	38	2	1
3	M	13	0	0
3	F	40	0	0

Can you please suggest the correct code?

[Malik Ehtisham]

bysort HHID: egen girlsunder12 = max(girlsbelow12)
replace girlsunder12 = 0 if missing(girlsunder12)

This might work for you. I checked and it worked for me. you can drop the variables girlsbelow12 and boysbelow12 later. I am a novice in stata and, therefore, don't know a more efficient way

[Nick Cox]

Please use dataex as requested in FAQ Advice #12. Here is a more direct solution. I don't see any advantage in adding memsex to the by: prefix, but go for it if it is what you want.

clear 
input HHID    memsex    age
1    2    11
1    1    8
1    1    37
1    2    34
2    2    5
2    2    6
2    1    11
2    1    40
2    2    38
3    1    13
3    2    40
end 

label def memsex 1 M 2 F
label val memsex memsex 

bysort HHID: egen girlsbelow12 = total(memsex == 2 & age <12)

[Amruta Bagwe]

Thank you, Nick and Malik. Both the methods worked.