Is there any way to create age and education variables of a father?

[Matthew Williams]

Hi all,

I am working with the Multiple Indicator Cluster Survey (MICS) whose research design is similar to that of Demographic Health Survey (DHS). The MICS I am using collect general information on all household members (stored in a separate household data file), detailed information on women aged 15-49 (stored in a separate women data file) and children under 5 (stored in a separate children data file). Since the MICS does not collect detailed information on men, so I have to rely on the household file to generate age and education variables of children' fathers (or husbands). The tricky part is that the household file contains line number of fathers, line number of children under 5 and line number of mothers. I do not how to match these information to get what I want. Thus any help would be highly appreciated.

Please note that the household head is not necessary husbands of women aged 15-49, it could be their parents. There is no information on husbands In the women data file except age of husbands.

The household data file can be matched with the women data file by the following command: merge 1:1 hh1 hh2 ln

Thank you.

Household data file

clear
input int hh1 byte(hh2 ln rela sex MOB) int YOB byte(age line_w1549 line_c04 mom_alive line_mom dad_alive line_dad line_ctaker line_edu edu1 edu2 mline fline)
1  1 1  1 1  5 1964 49 0 0 . . . . . 1 3 12 . .
1  1 2  2 2  6 1963 50 0 0 . . . . . 2 3 12 . .
1  1 3  3 1 10 1998 15 0 0 1 2 1 1 . 3 3  0 2 1
1  2 1  1 2  9 1957 56 0 0 . . . . . 1 5  . . .
1  2 2  3 2 12 1986 27 2 0 . . . . . 2 5  . . .
1  2 3  4 1 11 1983 30 0 0 . . . . . 3 5  . . .
1  2 4  5 1  4 2011  2 0 4 1 2 1 3 2 4 .  . 2 3
1  2 5  5 1  5 2013  0 0 5 1 2 1 3 2 5 .  . 2 3
1  3 1  1 2 98 1943 70 0 0 . . . . . 1 1  2 . .
1  3 2  3 2  3 1978 35 2 0 . . . . . 2 3 12 . .
1  3 3  4 1  4 1975 38 0 0 . . . . . 3 3  0 . .
1  3 4  5 2  3 2003 10 0 0 1 2 1 3 2 4 1  4 2 3
1  3 5  5 1  6 2007  6 0 0 1 2 1 3 2 5 1  0 2 3
1  4 1  1 1 10 1963 50 0 0 . . . . . 1 5  . . .
1  4 2  2 2 12 1983 30 2 0 . . . . . 2 5  . . .
1  4 3  3 1 11 1991 22 0 0 . . . . . 3 5  . . .
1  4 4  3 2 11 2007  6 0 0 1 2 1 1 2 4 1  0 2 1
1  4 5 11 2 10 1991 22 5 0 . . . . . 5 5  . . .
1  5 1  1 1 11 1964 49 0 0 . . . . . 1 5  . . .
1  5 2  2 2 12 1962 51 0 0 . . . . . 2 3 12 . .
1  5 3 11 2  2 1989 24 3 0 . . . . . 3 5  . . .
1  5 4  3 2  4 1993 20 4 0 . . . . . 4 5  . . .
1  5 5 11 2  3 1995 18 5 0 . . . . . 5 5  . . .
1  6 1  1 1 11 1952 61 0 0 . . . . . 1 5  . . .
1  6 2  2 2  8 1965 48 2 0 . . . . . 2 5  . . .
1  6 3  3 1  3 1995 18 0 0 . . . . . 3 5  . . .
1  7 1  1 1  6 1966 47 0 0 . . . . . 1 5  . . .
1  7 2  2 2  1 1975 38 2 0 . . . . . 2 5  . . .
1  7 3  3 2 11 1996 17 3 0 1 2 1 1 . 3 3 11 2 1
1  7 4  3 1  9 1998 15 0 0 1 2 1 1 . 4 3  0 2 1
1  9 1  1 2  4 1928 85 0 0 . . . . . 1 2  9 . .
1  9 2  4 2 11 1971 42 2 0 . . . . . 2 3 12 . .
1  9 3  3 1 10 1969 44 0 0 . . . . . 3 4  . . .
1  9 4  5 1  6 1997 16 0 0 1 2 1 3 . 4 3 10 2 3
1  9 5  5 2  3 2002 11 0 0 1 2 1 3 2 5 2  0 2 3
1 10 1  1 1 12 1938 75 0 0 . . . . . 1 3 12 . .
1 10 2  2 2 10 1927 86 0 0 . . . . . 2 5  . . .
1 11 1  1 1 11 1940 73 0 0 . . . . . 1 5  . . .
1 11 2  2 2  8 1948 65 0 0 . . . . . 2 5  . . .
1 11 3  3 1  8 1971 42 0 0 . . . . . 3 5  . . .
1 11 4  4 2  2 1983 30 4 0 . . . . . 4 5  . . .
1 11 5  5 1  3 2011  2 0 5 1 4 1 3 4 5 .  . 4 3
1 12 1  1 2  6 1960 53 0 0 . . . . . 1 5  . . .
1 12 2  2 1  3 1953 60 0 0 . . . . . 2 5  . . .
1 12 3  3 2  5 1990 23 3 0 . . . . . 3 5  . . .
1 13 1  1 1  5 1952 61 0 0 . . . . . 1 5  . . .
1 13 2  2 2 12 1953 60 0 0 . . . . . 2 5  . . .
1 13 3  3 1 12 1982 31 0 0 . . . . . 3 5  . . .
1 13 4  4 2 10 1984 29 4 0 . . . . . 4 5  . . .
1 13 5  3 2  2 1985 28 5 0 . . . . . 5 5  . . .
1 14 1  1 1  5 1955 58 0 0 . . . . . 1 5  . . .
1 14 2  2 2  2 1959 54 0 0 . . . . . 2 3 12 . .
1 14 3  3 1 10 1981 32 0 0 . . . . . 3 5  . . .
1 14 4  4 2  8 1987 26 4 0 . . . . . 4 5  . . .
1 14 5  5 2  3 2012  1 0 5 1 4 1 3 4 5 .  . 4 3
1 14 6  3 1 10 1986 27 0 0 . . . . . 6 5  . . .
1 15 1  1 1 11 1983 30 0 0 . . . . . 1 5  . . .
1 15 2  2 2  1 1982 31 2 0 . . . . . 2 5  . . .
1 15 3  3 2  4 2011  2 0 3 1 2 1 1 2 3 .  . 2 1
1 15 4 96 1  2 1984 29 0 0 . . . . . 4 5  . . .
1 15 5 96 1 11 1985 28 0 0 . . . . . 5 4  . . .
1 15 6 12 2  7 1987 26 6 0 . . . . . 6 5  . . .
1 15 7 96 2  6 1991 22 7 0 . . . . . 7 5  . . .
1 16 1  1 1  4 1963 50 0 0 . . . . . 1 3 12 . .
1 16 2  2 2  9 1969 44 2 0 . . . . . 2 4  . . .
1 16 3  3 1  2 1992 21 0 0 . . . . . 3 5  . . .
1 17 1  1 1  4 1942 71 0 0 . . . . . 1 5  . . .
1 17 2  2 2  6 1943 70 0 0 . . . . . 2 5  . . .
1 17 3  3 1  4 1980 33 0 0 . . . . . 3 5  . . .
1 17 4  4 2  8 1985 28 4 0 . . . . . 4 5  . . .
1 18 1  1 1  7 1956 57 0 0 . . . . . 1 5  . . .
1 18 2  2 2  7 1956 57 0 0 . . . . . 2 5  . . .
1 18 3  3 1  1 1982 31 0 0 . . . . . 3 5  . . .
1 18 4  4 2  5 1983 30 4 0 . . . . . 4 5  . . .
1 18 5  5 1 10 2008  5 0 0 1 4 1 3 4 5 0  . 4 3
1 19 1  1 1  8 1956 57 0 0 . . . . . 1 5  . . .
1 19 2  2 2  5 1959 54 0 0 . . . . . 2 5  . . .
1 19 3  3 1  5 1987 26 0 0 . . . . . 3 5  . . .
1 19 4  3 1  6 1990 23 0 0 . . . . . 4 5  . . .
1 19 5  4 2  6 1990 23 5 0 . . . . . 5 5  . . .
1 19 6  5 2  6 2012  1 0 6 1 5 1 4 5 6 .  . 5 4
1 19 7  4 2  9 1994 19 7 0 . . . . . 7 5  . . .
1 20 1  1 1  6 1977 36 0 0 . . . . . 1 5  . . .
1 20 2  2 2 12 1977 36 2 0 . . . . . 2 5  . . .
1 20 3  3 2 12 2005  8 0 0 1 2 1 1 2 3 1  2 2 1
1 20 4  3 1  9 2007  6 0 0 1 2 1 1 2 4 1  0 2 1
2  1 1  1 2  4 1962 51 0 0 . . . . . 1 2  9 . .
2  1 2  2 1  5 1965 48 0 0 . . . . . 2 2  9 . .
2  1 3  3 1 10 1986 27 0 0 . . . . . 3 3 12 . .
2  1 4  3 1  7 1992 21 0 0 . . . . . 4 3 12 . .
2  1 5  4 2  7 1996 17 5 0 8 . 8 . . 5 2  9 0 0
2  1 6  5 1 10 2012  1 0 6 1 5 1 4 5 6 .  . 5 4
2  2 1  1 1  3 1955 58 0 0 . . . . . 1 3 12 . .
2  2 2  8 2  7 1958 55 0 0 . . . . . 2 2  9 . .
2  3 1  1 2  4 1948 65 0 0 . . . . . 1 2  9 . .
2  4 1  1 1 10 1971 42 0 0 . . . . . 1 4  . . .
2  4 2  2 2  9 1974 39 2 0 . . . . . 2 3 12 . .
2  4 3  3 2 10 1995 18 3 0 . . . . . 3 5  . . .
2  4 4  3 1  7 2000 13 0 0 1 2 1 1 2 4 2  7 2 1
2  5 1  1 2  8 1976 37 1 0 . . . . . 1 1  5 . .
end

la var hh1 "cluster number"
la var hh2 "household number"
la var ln "household member line number"
la var rela "relationship to the head"
la var sex "sex"
la var age "age"
la var MOB "Months of birth"
la var YOB "Years of birth"
la var line_w1549 "line number of woman age 15 - 49"
la var line_c04 "line number for children age 0-4"
la var line_ctaker "line number of mother or primary caretaker for children 0-14 years of age"
la var line_dad "father's line number in hh"
la var line_mom "mother' line numeber in hh"
la var line_edu "Education line number"
la var edu1 "highest level of education attended"
la var edu2 "highest grade completed at that level"
la var mom_alive "is natural mother alive"
la var dad_alive "is natural father alive"

label def rela 1 "head", modify
label def rela 2 "wife / husband", modify
label def rela 3 "son / daughter", modify
label def rela 4 "son-in-law / daughter-in-law", modify
label def rela 5 "grandchild", modify
label def rela 8 "brother / sister", modify
label def rela 11 "niece / nephew", modify
label def rela 12 "other relative", modify
label def rela 96 "other (not related)", modify
label values rela rela

label def sex 1 "male", modify
label def sex 2 "female", modify
label values sex sex

label def MOB 98 "dk", modify
label values MOB MOB

label def line_w1549 0 "not eligible", modify
label values line_w1549 line_w1549

label def hl7b 0 "not eligible", modify
label values line_c04 line_c04

label def mom_alive 1 "yes", modify
label def mom_alive 8 "dk", modify
label values mom_alive mom_alive

label def dad_alive 1 "yes", modify
label def dad_alive 8 "dk", modify
label values dad_alive dad_alive

label def edu1 0 "preschool", modify
label def edu1 1 "primary", modify
label def edu1 2 "lower secondary", modify
label def edu1 3 "upper secondary", modify
label def edu1 4 "professional school", modify
label def edu1 5 "college/university & above", modify
label values edu1 edu1

label values edu2 edu2

Women data file

clear
input int hh1 byte(hh2 ln mstatus h_age)
1  2 2 1 30
1  3 2 1 38
1  4 2 1 50
1  6 2 1 61
1  7 2 1 47
1  9 2 1 44
1 11 4 1 42
1 13 4 1 31
1 14 4 1 32
1 15 2 1 30
1 16 2 1 50
1 17 4 1 33
1 18 4 1 31
1 19 5 1 23
1 19 7 1 26
1 20 2 1 36
2  1 5 1 21
2  4 2 1 42
2  5 1 1 39
2  6 3 1 30
2 11 2 1 54
2 11 4 1 22
2 12 3 1 28
2 13 1 1 40
2 14 1 1 45
2 15 2 1 38
2 15 6 1 30
2 16 4 1 33
3  1 4 1 33
3  1 6 1 30
3  2 4 1 40
3  3 4 1 31
3  5 2 1 46
3  6 4 1 36
3  7 4 1 42
3  7 9 1 33
3  8 3 1 39
3 10 3 1 42
3 11 3 1 30
3 12 4 1 35
3 13 2 1 44
3 14 4 1 27
3 16 4 1 47
3 17 4 1 29
3 19 2 1 45
3 20 1 1 41
4  3 2 1 54
4  5 3 1 31
4  6 2 1 52
4  9 2 1 51
4 10 2 1 36
4 15 1 1 37
4 16 2 1 52
4 17 4 1 39
4 19 2 1 34
4 20 3 1 32
5  2 2 1 51
5  3 2 1 42
5  4 2 1 42
5  7 2 1 47
5  8 2 1 54
5  9 4 1 42
5 11 4 1 36
5 13 2 1 47
5 15 4 1 33
5 16 2 1 43
5 18 2 1 43
5 19 2 1 32
5 20 2 1 50
6  2 4 1 30
6  4 2 1 45
6  5 2 1 40
6  6 2 1 33
6  8 2 1 46
6  9 3 1 47
6 10 4 1 31
6 11 3 1 34
6 12 2 1 49
6 14 4 1 34
6 14 5 1 30
6 17 2 1 54
6 18 4 1 35
6 18 5 1 29
6 19 4 1 30
6 20 2 1 30
7  1 2 1 57
7  2 2 1 52
7  3 2 1 42
7  7 2 1 57
7  8 2 1 29
7 12 2 1 44
7 13 2 1 36
7 14 3 1 40
7 16 5 1 24
7 17 3 1 30
7 18 4 1 43
7 19 2 1 36
7 20 2 1 52
8  3 4 1 30
8  4 1 1 28
end
label values mstatus ma1
label def ma1 1 "yes, currently married", modify
label values h_age ma2

[William Lisowski]

This seems to do what I understand you to want.

// create age reference dataset for matching
use hhfile, clear
keep hh1 hh2 ln age
rename ln ln_match
rename age age_match
save agefile, replace

// match age reference dataset using line_dad
use hhfile, clear
clonevar ln_match = line_dad
merge m:1 hh1 hh2 ln_match using agefile, keep(master match)
sort hh1 hh2 ln
rename age_match age_dad
drop ln_match _merge
list hh1 hh2 ln age line_dad age_dad if !missing(line_dad), sepby(hh1 hh2) noobs

. list hh1 hh2 ln age line_dad age_dad if !missing(line_dad), sepby(hh1 hh2) noobs

  +-------------------------------------------+
  | hh1   hh2   ln   age   line_dad   age_dad |
  |-------------------------------------------|
  |   1     1    3    15          1        49 |
  |-------------------------------------------|
  |   1     2    4     2          3        30 |
  |   1     2    5     0          3        30 |
  |-------------------------------------------|
  |   1     3    4    10          3        38 |
  |   1     3    5     6          3        38 |
  |-------------------------------------------|
  |   1     4    4     6          1        50 |
  |-------------------------------------------|
  |   1     7    3    17          1        47 |
  |   1     7    4    15          1        47 |
  |-------------------------------------------|
  |   1     9    4    16          3        44 |
  |   1     9    5    11          3        44 |
  |-------------------------------------------|
  |   1    11    5     2          3        42 |
  |-------------------------------------------|
  |   1    14    5     1          3        32 |
  |-------------------------------------------|
  |   1    15    3     2          1        30 |
  |-------------------------------------------|
  |   1    18    5     5          3        31 |
  |-------------------------------------------|
  |   1    19    6     1          4        23 |
  |-------------------------------------------|
  |   1    20    3     8          1        36 |
  |   1    20    4     6          1        36 |
  |-------------------------------------------|
  |   2     1    6     1          4        21 |
  |-------------------------------------------|
  |   2     4    4    13          1        42 |
  +-------------------------------------------+

[Matthew Williams]

Dear @William Lisowski,

Thank you for your useful suggestions and code. I appreciate that.
I have two follow-up questions as follows:
1) in the list results, is is correct that age would mean the age of children? and age_dad would obviously be the age of father.
2) Interestingly, when I added sex variable in the -list- command, it turns out that several of them are female. I would expect that all of them are male, is my expectation correct?

Thank you.

. list hh1 hh2 ln age line_dad age_dad sex if !missing(line_dad), sepby(hh1 hh2) noobs

  +----------------------------------------------------+
  | hh1   hh2   ln   age   line_dad   age_dad      sex |
  |----------------------------------------------------|
  |   1     1    3    15          1        49     male |
  |----------------------------------------------------|
  |   1     2    4     2          3        30     male |
  |   1     2    5     0          3        30     male |
  |----------------------------------------------------|
  |   1     3    4    10          3        38   female |
  |   1     3    5     6          3        38     male |
  |----------------------------------------------------|
  |   1     4    4     6          1        50   female |
  |----------------------------------------------------|
  |   1     7    3    17          1        47   female |
  |   1     7    4    15          1        47     male |
  |----------------------------------------------------|
  |   1     9    4    16          3        44     male |
  |   1     9    5    11          3        44   female |
  |----------------------------------------------------|
  |   1    11    5     2          3        42     male |
  |----------------------------------------------------|
  |   1    14    5     1          3        32   female |
  |----------------------------------------------------|
  |   1    15    3     2          1        30   female |
  |----------------------------------------------------|
  |   1    18    5     5          3        31     male |
  |----------------------------------------------------|
  |   1    19    6     1          4        23   female |
  |----------------------------------------------------|
  |   1    20    3     8          1        36   female |
  |   1    20    4     6          1        36     male |
  |----------------------------------------------------|
  |   2     1    6     1          4        21     male |
  |----------------------------------------------------|
  |   2     4    4    13          1        42     male |
  +----------------------------------------------------+

[William Lisowski]

1) in the list results, is is correct that age would mean the age of children? and age_dad would obviously be the age of father.
2) Interestingly, when I added sex variable in the -list- command, it turns out that several of them are female

The answer to this lies in the following. In the results of the merge, the variable age_dad originated in agefile (as age_match) and was added to the remaining variables, such as age and sex, in the observation from hhfile to which it was matched. Thus sex is that for the child, not that for the father, which was not part of the data in agefile.

It seems to me you are possibly unfamiliar with the merge command. If that is so, you should read up on it, which is best done by reviewing its full PDF documentation in the Stata Data Management Reference Manual PDF included in your Stata installaltion and accessible through Stata's Help menu, or by running help merge and clicking on the link to the full documentation at the top of its output.

[Matthew Williams]

The answer to this lies in the following. In the results of the merge, the variable age_dad originated in agefile (as age_match) and was added to the remaining variables, such as age and sex, in the observation from hhfile to which it was matched. Thus sex is that for the child, not that for the father, which was not part of the data in agefile.

It seems to me you are possibly unfamiliar with the merge command. If that is so, you should read up on it, which is best done by reviewing its full PDF documentation in the Stata Data Management Reference Manual PDF included in your Stata installaltion and accessible through Stata's Help menu, or by running help merge and clicking on the link to the full documentation at the top of its output.

Dear William,

Thank you so much for your detailed explanation, things are now clear to me.

Yes, you are right, I am exploring what the merge command does. In this regard, do you mind if I send a private message to you? It is not related to this thread, it is a bout the merge command in a quite specific situation.

Thank you.

[William Lisowski]

Statlist is a discussion forum among Stata users, some needing assistance using Stata, some with expertise with which to assist others, and many in both groups.

On Statalist the preference is to ask questions publicly rather than privately. If someone receives a private answer, they are the only beneficiary of the work the member put in to answer the question. If the question and answer appear in public on the forum, others with a similar question may find then answer they need, and others who are following Statalist to increase their knowledge of Stata may learn something new. That's good for others, and a better payoff for the member spending the time to answer the question.

If your question is not related to this thread, then it's best to post it as a new thread with a suitably descriptive title, so that other members with something to say on the topic may chime in.

[Matthew Williams]

Great advice and thanks for that. I will consider creating a new thread.

[Nick Cox]

It's entirely open to members to encourage private discussion -- or even to offer technical support for money. But FWIW, William Lisowski has posted his personal response in terms that echo what is already public as general forum policy.

https://www.statalist.org/forums/help#adviceextras #2 says the same.

Empirically most of the most active people here do what they do in public and don't offer the same service in private, even for money. Even the exceptions I know about are not really exceptions. That is, some people here are consultants but that typically means that they are hired by companies and there are detailed formal arrangements for any project.

[Matthew Williams]

Thanks Nick and William for your opinions regarding private messages. As suggested, I created a new thread, which can be accessed at: https://www.statalist.org/forums/for...ions-correctly

I will be happy to hear your comments and suggestions on the new thread.

Thank you.