Search the site...

SASCRUNCH TRAINING
  • Home
  • Member's Area
  • How to Start
  • SAS Interface
  • Creating a Data Set
  • Practical SAS Training Course
  • SAS Certified Specialist Training Program
  • Proc SQL Course
  • Introduction to Time Series Analysis
  • SAS Project Training Course
  • Full Training / Membership
  • Sign up
  • About us
  • Contact us
  • Home
  • Member's Area
  • How to Start
  • SAS Interface
  • Creating a Data Set
  • Practical SAS Training Course
  • SAS Certified Specialist Training Program
  • Proc SQL Course
  • Introduction to Time Series Analysis
  • SAS Project Training Course
  • Full Training / Membership
  • Sign up
  • About us
  • Contact us
Sentry Page Protection
Please Wait...
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.
Picture
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;
Picture
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.
Picture

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;

Picture

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:

Picture

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:

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

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.

Already a member? Go to member's area.