|
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
| 
