Reshaping Data with PROC TRANSPOSE in SAS

Welcome back to Mindful Data Minds! In this session, we’ll explore PROC TRANSPOSE, a SAS procedure used to restructure datasets — turning rows into columns or columns into rows. This is especially useful when preparing data for reporting or analysis.

Watch the Full Tutorial

What You Will Learn

How to use PROC TRANSPOSE to restructure datasets.
How to transpose numeric and character variables.
How to rename transposed columns.
The difference between BY and ID statements.
How to drop unnecessary columns for cleaner output.

Why Use PROC TRANSPOSE?

Sometimes data is not in the format we need. For example:

Original Dataset

RollNo   Name   Subject   Marks
1        A      English   85
1        A      Math      90
1        A      Science   88

Desired Format

Subject   A
English   85
Math      90
Science   88

PROC TRANSPOSE helps us achieve this transformation.

General Syntax

proc transpose data=&lt;dataset> out=&lt;new_dataset>;
   var &lt;variables>;
   by &lt;grouping_variable>;
   id &lt;variable>;
run;

DATA= → Input dataset
OUT= → Output dataset
VAR → Variables to transpose
BY → Groups data before transposing
ID → Creates column names from variable values

Default Behavior

By default, PROC TRANSPOSE transposes numeric variables:

proc transpose data=a;
run;

Converts numeric columns into rows.

Transposing Specific Variables

To transpose character variables as well:

proc transpose data=a;
   var name subject marks;
run;

Produces rows for Name, Subject, and Marks.

Renaming Transposed Column

By default, SAS creates _NAME_. You can rename it:

proc transpose data=a out=b name=transpose_column;
   var name subject marks;
run;

Column _NAME_ becomes transpose_column.

Using BY Statement

To transpose by groups (e.g., Subject):

proc transpose data=a out=b;
   by subject;
   var marks;
run;

Note: Data must be sorted by the BY variable.

If not sorted, use notsorted:

proc transpose data=a out=b;
   by subject / notsorted;
   var marks;
run;

Dropping Default Columns

To remove _NAME_:

data b;
   set b(drop=_NAME_);
run;

Using ID Statement

To use student names as column headers:

proc transpose data=a out=b;
   by subject;
   id name;
   var marks;
run;

Produces subjects as rows and student names as columns.

Next Step

Continue learning by exploring the next tutorial in this series. Also subscribe to get notified about new lessons.

Proc Feq

Loops in SAS

Have a Question?

Drop your doubts in the comments below or contact us.