Date:         Fri, 28 Jun 2002 17:21:46 -0400
Reply-To:     Nathaniel_Wooding@DOM.COM
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Nathaniel Wooding <Nathaniel_Wooding@DOM.COM>
Subject:      Re: transposing data (mult. obs to one obs)
Comments: To: April Bebee-Sanders <SANDERA@CCF.ORG>
Content-type: text/plain; charset=us-ascii

April

The following code appears to do what you ask.

Nat Wooding

data;
informat id 2. dt datetime. tst $3. ;
input id   dt            tst            val               nml;

cards;
1   02May99:3:13          wbc             2                 5.0
1   02May99:3:13          rbc             5                 7.0
1   02May99:4:30          eos            3                   .8
1   02May99:5:50          hct             11                15
1   02May99:6:50          hgb            8                   9
1   02May99:6:50          bili           2                   3
1   02May99:7:00          bun            9                   5
data trans;
    set;
     by id;
     if first.id then count=0;
     count+1;
proc transpose out=trans;
  by id count;

data trans;
     set trans;
     _name_=compress(_name_||count);
     drop count;

proc transpose out=trans;
     by id;
      id _name_;
      var col1;
proc print;run;



                    April
                    Bebee-Sanders        To:     SAS-L@LISTSERV.UGA.EDU
                    <SANDERA@CCF.O       cc:
                    RG>                  Subject:     transposing data (mult. obs to one obs)
                    Sent by:
                    "SAS(r)
                    Discussion"
                    <SAS-L@LISTSER
                    V.UGA.EDU>


                    06/28/02 04:23
                    PM
                    Please respond
                    to April
                    Bebee-Sanders






Greetings SAS L;

I have sas data that has multiple records per person.  I am attempting to
create a dataset  with just one record per person using proc transpose(for
now). In the current data  each id can have up to 7 observations; one for
each type of blood test taken with it's corresponding date and lab value.

This is how the data looks now:

id   dt                             tst            val               nml
1   02May99:3:13          wbc          2                 5.0
1   02May99:3:13          rbc            5                 7.0
1   02May99:4:30          eos           3                   .8
1   02May99:5:50          hct           11                15
1   02May99:6:50          hgb            8                   9
1   02May99:6:50          bili              2                   3
1   02May99:7:00          bun            9                   5

I'd like to get this to one obs per id, for example:

id      dt1                     tst1   val1  nml1  dt2
tst2  val2     nml2    .......
1   02May99:3:13      wbc     2      5.0    02May99:3:13    rbc    5
7.0

I've tried various constructs of this statement; using all vars and using
just tst on the var statement but the output produces only a transposed tst
variable.  Is this something I would need to do in multiple proc transposes
or should I be using a different approach altogether.

proc transpose data = a out = b (drop = _:)  prefix = tst;
  var tst ;   copy  dt val nml;
    by id;
run;

Is there also a quick way to relabel the tst column with the actual test
name,  tst1 => wbc, tst2 =>rbc...

Thanks,
    April

Have a great week-end!