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