generating edu level in hh data

[Shisho Jakas]

I would like to generate variable to figure out the education level of father and mother.
I do not know what is the best code for this.
The problem is the variable that show the relationship with household includes variety of relationship.
The problem is the relationsip is like household and spouse rather than father and mother
The variables are as follows and the values for rel_hh(relationship with household) and hig_edu(the level of highest education) are below.
I wanna generate variables like below
father's edu and mother's edu for each household or person if possible.
How can I deal with this?


clear
input long(hhid persid) byte(sex rel_hh hig_edu)
100101 10010104 1 4 6
100101 10010103 1 3 11
100101 10010105 1 5 0
100101 10010101 0 1 14
100101 10010102 1 2 13
100102 10010201 0 1 6
100102 10010203 0 3 17
100102 10010204 0 3 14
100102 10010202 1 2 3
100103 10010304 0 3 8
100103 10010303 0 3 14
100103 10010305 1 3 .
100103 10010301 0 1 8
100103 10010302 1 2 4
100104 10010404 0 8 .
100104 10010402 1 3 10
100104 10010403 0 10 14
100104 10010401 1 1 4
100105 10010506 1 3 10
100105 10010505 1 3 17
100105 10010504 1 3 7
100105 10010507 0 3 5
100105 10010502 1 2 6
100105 10010501 0 1 4
100105 10010503 1 3 7
100106 10010603 0 3 4
100106 10010604 0 3 2
100106 10010601 0 1 7
100106 10010602 1 2 7
100106 10010605 0 3 .
100107 10010703 0 3 9
100107 10010702 0 3 7
100107 10010701 1 1 3
100108 10010801 1 1 3
100108 10010802 0 3 3
100109 10010901 0 1 8
100109 10010904 0 3 7
100109 10010902 1 2 4
100109 10010905 1 3 6
100109 10010903 0 3 8
100109 10010906 1 3 0
100110 10011002 0 3 14
100110 10011001 0 1 3
100111 10011104 0 3 .
100111 10011102 1 2 11
100111 10011103 1 3 0
100111 10011101 0 1 14
100112 10011203 1 3 14
100112 10011205 0 3 3
100112 10011201 0 1 6
100112 10011202 1 2 10
100112 10011204 1 3 9
100201 10020105 1 8 .
100201 10020104 0 10 9
100201 10020102 1 2 8
100201 10020101 0 1 8
100201 10020103 1 3 11
100202 10020203 0 8 10
100202 10020201 1 1 .
100202 10020202 0 3 .
100202 10020204 1 8 6
100203 10020305 0 8 4
100203 10020302 1 2 .
100203 10020301 0 1 4
100203 10020306 0 8 88
100203 10020307 0 8 .
100203 10020304 1 3 13
100203 10020303 0 3 6
100204 10020403 1 3 3
100204 10020402 1 2 4
100204 10020404 1 3 6
100204 10020401 0 1 3
100205 10020502 0 2 .
100205 10020508 1 8 .
100205 10020507 0 8 .
100205 10020505 1 3 5
100205 10020506 0 10 .
100205 10020501 1 1 .
100205 10020503 0 3 7
100205 10020504 1 3 .
100206 10020605 1 3 .
100206 10020602 1 2 6
100206 10020603 0 3 10
100206 10020601 0 1 8
100206 10020604 0 3 6
100207 10020701 0 1 .
100207 10020702 1 2 7
100208 10020802 1 3 6
100208 10020801 0 1 8
100209 10020903 0 3 7
100209 10020902 1 3 5
100209 10020901 1 1 7
100210 10021005 0 3 7
100210 10021003 0 3 9
100210 10021002 1 2 .
100210 10021006 1 8 .
100210 10021004 0 3 9
100210 10021001 0 1 .
100211 10021102 0 7 .
100211 10021103 0 8 7
end
[/CODE]

1	Head	3840	22.3%
2	Spouse	3046	17.7%
3	Son/Daughter	7815	45.4%
4	Stepchild	39	0.2%
5	Adopted child/Foster child	16	0.1%
6	Parent	109	0.6%
7	Sibling	130	0.8%
8	Grand child	1186	6.9%
9	Nephew/Niece	112	0.7%
10	Son/Daughter-in-law	569	3.3%
11	Brother/Sister-in-law	86	0.5%
12	Parent-in-law	161	0.9%
13	Other relatives	95	0.6%
14	Servant	4	0.0%
15	Other non-relative including boarder	17	0.1%

CATEGORIES

0	Pre-school/Kindergarten	238	1.7%
1	Class 1 completed	630	4.6%
2	Class 2 completed	972	7.1%
3	Class 3 completed	1352	9.8%
4	Class 4 completed	1516	11.0%
5	Class 5 completed	1530	11.1%
6	Class 6 completed	1229	8.9%
7	Class 7 completed	1350	9.8%
8	Class 8 completed	1060	7.7%
9	Class 9 completed	717	5.2%
10	Class 10 completed	577	4.2%
11	Class 11 completed	425	3.1%
12	Class 1 2 completed	292	2.1%
13	Lower education certificate (diploma)	382	2.8%
14	Higher education certificate (BacII)	703	5.1%
15	Technical/vocational pre-secondary diploma/certificate	50	0.4%
16	Technical/vocational post-secondary diploma/certificate	30	0.2%
17	College/university undergraduate	47	0.3%
18	Bachelor degree (B.A., BSc, etc.)	377	2.7%
19	Masters degree (M.A., MSc, etc)	15	0.1%
20	Doctorate degree (PhD)	5	0.0%
21	Other	0	0.0%
88	No class completed	290	2.1%
98	Don´t know	0
Sysmiss		2967

[Maarten Buis]

clear
input long(hhid persid) byte(sex rel_hh hig_edu)
100101 10010104 1 4 6
100101 10010103 1 3 11
100101 10010105 1 5 0
100101 10010101 0 1 14
100101 10010102 1 2 13
100102 10010201 0 1 6
100102 10010203 0 3 17
100102 10010204 0 3 14
100102 10010202 1 2 3
100103 10010304 0 3 8
100103 10010303 0 3 14
100103 10010305 1 3 .
100103 10010301 0 1 8
100103 10010302 1 2 4
100104 10010404 0 8 .
100104 10010402 1 3 10
100104 10010403 0 10 14
100104 10010401 1 1 4
100105 10010506 1 3 10
100105 10010505 1 3 17
100105 10010504 1 3 7
100105 10010507 0 3 5
100105 10010502 1 2 6
100105 10010501 0 1 4
100105 10010503 1 3 7
100106 10010603 0 3 4
100106 10010604 0 3 2
100106 10010601 0 1 7
100106 10010602 1 2 7
100106 10010605 0 3 .
100107 10010703 0 3 9
100107 10010702 0 3 7
100107 10010701 1 1 3
100108 10010801 1 1 3
100108 10010802 0 3 3
100109 10010901 0 1 8
100109 10010904 0 3 7
100109 10010902 1 2 4
100109 10010905 1 3 6
100109 10010903 0 3 8
100109 10010906 1 3 0
100110 10011002 0 3 14
100110 10011001 0 1 3
100111 10011104 0 3 .
100111 10011102 1 2 11
100111 10011103 1 3 0
100111 10011101 0 1 14
100112 10011203 1 3 14
100112 10011205 0 3 3
100112 10011201 0 1 6
100112 10011202 1 2 10
100112 10011204 1 3 9
100201 10020105 1 8 .
100201 10020104 0 10 9
100201 10020102 1 2 8
100201 10020101 0 1 8
100201 10020103 1 3 11
100202 10020203 0 8 10
100202 10020201 1 1 .
100202 10020202 0 3 .
100202 10020204 1 8 6
100203 10020305 0 8 4
100203 10020302 1 2 .
100203 10020301 0 1 4
100203 10020306 0 8 88
100203 10020307 0 8 .
100203 10020304 1 3 13
100203 10020303 0 3 6
100204 10020403 1 3 3
100204 10020402 1 2 4
100204 10020404 1 3 6
100204 10020401 0 1 3
100205 10020502 0 2 .
100205 10020508 1 8 .
100205 10020507 0 8 .
100205 10020505 1 3 5
100205 10020506 0 10 .
100205 10020501 1 1 .
100205 10020503 0 3 7
100205 10020504 1 3 .
100206 10020605 1 3 .
100206 10020602 1 2 6
100206 10020603 0 3 10
100206 10020601 0 1 8
100206 10020604 0 3 6
100207 10020701 0 1 .
100207 10020702 1 2 7
100208 10020802 1 3 6
100208 10020801 0 1 8
100209 10020903 0 3 7
100209 10020902 1 3 5
100209 10020901 1 1 7
100210 10021005 0 3 7
100210 10021003 0 3 9
100210 10021002 1 2 .
100210 10021006 1 8 .
100210 10021004 0 3 9
100210 10021001 0 1 .
100211 10021102 0 7 .
100211 10021103 0 8 7
end

// I'll assume sex=1 means female
gen feduc2 = hig_edu if rel_hh==1 & sex == 0
replace feduc2 = hig_edu if rel_hh==2 & sex == 0
bys hhid : egen feduc = max(feduc2)
replace feduc = .a if !inlist(rel_hh, 3, 4, 5)
label var feduc "father's education"
drop feduc2

gen meduc2 = hig_edu if rel_hh==1 & sex == 1
replace meduc2 = hig_edu if rel_hh==2 & sex == 1
bys hhid : egen meduc = max(meduc2)
replace meduc = .a if !inlist(rel_hh, 3, 4, 5)
label var meduc "mother's education"
drop meduc2

label define educ .a "not a child"
label value meduc feduc educ

[Romalpa Akzo]

Maarten's code does not cover all parents-child relations. For example: (1) child (rel_hh == 1 or 7) and parents (rel_hh==6) or (2) child (rel_hh == 2 or 11) and parents (rel_hh==12) . Thus, modification seems necessary.

[Maarten Buis]

I don't think so:
7 is not a child of 1, so that should not be included
6 is a parent of the household head. This is typically excluded, as you would get a very selective sample of adults, i.e. those adults that still live with their parents.
a similar argument applies to 12

Anyhow, this is up to Shisho to decide: (s)he should carefully look at which parents and kids (s)he wants to look at and make a informed decision on how selective the sample may become in her/his context.

[Romalpa Akzo]

Maarten, logically, I believe 1(Head) and 7(Sibblings) are childrens of 6(Parents). 2(Spouse) and 11(Brother/Sister in Law) are children of 12(Parents in Law).

Even if the data might contain very rare case of big family, this logic is still necessary to comply with in deciding the father or mother's education.

[Robert Picard]

Here's another way to get the education of the children of the household's head and spouse of (using Maarten's assumption that sex==0 targets males):

gen target = inlist(rel_hh, 1,2) & sex == 0
bysort hhid (target): gen fedu = hig_edu[_N] if target[_N] & inlist(rel_hh, 3,4,5)

replace target = inlist(rel_hh, 1,2) & sex == 1
bysort hhid (target): gen medu = hig_edu[_N] if target[_N] & inlist(rel_hh, 3,4,5)

[Maarten Buis]

Maarten, logically, I believe 1(Head) and 7(Sibblings) are childrens of 6(Parents). 2(Spouse) and 11(Brother/Sister in Law) are children of 12(Parents in Law).

That is true, but not how this type of data is used. If one asks for this type of data for father's and mother's education, then that typically means one studies minors who live with their parents. Adding adults who live with their parents would seriously bias your results, and should be avoided.

[Shisho Jakas]

Thank you guys all!
I will focus only on a child (between 5-17), though.
Thank you so much!