🔄 Mastering PROC TRANSPOSE in SAS: Convert Rows to Columns and Vice Versa

PROC TRANSPOSE is a powerful SAS procedure used to reshape data by converting rows into columns or columns into rows. Whether you're preparing datasets for reporting or statistical analysis, PROC TRANSPOSE can simplify your task with just a few lines of code.

In this blog, you'll learn:

• What is PROC TRANSPOSE?
• When and why to use it
• Syntax and options
• Multiple real-world examples
• Tips and tricks for efficient use

---

🔍 What is PROC TRANSPOSE in SAS?

PROC TRANSPOSE is used to pivot data—turning variables (columns) into observations (rows), or vice versa. It's especially useful for:

• Summarizing repeated measures
• Restructuring long or wide datasets
• Preparing data for visualizations or modeling

---

📚 Basic Syntax of PROC TRANSPOSE

proc transpose data=input_data out=output_data <options>;
   by variable(s);         * Optional: groups data;
   id variable;            * Optional: names for new columns;
   var variable(s);        * Variables to transpose;
run;

---

📌 Key Options Explained

Option	Description
BY	Groups data before transposing
VAR	Specifies variables to transpose
ID	Uses values of a variable as new column names
NAME=	Renames the default _NAME_ column
LABEL=	Renames the default _LABEL_ column

---

✅ Example 1: Transposing Without BY or ID

🔹 Input Data

data sales;
   input Quarter $ Sales;
   datalines;
Q1 100
Q2 120
Q3 140
Q4 160
;

🔹 Transpose Code

proc transpose data=sales out=sales_transposed;
   var Sales;
run;

🔹 Output

NAME	COL1	COL2	COL3	COL4
Sales	100	120	140	160

---

✅ Example 2: Transposing with ID to Use Column Names

proc transpose data=sales out=sales_wide;
   id Quarter;
   var Sales;
run;

🔹 Output

NAME	Q1	Q2	Q3	Q4
Sales	100	120	140	160

---

✅ Example 3: Transpose with BY Grouping

🔹 Input Data

data student_scores;
   input Student $ Subject $ Score;
   datalines;
John Math 85
John English 78
John Science 92
Anna Math 88
Anna English 91
Anna Science 84
;

🔹 Transpose Code

proc sort data=student_scores;
   by Student;
run;

proc transpose data=student_scores out=scores_wide;
   by Student;
   id Subject;
   var Score;
run;

🔹 Output

Student	English	Math	Science
John	78	85	92
Anna	91	88	84

---

✅ Example 4: Transposing Multiple Variables

data patient_data;
   input ID $ Visit $ Height Weight;
   datalines;
P1 Visit1 170 65
P1 Visit2 171 66
P2 Visit1 160 60
P2 Visit2 161 61
;

🔹 Code

proc sort data=patient_data;
   by ID;
run;

proc transpose data=patient_data out=trans_height prefix=Height_;
   by ID;
   id Visit;
   var Height;
run;

proc transpose data=patient_data out=trans_weight prefix=Weight_;
   by ID;
   id Visit;
   var Weight;
run;

data final_transposed;
   merge trans_height trans_weight;
   by ID;
run;

🔹 Output

ID	Height_Visit1	Height_Visit2	Weight_Visit1	Weight_Visit2
P1	170	171	65	66
P2	160	161	60	61

---

🧠 Tips for Using PROC TRANSPOSE

• Always SORT your data before using BY.
• Use the PREFIX= option to create meaningful column names.
• Combine multiple transpositions for complex reshaping.
• Use NAME= and LABEL= to rename the default variables _NAME_ and _LABEL_.

---

🔎 When to Use PROC TRANSPOSE

Use Case	PROC TRANSPOSE?
Convert long to wide format	✅ Yes
Convert wide to long format	✅ Yes (reverse)
Reshape repeated measures	✅ Yes
Change actual data values	❌ No
Merge multiple reshaped tables	✅ Yes

---

📈 Conclusion

PROC TRANSPOSE is an essential tool in any SAS programmer’s toolkit. It simplifies the process of reshaping data for reporting, analysis, and modeling. With a good understanding of BY, ID, and VAR options, you can handle almost any data transformation challenge.