Proc Transpose

SAS Day 42: Proc Transpose

Proc Transpose is a powerful procedure for reshaping the data structures (i.e.Row observations to Column Variables or Vice Versa). 

Key options: VAR, BY, ID, Prefix, name

Basic Proc Transpose Syntax:

proc transpose data=adsl out=a(rename=(sex=label)) prefix=col; 
id trt1; 
var count; 
by x; 
run;

Monsterkoi / Pixabay

A classical example is to select the treatment group in ADSL by sex.

Current Data Structure: 

Sample Transposed Data Structure 1: separate treatment count  BY Label 

Sample Code 1:

proc sort data=c nodupkey;
by label treatment;
run;

proc transpose data=c out=d(drop=_name_) prefix=treat; 
id treatment; 
var count; 
by label ;
run;

Sample Transposed Data Structure 2: Separate sex count BY treatment

Sample Code 2:

proc sort data=c nodupkey;
by treatment label;
run;

proc transpose data=c out=d(drop=_name_) prefix=sex; 
id label; 
var count; 
by treatment ;
run;

Note: the transpose data need to sort in the “by” variable order before applying Proc Transpose.

Little Trick: we use ID option to prevent the case when a “BY” group is empty.

🐸Happy SAS Coding!