Date:         Fri, 31 May 2002 09:36:36 -0700
Reply-To:     "Huang, Ya" <ya.huang@PFIZER.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Huang, Ya" <ya.huang@PFIZER.COM>
Subject:      Re: Multiple proc transpose steps -> simpler solution
Comments: To: "Femminella, Oliver" <Oliver.Femminella@HALIFAXCETELEM.COM>
Content-Type: text/plain; charset="iso-8859-1"

Here is a data step solution. I wonder if Paul Dorfman
can cut it to one step.

data xx;
input id var1-var3  x$ y$ z$;
cards;
  1  2 3 4  a m p
  2  3 4 5  b n q
  2  4 5 6  c o r
;

* get the max number of rows for id;
proc sql noprint;
select max(n) into :nby
from ( select count(id) as n from xx group by id)
;

%put &nby;

data yy (drop=i);
 do i=1 by 1 until(last.id);
  set xx end=end;
  by id;
  array xx(&nby) $ x1-x%cmpres(&nby);
  array yy(&nby) $ y1-y%cmpres(&nby);
  array zz(&nby) $ z1-z%cmpres(&nby);
  xx(i)=x;
  yy(i)=y;
  zz(i)=z;
 end;
output;

data xx (drop=i);
 merge xx yy;
by id;
** the following is not necessary, if you don't mind x1-xn,;
** y1-yn etc. to show up in rest part of by group;
  array xx(&nby) $ x1-x%cmpres(&nby);
  array yy(&nby) $ y1-y%cmpres(&nby);
  array zz(&nby) $ z1-z%cmpres(&nby);
if not first.id then do;
  do i=1 to &nby;
  xx(i)='';
  yy(i)='';
  zz(i)='';
  end;
end;
run;

proc print heading=v noobs ;
run;
-------------------------
     v    v    v
     a    a    a
i    r    r    r                   x    x    y    y    z    z
d    1    2    3    x    y    z    1    2    1    2    1    2

1    2    3    4    a    m    p    a         m         p
2    4    5    6    c    o    r    b    c    n    o    q    r
2    4    5    6    c    o    r

Kind regards,

Ya Huang

-----Original Message-----
From: Femminella, Oliver [mailto:Oliver.Femminella@HALIFAXCETELEM.COM]
Sent: Friday, May 31, 2002 3:01 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Multiple proc transpose steps -> simpler solution


Dear SAS-Listers,

I have a data set (_DATA_) in the following format:

id_var var1-varN  x  y  z
  1      xxx - xxx   a m p
  2        :      :     b n q
  2        :      :     c o r
  :       :      :     :  : :

indexed by a primary key / identifier variables id_var.

Using the proc transpose I wanted to copy values form
several data fields (x,y, and z) so at to obtain the
desired format for the data set:


id_var var1 varN  x    y    z    x1   x2   xN    y1   y2   yN  z1   z2    zN
  1      xxx  xxx    a   m    p     a    .      .      .     .     .    .
.      .
  2        :      :    b   n    q      b    c     .      n    m    .    q
r      .
  2        :      :    c   o    r       .     .     .      .     .    .
.     .      .
  :       :      :    :    :    :


i.e. all values for x, y and z for the same id_var on the same row.

I 'm able to achieve this with 3 proc transpose executions:

proc transpose data=_DATA_
                      out=_DATA_
                   prefix=x LABEL=lbl let ;
by id_var;
copy var1-varN x y z
var x;
run;

proc transpose data=_DATA_
                      out=_DATA_
                   prefix=y LABEL=lbl let ;
by id_var;
copy var1-varN x x1-xN y z
var y;
run;

proc transpose data=_DATA_
                      out=_DATA_
                   prefix=z LABEL=lbl let ;
by id_var;
copy var1-varN x x1-xN y y1-yN z
var z;
run;

Can this be done in a single one ?

An alternative solution, I know, would be to sort the data by a
secondary key and use the lag function, retain statement or  by
group processing (e.g. first.id_var last.id_var), but proc
transpose should be able to do this processing, yes ?

Any suggestions ?

Cheers !

Oliver

--
Dr. Oliver Femminella
Decision support systems
Halifax Cetelem Credit Ltd.
Leo House, Railway Approach,
Wallington, Surrey SM6 0XD, U.K.
T: +44 (0)20 8254 7234
M: +44 (0)7929 981 950
F: +44(0)20 8669 8857
E: oliver.femminella@halifaxcetelem.com