How to combine information in each row into one row

[Nico Ochmann]

Dear Stata Forum,

I have columns individuals (pidp), wave (1), date of birth of kid 1 (kbdate1), date of birth of kid 2 (kbdate2), date of birth of kid3 (kbdate3).

What I would like to have is the below information in one row by individual pidp.

For the first pidp, have 1972 move one row up and 1977 move two rows up so that I end up with one row having, 68085687, 1, 1971, 1972, 1977.

Similarly, for the second pidp, move 1972 up one row, and 1975 move up two rows, ending up with 68085691, 1, 1970, 1972, 1975.

Finally, I would like to eliminate the rows from which I have moved information. That is eliminate row, 2 and 3 and 5 and 6.

Of course, my dataset is larger with more children variables and more observations.

But this is the basic idea.

I do hope I used dataex properly and the code below is hence useful.

I would really appreciate some help and thank you in advance as usual.

Best wishes.

Nico

* Example generated by -dataex-. To install: ssc install dataex
clear
input long pidp float(wave kbdate1 kbdate2 kbdate3)
68085687 1 1971    .    .
68085687 1    . 1972    .
68085687 1    .    . 1977
68085691 1 1970    .    .
68085691 1    . 1972    .
68085691 1    .    . 1975
end

[Nick Cox]

Are there other variables as well?

[Nico Ochmann]

Thanks for your quick reply. I appreciate that. To answer your question, there are more kids going from kbdate1 through kbdate16, that is it.

Best wishes.

Nico

[Phil Bromiley]

If you have this precise data structure, one way to do this would be to collapse the data - see collapse in the documentation. Alternatively, egen with a by condition might do it but would not eliminate extra rows.

[Nico Ochmann]

Thanks Phil and sorry about the late reply. I will try that. Cheers.