PROC TRANSPOSE
|
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 |
1. Convert records to variables - one variable into multiple variable. New variable names are in the ID variable.
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;
* make generic column variables when there are unique values to transpose
* create seq based on group by var;
data p_patientxx;
length seq 8.;
retain seq;
merge p_patientyy (in=a where=(count > 1)) p_patientxx ;
BY ousubjid;
if a;
seq = seq + 1;
if first.ousubjid then seq=0;
keep ousubjid seq usubjid;
run;
* by has group by var;
* var has unique names content var;
* id seq;
proc transpose data=p_patientxx out=tran_osubj prefix=c_;
BY ousubjid;
var usubjid;
id seq;
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]
proc transpose data=cholesterol_in out=cholesterol_trans; by subject visit; var ldl hdl; run; proc transpose data=cholesterol_trans out=cholesterol_out(drop=_NAME_) delim=_; by subject; var col1; id _NAME_ visit; run;
