saslearn007@gmail.com wrote: > Hello All, > > I have a question on transpose > > I have a sample data like this > > > a trt1 ae1 2 > a trt1 ae2 5 > a trt2 ae1 3 > a trt2 ae2 4 > > b trt1 ae1 12 > b trt1 ae2 15 > b trt2 ae1 13 > b trt2 ae2 14 > > > I want this to be transformed as follows > > trt1 trt2 > a ae1 2 3 > a ae2 5 4 > b ae1 12 13 > b ae2 15 14 > > > Any ideas using proc transpose > > I can think of transposing both separately but can I do at once. I > have just given example but I have a very big data set like this

It is unfortunate that you need to have a transpose where sortedness of exposure (ae1/ae2) changes.

Most likely you will need to develop a view that does within group rearrangement and output the desired structure when the last row of the group is encountered.

-------------------------------------------------- data learn; input group $ treatment $ exposure $ result; datalines; a trt1 ae1 2 a trt1 ae2 5 a trt2 ae1 3 a trt2 ae2 4 b trt1 ae1 12 b trt1 ae2 15 b trt2 ae1 13 b trt2 ae2 14 run;

* your table sounds like it is too big to be sorted;

proc sort data=learn out=learnSorted; by group exposure; run;

* which is unfortunate, because a sorted table could be easily transposed;

proc transpose data=learnSorted out=learnTransposed(drop=_name_); by group exposure; id treatment; var result; run;

* since the exposure is unsorted to start and needs to be sorted * a more complicated process is undertaken;

data learnTransposed2 / view=learnTransposed2; set learn; by group;

* array for rearranging the results of * exposure treatment combination;

array XT [2,2] _temporary_;

if first.group then do; call poke (repeat('00'x, dim(XT,1)*dim(XT,2)-1),addr(XT[1,1])); end;

* the data processing methodology expects * a very specific set of exposure and treatment values, expressly thus, * exposure is ae{N} where N is 1 or 2; * treatment is trt{N} where N is 1 or 2;

Xindex = input (substr(exposure,3,1),1.); Tindex = input (substr(treatment,4,1),1.);

XT[Xindex,Tindex] = result;

if last.group then do; do Xindex = 1 to 2; exposure = cats('ae',Xindex); array treatmnt trt1 trt2; do Tindex = 1 to 2; treatmnt[Tindex] = XT[Xindex,Tindex]; end; output; end; end; keep group exposure trt1 trt2; run;

proc compare base=learnTransposed compare=learnTransposed2; run;

-- Richard A. DeVenezia http://www.devenezia.com/