Transcript Intro to Proc Report with Excel Output

Intro to Proc
Report with Excel
Output
Barbara B. Okerson
WellPoint West Region Client
Reporting & Analytics
Presentation Outline
•What is Proc Report?
•Syntax
•Customization
•Writing to Excel
•Example with healthcare data
2
Proc Report
•SAS tool to summarize and display data
•Flexible and customizable
•Use Proc Report for:
– Summary Reports
– Statistics
– Detail Reports
– Compute Blocks
3
| FOR INTERNAL USE ONLY | DO NOT COPY
Proc Report Syntax
PROC REPORT DATA= datasetname <options>;
COLUMN variable list and column specifications;
DEFINE column / column usage and attributes;
COMPUTE column; compute block statements;
ENDCOMP;
RUN;
4
| FOR INTERNAL USE ONLY | DO NOT COPY
Using the COLUMN Statement
Column statement can be used to:
– Identify fields of interest
– Add headers
– Group fields
– Attach statistics to fields
Fields must be listed in the order to be displayed
5
| FOR INTERNAL USE ONLY | DO NOT COPY
Using the DEFINE Statement
Lists column and attributes (attributes after / )
The type attribute:
– DISPLAY - displays the value of the field (default for character fields).
– GROUP - Consolidate observations using this field.
– ANALYSIS - Used in calculations with a statistic (default for numeric
fields).
– COMPUTED- Specifies a field that is to be created in a compute block.
– ORDER - Sorts the data and forms groups for summary statistics,
– ACROSS - Used to create groups across rather than down the page.
6
| FOR INTERNAL USE ONLY | DO NOT COPY
Using the DEFINE Statement (continued)
Other DEFINE statement attributes:
– Format – specifies how column is formatted
– Width – width of column
– Noprint – do not display this column
– Flow – wrap wide text
– Statistic – statistic to be calculated
– Style – use style elements with the report
7
| FOR INTERNAL USE ONLY | DO NOT COPY
Sample Report Syntax
title1 'Using Proc Report';
title2 'Sample Report';
proc report data=sample nowd;
columns month group med_members rx_members;
define month/"Report Month";
define group / "Group Number";
define med_members / "Medical Members";
define rx_members / "Pharmacy Members";
run;
8
| FOR INTERNAL USE ONLY | DO NOT COPY
Output
9
| FOR INTERNAL USE ONLY | DO NOT COPY
Report Code with Formats and HTML Output
ods html style=statistical;
title1 'Using Proc Report';
title2 'Sample Report';
proc report data=sample nowd;
columns month group med_members
rx_members;
define month/"Report Month“ f=YYMMn6.;
define group / "Group Number";
define med_members / "Medical Members“ f=comma7.;
define rx_members / "Pharmacy Members“
f=comma7.;
run;
ods html close;
10
| FOR INTERNAL USE ONLY | DO NOT COPY
Sample Report Output with Formatting
11
| FOR INTERNAL USE ONLY | DO NOT COPY
Using the COMPUTE Statement
The COMPUTE statement can be used to:
– modify values within a column
– insert lines of text into the report
– to create columns
– control the content of a column
12
| FOR INTERNAL USE ONLY | DO NOT COPY
Add a COMPUTE to Sample Report
proc report data=sample nowd;
columns month group med_members rx_members ratio ;
define month/"Report Month";
define group / "Group Number";
define med_members / analysis mean "Medical Members";
define rx_members / analysis mean "Pharmacy Members";
define ratio/computed;
compute ratio;
ratio = rx_members.mean/med_members.mean;
endcompute;
run;
13
| FOR INTERNAL USE ONLY | DO NOT COPY
New Sample Report
14
| FOR INTERNAL USE ONLY | DO NOT COPY
Creating Subtotals and Totals in Proc Report
ods html style=seaside;
title1 'Using Proc Report';
title2 'Sample Report';
proc Report data=sample nowd;
columns group month med_members
rx_members ;
define group / group "Group Number";
define month/ order "Report Month" f=yymmn6.;
define med_members / "Medical Members" f=comma6.;
define rx_members / "Pharmacy Members" f=comma6.;
break after group/summarize;
rbreak after/summarize;
run;
ods html close;
15
| FOR INTERNAL USE ONLY | DO NOT COPY
Report With Subtotals and Totals
16
| FOR INTERNAL USE ONLY | DO NOT COPY
Group and Order
•Order fields
• ORDER=FORMATTED Sorts by a field’s formatted values
• ORDER=DATA Sorts in the order that the field values are encountered in the data set
• ORDER=INTERNAL Sorts by a field’s unformatted values
• ORDER=FREQ Sorts by frequency counts of the field values
•Group fields – consolidate observations under grouped
fields
17
| FOR INTERNAL USE ONLY | DO NOT COPY
Additional Customizations
•Titles and Footnotes
•Writing to Microsoft Excel
•Splitting and spanning column headers
•Number formats
•Modifying cell widths
•Run date
•Line color
•Readability option
18
| FOR INTERNAL USE ONLY | DO NOT COPY
CXXXXX Sample Company
CONTRACTS, MEMBERSHIP AND PAID AMOUNT BY SETTING BY MONTH
Paid Period: Nov 01, 2012 through Dec 31, 2012
RIM KKKKK
Total Paid
Medical Membership
Paid
SUBGROUP Month
D001
201211
D001
201212
D002
201211
D002
201212
D003
201211
D003
201212
D004
201211
D004
201212
D005
201211
D005
201212
D006
201211
D006
201212
D007
201211
D007
201212
D008
201211
D008
201212
D009
201211
D009
201212
D010
201211
D010
201212
D011
201211
D011
201212
D012
201211
D012
201212
# of
# of
Subscribers Members
138
303
137
309
36
65
36
65
1
2
1
1
435
1,002
444
1,022
157
336
162
341
240
619
96
188
25
52
26
53
18
49
17
48
11
26
11
26
50
102
49
100
26
64
26
68
37
82
36
80
Medical
Inpatient
$4,190.42
$35,512.34
$25,824.28
$30,067.43
0.00
0.00
$15,425.73
$6,371.27
0.00
$5,730.06
($3,563.49)
0.00
$2,737.00
0.00
$33,225.82
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
Outpatient
$34,506.14
$11,419.26
$12,830.69
$29,250.02
($484.84)
$30.00
$34,549.57
$97,904.09
$7,073.37
$9,189.49
$40,886.51
$4,219.50
$611.15
$1,950.26
$843.94
$3,982.00
$997.05
$812.64
$414.34
$6,392.27
$3,550.82
$7,537.12
$55.50
$1,433.25
Professional
$17,244.16
$34,761.10
$20,628.16
$21,213.57
0.00
$42.57
$80,725.13
$99,527.87
$14,570.07
$39,474.00
$68,142.38
$14,908.22
$12,366.13
$9,365.65
$7,319.00
$19,721.13
$1,585.82
$2,601.77
$6,137.90
$2,390.63
$2,312.25
$7,680.21
$408.88
$1,537.67
Medical
Paid
Amount
$55,940.72
$81,692.70
$59,283.13
$80,531.02
($484.84)
$72.57
$130,700.43
$203,803.23
$21,643.44
$54,393.55
$105,465.40
$19,127.72
$15,714.28
$11,315.91
$41,388.76
$23,703.13
$2,582.87
$3,414.41
$6,552.24
$8,782.90
$5,863.07
$15,217.33
$464.38
$2,970.92
Pharmacy
Dental
$14,620.17
0.00
$13,739.25
0.00
$8,991.82
0.00
$14,421.54
0.00
0.00
0.00
0.00
0.00
$37,846.83
0.00
$43,066.26
0.00
$12,073.21
0.00
$22,368.11
0.00
$74,125.86
0.00
$4,109.04
0.00
$3,842.65
0.00
$2,080.27
0.00
$1,232.95
0.00
$5,071.86
0.00
$740.48
0.00
$435.58
0.00
$4,096.83
0.00
$3,274.66
0.00
$1,908.87
0.00
$5,830.36
0.00
0.00
0.00
($4.49)
0.00
Vision
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
Total
$70,560.89
$95,431.95
$68,274.95
$94,952.56
($484.84)
$72.57
$168,547.26
$246,869.49
$33,716.65
$76,761.66
$179,591.26
$23,236.76
$19,556.93
$13,396.18
$42,621.71
$28,774.99
$3,323.35
$3,849.99
$10,649.07
$12,057.56
$7,771.94
$21,047.69
$464.38
$2,966.43
This confidential information should not be distributed without Anthem's prior written consent and should only be used to review health care utilization.
The Anthem Blue Cross and Blue Shield Companies are independent licensees of the Blue Cross and Blue Shield Association.
Group Number(s) - CXXXXX selected segments
| FOR INTERNAL USE ONLY | DO NOT COPY
19
Run Date = 03/06/2013
Proc Report Code Statements
proc report nowd data=sample2 split='/';
columns subgroup month ('-Medical Membership-' contracts members) ('-Total Paid-‘ ('-Medical-'
inpatient_paid outpatient_paid professional_paid tot_med ) pharmacy dental vision total);
define subgroup / order order=data display format =$10. style=[cellwidth= .85 in];
define month / order display 'Paid Month' format=$6.;
define contracts/ display '# of Subscribers' style=[tagattr="format:#,###" cellwidth= .9 in];
define members / display '# of Members' style=[tagattr="format:#,###"];
define inpatient _paid/ display 'Inpatient'
style(column)={tagattr='format:$#,##0.00;[Red]($#,##0.00);[Black]0.00;'};
define outpatient_paid / display 'Outpatient'
style(column)={tagattr='format:$#,##0.00;[Red]($#,##0.00);[Black]0.00;' cellwidth= 1 in};
define professional _paid/ display 'Professional'
style(column)={tagattr='format:$#,##0.00;[Red]($#,##0.00);[Black]0.00;' cellwidth= 1.1 in};
define tot_med/ display 'Medical/Paid/Amount'
style(column)={tagattr='format:$#,##0.00;[Red]($#,##0.00);[Black]0.00;'};
define pharmacy/ display 'Pharmacy'
style(column)={tagattr='format:$#,##0.00;[Red]($#,##0.00);[Black]0.00;'};
define dental / display 'Dental' style(column)={tagattr='format:$#,##0.00;[Red]($#,##0.00);[Black]0.00;'};
define vision / display 'Vision' style(column)={tagattr='format:$#,##0.00;[Red]($#,##0.00);[Black]0.00;'};
define total / display 'Total' style(column)={tagattr='format:$#,##0.00;[Red]($#,##0.00);[Black]0.00;'};
run;
| FOR INTERNAL USE ONLY | DO NOT COPY
20
Spanning Headers
Columns subgroup month
('-Medical Membership-' contracts members)
('-Total Paid-‘ ('-Medical-' inpatient_paid outpatient_paid
professional_paid tot_med ) pharmacy dental vision
total);
21
| FOR INTERNAL USE ONLY | DO NOT COPY
Style Attributes
define contracts/ display '# of Subscribers'
style=[tagattr="format:#,###" cellwidth= .9 in];
define members / display '# of Members‘
style=[tagattr="format:#,###"];
define inpatient _paid/ display 'Inpatient‘
style(column)={tagattr='format:$#,##0.00;[Red]($#,
##0.00);[Black]0.00;'};
define outpatient_paid / display 'Outpatient‘
style(column)={tagattr='format:$#,##0.00;[Red]($#,
##0.00);[Black]0.00;' cellwidth= 1 in};
22
| FOR INTERNAL USE ONLY | DO NOT COPY
Peripheral Code
ods Tagsets.ExcelXP file='R:\BOkerson\WellPoint SAS Users Group\Proc
Report March 2013\new_style3.xml' style=minimal
options(embedded_titles='yes' embedded_footnotes='yes');
ods tagsets.excelxp options(sheet_name = 'Sample');
title1 j=c
title2 j=c
MONTH';
title3 j=c
title4 j=c
'CXXXXX Sample Company';
'CONTRACTS, MEMBERSHIP AND PAID AMOUNT BY SETTING BY
'Paid Period: Nov 01, 2012 through Dec 31, 2012';
'RIM KKKKK';
Proc report……
footnote2 j=c "This confidential information should not be
distributed without Anthem's prior written consent and should only be
used to review health care utilization.";
footnote3 j=c "The Anthem Blue Cross and Blue Shield Companies are
independent licensees of the Blue Cross and Blue Shield
Association.";
footnote4 j=c "Group Number(s) - CXXXXX selected segments";
footnote5 j=l "Run Date = &rundate";
ODS Tagsets.ExcelXP close;
| FOR INTERNAL USE ONLY | DO NOT COPY
23
Titles and Footnotes
•Justification (j=c)
•Other options
–
–
–
–
–
Height
Font
Color
Background color
Underline
24
| FOR INTERNAL USE ONLY | DO NOT COPY
Writing to Excel
ODS tagsets.excelxp
file='R:\bokerson\Sample\CXXXXX_SampleCompany_MonRpt_EDW_RIM
KKKKK_201211_201212.xml‘ style=minimal
options(embedded_titles='yes‘ embedded_footnotes='yes');
ODS tagsets.excelxp options(sheet_name = ‘Sample');
…….report statements……
ODS tagsets.excelxp close;
25
| FOR INTERNAL USE ONLY | DO NOT COPY
Setting the Style and Destination Type
ODS tagsets.excelxp …. style=minimal ….
•ODS style – collection of elements
•ODS style attributes –e.g. font, color, width
•ODS Tagsets – associate template with destination
•ODS ExcelXP Tagset Destination – output in Microsoft’s
SpreadsheetML XML format
•Combine style and tagset
26
| FOR INTERNAL USE ONLY | DO NOT COPY
Style=seaside
•Bolded title and headers
•Shaded background for title cells
•Colored grid lines
CXXXXX Sample Company
CONTRACTS, MEMBERSHIP AND PAID AMOUNT BY SETTING AND MONTH
Paid Period: Nov 01, 2012 through Dec 31, 2012
RIM KKKKK
Total Paid
Medical Membership
SUBGROU
P
Paid
Month
# of
# of
Subscribe
Members
rs
Medical
Inpatient Outpatient
Professio
nal
Medical
Paid
Amount
Pharmacy
Dental
Vision
Total
D001
201211
138
303
$4,190.42 $34,506.14 $17,244.16 $55,940.72 $14,620.17
0.00
0.00 $70,560.89
D001
201212
137
309 $35,512.34 $11,419.26 $34,761.10 $81,692.70 $13,739.25
0.00
0.00 $95,431.95
D002
201211
36
65 $25,824.28 $12,830.69 $20,628.16 $59,283.13
$8,991.82
0.00
0.00 $68,274.95
D002
201212
36
65 $30,067.43 $29,250.02 $21,213.57 $80,531.02 $14,421.54
0.00
0.00 $94,952.56
27
| FOR INTERNAL USE ONLY | DO NOT COPY
Style=default
•Shaded backgrounds
•Italic and bolded header
•White grid lines
CXXXXX Sample Company
CONTRACTS, MEMBERSHIP AND PAID AMOUNT BY SETTING AND MONTH
Paid Period: Nov 01, 2012 through Dec 31, 2012
RIM KKKKK
CXXXXX Sample Company
CONTRACTS, MEMBERSHIP AND PAID AMOUNT BY SETTING AND MONTH
Paid Period: Nov 01, 2012 through Dec 31, 2012
RIM KKKKK
Total Paid
Medical
Medical
Paid
# of
# of
SUBGR Mont Subscri Memb
Outpatien Professio
OUP
h
bers
ers Inpatient
t
nal
Medical
Paid
Amount
D001
D001
D002
D002
$55,940.72 $14,620.17
$81,692.70 $13,739.25
$59,283.13 $8,991.82
$80,531.02 $14,421.54
201211
201212
201211
201212
138
137
36
36
303 $4,190.42
309 $35,512.34
65 $25,824.28
65 $30,067.43
$34,506.14
$11,419.26
$12,830.69
$29,250.02
$17,244.16
$34,761.10
$20,628.16
$21,213.57
Pharma Denta
cy
l
Vision
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
Total
$70,560.89
$95,431.95
$68,274.95
$94,952.56
Total Paid
Medical
Medical
Paid
# of
# of
SUBGR Mont Subscri Memb
Outpatien Professio
OUP
h
bers
ers Inpatient
t
nal
Medical
Paid
Amount
D001
D001
D002
D002
$55,940.72 $14,620.17
$81,692.70 $13,739.25
$59,283.13 $8,991.82
$80,531.02 $14,421.54
201211
201212
201211
201212
138
137
36
36
303 $4,190.42
309 $35,512.34
65 $25,824.28
65 $30,067.43
$34,506.14
$11,419.26
$12,830.69
$29,250.02
$17,244.16
$34,761.10
$20,628.16
$21,213.57
Pharma Denta
cy
l
Vision
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
Total
$70,560.89
$95,431.95
$68,274.95
$94,952.56
28
| FOR INTERNAL USE ONLY | DO NOT COPY
Available SAS Styles
To view available styles run the following code:
proc template;
list styles;
run;
SAS writes the list of available styles in the output window.
29
| FOR INTERNAL USE ONLY | DO NOT COPY
Add Group And Order
Order:
define subgroup / order order=data display
format =$10. style=[cellwidth= .85 in];
define month /
format=$6.;
order display 'Paid Month'
Group:
define subgroup / group order=data display
format =$10. style=[cellwidth= .85 in];
define month /
format=$6.;
order display 'Paid Month'
30
| FOR INTERNAL USE ONLY | DO NOT COPY
Order and Group Output
Paid
SUBGROUP
Month
D001
201211
D001
201212
D002
201211
D002
201212
D003
201211
D003
201212
# of
# of
Subscribers Members
138
303
137
309
36
65
36
65
1
2
1
1
Paid
SUBGROUP Month
D001
201211
201212
D002
201211
201212
D003
201211
201212
# of
# of
Subscribers Members
138
303
137
309
36
65
36
65
1
2
1
1
Corner of original report.
Corner of report with
subgroup as group field.
31
| FOR INTERNAL USE ONLY | DO NOT COPY
Automatic Run Date
•Store date in macro field:
data _null_;
today_date=today();
CALL SYMPUT('rundate',put(today_date,mmddyy10.));
run;
•Use macro field in footnote:
footnote5 j=l "Run Date = &rundate";
32
| FOR INTERNAL USE ONLY | DO NOT COPY
Other Enhancements
•Line color
•Highlight every other row
•Write to multiple worksheets
33
| FOR INTERNAL USE ONLY | DO NOT COPY
Proc Template to Modify Line Color
proc template;
define style styles.test;
parent=styles.minimal;
style table from table/
bordercolor =blue;
end;
run;
ods Tagsets.ExcelXP file='R:\BOkerson\WellPoint SAS Users
Group\new_style4x.xml' style=test options(embedded_titles='yes'
embedded_footnotes='yes' );
34
| FOR INTERNAL USE ONLY | DO NOT COPY
CXXXXX Sample Company
CONTRACTS, MEMBERSHIP AND PAID AMOUNT BY SETTING AND MONTH
Paid Period: Nov 01, 2012 through Dec 31, 2012
RIM KKKKK
Total Paid
Medical Membership
SUBGRO
UP
D001
D001
D002
D002
D003
D003
D004
D004
Medical
Medical
Paid
Amount
$55,940.72
$81,692.70
$59,283.13
$80,531.02
Paid
# of
# of
Month Subscribers Members Inpatient Outpatient Professional
Pharmacy Dental Vision
Total
201211
138
303 $4,190.42 $34,506.14 $17,244.16
$14,620.17
0.00
0.00 $70,560.89
201212
137
309 $35,512.34 $11,419.26 $34,761.10
$13,739.25
0.00
0.00 $95,431.95
201211
36
65 $25,824.28 $12,830.69 $20,628.16
$8,991.82
0.00
0.00 $68,274.95
201212
36
65 $30,067.43 $29,250.02 $21,213.57
$14,421.54
0.00
0.00 $94,952.56
201211
1
2
0.00
0.00
0.00
0.00
0.00
201212
1
1
0.00
$30.00
$42.57
$72.57
0.00
0.00
0.00
$72.57
201211
435
1,002 $15,425.73 $34,549.57 $80,725.13 $130,700.43 $37,846.83
0.00
0.00 $168,547.26
201212
444
1,022 $6,371.27 $97,904.09 $99,527.87 $203,803.23 $43,066.26
0.00
0.00 $246,869.49
This confidential information should not be distributed without Anthem's prior written consent and should only be used to review health care
The Anthem Blue Cross and Blue Shield Companies are independent licensees of the Blue Cross and Blue Shield Association.
Group Number(s) - CXXXXX selected segments
| FOR INTERNAL USE ONLY | DO NOT COPY
35
Compute Group Code for Line Shading
Compute subgroup;
line_count+1;
if mod(line_count,2)=0 then do;
call define (_row_, "style",
"style=[background=cxF2F2DF]");
end;
Endcomp;
36
| FOR INTERNAL USE ONLY | DO NOT COPY
CXXXXX Sample Company
CONTRACTS, MEMBERSHIP AND PAID AMOUNT BY SETTING AND MONTH
Paid Period: Nov 01, 2012 through Dec 31, 2012
RIM KKKKK
Total Paid
Medical Membership
Paid
SUBGROUP Month
D001
201211
D001
201212
D002
201211
D002
201212
D003
201211
D003
201212
D004
201211
D004
201212
D005
201211
D005
201212
D006
201211
D006
201212
D007
201211
D007
201212
D008
201211
D008
201212
D009
201211
D009
201212
D010
201211
D010
201212
D011
201211
D011
201212
D012
201211
D012
201212
# of
# of
Subscribers Members Inpatient
138
303 $4,190.42
137
309 $35,512.34
36
65 $25,824.28
36
65 $30,067.43
1
2
0.00
1
1
0.00
435
1,002 $15,425.73
444
1,022 $6,371.27
157
336
0.00
162
341 $5,730.06
240
619
96
188
0.00
25
52 $2,737.00
26
53
0.00
18
49 $33,225.82
17
48
0.00
11
26
0.00
11
26
0.00
50
102
0.00
49
100
0.00
26
64
0.00
26
68
0.00
37
82
0.00
36
80
0.00
.
.
.
Medical
Outpatient
$34,506.14
$11,419.26
$12,830.69
$29,250.02
$30.00
$34,549.57
$97,904.09
$7,073.37
$9,189.49
$40,886.51
$4,219.50
$611.15
$1,950.26
$843.94
$3,982.00
$997.05
$812.64
$414.34
$6,392.27
$3,550.82
$7,537.12
$55.50
$1,433.25
Medical
Paid
Amount
$55,940.72
$81,692.70
$59,283.13
$80,531.02
Professional
Pharmacy
$17,244.16
$14,620.17
$34,761.10
$13,739.25
$20,628.16
$8,991.82
$21,213.57
$14,421.54
0.00
0.00
$42.57
$72.57
0.00
$80,725.13 $130,700.43 $37,846.83
$99,527.87 $203,803.23 $43,066.26
$14,570.07 $21,643.44 $12,073.21
$39,474.00 $54,393.55 $22,368.11
$68,142.38 $105,465.40 $74,125.86
$14,908.22 $19,127.72 $4,109.04
$12,366.13 $15,714.28 $3,842.65
$9,365.65 $11,315.91 $2,080.27
$7,319.00 $41,388.76 $1,232.95
$19,721.13 $23,703.13 $5,071.86
$1,585.82
$2,582.87
$740.48
$2,601.77
$3,414.41
$435.58
$6,137.90
$6,552.24 $4,096.83
$2,390.63
$8,782.90 $3,274.66
$2,312.25
$5,863.07 $1,908.87
$7,680.21 $15,217.33 $5,830.36
$408.88
$464.38
0.00
$1,537.67
$2,970.92
Dental
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
Vision
Total
0.00 $70,560.89
0.00 $95,431.95
0.00 $68,274.95
0.00 $94,952.56
0.00
0.00
$72.57
0.00 $168,547.26
0.00 $246,869.49
0.00 $33,716.65
0.00 $76,761.66
0.00 $179,591.26
0.00 $23,236.76
0.00 $19,556.93
0.00 $13,396.18
0.00 $42,621.71
0.00 $28,774.99
0.00
$3,323.35
0.00
$3,849.99
0.00 $10,649.07
0.00 $12,057.56
0.00
$7,771.94
0.00 $21,047.69
0.00
$464.38
0.00
$2,966.43
This confidential information should not be distributed without Anthem's prior written consent and should only be used to review health care
The Anthem Blue Cross and Blue Shield Companies are independent licensees of the Blue Cross and Blue Shield Association.
Group Number(s) - CXXXXX selected segments
| FOR INTERNAL USE ONLY | DO NOT COPY
37
Run Date = today()
Code for Multiple Sheets with By-Value
ods Tagsets.excelxp
file='R:\BOkerson\totals2.xml' style=minimal
options(embedded_titles='yes' embedded_footnotes='yes' );
ods tagsets.excelxp options(sheet_name = '#byval(subgroup)');
proc Report nowd data=sample3 headline headskip split='/';
columns ('-#byval(Subgoup)-'month ('-Medical Membership-'
contracts members) ('-Total Paid-' ('-Medical-' inpatient_paid
outpatient_paid professional_paid tot_med) pharmacy ));
define month / order 'Paid Month' format=$6.;
.............
.............
by subgroup;
run;
ODS Tagsets.excelxp close;
38
| FOR INTERNAL USE ONLY | DO NOT COPY
Proc Report By Value Output
39
| FOR INTERNAL USE ONLY | DO NOT COPY
Add a Logo to Proc Report Output
•MSOffice2K_x tagset
• Download from
http://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/index.html
• Run the code before running Proc Report
•WellPoint logo in upper left corner
•Options
image_path="R:\bokerson\wellpoint_logo.gif"
image_height="45"
image_width="90"
•
|
40
CXXXXX Sample Company
CONTRACTS, MEMBERSHIP AND PAID AMOUNT BY SETTING
AND MONTH
Paid Period: Nov 01, 2012 through Dec 31, 2012
RIM KKKKK
Subgroup
D001
D001
D002
D002
D003
D003
D004
D004
Medical Membership
# of Members
# of
Paid Month
Subscribers
201211
201212
201211
201212
201211
201212
201211
201212
138
137
36
36
1
1
435
444
303
309
65
65
2
1
1002
1022
Inpatient
$4,190.42
$35,512.34
$25,824.28
$30,067.43
$0.00
$0.00
$15,425.73
$6,371.27
Total Paid
Medical
Medical
Professional
Outpatient
Paid
Amount
$17,244.16 $55,940.72
$34,506.14
$34,761.10 $81,692.70
$11,419.26
$20,628.16 $59,283.13
$12,830.69
$21,213.57 $80,531.02
$29,250.02
($484.84)
$0.00
($484.84)
$72.57
$42.57
$30.00
$80,725.13 $130,700.43
$34,549.57
$99,527.87 $203,803.23
$97,904.09
Pharmacy
Total
$14,620.17 $70,560.89
$13,739.25 $95,431.95
$8,991.82 $68,274.95
$14,421.54 $94,952.56
($484.84)
$0.00
$72.57
$0.00
$37,846.83 $168,547.26
$43,066.26 $246,869.49
This confidential information should not be distributed without Anthem's prior written consent and should only be used to review health care utilization.
The Anthem Blue Cross and Blue Shield Companies are independent licensees of the Blue Cross and Blue Shield Association.
Group Number(s) - CXXXXX selected segments
Run Date = 05/13/2013
41
Questions???
| FOR INTERNAL USE ONLY | DO NOT COPY
42
Contact Information
Barbara B. Okerson, Ph.D
Senior Health Information Consultant,
West Region Client Reporting & Analytics
WellPoint Health Care Analytics
Phone: 804-662-5287
Email: [email protected]
43
| FOR INTERNAL USE ONLY | DO NOT COPY