Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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.
    Code:
    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

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

    Comment


    • #3
      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.

      Comment


      • #4
        Code:
        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).
        Last edited by Ali Atia; 27 May 2021, 20:38.

        Comment


        • #5
          Originally posted by Ali Atia View Post
          Code:
          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.

          Comment

          Working...
          X