the report procedure and ods destination for microsoft excel: … · 2020. 2. 2. · •proc report...
TRANSCRIPT
![Page 1: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/1.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
The REPORT Procedure and ODS Destination for Microsoft Excel: The Smarter, Faster Way to Create First-Rate Excel Reports
Jane Eslinger, SAS Institute Inc.
![Page 2: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/2.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
PROGRAMMERS
![Page 3: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/3.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
BOSSES
![Page 4: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/4.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Current Process
Develop SAS Program
Export Data
Manually Create Excel File
Collect Feedback
Recreate Excel File
Receive New Data
![Page 5: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/5.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
New Way
PROC REPORT
ODS destination
for Excel
Final Report
![Page 6: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/6.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
ODS EXCEL Statement and Document Options
• Author
• Category
• Comments
• Keywords
• Status
• Title
![Page 7: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/7.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
ODS EXCEL Statement OPTIONS Option
• Approximately 45 suboptions!
• Half control printing aspects
• Half control worksheet and table features
![Page 8: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/8.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
OPTIONS Suboptions for Worksheet Features
• EMBEDDED_TITLES places titles in worksheet
• EMBEDDED_FOOTNOTES places footnotes in worksheet
• TAB_COLOR changes background color of tab name
• START_AT specifies cell to place first piece of output
• FROZEN_HEADERS forces header rows to be static
![Page 9: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/9.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
OPTIONS Suboptions for Worksheet Features
ods excel file='example.xlsx'
options(embedded_titles='yes'
embedded_footnotes='yes'
tab_color='purple'
start_at='2,4'
frozen_headers='yes');
title 'This title will appear in the worksheet';
footnote 'This footnote will appear in the
worksheet';
proc report data=sashelp.class;
run;
ods excel close;
![Page 10: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/10.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
![Page 11: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/11.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
OPTIONS Suboptions for Table Features
• HIDDEN_ROWS hides specific rows
• AUTOFILTER turns on Excel filtering capability
• ABSOLUTE_COLUMN_WIDTH specifies column width
• ABSOLUTE_ROW_HEIGHT specifies row height
![Page 12: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/12.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
OPTIONS Suboptions for Table Features
ods excel file='example.xlsx'
options(hidden_rows='6'
autofilter='1-3'
absolute_column_width='16'
absolute_row_height='25');
proc report data=sashelp.cars;
run;
ods excel close;
![Page 13: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/13.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
OPTIONS Suboptions for Table Features
![Page 14: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/14.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Suboptions for Sheet Name and Sheet Creation
• SHEET_INTERVAL• Default value is TABLE
• PROC REPORT creates one table
• BY statement or PAGE option generates multiple tables
• SHEET_NAME• Default value is procedure based
• ‘Report 1 – Detailed and-or summarized report’
• SHEET_LABEL• Default value is NONE
• Prepends default sheet name
![Page 15: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/15.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Multiple Tables on Multiple Worksheets
![Page 16: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/16.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Multiple Tables on Multiple Worksheetsods excel file='example.xlsx'
options(sheet_interval='none' sheet_name='Class');
proc report data=sashelp.class;
column age height weight;
define age / group;
define height / mean;
define weight / mean;
rbreak before / summarize;
run;
proc report data=sashelp.class;
column sex height weight;
define sex / group;
define height / mean;
define weight / mean;
rbreak before / summarize;
run;
![Page 17: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/17.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Multiple Tables on Multiple Worksheets
ods excel options(sheet_interval='output');
ods exclude all;
data _null_;
declare odsout obj();
run;
ods select all;
ods excel options(sheet_interval='none'
sheet_name='Heart');
![Page 18: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/18.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Multiple Tables on Multiple Worksheets
ods excel options(sheet_interval='now'
sheet_name='Heart');
![Page 19: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/19.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Multiple Tables on Multiple Worksheetsproc report data=sashelp.heart;
column bp_status height weight;
define bp_status / group;
define height / mean;
define weight / mean;
rbreak before / summarize;
run;
proc report data=sashelp.heart;
column sex height weight;
define sex / group;
define height / mean;
define weight / mean;
rbreak before / summarize;
run;
ods excel close;
![Page 20: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/20.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Multiple Tables on Multiple Worksheets
![Page 21: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/21.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Create Custom Report
![Page 22: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/22.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Data Preparation
data pricedata;
length productname $50.;
set sashelp.pricedata;
if productname="Product8" then product=8;
else if productname="Product9" then product=9;
else if productname="Product10" then product=10;
else if productname="Product11" then product=11;
productnum = put(product,z3.);
run;
![Page 23: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/23.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Pricedata Data Set
![Page 24: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/24.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Snippet 1ods excel file='example.xlsx' options(embedded_titles='yes'
frozen_headers='yes');
title "Year-end Sales Totals";
proc report data=pricedata;
column region regionname product productname productnum
cost price sale;
define region / group order=internal noprint;
define regionname / group noprint;
define product / group order=internal noprint;
define productname / group;
define productnum / group 'Product Number';
run;
ods excel close;
![Page 25: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/25.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Snippet 1 Output
![Page 26: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/26.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Snippet 2
define productnum / group 'Product Number' format=$3.;
define cost /
style(column)=[tagattr="format:$#,###;$-#,###"];
define price /
style(column)=[tagattr="format:$#,###.00;$-#,###.00"];
define sale /
style(column)=[tagattr="format:#,###"];
![Page 27: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/27.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Snippet 2 Output
![Page 28: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/28.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
define productnum / group 'Product Number'
style(column)=[tagattr="format:000];
![Page 29: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/29.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Snippet 3
compute before regionname /
style=[background=lightblue just=l
font_weight=bold];
line regionname $20.;
endcomp;
![Page 30: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/30.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Snippet 3 Output
![Page 31: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/31.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Snippet 4
break after regionname / summarize;
rbreak after / summarize;
![Page 32: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/32.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Snippet 4 Output
![Page 33: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/33.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Snippet 5proc report data=pricedata
style(summary)=[font_weight=bold];
compute after regionname;
❶ productname = catx(' ','Total',regionname);
❷ call define(_row_,'style',
'style=[bordertopstyle=solid
bordertopwidth=1pt bordertopcolor=black
borderbottomstyle=solid borderbottomwidth=1pt
borderbottomcolor=black]');
❸ line ' ';
endcomp;
![Page 34: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/34.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Snippet 5 Output
![Page 35: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/35.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Snippet 6
compute after;
productname = "All Regions";
call define(_row_,'style',
'style=[bordertopstyle=solid
bordertopwidth=1pt
bordertopcolor=black
borderbottomstyle=solid
borderbottomwidth=1pt
borderbottomcolor=black]');
endcomp;
![Page 36: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/36.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Snippet 6 Output
![Page 37: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/37.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Before and After
![Page 38: The REPORT Procedure and ODS Destination for Microsoft Excel: … · 2020. 2. 2. · •PROC REPORT creates one table •BY statement or PAGE option generates multiple tables •SHEET_NAME](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133c013dfd10f4dd73b4a6d/html5/thumbnails/38.jpg)
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Before After