Sentry Page Protection

Please Wait...

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.

Data Rev; Input Month $ Revenue; Datalines; Jan 253000 Feb 460000 Mar 365000 ; Run;

Data set: REV

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;

Data set: T_REV

​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. 

Before Transpose

The Monthly information is transposed into the column headers.

After Transpose

3. VAR statement

​The VAR statement specifies the results to be transposed.

In our example, the VAR variable is Revenue. 

Before Transpose

The revenue information is transposed as a result.

After Transpose

---

Exercise

Copy and run the TEMP data sets from the yellow box below:

Data TEMP; Input Hours $ Temperature; Datalines; H0 15 H1 13 H2 13 H3 14 H4 14 H5 14 H6 13 H7 14 H8 14 H9 14 H10 14 H11 15 H12 16 H13 17 H14 16 H15 16 H16 16 H17 15 H18 15 H19 14 H20 14 H21 14 H22 14 H23 15 ; run;

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.

Next

---

Need some help? 

Get Hint

HINT:
Properly identify the ID variable and the VAR variable before you transpose the data set.

Get Solution

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.

---