Transpose data by Proc Transpose and Data Step Array

[Datura]

In SAS, if we want to reshape data from long to wide, we have 2 options: Proc Transpose and DATA step array. Proc Transpose is easy but it is NOT flexible. Array method require more syntax, however, it is much more flexible than Proc Transpose. Therefore, sometimes we need to use Array to reshape data when Proc Transpose fails to work. Below code illustrate the 2 methods

1. Proc Transpose: it is easy to use with concise syntax.

 Data AAA;
 Input Sales_ID $ Region $  Turnover ;
 cards;
 X A 7
 Y C 8
 Y D 5
 Z B 6
 Z M 333
 ;
 run;
 Proc sort data=AAA;   By sales_ID;   run;

 Proc transpose data=AAA  out=BBB (drop=_Name_)   prefix=Region_ ;
 ID region;
 Var  turnover;
 By Sales_ID;
 run;

Below is the output:
Obs Sales_ID Region_A Region_C Region_D Region_B Region_M
1 X 7 . . . .
2 Y . 8 5 . .
3 Z . . . 6 333

2. Array method: flexible and customizable. Please note that we must use RETAIN statement to fulfill our task.

 proc sort data=AAA; by Sales_ID ; run;

 data BBB;
 set AAA;
 by Sales_ID;
 if      Region='A'  then n=1;
 else if Region='B'  then n=2; 
 else if Region='C'  then n=3;
 else if Region='D'  then n=4;
 else if Region='M'  then n=5;
 else n=999;

 array region_t(*) region_A region_B region_C region_D region_M;
 retain region_A region_B region_C region_D region_M;
 if first.Sales_ID then do i=1 to dim(region_t);
 region_t(I)=.  ;
 end;

 region_t(n)=Turnover;
 if last.Sales_ID;
 drop n i Turnover region;
 run; 

It produces the identical results as the Proc Transpose.

• This discussion was modified 3 years, 10 months ago by  Datura.