Hi everyone,

I have a household level data where parents give information on themselves and their children. I would like to turn this data into children level data. However, I have not a lot of clue about how to do it. I appreciate if someone can help me with this.

Below, you can see 3 variables I got from the data. I have mergeid (id of parents), w7_ch_gender1 (gender of children), and w7_ch_number (number of children that parents have). So first, I would like to create a unique id number for each children and then I would like to transpose the data in a way that it will be at children level. parents id will be repeated depending on how many children they have. I am not sure if i am clear but i will demonstrate what I exactly want in the second example:

mergeid w7_ch_gender1 w7_ch_number
AT-014640-01 Male 2
AT-014640-02 Male 2
AT-015615-01 Female 2
AT-015935-01 Female 1
AT-017298-01 Female 2
AT-017821-02 Female 3


Example of what I would like to achieve:
So mergeid is doubled if both parents answered the questions so is the number of children (row-wise). There is a unique id number for each child.


mergeid number of children childid gender
AT-000674-01 2 19701 female
AT-000674-01 2 19731 female
AT-001492-02 6 19702 male
AT-001492-01 6 19702 male
AT-001492-02 6 19732 female
AT-001492-01 6 19732 female
AT-001492-01 6 19742 male
AT-001492-02 6 19742 male
AT-001492-01 6 19801 female
AT-001492-02 6 19801 female
AT-001492-01 6 19822 male
AT-001492-02 6 19822 male
AT-001492-02 6 19831 male
AT-001492-01 6 19831 male

Can someone help me with the coding? Thanks in advance!