Date:         Tue, 14 Oct 2003 19:24:37 -0600
Reply-To:     Jack Hamilton <JackHamilton@FIRSTHEALTH.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jack Hamilton <JackHamilton@FIRSTHEALTH.COM>
Subject:      Re: proc transpose question
Comments: To: pmundell@ATTGLOBAL.NET
Content-Type: text/plain; charset=us-ascii

If you know in advance the maximum value of trait, you don't need a PROC
TRANSPOSE at all, and you can do it in one data step:

=====
data test;
   infile cards;
   input  @1  id     $7.
          @12 trait  1.
          @25 n_rec  1.
          @38 mean   3.
          @50 sol    6.
          @61 inb    4.;
cards;
1950011    1            1            0           -0.015     1
1950011    2            1            3.4         0.0798     1
1950011    3            1            .78         -0.043     1
1989269    1            1            0           -0.032     1.06
1989269    2            1            0           -0.0977    1.06
1989269    3            1            .98         0.025      1.06
;;;; run;

%let MaxRecs = 3;

data new;

   set test;
      by id;

   array nrecs  n_rec1-n_rec&MaxRecs.;
   array means  mean1-mean&MaxRecs.;
   array sols   sol1-sol&MaxRecs.;

   retain n_rec1-n_rec&MaxRecs mean1-mean&MaxRecs. sol1-sol&MaxRecs.;
   drop n_rec mean sol trait;

   if first.id then
      do over nrecs;
         nrecs = .;
         means = .;
         sols = .;
      end;

   _i_ = trait;

   nrecs = n_rec;
   means = mean;
   sols = sol;

   if last.id then
      output;

*****; run;

proc print;

*****; run;
=====

You should probably convert this to explicit array form, rather than
the now-undocumented implicit array form used here.  You could also use
a DOW loop and remove the RETAIN statement.




--
JackHamilton@FirstHealth.com
Manager, Technical Development
Metrics Department, First Health
West Sacramento, California USA

>>> "Paul Mundell" <pmundell@ATTGLOBAL.NET> 10/14/2003 5:22 PM >>>
I've got a fairly tricky proc transpose question.  If I have a SAS data
set
such as:

    id                trait        n_rec    mean            sol
inb
    1950011    1            1            0                -0.015
1
    1950011    2            1            3.4             0.0798
   1
    1950011    3            1            .78            -0.043
1
    1989269    1            1            0            -0.032
1.06
    1989269    2            1            0            -0.0977
1.06
   1989269    3             1            .98        0.025
1.06

Will proc transpose allow me to get this data into a form with one row
per
id number?

I.e.:
    id           inb     n_rec1    mean1    sol1            n_rec2
mean2
sol2            n_rec3 ....
1950011    1        1            0            -0.015       1
3.4
0.0798        .....
1989269    1.06    1            0        -0.032        1
        0        -0.0977         ......

Note, in this example, the variable inb stays constant for each value
of id,
so it would be copied and not transposed, but my challenge is to
assemble
the 3 traits and their corresponding values (n_rec mean sol) into one
row
per id value.

I know this can be done in a couple of data steps, but can it be done
in one
step using proc transpose?
Thanks for your help, Paul Mundell