Long to wide with continuous time

[Nadia Koyratty]

Hi everyone,

I am looking for a way to convert a dataset from the long format to a wide format. However, I only have time as a continuous variable in weeks at multiple dp.
I can't figure out how to assign a visit variable via a code. I mean I can do it manually, but there's I have a large N.
Below is an example of 10 observations from the dataset:

id week age sex
1 0 36.4271 Male
1 7.5714 36.4271 Male
1 15.5714 36.4271 Male
1 23.5714 36.4271 Male
1 32.5714 36.4271 Male
1 40 36.4271 Male
2 0 47.8467 Male
2 8 47.8467 Male
2 16 47.8467 Male
2 23 47.8467 Male
2 30.7143 47.8467 Male
2 39 47.8467 Male
3 0 60.2875 Male
4 0 36.5969 Male
4 7.1429 36.5969 Male
4 16.1429 36.5969 Male
4 32.4286 36.5969 Male
5 0 35.948 Male
5 8 35.948 Male
5 16 35.948 Male
5 24 35.948 Male
5 32 35.948 Male
5 40 35.948 Male

Any help or guidance will be much appreciated. I am using stata v17.

[Nick Cox]

It is not clear

* why you think this would be helpful, especially as age doesn't change in the data you show.

* whether "male" is a string value or a value label, as you didn't use dataex; I've assumed the latter.

As week can take on fractional values, I can think of only one way to do this.

clear 
input id week age sex
1 0 36.4271 0
1 7.5714 36.4271 0
1 15.5714 36.4271 0
1 23.5714 36.4271 0
1 32.5714 36.4271 0
1 40 36.4271 0
2 0 47.8467 0
2 8 47.8467 0
2 16 47.8467 0
2 23 47.8467 0
2 30.7143 47.8467 0
2 39 47.8467 0
3 0 60.2875 0
4 0 36.5969 0
4 7.1429 36.5969 0
4 16.1429 36.5969 0
4 32.4286 36.5969 0
5 0 35.948 0
5 8 35.948 0
5 16 35.948 0
5 24 35.948 0
5 32 35.948 0
5 40 35.948 0
end 

label def sex 0 Male 1 Female 
label val sex sex 

bysort id (week) : gen time = _n 
reshape wide age week, i(id) j(time) 

list 

     +-----------------------------------------------------------------------------------------------------+
  1. | id | week1 |    age1 |  week2 |    age2 |   week3 |    age3 |   week4 |    age4 |   week5 |    age5 |
     |  1 |     0 | 36.4271 | 7.5714 | 36.4271 | 15.5714 | 36.4271 | 23.5714 | 36.4271 | 32.5714 | 36.4271 |
     |-----------------------------------------------------------------------------------------------------|
     |              week6              |                 age6              |              sex              |
     |                 40              |              36.4271              |                0              |
     +-----------------------------------------------------------------------------------------------------+

     +-----------------------------------------------------------------------------------------------------+
  2. | id | week1 |    age1 |  week2 |    age2 |   week3 |    age3 |   week4 |    age4 |   week5 |    age5 |
     |  2 |     0 | 47.8467 |      8 | 47.8467 |      16 | 47.8467 |      23 | 47.8467 | 30.7143 | 47.8467 |
     |-----------------------------------------------------------------------------------------------------|
     |              week6              |                 age6              |              sex              |
     |                 39              |              47.8467              |                0              |
     +-----------------------------------------------------------------------------------------------------+

     +-----------------------------------------------------------------------------------------------------+
  3. | id | week1 |    age1 |  week2 |    age2 |   week3 |    age3 |   week4 |    age4 |   week5 |    age5 |
     |  3 |     0 | 60.2875 |      . |       . |       . |       . |       . |       . |       . |       . |
     |-----------------------------------------------------------------------------------------------------|
     |              week6              |                 age6              |              sex              |
     |                  .              |                    .              |                0              |
     +-----------------------------------------------------------------------------------------------------+

     +-----------------------------------------------------------------------------------------------------+
  4. | id | week1 |    age1 |  week2 |    age2 |   week3 |    age3 |   week4 |    age4 |   week5 |    age5 |
     |  4 |     0 | 36.5969 | 7.1429 | 36.5969 | 16.1429 | 36.5969 | 32.4286 | 36.5969 |       . |       . |
     |-----------------------------------------------------------------------------------------------------|
     |              week6              |                 age6              |              sex              |
     |                  .              |                    .              |                0              |
     +-----------------------------------------------------------------------------------------------------+

     +-----------------------------------------------------------------------------------------------------+
  5. | id | week1 |    age1 |  week2 |    age2 |   week3 |    age3 |   week4 |    age4 |   week5 |    age5 |
     |  5 |     0 |  35.948 |      8 |  35.948 |      16 |  35.948 |      24 |  35.948 |      32 |  35.948 |
     |-----------------------------------------------------------------------------------------------------|
     |              week6              |                 age6              |              sex              |
     |                 40              |               35.948              |                0              |
     +-----------------------------------------------------------------------------------------------------+

. 
.

[Hemanshu Kumar]

Since age and sex both appear to be constant and the only thing changing is week, perhaps the -reshape- command in Nick's solution could instead be

reshape wide week, i(id age sex) j(time)

[William Lisowski]

Perhaps week actually represents the number of days divided by 7. In the example data we see

. generate d = 7*mod(week,1)

. tab d

          d |      Freq.     Percent        Cum.
------------+-----------------------------------
          0 |         15       65.22       65.22
     1.0003 |          1        4.35       69.57
   1.000303 |          1        4.35       73.91
   3.000202 |          1        4.35       78.26
   3.999798 |          3       13.04       91.30
   3.999801 |          1        4.35       95.65
   5.000101 |          1        4.35      100.00
------------+-----------------------------------
      Total |         23      100.00

so the fractional parts of the week variable all appear to represent 0, 1/7, 3/7, 4/7, and 5/7 in the example data.

This suggests solving the reshape problem by returning to the original measure of time in days.

[Nick Cox]

I disagree with #3 as there is no point in this data structure if time is the only variable that changes with time. I suspect that the OP faked their dataset.