Data Manipulation [12-18]
Transposing Data Set
Transposing data set is also a common data manipulation task.
Let's take a look at a very simple example.
The REV data set contains the monthly revenue in the first quarter of 2014.
The revenue information is currently stored under 1 variable: Revenue.
Let's take a look at how you can transpose the Revenue variable into 3 different columns.
Example
Proc Transpose data=rev out=t_rev;
id month;
var revenue;
Run;
The monthly revenue is split into three columns!
Code
Proc Transpose consists of mainly 3 parts:
1. OUT option
The OUT option allows you to create a "transposed" data set.
In our example, the transposed data set is T_REV.
2. ID statement
The ID statement specifies the variable to be transposed as the column headers
In our example, the ID variable is MONTH.
The Monthly information is transposed into the column headers.
3. VAR statement
The VAR statement specifies the results to be transposed.
In our example, the VAR variable is Revenue.
The revenue information is transposed as a result.
Exercise
Copy and run the TEMP data sets from the yellow box below:
Copy and run the TEMP data sets from the yellow box below:
THe TEMP data set contains the 24-hour temperature on a particular day.
Transpose the Temperature into 24 different columns.
Calculate the average temperature before (<12pm) and after noon (>12pm).
Create any variable or data set if needed.
Need some help?
HINT:
Properly identify the ID variable and the VAR variable before you transpose the data set.
SOLUTION:
Proc Transpose Data=Temp Out=Temp2;
Id Hours;
Var Temperature;
Run;
Data Temp3;
Set Temp2;
Format Before12 After12 3.0;
Before12 = mean(of H0-H11);
After12 = mean(of H12-H23);
Run;
Fill out my online form.