2 min read

SAS Snippet: Reshape Data Using SAS DoW Loop (From Long to Wide)

F_Carpenter_cover.indd

In Art Carpenter’s latest book, Carpenter’s Guide to Innovative SAS Techniques, a data step approach to transpose data (from long to wide) works like (Ch2.4.2):

Art_SAS_transpose

data tst;
    input type $ grp value $3.;
datalines;
A 1 a
A 2 aa
A 3 aaa
B 1 b
B 2 bb
B 3 bbb
C 1 c
C 2 cc
C 3 ccc
;

data art(keep=type grp1-grp3);
   set tst;
   by type;
   retain grp1-grp3 ;
   array grps {3} $ grp1-grp3;
   if first.type then do i = 1 to 3;
      grps{i} = “ “;
   end;

   grps{grp} = value;
   if last.type then output art ;
run;

And such logic can be best demonstrated by a DoW Loop:

data dow(keep=type grp1-grp3);
     array grps[3] $ grp1-grp3;
     do n = 1 by 1 until(last.type);
        set tst;
        by type;
        grps[grp]=value;
     end;
run;

/Note/

1. The traditional PROC TRANSPOSE approach:

proc transpose data=tst
               out=trans(drop=_:)
               prefix=grp;
   by type;
   id grp;
   var value;
run;

2. Why use data step approach (both Art and DoW) to transpose data against the TRANSPOSE procedure:

  • it’s much faster since data step array used
  • save codes when complex transformation needed
  • last but not least, it’s cool!

3. Arthur Tabachneck maintains a general data step transposing macro, %transpose and you can call it like:

%transpose(data=tst, out=mac,
            by=type, var=value,
            id=grp)