transpose with multiple variables in SAS

3
votes

In SAS, I have column variables in sequence with corresponding column variables stacked side by side, such as the following:

 id lab1 lab2 lab3 dt1  dt2  dt3  bili1 bili2 bili3 alb1 alb2 alb3
 3  dx   sx   sx   2/04 2/06 3/08 x.x   x.x   x.x   x.x  x.x  x.x 
 4  dx   tx   tx   5/05 3/06 9/06 x.x   x.x   x.x   x.x  x.x  x.x

and I want to transpose to the following long format:

 id lab dt   bili alb
 3  dx  2/04 x.x  x.x
 3  sx  2/06 x.x  x.x
 3  sx  3/08 x.x  x.x
 4  dx  5/05 x.x  x.x
 4  tx  3/06 x.x  x.x
 4  tx  9/06 x.x  x.x

but I can't seem to manipulate this correctly, by putting 

var lab1-lab3 dt1-dt3 bili1-bili3 alb1-alb3;

there's actually no difference between that and

var lab1-alb3

so each column is treated independently, but I'd like to cluster these so SAS knows they have individual columns in the long dataset. Some forums have advised me to just perform the transpose several times and merge the output data. This seems inelegant, inefficient, hard to read, and tedious... more so than SAS is on its own. Is there no syntax to perform this in a single data or proc step?

2
sas

2 Answers

8
votes

You're not going to get there with PROC TRANSPOSE without a lot of extra work. Most of the time, Wide to Long is easier handled within the data step. In this case you can do it very easily with arrays.

data have;
input id lab1 $ lab2 $ lab3 $ dt1 $ dt2 $ dt3 $ 
      bili1 $ bili2 $ bili3 $ alb1 $ alb2 $ alb3 $;
datalines;
 3  dx   sx   sx   2/04 2/06 3/08 x.x   x.x   x.x   x.x  x.x  x.x 
 4  dx   tx   tx   5/05 3/06 9/06 x.x   x.x   x.x   x.x  x.x  x.x 
;;;;
run;

data want;
set have;
array labs lab1-lab3;
array dts dt1-dt3;
array bilis bili1-bili3;
array albs alb1-alb3;

do _t = 1 to dim(labs);
  lab = labs[_t];
  dt  = dts[_t];
  bili= bilis[_t];
  alb = albs[_t];
  output;
end;
keep id lab dt bili alb;
run;
0
votes

You could do it in a single data step, wrapped in a macro, like this:

%macro trans;

data want(keep = id lab dt bili alb);
set have;
%do i = 1 %to 3;
    lab = lab&i;
    dt = dt&i;
    bili = bili&i;
    alb = alb&i;
    output;
%end;
run;

%mend;

%trans; run;