How to construct children data based on their parents' data?

[Matthew Williams]

Hi all,

I have a household dataset in which respondents' data are in a long layout but their children's information are in a wide layout. I want to create a dataset in a long layout for children (only biological children, not considering children-in-law or adopted ones) and then I will use the created children dataset to merge with that of respondents to utilize other information of respondents. I would appreciate if anyone can help me with this issue.

In the survey, there is a section called household members information which contains information of all household members on age, gender, relationship with respondents, marital status, and education. The interview procedure is that respondents are asked to list out all of those information, starting with respondents first and then other household members. Thus, in a data example below, variables b01-b61 are information on respondents and the rest are for other household members. Since respondents are the interviewees so qid is the uniquely identified observation for each respondent.

Thank you.

clear
input long qid byte(b01 b21s b31r) int b41y byte(b51 b61 b02 b22s b32r) int b42y byte(b52 b62 b03 b23s b33r) int b43y byte(b53 b63 b04 b24s b34r) int b44y byte(b54 b64 b05 b25s b35r) int b45y byte(b55 b65)
 11 1 2 1 1938 1 6 2 2  4 1970 2 7 3 1 10 1999 1 3 4 1 10 1999 1 3 5 1 10 2001 1 2
 13 1 1 1 1951 2 7 2 2  2 1950 2 7 3 1  3 1975 2 7 4 2  5 1979 2 7 5 1 10 2005 1 2
 15 1 1 1 1950 2 5 2 2  2 1950 2 4 0 0  0    0 0 0 0 0  0    0 0 0 0 0  0    0 0 0
 16 1 2 1 1946 2 3 2 1  2 1945 2 5 3 1  3 1971 2 5 4 2  5 1975 2 5 5 1 10 1995 1 4
 18 1 2 1 1936 2 2 2 1  2 1931 2 4 3 2 10 1993 1 5 4 2 10 1994 1 4 0 0  0    0 0 0
 19 1 2 1 1929 5 2 0 0  0    0 0 0 0 0  0    0 0 0 0 0  0    0 0 0 0 0  0    0 0 0
110 1 2 1 1931 2 2 2 1  2 1928 2 7 3 1  3 1959 2 7 4 2  5 1964 2 5 5 2 10 1988 1 5
111 1 1 1 1946 2 7 2 2  2 1948 2 5 3 1  3 1977 2 7 4 2  5 1977 2 7 5 1 10 2005 1 0
112 1 1 1 1928 2 6 2 1  3 1977 2 6 3 2  5 1981 2 6 4 2 10 2005 1 2 5 1 10 2006 1 0
113 1 2 1 1932 5 6 2 2  5 1963 2 6 3 1 10 1990 1 5 4 2 10 1993 1 5 0 0  0    0 0 0
114 1 2 1 1930 5 2 0 0  0    0 0 0 0 0  0    0 0 0 0 0  0    0 0 0 0 0  0    0 0 0
115 1 1 1 1951 2 5 2 2  2 1959 2 5 3 1 10 2008 1 0 0 0  0    0 0 0 0 0  0    0 0 0
119 1 2 1 1945 2 4 2 1  2 1940 2 4 3 2 10 1999 1 3 0 0  0    0 0 0 0 0  0    0 0 0
120 1 1 1 1940 2 4 2 2  2 1945 2 4 3 2 10 1999 1 3 0 0  0    0 0 0 0 0  0    0 0 0
122 1 1 1 1930 2 7 2 2  2 1954 2 2 0 0  0    0 0 0 0 0  0    0 0 0 0 0  0    0 0 0
123 1 2 1 1950 2 5 2 1  2 1948 2 7 3 2  5 1983 2 7 4 1  3 1988 1 7 5 1 10 2010 1 0
124 1 1 1 1940 2 2 2 2  2 1939 2 2 3 1 10 2000 1 3 0 0  0    0 0 0 0 0  0    0 0 0
125 1 2 1 1942 2 6 2 1  2 1942 2 7 3 1  3 1982 2 7 4 2  5 1983 2 7 5 1 10 2009 1 0
126 1 2 1 1930 5 2 0 0  0    0 0 0 0 0  0    0 0 0 0 0  0    0 0 0 0 0  0    0 0 0
127 1 2 1 1942 5 2 0 0  0    0 0 0 0 0  0    0 0 0 0 0  0    0 0 0 0 0  0    0 0 0
128 1 1 1 1933 2 4 2 2  2 1933 2 1 0 0  0    0 0 0 0 0  0    0 0 0 0 0  0    0 0 0
129 1 2 1 1949 5 6 2 2  4 1985 2 6 3 1  5 1983 2 7 4 2 10 2010 1 0 0 0  0    0 0 0
134 1 2 1 1949 2 4 2 1  2 1943 2 4 3 1  3 1975 2 5 4 2  5 1977 2 5 5 1 10 2005 1 2
136 1 1 1 1936 2 2 2 2  2 1940 2 2 3 1  3 1963 1 4 4 2  4 1967 2 4 5 1  5 1961 2 5
137 1 1 1 1941 5 5 2 1  3 1976 2 6 3 2  5 1977 2 5 4 2  3 1980 2 7 5 1  3 1983 1 6
138 1 2 1 1927 5 2 2 2  4 1969 2 5 3 1  5 1968 2 4 0 0  0    0 0 0 0 0  0    0 0 0
139 1 2 1 1929 5 5 2 1  3 1955 2 5 3 2  5 1957 2 5 4 1 10 1990 1 5 0 0  0    0 0 0
140 1 2 1 1948 2 3 2 1  2 1944 2 4 3 1  3 1978 2 4 0 0  0    0 0 0 0 0  0    0 0 0
141 1 2 1 1924 5 2 2 1  3 1970 2 5 3 2  5 1972 2 5 4 1 10 1998 1 3 5 1 10 2008 1 0
142 1 1 1 1931 2 3 2 2  2 1933 2 3 3 2  4 1959 1 4 0 0  0    0 0 0 0 0  0    0 0 0
146 1 1 1 1941 2 5 2 2  2 1945 2 3 3 1  3 1976 3 5 4 1  3 1978 2 5 5 2  5 1987 2 5
147 1 2 1 1936 2 1 2 1  2 1934 2 3 3 1  3 1957 1 4 0 0  0    0 0 0 0 0  0    0 0 0
148 1 2 1 1951 2 4 2 1  2 1936 2 4 3 1  3 1982 2 5 4 2  5 1982 2 5 5 1 10 2005 1 2
149 1 2 1 1950 2 3 2 1  2 1945 2 4 0 0  0    0 0 0 0 0  0    0 0 0 0 0  0    0 0 0
150 1 1 1 1945 2 4 2 2  2 1950 2 3 0 0  0    0 0 0 0 0  0    0 0 0 0 0  0    0 0 0
151 1 2 1 1938 5 3 2 1  3 1975 2 3 3 2  5 1978 2 4 4 2 10 2007 1 0 5 2 10 2010 1 0
152 1 1 1 1951 2 4 2 2  2 1958 2 4 3 1  3 1986 2 3 4 2  5 1986 2 4 5 1  3 1992 1 3
153 1 2 1 1928 5 2 2 1  3 1955 2 4 3 2  5 1958 2 3 4 1 10 1985 1 5 5 1 10 1990 1 4
154 1 2 1 1947 2 2 2 1  2 1939 2 3 3 1  3 1977 2 4 4 2  5 1989 2 5 5 2 10 2001 1 2
155 1 2 1 1944 5 2 2 1 10 2001 1 2 3 1 10 1994 1 3 0 0  0    0 0 0 0 0  0    0 0 0
156 1 2 1 1930 2 1 2 1  2 1933 2 4 3 1  3 1957 2 4 4 2  5 1956 2 3 5 1 10 1981 2 4
159 1 2 1 1936 5 3 2 1  3 1962 2 4 3 2  5 1966 2 4 4 1 10 1986 1 4 5 2 10 1988 1 6
161 1 2 1 1937 5 5 2 1  4 1968 1 5 3 1  3 1963 2 6 4 2  5 1964 2 5 5 2 10 1989 1 7
162 1 2 1 1948 3 1 0 0  0    0 0 0 0 0  0    0 0 0 0 0  0    0 0 0 0 0  0    0 0 0
163 1 2 1 1929 2 6 2 1  2 1927 2 7 3 1  3 1956 2 4 4 2  5 1983 2 4 5 1 10 2003 1 0
164 1 2 1 1950 2 6 2 1  2 1949 2 6 3 1  3 1973 2 7 4 2  5 1973 2 7 5 1  3 1979 2 7
167 1 1 1 1945 2 7 2 2  2 1948 2 7 3 2  4 1978 2 9 4 1  5 1983 2 9 5 2 10 2010 1 0
168 1 2 1 1938 2 4 2 1  2 1930 2 4 3 1  3 1973 2 7 4 2  4 1975 2 7 5 1 10 2000 1 3
169 1 1 1 1929 2 7 2 2  2 1931 2 4 3 1  3 1959 2 4 4 2  5 1966 2 3 5 1 10 1992 2 5
171 1 2 1 1930 2 4 2 1  2 1921 2 3 3 1  3 1962 2 5 4 2  5 1965 2 5 5 1 10 1989 1 5
173 1 1 1 1929 2 2 2 2  2 1938 2 3 3 1  3 1970 2 5 4 2  5 1987 2 5 0 0  0    0 0 0
175 1 1 1 1937 2 7 2 2  2 1943 2 7 3 1  3 1980 1 8 0 0  0    0 0 0 0 0  0    0 0 0
176 1 1 1 1944 2 9 2 2  2 1945 2 7 3 1  3 1979 2 8 4 2 10 2000 1 2 0 0  0    0 0 0
177 1 1 1 1938 2 7 2 2  2 1943 2 6 0 0  0    0 0 0 0 0  0    0 0 0 0 0  0    0 0 0
178 1 2 1 1949 2 3 2 1  2 1946 2 6 3 1  3 1974 2 6 4 2  5 1975 2 6 5 1 10 1995 1 4
180 1 1 1 1946 2 4 2 2  2 1953 2 4 3 1  3 1990 1 5 0 0  0    0 0 0 0 0  0    0 0 0
181 1 2 1 1950 2 3 2 1  2 1950 2 4 3 1  3 1985 1 7 0 0  0    0 0 0 0 0  0    0 0 0
182 1 2 1 1925 5 2 2 1  3 1957 4 4 3 2  4 1962 1 7 4 1 10 1993 1 5 0 0  0    0 0 0
183 1 2 1 1931 5 1 2 2  4 1953 2 4 3 2  4 1949 1 1 4 1  3 1962 2 4 5 2  5 1965 2 5
184 1 2 1 1949 1 1 2 2  7 1931 5 1 3 2 12 1953 2 4 4 1 12 1962 2 4 5 2 12 1965 2 5
186 1 2 1 1935 5 2 2 1  3 1955 2 4 3 2  5 1962 2 5 4 1 10 1986 2 5 5 2 10 1985 2 5
188 1 2 1 1940 5 3 2 1  3 1964 2 4 0 0  0    0 0 0 0 0  0    0 0 0 0 0  0    0 0 0
190 1 2 1 1945 5 5 2 1  3 1971 2 5 3 2  5 1974 2 4 4 2 10 2001 1 2 5 2 10 2009 1 0
191 1 1 1 1948 2 7 2 2  2 1951 2 7 3 1  3 1984 1 7 0 0  0    0 0 0 0 0  0    0 0 0
192 1 1 1 1939 2 5 2 2  2 1946 2 5 3 1  3 1971 1 7 4 1  3 1977 1 5 0 0  0    0 0 0
193 1 2 1 1927 5 2 2 1  3 1964 2 7 3 2  5 1968 2 7 4 1 10 2003 1 2 5 2 10 1995 1 4
194 1 1 1 1941 2 3 2 2  2 1941 2 2 3 1 10 1986 3 4 4 1 10 2007 1 0 0 0  0    0 0 0
196 1 2 1 1930 5 1 2 2  4 1956 1 4 0 0  0    0 0 0 0 0  0    0 0 0 0 0  0    0 0 0
197 1 2 1 1951 2 6 2 1  2 1949 2 7 3 1  3 1981 2 7 4 2  5 1985 2 7 5 2 10 2007 1 0
199 1 2 1 1945 2 5 2 1  2 1931 2 4 0 0  0    0 0 0 0 0  0    0 0 0 0 0  0    0 0 0
331 1 1 1 1946 2 2 2 2  2 1963 2 5 3 2  4 1993 2 4 0 0  0    0 0 0 0 0  0    0 0 0
332 1 2 1 1936 5 2 2 1  3 1968 2 3 3 2  5 1970 2 4 4 1 10 1993 1 5 0 0  0    0 0 0
333 1 2 1 1930 2 1 2 1  2 1932 2 4 3 1  3 1973 2 3 4 2  5 1975 2 4 5 1 10 1997 1 3
336 1 2 1 1950 2 7 2 1  2 1947 2 7 3 1  3 1975 2 7 4 2  5 1979 2 7 5 1 10 2002 1 2
337 1 2 1 1926 5 1 2 1  3 1965 2 3 3 2  5 1970 2 4 4 1 10 1992 1 5 5 1 10 1996 1 3
338 1 2 1 1941 5 3 2 2  4 1967 2 4 3 1 10 2006 1 2 0 0  0    0 0 0 0 0  0    0 0 0
362 1 2 1 1939 2 7 2 1  2 1933 2 7 3 1  3 1963 2 5 4 2  5 1967 2 5 5 2 10 1994 1 1
363 1 1 1 1950 2 4 2 2  2 1952 2 3 3 2  4 1977 2 4 4 1 10 1998 1 3 0 0  0    0 0 0
366 1 1 1 1935 2 7 2 2  2 1940 2 6 3 1  3 1960 2 5 4 2  5 1970 2 4 5 1 10 1992 1 5
367 1 1 1 1948 2 6 2 2  2 1952 2 6 3 2 10 1992 1 5 0 0  0    0 0 0 0 0  0    0 0 0
368 1 1 1 1945 2 7 2 2  2 1950 2 7 3 1 10 2008 1 0 0 0  0    0 0 0 0 0  0    0 0 0
369 1 1 1 1925 2 5 2 2  2 1930 2 4 3 1  3 1949 2 6 4 2  5 1949 2 6 5 2 10 1987 1 7
382 1 2 1 1929 2 2 2 1  2 1929 2 3 0 0  0    0 0 0 0 0  0    0 0 0 0 0  0    0 0 0
383 1 1 1 1929 2 3 2 2  2 1939 2 2 0 0  0    0 0 0 0 0  0    0 0 0 0 0  0    0 0 0
384 1 2 1 1947 2 3 2 1  2 1940 2 4 3 1  3 1975 2 7 4 2  5 1982 2 4 5 1 10 2006 1 0
385 1 1 1 1950 2 4 2 2  2 1951 2 3 0 0  0    0 0 0 0 0  0    0 0 0 0 0  0    0 0 0
387 1 2 1 1938 2 1 2 1  2 1937 2 5 3 1  3 1977 2 4 4 2  5 1980 2 7 5 2 10 2006 1 0
389 1 1 1 1939 2 3 2 2  2 1947 2 4 3 1  3 1975 2 7 4 2  5 1982 2 4 5 1 10 2006 1 0
461 1 1 1 1927 2 1 2 2  2 1931 2 1 3 1 10 1979 2 7 4 2 10 1992 1 5 0 0  0    0 0 0
462 1 2 1 1932 2 1 2 1  2 1927 2 1 3 1 10 1979 2 7 4 2 10 1992 1 5 0 0  0    0 0 0
463 1 1 1 1930 2 6 2 2  2 1947 2 7 3 1  3 1979 1 8 0 0  0    0 0 0 0 0  0    0 0 0
464 1 2 1 1945 2 7 2 1  2 1940 2 7 3 1  3 1973 2 7 4 2  5 1977 2 7 5 2 10 2006 1 0
465 1 2 1 1936 5 3 2 1  3 1966 2 7 3 2  5 1974 2 7 4 2 10 2003 1 2 5 2 10 2011 1 0
466 1 1 1 1950 2 9 2 2  2 1960 2 7 0 0  0    0 0 0 0 0  0    0 0 0 0 0  0    0 0 0
469 1 2 1 1948 2 4 2 1  2 1947 2 7 3 1  3 1981 1 7 4 2 10 1984 1 5 5 2 10 2001 1 2
491 1 1 1 1949 2 5 2 2  2 1961 2 6 3 2  4 1983 2 6 4 2  4 1991 2 5 5 2 10 2011 1 0
493 1 1 1 1932 2 5 2 2  2 1935 2 4 3 2  4 1958 5 7 0 0  0    0 0 0 0 0  0    0 0 0
494 1 2 1 1950 5 3 2 1  3 1982 1 7 0 0  0    0 0 0 0 0  0    0 0 0 0 0  0    0 0 0
496 1 1 1 1930 2 6 2 2  2 1944 2 5 3 1  3 1973 2 8 4 2  5 1977 2 7 5 2 10 2001 1 2
497 1 1 1 1940 2 7 2 2  2 1950 2 3 3 2  4 1983 1 6 0 0  0    0 0 0 0 0  0    0 0 0
end
label values b21s LABEL_B21
label def LABEL_B21 1 "Male", modify
label def LABEL_B21 2 "Female", modify
label values b31r LABEL_B31R
label def LABEL_B31R 1 "Respondent", modify
label values b51 LABEL_B51
label def LABEL_B51 1 "Single", modify
label def LABEL_B51 2 "Married", modify
label def LABEL_B51 3 "Divorced", modify
label def LABEL_B51 5 "Widow", modify
label values b61 LABEL_B61
label def LABEL_B61 1 "No scholing", modify
label def LABEL_B61 2 "Incomplete primary school", modify
label def LABEL_B61 3 "Primary school", modify
label def LABEL_B61 4 "Lower secondary school", modify
label def LABEL_B61 5 "Upper secondary school", modify
label def LABEL_B61 6 "Prof secondary education", modify
label def LABEL_B61 7 "Junior college/University", modify
label def LABEL_B61 9 "Doctor", modify
label values b22s LABEL_B22
label def LABEL_B22 1 "Male", modify
label def LABEL_B22 2 "Female", modify
label values b32r LABEL_B32R
label def LABEL_B32R 2 "Spouse", modify
label def LABEL_B32R 3 "Son", modify
label def LABEL_B32R 4 "Daughter", modify
label def LABEL_B32R 5 "Son/daughter in law", modify
label def LABEL_B32R 7 "Parent", modify
label def LABEL_B32R 10 "Grand children", modify
label values b52 LABEL_B52
label def LABEL_B52 1 "Single", modify
label def LABEL_B52 2 "Married", modify
label def LABEL_B52 4 "Separated", modify
label def LABEL_B52 5 "Widow", modify
label values b62 LABEL_B62
label def LABEL_B62 1 "No scholing", modify
label def LABEL_B62 2 "Incomplete primary school", modify
label def LABEL_B62 3 "Primary school", modify
label def LABEL_B62 4 "Lower secondary school", modify
label def LABEL_B62 5 "Upper secondary school", modify
label def LABEL_B62 6 "Prof secondary education", modify
label def LABEL_B62 7 "Junior college/University", modify
label values b23s LABEL_B23
label def LABEL_B23 1 "Male", modify
label def LABEL_B23 2 "Female", modify
label values b33r LABEL_B33R
label def LABEL_B33R 3 "Son", modify
label def LABEL_B33R 4 "Daughter", modify
label def LABEL_B33R 5 "Son/daughter in law", modify
label def LABEL_B33R 10 "Grand children", modify
label def LABEL_B33R 12 "Other relatives", modify
label values b53 LABEL_B53
label def LABEL_B53 1 "Single", modify
label def LABEL_B53 2 "Married", modify
label def LABEL_B53 3 "Divorced", modify
label def LABEL_B53 5 "Widow", modify
label values b63 LABEL_B63
label def LABEL_B63 0 "Still young", modify
label def LABEL_B63 1 "No scholing", modify
label def LABEL_B63 2 "Incomplete primary school", modify
label def LABEL_B63 3 "Primary school", modify
label def LABEL_B63 4 "Lower secondary school", modify
label def LABEL_B63 5 "Upper secondary school", modify
label def LABEL_B63 6 "Prof secondary education", modify
label def LABEL_B63 7 "Junior college/University", modify
label def LABEL_B63 8 "Master", modify
label def LABEL_B63 9 "Doctor", modify
label values b24s LABEL_B24
label def LABEL_B24 1 "Male", modify
label def LABEL_B24 2 "Female", modify
label values b34r LABEL_B34R
label def LABEL_B34R 3 "Son", modify
label def LABEL_B34R 4 "Daughter", modify
label def LABEL_B34R 5 "Son/daughter in law", modify
label def LABEL_B34R 10 "Grand children", modify
label def LABEL_B34R 12 "Other relatives", modify
label values b54 LABEL_B54
label def LABEL_B54 1 "Single", modify
label def LABEL_B54 2 "Married", modify
label values b64 LABEL_B64
label def LABEL_B64 0 "Still young", modify
label def LABEL_B64 2 "Incomplete primary school", modify
label def LABEL_B64 3 "Primary school", modify
label def LABEL_B64 4 "Lower secondary school", modify
label def LABEL_B64 5 "Upper secondary school", modify
label def LABEL_B64 6 "Prof secondary education", modify
label def LABEL_B64 7 "Junior college/University", modify
label def LABEL_B64 9 "Doctor", modify
label values b25s LABEL_B25
label def LABEL_B25 1 "Male", modify
label def LABEL_B25 2 "Female", modify
label values b35r LABEL_B35R
label def LABEL_B35R 3 "Son", modify
label def LABEL_B35R 5 "Son/daughter in law", modify
label def LABEL_B35R 10 "Grand children", modify
label def LABEL_B35R 12 "Other relatives", modify
label values b55 LABEL_B55
label def LABEL_B55 1 "Single", modify
label def LABEL_B55 2 "Married", modify
label values b65 LABEL_B65
label def LABEL_B65 0 "Still young", modify
label def LABEL_B65 1 "No scholing", modify
label def LABEL_B65 2 "Incomplete primary school", modify
label def LABEL_B65 3 "Primary school", modify
label def LABEL_B65 4 "Lower secondary school", modify
label def LABEL_B65 5 "Upper secondary school", modify
label def LABEL_B65 6 "Prof secondary education", modify
label def LABEL_B65 7 "Junior college/University", modify

[Matthew Williams]

I would be grateful if anyone can help me with the data issue in #1. Thanks.

[Justin Niakamal]

I would suggest taking a single qid from your data example above and showing what the desired layout looks like based on that qid. It might help those of us that aren't familiar with this data to give you some helpful direction.

[Ali Atia]

reshape long b0 b2@s b3@r b4@y b5@ b6@,i(qid) j(new,string)
keep if inlist(b3,3,4)

This produces a long dataset containing only data for biological children (i.e. those coded as 3 or 4 in the b3*r variables).

[Matthew Williams]

reshape long b0 b2@s b3@r b4@y b5@ b6@,i(qid) j(new,string)
keep if inlist(b3,3,4)

This produces a long dataset containing only data for biological children (i.e. those coded as 3 or 4 in the b3*r variables).

Thank you Justin and Ali. Code provided by Ali in #4 is exactly what I am looking for. I now need an additional help. Specifically, I want to generate a variable indicating year of birth of a child with the highest educational level (condition 1) and in cases, if there are two children (or more) with the same educational level, choose the oldest one (condition 2).

Let's take an example using the data provided in #1 and code provided in #4. Take a look at pid=183 and this individual has three children. I want to create a binary variable identifying the one born in 1953 (e.g., =1 and 0 or missing otherwise) because she is older though her educational level is the same as the one born in 1962.

Thank you.