Date:         Wed, 28 Jul 2004 12:18:37 +0200
Reply-To:     "Groeneveld, Jim" <jim.groeneveld@VITATRON.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Groeneveld, Jim" <jim.groeneveld@VITATRON.COM>
Subject:      Re: Reshaping data
Comments: To: "Kevin F. Spratt" <kevin-spratt@UIOWA.EDU>
Content-Type: text/plain; charset="iso-8859-1"

Hi Kevin,

Try (tested):

%MACRO Restruct (NrOuts=, NrIcodes=);
  %LOCAL I J;
DATA TestData;
  INFILE "Kevin.dat" MISSOVER;
  INPUT id i_code
  %DO J = 1 %TO &NrOuts;
    Out&J : $2.
  %END;
  ; * end of INPUT;
RUN;

PROC PRINT DATA=TestData; RUN;

DATA Restruct (DROP = I J N icode
  %DO J = 1 %TO &NrOuts;
    Out&J
  %END;
  ); * end of DATA;
  SET TestData (RENAME=(I_code=Icode));
    BY Id; * must be sorted according to ID ;

  ARRAY I_Code (&NrIcodes);
  ARRAY Out_ (&NrOuts) $2 Out1 - Out&NrOuts;
  ARRAY Out (&NrOuts, &NrIcodes) $2
    %DO J = 1 %TO &NrOuts;
      %DO I = 1 %TO &NrIcodes;
        Out&J&I
      %END;
    %END;
  ; * end of array;

  RETAIN I_code Out; * array elements;

  IF (FIRST.ID) THEN DO; * initialize / reset to missing;
    N = 0; * (reset I_code sequence number for ID);
    DO I = 1 TO &NrIcodes;
      I_code(I) = '';
      DO J = 1 TO &NrOuts;
        Out(J,I) = '';
      END;
    END;
  END;

  N + 1; * increment I_code sequence number for ID;

  I_code(N) = Icode;
  DO J = 1 TO &NrOuts;
    Out(J,N) = Out_(J);
  END;

  IF (LAST.ID) THEN OUTPUT;
RUN;

%MEND Restruct;

%Restruct (NrOuts=3, NrIcodes=8);

PROC PRINT DATA=Restruct; RUN;

Regards - Jim.
--
.  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .

Jim Groeneveld, MSc.
Biostatistician
Science Team
Vitatron B.V.
Meander 1051
6825 MJ  Arnhem
Tel: +31/0 26 376 7365
Fax: +31/0 26 376 7305
Jim.Groeneveld@Vitatron.com
www.vitatron.com

My computer says 'beep'. What's wrong with that? It's talking too much!

[common disclaimer]


-----Original Message-----
From: Kevin F. Spratt [mailto:kevin-spratt@UIOWA.EDU]
Sent: Wednesday, July 28, 2004 00:25
To: SAS-L@LISTSERV.UGA.EDU
Subject: Reshaping data


I am looking at a relatively large trauma data base that is organized to
have separate records
for each injury for each person with a common id with multiple tables for
each case.

I haven't figured out if there are variables across tables that will link
specific ids to
procedures, but I'm hoping so.  For example if id 1 has 2 i_codes 1 and 2
and one data set
has two records for the person but another data set has one record for the
person, I'm hoping
that I can figure out which the of the two records in data set one the
information in data
set two should be matched two.  .

For simplicity assume one tables, now SAS data set one.

Suppose data set one is structured as:  (the number of i_codes per ID can vary)

id       i_code out1 out2 out3
1          1      y1   y2  y3  /* y indicating some outcome score */
1          2      y4   y5  y6
1          3      y7   y8  y9
2          3      y1   y2  y3
.
.
.
12345678   8      y1   y2  y3  /* yes some of these tables have more than a
million records */

I would like this data set structured as:

id        i_code1 out11 out21 out31 i_code2 out12 out22 out32 i_code3 out13
out23 out33
1             1     y1   y2    y3     2      y4    y5    y6      3     y7
  y8     y9
2             3     y1   y2    y3     .       .     .     .      .      .
   .     .
12345678      8     y1   y2    y3     .       .     .     .      .      .
   .     .

In real live, the number of variables for some tables are substantially
more than the three I
have indicated above.


Any suggestions welcomed.




______________________________________________________________________
                                                                          __________________
Kevin F. Spratt,
Ph.D.                              |                               |
Iowa Testing Programs            (ITP)     (                                 )
University of Iowa Back Care (UIBC)   \                                \
_  University
Iowa Spine Research
Center   (ISRC)    \                     *             \  of Iowa
224-D Lindquist
Center                             \                                   /
Hawkeyes
The University of Iowa                              |_________________   )
Iowa City, Iowa
52242                                                             V
(319) 335-5572         (voice-Lindquist)
(319) 335-9921         (voice-Westlawn)
(319) 335-6399         (fax)
(319) 530-8254         (cell & Voice mail)
Kevin-Spratt@Uiowa.edu (e-mail)
_______________________________________________________________________