Transcript SAS Macros - School of Public Health

®
A Set of SAS Macros for Producing
Statistical Reports
Greg Grandits, M.S.
Ken Svendsen, M.S.
Division of Biostatistics
University of Minnesota
Example Statistical Report
Result:
1
2
3
4
5
6
7
8
9
123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1
2
Number (%) of Selected CVD Events by Treatment Group
3
And Hazard Ratio from Cox Regression Analyses
4
5
6
7
8
9
Experimental
Standard
Cox Regression Summary
10
11 Endpoint
N
%
N
%
HR
L95% U95%
P-Val
12
13
14 Primary CVD
1.02
0.88
1.18
0.771
364
4.5
365
4.4
15
0.82
0.65
1.03
0.089
MI (Fatal/NF)
133
1.6
166
2.0
16
1.15
0.90
1.48
0.265
Stroke (Fatal/NF)
133
1.6
118
1.4
17
1.09
0.87
1.37
0.471
CVD Death
152
1.9
143
1.7
18
19 Any CVD Hospitalization
1.05
0.95
1.16
0.307
793
9.7
775
9.3
20
1.01
0.82
1.26
0.913
Revascularization
163
2.0
166
2.0
21
0.87
0.66
1.15
0.330
TIA
89
1.1
105
1.3
22
1.09
0.89
1.33
0.389
Angina
202
2.5
190
2.3
23
1.30
1.00
1.69
0.051
Heart Failure
126
1.5
100
1.2
24
1.26
0.67
2.34
0.474
Hypertension
22
0.3
18
0.2
25
0.81
0.49
1.35
0.426
Renal Failure
27
0.3
34
0.4
26
27
28
29
30
Goals for Developing Report Macros
• Place text anywhere onto page
• Macros take care of tedious formatting
• Have code that is easy to write and modify
• Place several types of statistics from
multiple procedures onto a single page
- N, means, SDs, etc.
- p-values, RRs, regression coefficients, etc.
SAS Tools Needed for Macros
• Data step
• Put statement
• Ability to output statistics
from PROCs to SAS datasets
• ODS
Step 1: Declare Column Widths
%report;
%colset (25 7 7 2X 7 7 4X 7 7 7 2X 7);
Result:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
1
2
3
4
5
6
7
8
9
123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
WIDTH = 25
7
7
COL 1
COL 2
COL 3
2X
7
COL 4
7
COL 5
4X
7
COL 6
7
COL 7
7
COL 8
2X
7
COL 9
Step 2: Move Text To Page
%move (‘Number (%) of Selected CVD Events by Treatment Group’:
‘And Hazard Ratio from Cox Regression Analyses’, col=1-9, line=3L2);
Result:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
1
2
3
4
5
6
7
8
9
123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
Number (%) of Selected CVD Events by Treatment Group
And Hazard Ratio from Cox Regression Analyses
Step 2: Move Text To Page
%move (‘Experimental’ : ’Standard’: ’Cox Regression Summary’,
col = 2-3 4-5 6-9, line = 9, u=y);
Result:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
1
2
3
4
5
6
7
8
9
123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
Number (%) of Selected CVD Events by Treatment Group
And Hazard Ratio from Cox Regression Analyses
Experimental
Standard
Cox Regression Summary
Step 2: Move Text To Page
%move (‘Endpoint’, col=1, center=n, line=11, u=y);
%move (‘N’:’%’, col=2.5, line=11, u=y);
%move (‘HR’:’L95%’:’U95%’:’P-Val’, col=6.9, line=11, u=y);
Result:
1
2
3
4
5
6
7
8
9
123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1
2
3
4
5
6
7
8
9
10
11 Endpoint
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Number (%) of Selected CVD Events by Treatment Group
And Hazard Ratio from Cox Regression Analyses
Experimental
N
%
Standard
Cox Regression Summary
N
HR
%
L95%
U95%
P-Val
Step 2: Move Text To Page
%move (‘Primary CVD’:’MI’:’Stroke’:’CVD Death’:’Any CVD Hospitalization”:
‘Revascularization’:’TIA’:’Angina’:’Heart Failure’:’Hypertension’:
‘Renal Failure’, col=1, center=n, line=14L4 19L7) ;
Result:
1
2
3
4
5
6
7
8
9
123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1
2
Number (%) of Selected CVD Events by Treatment Group
3
And Hazard Ratio from Cox Regression Analyses
4
5
6
7
8
9
Experimental
Standard
Cox Regression Summary
10
11 Endpoint
N
%
N
%
HR
L95% U95%
P-Val
12
13
14 Primary CVD
15
MI (Fatal/NF)
16
Stroke (Fatal/NF)
17
CVD Death
18
19 Any CVD Hospitalization
20
Revascularization
21
TIA
22
Angina
23
Heart Failure
24
Hypertension
25
Renal Failure
26
27
28
29
30
Step 3: Move Statistics To Page
set out1;
%nmove (sum1-sum22, col = 2 4, line = 14L4 19L7, fmt=5.0);
%nmove (m1-m22, col = 3 5, fmt=5.1, scaler=100);
Result:
1
2
3
4
5
6
7
8
9
123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1
2
Number (%) of Selected CVD Events by Treatment Group
3
And Hazard Ratio from Cox Regression Analyses
4
5
6
7
8
9
Experimental
Standard
Cox Regression Summary
10
11 Endpoint
N
%
N
%
HR
L95% U95%
P-Val
12
13
14 Primary CVD
364
4.5
365
4.4
15
MI (Fatal/NF)
133
1.6
166
2.0
16
Stroke (Fatal/NF)
133
1.6
118
1.4
17
CVD Death
152
1.9
143
1.7
18
19 Any CVD Hospitalization
793
9.7
775
9.3
20
Revascularization
163
2.0
166
2.0
21
TIA
89
1.1
105
1.3
22
Angina
202
2.5
190
2.3
23
Heart Failure
126
1.5
100
1.2
24
Hypertension
22
0.3
18
0.2
25
Renal Failure
27
0.3
34
0.4
26
27
28
29
30
Step 3: Move Cox Regression Statistics
set out2;
%nmove (r1-r11, L1-L11, u1-u11, col=6.8, fmt=5.2);
%nmove (p1-p11, col=9, fmt=6.3);
Result:
1
2
3
4
5
6
7
8
9
123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1
2
Number (%) of Selected CVD Events by Treatment Group
3
And Hazard Ratio from Cox Regression Analyses
4
5
6
7
8
9
Experimental
Standard
Cox Regression Summary
10
11 Endpoint
N
%
N
%
HR
L95% U95%
P-Val
12
13
14 Primary CVD
1.02
0.88
1.18
0.771
364
4.5
365
4.4
15
0.82
0.65
1.03
0.089
MI (Fatal/NF)
133
1.6
166
2.0
16
1.15
0.90
1.48
0.265
Stroke (Fatal/NF)
133
1.6
118
1.4
17
1.09
0.87
1.37
0.471
CVD Death
152
1.9
143
1.7
18
19 Any CVD Hospitalization
1.05
0.95
1.16
0.307
793
9.7
775
9.3
20
1.01
0.82
1.26
0.913
Revascularization
163
2.0
166
2.0
21
0.87
0.66
1.15
0.330
TIA
89
1.1
105
1.3
22
1.09
0.89
1.33
0.389
Angina
202
2.5
190
2.3
23
1.30
1.00
1.69
0.051
Heart Failure
126
1.5
100
1.2
24
1.26
0.67
2.34
0.474
Hypertension
22
0.3
18
0.2
25
0.81
0.49
1.35
0.426
Renal Failure
27
0.3
34
0.4
26
27
28
29
30
Obtaining the Summary Statistics
%let evlist = primary mi str cvddth allcvd corevas tia
angina chf acchyp renalf ;
%breakdn (data=temp, class= group 2, var=&evlist, out=out1)
• Computes summary statistics for each variable in var by
each level in group
• Stores statistics in one observation dataset out1 (M1-M22
contains means)
Obtaining P-values from PROG PHREG
%phregp (data=temp, dlist=&evlist, strata=country,
tlist=&tmlist, ilist=group, factor=group, out=out2)
• Performs PHREG for each variable in dlist with
independent variables in ilist
• Factor indicates which independent variable(s) statistics
are requested
• Out names the one observation SAS dataset which
contains p-values
• P-values are named P1-P11
Complete Program for Example - Assume Data is Ready to Go
%breakdn (data=temp, class= group 2, var=&evlist, out=out1);
%phregp (data=lifetab, dlist = &evlist, strata=country, tlist= &tmlist,
ilist=group, factor=group, out = out2);
%report
%colset
(25 7 7 2X 7 7 4X 7 7 7 2X 7)
%move
(‘Number (%) of Selected CVD Events by Treatment Group’:
‘And Hazard Ratio from Cox Regression Analyses’, col=1-9, line=3L2)
(‘Experimental’:’Standard’:’Cox Regression Summary’, col=2-3 4-5 6-9, line=9, u=y)
%move
%move
%move
(‘Endpoint’, col=1, center=2, line=11, u=y)
(‘N’: ‘%’, col=2.5, line=11, u=y)
(‘HR’:’L95%’:’U95%’:’P-Val’, col=6.9, line=11, u=y)
%move
(‘Primary’:’MI’:’Stroke’:’CVD Death’:’Any CVD Hospitalization’:’Revascularization’:
‘TIA’: ‘Angina’:’Heart Failure’:’Hypertension’:’Renal Failure’,
col=1, center=n, line=12L4 19L7)
%move
set out1;
%nmove
%nmove
(sum1-sum22, col=2 4 , line=14L4 19L7, fmt=5.0)
(m1-m22, col=3 5 , fmt=5.1, scaler=100)
set out2;
%nmove
%nmove
(r1-r11 L1-L11 u1-u11, col=6.8, line=14L4 19L7, fmt=5.2)
(p1-p11, col=9, fmt=6.3)
Behind the Scenes
%move (‘Number (%) of Selected CVD Events by Treatment Group’:
‘And Hazard Ratio from Cox Regression Analyses’,
col=1-9, line=3L2) ;
Code Generated:
PUT #3 @ 19 ‘Number (%) of Selected CVD Events by Treatment Group’;
PUT #4 @ 23 ‘And Hazard Ratio from Cox Regression Analyses’;
Behind the Scenes
%breakdn (data=temp, class=group 2, var=&evlist, out=out1);
Code Generated:
PROC MEANS DATA=temp;
CLASS group;
VAR primary mi str cvddth allcvd corevas tia angina chf acchyp renalf;
OUTPUT OUT=out1 N=n1-n11 MEAN=m1-m11 STD=s1-s11
SUM=sum1-sum11
MAX=max1-max11 MIN=min1-min11;
*** Further data step to get into one observation dataset ***
22 N’s; n1-n22
Behind the Scenes
%phregp (data=temp, dlist=&evlist, strata=country,
tlist=&tmlist, ilist=group, factor=group, out=out2) ;
Code Generated:
ODS output ParamterEstimates (match_all = _est_ persist=proc) = _est_;
PROC PHREG DATA=temp;
MODEL tprimary*primary(0) = group;
PROC PHREG DATA=temp;
MODEL tmi*mi(0) = group;
. . . more phregs
*** More data steps to get desired one observation dataset ***
Statistic Generating Macros
STATISTICS
OUTPUT
MACRO
PROC
DESCRIPTION OF MACRO
breakdn
summary
N, mean,
sdev, etc.
summary statistics by
level of class variable(s)
freqdis
summary
counts, %s
cum. counts,
cum %s
distribution of variable
by level of other variable
glmp
glm
p-values,
LS means
statistics from ANOVA
Statistic Generating Macros (cont’d.)
MACRO
PROC
STATISTICS
OUTPUT
DESCRIPTION OF MACRO
regp
reg
p-values, coef.
SE, t-stat
statistics from linear
regression
phregp
phreg
p-values, coef.
HRs, 95% CI
statistics from
proportional hazards
model
Others: logistp, chisqp, mixedp (All make 1-record datasets)
Report Macros
1. %report
Starts new report
2. %colset
Defines columns and column widths
3. %move
Moves text strings to report page
4. %nmove
Moves numeric values from SAS
dataset to report page
Summary - Order of a Report
• 1 or more statistic generating macros
• %report
• %colset (define column widths)
• %move statements (for text placement)
• set statement (bring in data with statistics)
• %nmove statements (for statistics placement)
[more set/nmove statements]
Making HTML and WORD Tables
With minor changes an HTML table can be
created which can be imported into Word.
Additional features:
- Font parameters: type, size, italics, bold, etc.
- Table parameters: indenting, cell spacing, etc.
- Combine and format multiple statistics in a
single column
• 45.5 ± 7.3
• 364 (4.5%)
• 0.82 (0.65 – 1.03)
Number and Percent of Selected Cardiovascular Events by Treatment Group
And Hazard Ratio (Experimental/Standard) from Cox Regression Analyses
Number of Patients
With Event
Endpoint
Cox Regression Analyses
Experimental/Standard
Experimental
Standard
HR (95% CI)
P-value
364 (4.5%)
365 (4.4%)
1.02 (0.88 - 1.18)
0.771
MI (Fatal/NF)
133 (1.6%)
166 (2.0%)
0.82 (0.65 - 1.03)
0.089
Stroke (Fatal/NF)
133 (1.6%)
118 (1.4%)
1.15 (0.90 - 1.48)
0.265
CVD Death
152 (1.9%)
143 (1.7%)
1.09 (0.87 - 1.37)
0.471
793 (9.7%)
775 (9.3%)
1.05 (0.95 - 1.16)
0.307
Revascularization
163 (2.0%)
166 (2.0%)
1.01 (0.82 - 1.26)
0.913
TIA
89 (1.1%)
105 (1.3%)
0.87 (0.66 - 1.15)
0.330
Angina
202 (2.5%)
190 (2.3%)
1.09 (0.89 - 1.33)
0.389
Heart Failure
126 (1.5%)
100 (1.2%)
1.30 (1.00 - 1.69)
0.051
Hypertension
22 (0.3%)
18 (0.2%)
1.26 (0.67 - 2.34)
0.474
Renal Failure
27 (0.3%)
34 (0.4%)
0.81 (0.49 - 1.35)
0.426
Primary CVD
Any CVD Hospitalization
Example Calls
%move (‘Number of Patients<br>With Event’, col=2-3, line=9,
fontweight=bold)
%move (‘Endpoint’, col=1, center=n, line=11, fontstyle=italic
fontweight=bold)
* This moves the HR and 95% CI to a single column;
%nmove (r1-r11 L1-L11, u1-u11, line=14L4 19L7, col=4,
combine=y, fmt=5.2 5.2 5.2, fchar=3)
Advantages of Using Report Macros
• Customized tables, including statistics from
multiple procedures
• Great for routine reports where only the data is
changing (e.g. DSMB report)
• No transcription of numbers
• Templates can be developed
• Journal style tables
Cautionary Note
Tables look great but the numbers are wrong !
With freedom comes responsibility – check your
program.
How to Get Report Macros
Code and Documentation
http://www.biostat.umn.edu/~greg-g
E-mail (correspondence)
[email protected]
Spotlight on
MOVE
• Places text onto the report page
• Use
%move (‘string1’: ‘string2’: …, line=, col=, center=, under=, pat=,
mfirst=)
• ‘string1’: ‘string2’: … text to be placed on report page
• line
which lines?
• col
which columns?
• center
center text?
• under
underline text?
• pat
line skipping patterns
• mfirst
columns or lines to advance first
MOVE Parameter: Text Strings
%move (‘string1’: ‘string2’, line=, col=, center=, under=, pat=, mfirst=)
• Text strings are enclosed in quotes separated by colons
Example:
‘Men’: ‘Women’: ‘Total’
MOVE Parameters: line and pat
%move ( ‘string1’: ‘string2’, line=, col=, center=, under=, pat=, mfirst=)
• Line numbers can be given in two ways
line = 12 21 23
line = 12L3 (same as 12 13 14)
• Pat can be used to skip lines
line = 12L3
pat = 1st/1 (will double space)
This is the same as line = 12 14 16
MOVE Parameter: col
%move ( ‘string1’: ‘string2’, line=, col=, center=, under=, pat= ,
mfirst=)
• Columns can be specified in three ways
col = 3 4 8
col = 2.8 (same as 2 3 4 5 6 7 8)
col = 2-3 (text moved to the combined column 2 and 3)
MOVE Parameters: center and under
%move ( ‘string1’: ‘string2’, line=, col=, center=, under=, pat= , mfirst=)
• center=Y
center=N
• under=Y
under=N
for centered text (default)
for left justified text
for underlined text
text not underlined (default)
MOVE Parameter: mfirst
%move ( ‘string1’: ‘string2’, line=, col=, center=, under=, pat= , mfirst=)
• Mfirst=L
for lines advancing first (default)
Example:
%move (‘1’: ‘2’: ‘3’: ‘4’, col=1 2, line=12, mfirst=L)
Output:
1
2
• Mfirst=C
3
4
for columns advancing first
Example:
%move (‘1’: ‘2’: ‘3’: ‘4’, col=12, line=1 2, mfirst=c)
Output:
1
3
2
4
Spotlight on
NMOVE
• Places numeric values of variables to the report page
• Use
%nmove (var1 var2 …, line=, col=, pat=, mfirst=, fmt=, scaler=)
• var1 var2 … variables to be placed on report page
fmt
what format?
scaler
Number each variable is multiplied by prior to being
placed on report page
• Parameters
line, col, pat, and mfirst are the same as in %move
NMOVE Parameter: Variable Lists
%nmove ( var1 var2 …, line=, col=, pat=, mfirst=, fmt=, scaler=)
• Variable names can be given in two ways
age chol dbp (move values of these variables to report page)
M1-M15
(move values of variable M1 through M15 to report
page – 15 variables)
NMOVE Parameter: fmt
%nmove (var1 var2 …, line=, col=, pat=, mfirst=, fmt=, scaler=)
• Formats are specified as number1.number2
number1
width of field
number2
number of decimals used
NMOVE Parameter: scaler
%nmove (var1 var2 …, line=, col=, pat=, mfirst=, fmt=, scaler=)
• Each variable is multiplied by the value specified before placed on
report page
• Used for converting fractions to percentages, or vice versa
Example: scaler=100 (multiplies values by 100)