Dear Statalisters,

This is my first question, I apologize if it is not formatted correctly. I am struggling with something that is likely relatively simple for those with experience with commands such as by, sort, egen.

I have a data set of households and for each "head" of household, I want to generate a value for their children's income. In the example posted below, for the variable 'Relationship', 1= head of household, 3 = one of the head of household's children.

Essentially, I would love some coding advice on how to execute something like this: For each head of household, generate a value denoting their children's MonthlyUSD or an average value if there is more than one child per household.

Thank you kindly in advance! And please let me know if you require any other information.



Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input str7 hhid str9 persid int Age byte Relationship float MonthlyUSD
"0100101" "010010101" 77 1         .
"0100101" "010010103" 36 3         0
"0100102" "010010201" 38 1         0
"0100102" "010010203" 12 3         .
"0100102" "010010204" 11 3         .
"0100102" "010010205"  1 3         .
"0100103" "010010301" 50 1         0
"0100103" "010010303" 15 3 148.14815
"0100103" "010010304" 11 3         .
"0100104" "010010401" 73 1         .
"0100104" "010010402" 43 3         .
"0100105" "010010501" 42 1         0
"0100105" "010010502" 24 3 148.14815
"0100105" "010010503" 19 3         0
"0100105" "010010504" 14 3         .
"0100106" "010010601" 33 1 155.55556
"0100106" "010010603" 10 3         .
"0100106" "010010604"  1 3         .
"0100107" "010010701" 35 1  207.4074
"0100107" "010010703" 11 3         .
end
label values Relationship Q01AC06
label def Q01AC06 1 "Head", modify
label def Q01AC06 3 "Son/Daughter", modify