```Date: Fri, 31 May 2002 09:36:36 -0700 Reply-To: "Huang, Ya" Sender: "SAS(r) Discussion" From: "Huang, Ya" Subject: Re: Multiple proc transpose steps -> simpler solution Comments: To: "Femminella, Oliver" 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 ```

Back to: Top of message | Previous page | Main SAS-L page