1
PharmaSUG 2015 - Paper TT12
PROC TRANSPOSE
®
For Fun And Profit
John J. Cohen, Advanced Data Concepts, LLC, Newark, DE
Abstract
Occasionally we are called upon to transform data from one format
into a “flipped,” sort of mirror image.
Namely if the data were organized in rows and columns, we need to transpose these same data to be
arranged instead in columns and rows. A perfectly reasonable view of incoming lab data, ATM
transactions, or
web “click” streams
may look “wrong” to us. A
lternatively extracts from external
databases and production systems may need massaging prior to proceeding in SAS
®
. Finally, certain
SAS procedures may require a precise data structure, there may be particular requirements for data
visualization and graphing (such as date or time being organized horizontally/along the row rather than
values in a date/time variable), or the end user/customer may have specific deliverable requirements.
Traditionalists prefer using the DATA step and combinations of Array, Retain, and Output statements.
This approach works well but for simple applications may require more effort than is necessary. For folks
who intend to do much of the project work in, say, MS/Excel
®
, the resident transpose option when pasting
data is a handy short cut. However, if we want a simple, reliable method in SAS which once understood
will require little on-going validation with each new run, then PROC TRANSPOSE is a worthy candidate.
We will step through a series of examples, elucidating some of the internal logic of this procedure and its
options. We will also touch on some of the issues which cause folks to shy away and rely on other
approaches.
Introduction
We will take a simple dataset and transpose it at the simplest level using a standard Proc Sort/Data step
approach. We will demonstrate the same using Excel transpose. And finally we will use Proc Transpose
to transform the same data. We will then explore Proc Transpose in increasing complexity as we test
some of the many options. We will demonstrate certain errors and test strategies for overcoming these.
When completed, we expect that you will also agree that Proc Transpose has value as another tool in
your SAS tool kit.
The dataset we will use for our examples consist of a modified/simplified version of a standard sample
dataset provided with most SAS installations, sashelp.retail. It will look substantially similar to Figure 1:
Figure 1
–
sashelp.retail dataset
