Transcript SAS Global Forum 2013, Techie Tip on PROC TRANSPOSE, Kara

San Francisco 2013
 Theme:
 Big
Strength in Numbers
Data and Business
Intelligence (BI) Applications
Paper 428-2013: Cox Propotional Hazard Model
Evaluation in one Shot. (Polina Kukhareva.
Proceedings of the SAS® Global Forum 2013
Conference. Cary, NC: SAS Institute Inc. )
•
Martingale residual plots
•
Tests linearity in the log scale assumption
•
Schoenfeld residual plot to check PH




%let predictors=age systolic_bp diastolic_bp ldl bmi
diabetes smoking sex treatment activity;
%let class = diabetes(ref="0") smoking(ref="0")
sex(ref="0") treatment(ref="0") activity(ref="average")
%check_Cox_assumptions_all (_NUMBER=1,
_DATA=simCox, _PREDICTORS=&predictors,
_class=&class, _OUTCOME=CV_event, _FU=CV_time,
_STOP= activity,
_EXCLUDE= systolic_bp diastolic_bp ldl diabetes
smoking sex)

Quantlife Procedure for Survival Analysis
◦ Covariate analysis
◦ Summarize lifetime distribution by percentiles
(25th, 50th, 75th)
◦ Separate regression models for each quartile
proc quantlife data=pbc log method=na plot=(quantplot survival)
seed=1268;
model Time*Status(0)=logBilirubin logProtime logAlbumin Age Edema
/ quantile=(0.1 0.2 0.3 0.4 0.5 0.6 0.75);
run;
ID
1
1
2
2
3
3
4
4
Date
22-Sep-09
09-Oct-09
03-Mar-10
14-Mar-10
20-Apr-10
29-Apr-10
03-May-10
29-May-10
Time
1
2
1
2
1
2
1
2
Gender
M
M
F
F
F
F
F
F
Score
health
18
22
7
16
8
17
11
20
Score
mobility
32
29
20
25
22
19
25
proc transpose
data=have
out=want
(drop=_:)
prefix=var1_;
by idnum;
var var1;
id date;
run;

What would Art do?
Paper 538-2013
SAS Global Forum 2013. Beyond the Basics.
Arthur S. Tabachneck, Ph.D., myQNA, Inc., Thornhill, Ontario Canada
Xia Ke Shan, Chinese Financial Electrical Company, Beijing, China
Robert Virgile, Robert Virgile Associates, Inc., Lexington, MA
Joe Whitehurst, High Impact Technologies, Atlanta GA



SAS Marco
Flips tall data sets to wide data sets or flips
wide data sets to wider data sets
Macro creates and runs a program that
creates a data step using arrays

%macro transpose(libname_in=mydata,
libname_out=, data=, out=, by=,
prefix=, var=, autovars=, id=, var_first=,
format=, delimiter=,
copy=, drop=, sort=, sort_options,
guessingrows=);
The
How the macro works
if we have: dataset have
idnum
date
var1
var2
1
1
1
1
2
2
2
2
31MAR2013
30JUN2013
30SEP2013
31DEC2013
31MAR2013
30JUN2013
30SEP2013
31DEC2013
1
2
3
4
5
6
7
8
SD
EF
HK
HL
GH
MM
JH
MS
Art Tabachneck. A Better Way to Flip(Transpose) a SAS® Dataset. Presented at MWSUG 24, 2013.
How the macro works
and we need: dataset need
idnum var1
Qtr1
var2
Qtr1
var1
Qtr2
var2
Qtr2
var1
Qtr3
var2
Qtr3
var1
Qtr4
var2
Qtr4
1
1
SD
2
EF
3
HK
4
HL
2
5
GH
6
MM
7
JH
8
MS
and we submit:
%transpose(data=have, out=want,
by=idnum,
id=date,
format=qtr1., delimiter=_Qtr,
var=var1-var2, sort=yes)
Art Tabachneck. A Better Way to Flip(Transpose) a SAS® Dataset. Presented at MWSUG 24, 2013.
How is it Different/Better than
Proc Transpose?
Does it Go Beyond Proc
Transpose?
and you can use almost all the features
that you can with PROC TRANSPOSE
plus some additional ones
%transpose( libname_in=,
data=,
by=,
var=,
id=,
format=,
copy=,
sort=,
use_varname=,
guessingrows=)
libname_out=,
out=,
prefix=,
autovars=,
var_first=,
delimiter=,
drop=,
sort_options=,
preloadfmt=,
Art Tabachneck. A Better Way to Flip(Transpose) a SAS® Dataset. Presented at MWSUG 24, 2013
the %transpose macro's features
parameter: drop
the variable(s) you want dropped from the transposed file
** only relevant if you want to drop any of the by variables **
%transpose( libname_in=,
data=,
by=,
var=,
id=,
format=,
copy=,
sort=,
use_varname=,
guessingrows=)
libname_out=,
out=,
prefix=,
autovars=,
var_first=,
delimiter=,
drop=,
sort_options=,
preloadfmt=,
Art Tabachneck. A Better Way to Flip(Transpose) a SAS® Dataset. Presented at MWSUG 24, 2013
the %transpose macro's features
parameter: autovars
determines whether char(acter), num(eric) or all variables should
be transposed if the var parameter is null
%transpose( libname_in=,
libname_out=,
data=,
out=,
by=,
prefix=,
var=,
* * * * * * F E A T U R Eautovars=,
******
id=,
var_first=,
Where PROC
TRANSPOSE will only
include all numeric
format=,
variables
if there is no vardelimiter=,
statement,
copy=,
drop=,
sort=,
sort_options=,
this parameter lets
you indicate if you want
all numeric variables,
use_varname=,
preloadfmt=,
all character
variables or simply
all variables
guessingrows=)
Art Tabachneck. A Better Way to Flip(Transpose) a SAS® Dataset. Presented at MWSUG 24, 2013
the %transpose macro's features
parameter: sort
whether the input dataset should be sorted (YES or NO):
for both, only &by, &id, &var and &copy variables will be used
If Yes, the input data will be sorted using the noequals option
%transpose( libname_in=,
data=,
by=,
var=,
id=,
format=,
copy=,
sort=,
use_varname=,
guessingrows=)
libname_out=,
out=,
prefix=,
autovars=,
var_first=,
delimiter=,
drop=,
sort_options=,
preloadfmt=,
Art Tabachneck. A Better Way to Flip(Transpose) a SAS® Dataset. Presented at MWSUG 24, 2013
What’s in it for me??
Would you be interested in knowing how to
obtain the same result with the following code?
No system variables to drop
%transpose( data=have, out=want, by=idnum,
var=var1, id=date, sort=yes,
delimiter=_)
No need for a prefix (var names automatically included)
No need to differentiate between options and statements
as they are all of the form: parameter=value,
No need to presort your data
Art Tabachneck. A Better Way to Flip(Transpose) a SAS® Dataset. Presented at MWSUG 24, 2013
would you be interested in knowing how to
obtain the right result with the following code?
%transpose(data=have, out=need,
by=idnum,
id=date,
format=qtr1., delimiter=_Qtr,
var=var1-var2, sort=yes)
How about if you knew that the macro:
only requires one step
only needs one pass through the data
doesn't produce distorted results
can run more than 50 times faster than PROC
TRANSPOSE
Art Tabachneck. A Better Way to Flip(Transpose) a SAS® Dataset. Presented at MWSUG 24, 2013
Compare the performance of the following two
sets of almost identical code run on a file with
40,000 records and 1,002 variables
PROC SORT data=have out=need;
by idnum date;
run; took 2.41 seconds CPU time
PROC TRANSPOSE data=need out=want (drop=_:)
prefix=var1_Qtr;
by idnum;
var var1;
id date;
format date Qtr1.;
run; took 0.74 seconds CPU time
Art Tabachneck. A Better Way to Flip(Transpose) a SAS® Dataset. Presented at MWSUG 24, 2013

Thanks to Art Tabachneck for providing
access and sharing his slides
http://www.sascommunity.org/wiki/A_Better_
Way_to_Flip_(Transpose)_a_SAS_Dataset