Training Videos: |
Whether you want to switch records to variables or vairables to records, PROC TRANSPOSE can handle it all. In general, three types of variables can be applied - BY variables, NEW_VARIABLE_NAME content variable, and RESULT variables. Use the PREFIX option to add prefix to variable names. Remember to sort the dataset first. See Alternative to Proc Transpose training session.
BY |
BY |
FROM RECORDS |
VAR |
|
---|---|---|---|---|
# |
STATE |
CITY |
SEASON |
TEMP |
1 |
SC |
Greenville |
Summer |
80 |
2 |
SC |
Greenville |
Winter |
60 |
BY |
BY |
TO VARIABLES |
VAR |
VAR |
|
# |
STATE |
CITY |
|
SUMMER |
WINTER |
1 |
SC |
Greenville |
|
80 |
60 |
PROC TRANSPOSE DATA=TEST OUT=TRANS_DS (DROP = _NAME_ _LABEL_);
BY STATE CITY;
ID SEASON;
VAR TEMP;
RUN;
BY |
BY |
FROM VARIABLES |
VAR |
VAR |
|
# |
STATE |
CITY |
SUMMER |
WINTER |
|
1 |
SC |
Greenville |
80 |
60 |
BY |
BY |
TO RECORDS |
VAR |
|
---|---|---|---|---|
# |
STATE |
CITY |
_NAME_/_LABEL |
COL1 |
1 |
SC |
Greenville |
Summer |
80 |
2 |
SC |
Greenville |
Winter |
60 |
2. Convert variables to records - multiple variables into one variable. _NAME_ is the original variable name and _LABEL_ is the original variable label. These variables are to be used to subset the final dataset. Generally, the next steps are to rename _NAME_ or _LABEL_ and the COL1 to more meaningful names. Note that the order of the final dataset is based on the order of the variables in the BY statement.
PROC TRANSPOSE DATA=TEST OUT=TRANS_DS;
BY STATE CITY;
VAR SUMMER WINTER;
RUN;
PROC TRANSPOSE Tips |
---|
Best not to assign numeric ID variable |
Animated Guide Proc Transpose, Russ Lavery
Beginner Paper Learn the Basics of Proc Transpose, Douglas Zirbel
The Proc Transpose Cookbook, Douglas Zirbel [Double Transpose]
Advance Paper Some Uses (And Handy Abuses) of Proc Transpose, Ralph Leighton
SAS® Reference PROC TRANSPOSE Syntax
SAS Tip Double PROC TRANSPOSE method for reshaping your data set with multiple BY variables
1. Changing the Shape of Your Data: PROC TRANSPOSE vs. Arrays, Bob Virgile
2. An Introduction to Reshaping (TRANSPOSE) and Combining (MATCH-MERGE) SAS® Data Sets,
Mike Zdeb
3. An Alternative Method of Transposing Data without the Transpose Procedure, Sunil Gupta
4. Visualizing PROC TRANSPOSE, Daniel Boisvert, Shafi Chowdhury
5. Simplifying Effective Data Transformation Via PROC TRANSPOSE, Arthur Li [Copy]
7. Taming the PROC TRANSPOSE, Matt Taylor
8. A Row Is a Row Is a Row, or Is It? A Hands-On Guide to Transposing Data, Christianna Williams [HOW]
9. The TRANPOSE Procedure or How to Turn It Around, Janet Stuelpner
10. Horizontal Data Sorting and Insightful Reporting: A Useful SAS® Technique, Justin Jia, Amanda Lin
11. Five Ways to Flip-Flop Your Data, Joshua M. Horstman [Multiple Variables, Double Transpose]