Date:         Fri, 6 Feb 1998 09:56:07 +0100
Reply-To:     Jan Bergers <J.Bergers@MEMIC.UNIMAAS.NL>
Sender:       "SPSSX(r) Discussion" <SPSSX-L@UGA.CC.UGA.EDU>
From:         Jan Bergers <J.Bergers@MEMIC.UNIMAAS.NL>
Organization: University of Maastricht,the Netherlands
Subject:      Re: rows to columns
Content-Type: text/plain; charset=us-ascii

Yuan Zhang wrote:
 
> >> We have hospitalisation data that lists a number of diagnoses per case
> >> (patient).  Each diagnosis is a record in the file but we want each
> patient
> >> to be a record in the file with a number of diagnosis variables (e.g.
> dx1,
> >> dx2, dx3).  In other words we want the file set up to go from:
> >>
> >> ID#    DATE        DX
> >>
> >> 111    01/01/95    410
> >> 111    01/01/95    435
> >> 111    01/01/95    676
> >> 111    28/09/95    412
> >> 222    14/02/95    292
> >> 222    14/02/95    810
> >> 333    23/06/95    304
> >> 333    23/06/95    902
> >> 333    30/12/95    103
> >>
> >> To this:
> >>
> >> ID#    DATE        DX1    DX2    DX3
> >> 111    01/01/95    410    435    676
> >> 111    28/09/95    412    .        .
> >> 222    14/02/95    292    810    .
> >> 333    23/06/95    304    902    .
> >> 333    30/12/95    103    .        .
> >>
> >> Any suggestions?  Many thanks in advance.
>
> You need transpose the data set (or flip).
 
  I do not think that transpose will work.
You need to define a vector with length=max number of records of any one
patient
You need to fill the vector with all the diagnoses of each patient. Then you
need
to do a aggregate to get a file with one record per patient. Greetings, Jan
Bergers
 
PS: this is in my opinion a common problem with data coming out of relational
databases,
where repeating fields or groups of fields are generally out-normalized. It
would be nice
if SPSS had some sort of provision for this kind of transformation in its
menu.
Also researchers have to be careful with this type of data, in defining
properly their
unit of interest.