Sentry Page Protection
Data Manipulation [13-18]
Tips for Proc Transpose
A common issue when transposing data set is having more than 1 observation in the ID group.
Let’s take a look at this example.
Data set: Q2
The Q2 data set contains the profit generated in the second quarter for two retail stores.
There are 2 profits per month. 1 for each store.
Now, let’s transpose the data set.
Example
Proc Transpose data=Q2 out=t_Q2;
Id month;
Var profit;
Run;
Data set: T_REV
The Log Window shows an error message!
[ERROR: The ID value "April" occurs twice in the input data set.]
The problem when transposing this data set is that there are more than 1 observation in each of the ID groups.
For example, there are 2 observations for the month of April.
The month of April will be transposed into a single column.
Having two observations will cause an error.
BY statement
This issue can be resolved by simply adding a BY statement.
Example
Proc sort data=Q2;
By Store;
Run;
Proc Transpose data=Q2 out=t_Q2;
Id month;
Var profit;
By store;
Run;
The BY statement allows you to transpose the data set for the two different stores.
With only 1 observation per store, SAS is able to transpose the data set without error:
Please note that the data set was sorted prior to being transposed.
This is needed when having the BY statement in Proc Transpose.
Exercise
Copy and run the FLIGHT data sets from the yellow box below:
Copy and run the FLIGHT data sets from the yellow box below:
The FLIGHT data set contains 4 variables:
- FROM: the departing city
- TO: the arriving city
- AIRLINE: the airline
- PRICE: the price of the flight ticket
Task
Transpose the data set so that the prices from each airline is listed in individual columns. Calculate the minimum price among all the airlines in each route.
Create any data set or variable if needed.
Need some help?
HINT:
You may need more than 1 variable in the BY statement.
SOLUTION:
Proc sort data=Flight;
By From To;
Run;
Proc Transpose data=Flight out=t_flight;
id airline;
var price;
by from to;
run;
Data Flight2;
Set t_flight;
min_price = min(Air_canada, American_airline, Air_Transat, Virgin_Airline, Cathay_pacific, Hainan_Airline);
Run;
Fill out my online form.