SAS : Transpose Multiple Variables

Live Online Training : SAS Programming with 50+ Case Studies

- Explain Programming Concepts in Simple English
- Live Projects
- Scenario Based Questions
- Job Placement Assistance
- Get 10% off till Sept 25, 2017
- Batch starts from October 8, 2017

This tutorial explains how to reshape data by converting multiple variables from long format to wide format. This tutorial explores 'Double Transpose' SAS method which is used to transpose multiple variables. It's a very handy technique to solve this challenge. Otherwise, it would be a very time consuming task. This post includes a sample data set which is used for demonstrating the method for transposing more than one variable.

Create a sample data set

The code below creates a data set named 'temp' which constitutes five variables - ID, TIME, X1, X2, X3.
data temp;
input ID time $ x1-x3;
cards;
1 Y1 85 85 86
1 Y2 80 79 70
1 Y3 78 77 87
2 Y1 79 79 79
2 Y2 83 83 85
;
run; 
SAS : Reshape Data

We want to see our output look like the table shown in the image below -
SAS : Transpose Variables
Solution :
proc sort data=temp;
by ID time;
run;

proc transpose data=temp out=out1;
by ID time;
var x1-x3;
run;
proc transpose data=out1 delimiter=_ out=new2(drop=_name_);
by ID;
var col1;
id _name_ time;
run;
How it works :

1. First step, it is required to sort the variables 'ID' 'time' before using them in BY statement in PROC TRANSPOSE. It is done with PROC SORT.

2. In the first transpose of the above code, we are telling SAS to store information of all the variables in a single variable and the respective values in the another variable. And we do not want to transpose variables ID and Time. Hence, we have specified them in BY statement. See the following output generated in this step -

ID Time _NAME_ COL1
1 Y1 x1 85
1 Y1 x2 85
1 Y1 x3 86
1 Y2 x1 80
1 Y2 x2 79
1 Y2 x3 70
1 Y3 x1 78
1 Y3 x2 77
1 Y3 x3 87
2 Y1 x1 79
2 Y1 x2 79
2 Y1 x3 79
2 Y2 x1 83
2 Y2 x2 83
2 Y2 x3 85

3. Second transpose further reshapes the data from long to wide format. It generates the desired output. The delimiter= option is used to place a separator that separates values of two ID variables.

SAS Tutorials : 100 Free SAS Tutorials

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has close to 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like retail and commercial banking, Telecom, HR and Automotive.


While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

1 Response to "SAS : Transpose Multiple Variables"

Next → ← Prev