Converting Wave Based Data to Age Based Data

[Ben Kail]

Hello Statlisters!

I have panel data (currently in wide form) where each person is followed over several waves, and data is collected on certain variables each wave. What I would like to do is take that data and convert it to age-based data.

So, here is some example data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(id w1age w2age w3age w1cat w2cat w3cat)
1 21 23 25 2 3 1
2 20 22 24 1 3 1
3 22 24 26 1 1 2
4 19 21 23 1 2 2
5 22 24 26 1 3 2
end

In this example "id" is the person specific identifier, and the prefix "w#age" variables are the age at which the person was in wave# (where there are 3 waves) and "w#cat" is the category the person is in in each of the three waves. I'd like to convert that data to something that looks like this:

id a19cat a20cat a21cat a22cat a23cat a24cat a25cat a26cat 1 . . 2 . 3 . 1 . 2 1 . 3 . 1 . 3 . . . 1 . 1 . 2 4 1 . 2 . 2 . . . 5 . . . 1 . 3 . 2

Here, the prefix "a#" corresponds to the persons age. They have missing values at ages where they weren't observed and values on the category variable at the ages for when they were observed. So, for example, person was 21 at wave 1 (w1age) and was in category 2 at wave 1 (w1cat), 3 at wave2, and 1 at wave3, and I want that to become categorized as "2" for the new variable a21cat, 3 at a23cat, 1 at a25cat, and missing on all others.

I hope I've explained this clearly.

Thanks in advance for any assistance.

Kind regards,
Ben

[Ali Atia]

reshape long @age @cat,i(id) j(new,str)
reshape wide cat, i(id new) j(age)
collapse (firstnm) cat*,by(id)
rename cat* a*cat

. list,noobs

  +----------------------------------------------------------------------------+
  | id   a19cat   a20cat   a21cat   a22cat   a23cat   a24cat   a25cat   a26cat |
  |----------------------------------------------------------------------------|
  |  1        .        .        2        .        3        .        1        . |
  |  2        .        1        .        3        .        1        .        . |
  |  3        .        .        .        1        .        1        .        2 |
  |  4        1        .        2        .        2        .        .        . |
  |  5        .        .        .        1        .        3        .        2 |
  +----------------------------------------------------------------------------+

[Ben Kail]

Perfect. Thanks!