Ok, so you know how to create multiple sheets in Excel, but can anyone tell me how to control the name of the sheets when they are all created at once?
In the ODS destination for Excel, the suboption SHEET_INTERVAL is set to TABLE by default. So what does that mean for PROC REPORT? Normally PROC REPORT will only create one table. However, it will generate multiple tables when using any one of the following:
- The BY statement is used.
- The PAGE option is used on a DEFINE statement.
- The PAGE option is used on a BREAK statement.
Using the PAGE option on the DEFINE statement is important for really wide tables being sent to destinations with physical page limitations. The PAGE option for the BREAK statement is used a lot in the PDF and RTF destinations to control how a table breaks across pages. But, for the Excel destination, what if you want to control sheet creation and the name of the sheets?
Use the BY statement!
options nobyline; ods excel file='example.xlsx' options(sheet_name='#byval1'); proc report data=sashelp.cars(obs=100); by make; column make model type mpg_city mpg_highway invoice; define make / group; run; ods excel close;
This example uses #BYVAL1 for the SHEET_NAME suboption so that the name is the value of the MAKE variable. A BY statement is added to the PROC REPORT step so that one table is created for each value of MAKE.
Does your job require you to create reports in Microsoft Excel on a quarterly, monthly, or even weekly basis? Are you creating all or part of these reports by hand, referencing another sheet containing rows and rows and rows of data? If so, stop! There is a better way!
Editor's Note: If you weren’t able to hear Jane present at SAS Global Forum, check out her paper for more on this topic.
Now for some more fun…have a tip to share?
The first 2 people to comment on this blog post with a tip or trick related to this topic will win a hardcopy of Jane’s book “The SAS Programmer’s PROC REPORT Handbook: Basic to Advanced Reporting Techniques”.
Be sure to enter your e-mail address when you write your comment so we can contact you if you are a winner. Only one book per commenter and, sorry, but offer is for U.S. addresses only.
Want tips on other topics? Check out these great tricks.