How to identify grand children in the household?

[Matthew Williams]

Dear all,

I want to identify grandchildren in each household and I would appreciate any help. The data, including two parts, are as follows:

1) Data 1 collects information on those who live in the same household only. So, there could be the respondent, spouse, respondent's parents, respondent's children or respondent's great/grand children. Basically, I can use relation variable to identify grand children, however, its responses include respondent's great/grand children, rather than solely grand children.

2) Data 2 collects on information on children who live either in the same household or outside the household. There are two variables in this dataset that (I think) could help me obtain what I am looking for. The first variable is child_codeline, which is the codeline for children living in the same household taken from codeline variable in the data1. This variable is missing for children who live outside the household. The second variable is has_any_child, indicating whether respondent's children have any child or not.

Based on the data structure, I think one could only identify grand children who live in the same household only. Please note that the respondent is the one who answers the questionnaire. Thank you.

Data 1: members in the same household

clear
input long qid byte(codeline sex relation) int yob
 11 1 2  1 1938
 11 2 2  4 1970
 11 3 1 10 1999
 11 4 1 10 1999
 11 5 1 10 2001
 12 1 2  1 1961
 12 2 1  2 1960
 12 3 1  3 1984
 12 4 1  3 1991
 13 1 1  1 1951
 13 2 2  2 1950
 13 3 1  3 1975
 13 4 2  5 1979
 13 5 1 10 2005
 13 6 1 10 2006
 14 1 2  1 1960
 14 2 2  4 1992
 14 3 1  4 1999
 15 1 1  1 1950
 15 2 2  2 1950
 16 1 2  1 1946
 16 2 1  2 1945
 16 3 1  3 1971
 16 4 2  5 1975
 16 5 1 10 1995
 16 6 2 10 1998
 17 1 2  1 1957
 17 2 1  2 1949
 17 3 1  3 1992
 17 4 1  3 1980
 17 5 2  5 1980
 17 6 2 10 2005
 17 7 1 10 2008
 18 1 2  1 1936
 18 2 1  2 1931
 18 3 2 10 1993
 18 4 2 10 1994
 19 1 2  1 1929
110 1 2  1 1931
110 2 1  2 1928
110 3 1  3 1959
110 4 2  5 1964
110 5 2 10 1988
110 6 1 10 1995
111 1 1  1 1946
111 2 2  2 1948
111 3 1  3 1977
111 4 2  5 1977
111 5 1 10 2005
111 6 2 10 2010
112 1 1  1 1928
112 2 1  3 1977
112 3 2  5 1981
112 4 2 10 2005
112 5 1 10 2006
112 6 2  2 1940
113 1 2  1 1932
113 2 2  5 1963
113 3 1 10 1990
113 4 2 10 1993
114 1 2  1 1930
115 1 1  1 1951
115 2 2  2 1959
115 3 1 10 2008
116 1 1  1 1955
116 2 2  2 1955
116 3 1  3 1978
116 4 2  4 1983
116 5 1  5 1982
116 6 2 10 2010
117 1 1  1 1958
117 2 2  2 1959
117 3 1  3 1983
118 1 2  1 1959
118 2 1  2 1958
118 3 1  3 1982
118 4 1  3 1983
118 5 2  5 1984
118 6 1 10 2007
119 1 2  1 1945
119 2 1  2 1940
119 3 2 10 1999
120 1 1  1 1940
120 2 2  2 1945
120 3 2 10 1999
121 1 2  1 1954
121 2 1  2 1930
122 1 1  1 1930
122 2 2  2 1954
123 1 2  1 1950
123 2 1  2 1948
123 3 2  5 1983
123 4 1  3 1988
123 5 1 10 2010
124 1 1  1 1940
124 2 2  2 1939
124 3 1 10 2000
125 1 2  1 1942
125 2 1  2 1942
125 3 1  3 1982
end
label values sex LABEL_B216S
label def LABEL_B216S 1 "Male", modify
label def LABEL_B216S 2 "Female", modify
label values relation LABEL_B316R
label def LABEL_B316R 1 "Respondent", modify
label def LABEL_B316R 2 "Spouse", modify
label def LABEL_B316R 3 "Son", modify
label def LABEL_B316R 4 "Daughter", modify
label def LABEL_B316R 5 "Son/daughter in law", modify
label def LABEL_B316R 10 "Great/grand children", modify

Data 2: Children living both inside and outside he household

clear
input long qid byte(codeline child_sex child_type) int child_yob byte(child_codeline has_any_child)
 11 1 1 1 1967 . 1
 11 2 2 4 1970 . 1
 11 3 1 1 1975 . 1
 11 4 2 4 1983 . 1
 11 5 1 4 1960 . 1
 11 6 2 1 1970 2 1
 12 1 1 1 1984 3 2
 12 2 1 1 1991 4 2
 13 1 1 1 1981 . 1
 13 2 2 4 1981 . 1
 13 3 1 1 1978 . 2
 13 4 2 4 1988 . 2
 13 5 1 1 1975 3 1
 13 6 2 4 1979 4 1
 14 1 2 1 1983 . 1
 14 2 2 1 1988 . 1
 14 3 1 4 1974 . 1
 14 4 1 4 1982 . 1
 14 5 2 1 1992 2 2
 14 6 1 1 1999 3 2
 15 1 1 1 1977 . 1
 15 2 2 1 1982 . 1
 15 3 2 1 1984 . 1
 15 4 2 4 1977 . 1
 15 5 1 4 1986 . 1
 15 6 1 4 1983 . 1
 16 1 1 1 1974 . 1
 16 2 1 1 1977 . 1
 16 3 2 1 1975 . 1
 16 4 2 4 1977 . 1
 16 5 2 4 1975 . 1
 16 6 1 4 1974 . 1
 16 7 1 1 1971 3 1
 16 8 2 4 1975 4 1
 17 1 1 1 1992 3 2
 17 2 1 1 1980 4 1
 17 3 2 4 1980 5 1
 18 1 1 1 1963 . 1
 18 2 2 4 1963 . 1
 18 3 2 1 1965 . 1
 18 4 2 1 1968 . 1
 18 5 1 4 1956 . 1
 18 6 2 1 1970 . 1
 18 7 1 4 1958 . 1
 18 8 2 1 1972 . 1
 18 9 1 4 1969 . 1
 19 1 2 1 1953 . 1
 19 2 1 4 1952 . 1
 19 3 2 1 1951 . 1
 19 4 1 4 1949 . 1
 19 5 2 1 1960 . 1
 19 6 1 4 1959 . 1
 19 7 2 1 1968 . 1
 19 8 1 4 1966 . 1
110 1 2 1 1963 . 1
110 2 1 4 1961 . 1
110 3 1 1 1959 3 1
110 4 2 4 1964 4 1
111 1 2 1 1972 . 1
111 2 2 1 1975 . 1
111 3 1 4 1970 . 1
111 4 1 4 1976 . 1
111 5 1 1 1977 3 1
111 6 2 4 1977 4 1
112 1 1 1 1977 2 1
112 2 2 4 1981 3 1
113 1 1 1 1958 . 1
113 2 2 4 1960 . 1
113 3 2 1 1960 . 1
113 4 1 4 1958 . 1
113 5 2 1 1969 . .
113 6 1 1 1961 . 1
113 7 2 4 1963 2 1
114 1 1 1 1961 . 1
114 2 2 4 1963 . 1
114 3 2 1 1957 . 1
114 4 1 4 1955 . 1
115 1 2 1 1983 . 1
115 2 1 4 1981 . 1
115 3 2 1 1986 . 2
115 4 1 4 1985 . 2
116 1 1 1 1978 3 2
116 2 2 1 1983 4 1
116 3 1 4 1982 5 1
117 1 2 1 1986 . 1
117 2 1 4 1978 . 1
117 3 1 1 1983 3 2
118 1 1 1 1982 3 2
118 2 1 1 1983 4 1
118 3 2 4 1984 5 1
119 1 2 1 1969 . 2
119 2 1 1 1971 . 1
119 3 1 1 1981 . 2
119 5 2 4 1970 . 1
120 1 2 1 1969 . 2
120 2 1 1 1971 . 1
120 3 1 1 1981 . 2
120 4 2 4 1970 . 1
120 5 1 4 1964 . 2
121 1 1 1 1976 . 1
end
label values child_sex LABEL_B1127
label def LABEL_B1127 1 "Male", modify
label def LABEL_B1127 2 "Female", modify
label values child_type LABEL_B1227
label def LABEL_B1227 1 "Biological", modify
label def LABEL_B1227 4 "In law", modify
label values has_any_child LABEL_B1627
label def LABEL_B1627 1 "Yes", modify
label def LABEL_B1627 2 "No", modify

[Sergiy Radyakin]

Is that German SOEP data?

It is not clear what your goal is. I see at least these possible goals:

A) identify all grandchildren (of the respondent only)
B) identify all grandchildren (of all persons in the household)
C) identify all persons who have grandchildren (observed or not)

Taking A as a possible target, seems to boil down to

list if relation==10

Here it may be more useful to start with an example, take a family of Smiths, John, Mary, Peter, ... and describe what their relationships are, and then what data corresponds to that family corresponding to the protocols of your survey, then see if you can recover what you wish from this example.

Best, Sergiy

[Matthew Williams]

Dear Sergiy,

Thank you for your response. The option A is what I wanted to have. Solely relying on relation==10 cannot solve the issue because as stated in #1, relation==10 is for great AND grand children. That is why one may need to incorporate the respondent’s children information in data 2.

No, it’s not SOEP. The data comes from other countries.

[Matthew Williams]

Is there anyone who can help me with this issue? Thank you.