Restructuring data - Please help with creating an index variable strategy

[dmunneke]

I have a large dataset in long format (multiple records per person for each observation).  I'd like to restructure the data in wide format in a time series format.   I'd like to use tenure as the index variable and I'd like to categorize it into 1 month segments across 24 months.  

I can recode tenure to do this but because there are infrequent occurrences where multiple observations occur in one month, it will create duplicate values and fail when restructuring.

It would probably be ideal to average the multiple observations so I have a single score per month and then recode the data for purposes of an index variable.

Data is a format like this

  Record #   ID      Name       Tenure    DV1Score  DV2Score  DVScore3
      1         222    John Doe     .24          20            50           60
      2         222    John Doe     .14          10            30           70
      3         222    John Doe     .11          10            30           70


I want this
   
     
    222  John Doe  DV1ScoreMonth1  DV1ScoreMonth2  DV1ScoreMonth3  DV2ScoreMonth1...


Thoughts?  Any help would be VERY appreciated!

Thanks!

DM

[David Marso]

AGGREGATE?
However!!!
TSA in SPSS likes its data in LONG format!!!
----

dmunneke wrote

I have a large dataset in long format (multiple records per person for each observation).  I'd like to restructure the data in wide format in a time series format.   I'd like to use tenure as the index variable and I'd like to categorize it into 1 month segments across 24 months.  

I can recode tenure to do this but because there are infrequent occurrences where multiple observations occur in one month, it will create duplicate values and fail when restructuring.

It would probably be ideal to average the multiple observations so I have a single score per month and then recode the data for purposes of an index variable.

Data is a format like this

  Record #   ID      Name       Tenure    DV1Score  DV2Score  DVScore3
      1         222    John Doe     .24          20            50           60
      2         222    John Doe     .14          10            30           70
      3         222    John Doe     .11          10            30           70


I want this
   
     
    222  John Doe  DV1ScoreMonth1  DV1ScoreMonth2  DV1ScoreMonth3  DV2ScoreMonth1...


Thoughts?  Any help would be VERY appreciated!

Thanks!

DM