How can I reshape without a unique "j" variable in Stata?

Question

So here's my trouble: I'd like to reshape a long-format data file to wide. However, I don't have a unique "j" variable; each record in the long-format file has several key variables.

For example, I'd like to take this:

| caseid | gender | age | relationship to respondent|
|---------------------------------------------------|
| 1234   |   F    | 89  | mother                    |
| 1234   |   F    | 10  | daughter                  |
| 1235   |   M    | 15  | cousin                    |
etc

and turn it into this:

|caseid  | gender1 | age1 | rel1  |  gender2  | age2 | rel2    |
|--------------------------------------------------------------|
| 1234   | F       | 89   | mother| F         | 10   | daughter|
| 1235   | M       | 15   | cousin| .         | .    | .       |
etc

However, the data lack the suffix variable necessary for the reshape command. Is there a way that Stata will automatically generate this suffix?

A5C1D2H2I1M1N2O1R2T1 A5C1D2H2I1M1N2O1R2T1 · Accepted Answer · 2012-06-26T07:18:38

Sample data:

     +----------------------------------+
     | caseid   gender   age   relati~p |
     |----------------------------------|
  1. |   1234        F    89     mother |
  2. |   1234        F    10   daughter |
  3. |   1235        M    15     cousin |
  4. |   1235        F    14     sister |
  5. |   1235        F    55     mother |
     |----------------------------------|
  6. |   1236        M    32    brother |
  7. |   1236        M    68     father |
     +----------------------------------+

Generate a new id:

. by caseid: gen newid = _n

Gives you this:

     +------------------------------------------+
     | caseid   gender   age   relati~p   newid |
     |------------------------------------------|
  1. |   1234        F    89     mother       1 |
  2. |   1234        F    10   daughter       2 |
  3. |   1235        M    15     cousin       1 |
  4. |   1235        F    14     sister       2 |
  5. |   1235        F    55     mother       3 |
     |------------------------------------------|
  6. |   1236        M    32    brother       1 |
  7. |   1236        M    68     father       2 |
     +------------------------------------------+

Which you can now reshape with this:

. reshape wide gender age relationship, i(caseid) j(newid)

To get this:

     +--------------------------------------------------------------------------------------------+
     | caseid   gender1   age1   relati~1   gender2   age2   relati~2   gender3   age3   relati~3 |
     |--------------------------------------------------------------------------------------------|
  1. |   1234         F     89     mother         F     10   daughter                .            |
  2. |   1235         M     15     cousin         F     14     sister         F     55     mother |
  3. |   1236         M     32    brother         M     68     father                .            |
     +--------------------------------------------------------------------------------------------+

How can I reshape without a unique "j" variable in Stata?

1 Answers