PROC TRANSPOSE- Reshape table Long to Wide; Wide to Long

In order to reshape table from Long to wide and from wide to long in SAS we will be using PROC TRANSPOSE. PROC TRANSPOSE statement in SAS is explained below with which we have depicted an example to reshape table from Long to wide in SAS. Another example to reshape to wide to long in SAS is shown below.

Syntax – PROC TRANSPOSE

The general format of PROC TRANSPOSE is:

• The BY variables determines the row structure of the transposed dataset.
• Data must be sorted on BY variables before running PROC TRANSPOSE.
• ID variables become the column of the transposed dataset.
• VAR – These are the values that will appear in the cells of the transposed variables.

So we will be using EMP_DET Table in our example

Simple proc transpose in SAS – long to wide

We will see an example of simple PROC TRANSPOSE Below, Which converts long to wide in SAS.

• Step 1: Sort by BY variable. Here BY variable is employee.
• Step 2: Mention PROC TRANSPOSE .
  • BY Variable (employee variable) will become the Row of the transposed table
  • ID Variables becomes column of the transposed data
  • VAR becomes values inside.
  • Prefix is the values which comes as the prefix of the transposed column

/* Simple proc transpose - long to wide */ 

proc sort data = EMP_DET; 
by Employee; 
run; 
 
proc transpose data = EMP_DET out=EMP_DET_transpose prefix=Y_; 
by Employee; 
id year; 
var salary_in_USD; 
run;

So the resultant transposed table from Long to wide will be

Simple proc transpose in SAS – Wide to Long

Let’s transpose Table 2 from Wide to Long

• Step 1: Mention PROC TRANSPOSE .
  • BY Variable (employee variable) will become the Row of the transposed table
  • ID Variables becomes column of the transposed data
  • VAR becomes values inside

/* Simple proc transpose - wide to long */ 

proc transpose data = EMP_DET_transpose out=EMP_DET_fin; 
by Employee; 
id _NAME_; 
var 'Y_2018'N 'Y_2019'N 'Y_2020'N;  
run; 

So the resultant transposed table from wide to long will be

Transpose with two columns – Long to Wide

Let’s see an example on how to transpose the Table 1 by two columns step by step

Step 1: Transpose based on first column : Transpose the table 1 by salary

/* transpose First column */ 
 
proc transpose data = EMP_DET out = EMP_DET_transpose_sal prefix=sal_; 
by Employee; 
id year;
var salary_in_USD; 
run;

Step 2: Transpose based on Second column – Transpose the table 1 by EXP_in_years

/* transpose Second column */ 

proc transpose data = EMP_DET out = EMP_DET_transpose_exp prefix=exp_; 
by Employee; 
id year; 
var EXP_in_years; 
run;

Step 3: Merge both the transposed dataset – Merge both the transposed data sets so that we perform transpose by two columns in roundabout way

/*merge both the transposed dataset */ 

data wide (drop = _name_); 
  merge EMP_DET_transpose_sal (in =aa) EMP_DET_transpose_exp; 
  by Employee; 
  if aa; 
run; 

Transpose – with two id variables (column variables)

We transpose the Table 1 by two variables i.e. by year and district

/* transpose - with two id variables -column variables */ 
 
proc transpose data = EMP_DET out= EMP_DET_transpose prefix= YearDist_; 
by Employee; 
id year District; 
var salary_in_USD; 
run; 

So the resultant table will be