Making household-level variables using individual-level variables

[Daeeun Bae]

Hello. I have a data set that has the following structure.

hhcode member_id age
1 1 45
1 2 23
1 3 31
2 1 50
2 2 38
2 3 31
2 4 14
3 1 65
3 2 31
3 3 15

I want to make it as follows.

hhcode age_1 age_2 age_3 age_4
1 45 23 31 .
2 50 38 31 14
3 65 31 15 .


Thank you.

[Andrew Musau]

Your dataset is currently in long layout. Whatever you want to do, it is most likely that it will be easier leaving the dataset as is. However, on your specific question, you want to reshape wide.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(hhcode member_id age)
1 1 45
1 2 23
1 3 31
2 1 50
2 2 38
2 3 31
2 4 14
3 1 65
3 2 31
3 3 15
end

reshape wide age, i(hhcode) j(member_id)

Res.:

. l

     +------------------------------------+
     | hhcode   age1   age2   age3   age4 |
     |------------------------------------|
  1. |      1     45     23     31      . |
  2. |      2     50     38     31     14 |
  3. |      3     65     31     15      . |
     +------------------------------------+

[Clyde Schechter]

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(hhcode member_id age)
1 1 45
1 2 23
1 3 31
2 1 50
2 2 38
2 3 31
2 4 14
3 1 65
3 2 31
3 3 15
end

reshape wide age, i(hhcode) j(member_id)

That said, most analysis and data management commands in Stata work best with data in the long layout that you started with. There are only a handful of things that really work well in Stata with wide data. So you may well come to regret doing this. Think carefully about where you are going with your data. Unless you know that you will be working primarily with commands that work best with wide data, just stay with what you started with.

In the future, when showing data examples, please use the -dataex- command to do so, as I have here. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

Added: Crossed with #2, where Andrew Musau make the same points more concisely.