How to control the name of Excel sheets when they are all created at once

11

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:

  1. The BY statement is used.
  2. The PAGE option is used on a DEFINE statement.
  3. 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.

 

 

Share

About Author

Jane Eslinger

SAS Technical Support Analyst

Jane is a Technical Support Analyst at SAS Institute Inc., in Cary, NC. She supports the REPORT procedure, ODS, and other Base SAS procedures. Before she joined SAS, Jane worked as a statistical programmer in the social science and clinical research fields. She is a graduate of NC State University with a Bachelor of Science in Statistics.

11 Comments

  1. Great post. One tip that comes to mind is to be cautious of the names supplied by the byval. Excel worksheet names can only be 31 characters and cannot contain \ / ? * [ ] characters.

    • Jane Eslinger
      Jane Eslinger on

      Brian,
      Thank you for your comment. You are correct, Excel worksheet names have a limit to the number of characters they can be. The ODS destination imposes a limit of 28 characters long because ODS wants to make sure the sheet names are unique. Sheet names must be unique or Excel will not open the file.

      Someone from our marketing team will reach out to you via email for your mailing information where to send the copy of my book.

      Jane

  2. I can't use the ODS EXCEL destination yet (b/c I'm still using an older version of SAS), but I just experimented and found that this also works with the ExcelXP tagset. Very cool!

    I also found that I can add extra text in the sheet_name option (this is useful to me b/c sometimes I have multiple worksheets with the same BYVAL label, e.g. "TX Patient Detail", "WY Patient Detail", "TX Site List", "WY Site List", etc.). I get confused sometimes about the interaction of the sheet_name, sheet_label, and sheet_interval options, and the approach you presented is much easier.

    options nobyline;
    ods listing close;
    ods tagsets.excelxp file='C:\Users\ewa374n\Desktop\example.xml' options (sheet_name='#byval1 Summary');
    proc report data=sashelp.cars (obs=100);
    by make;
    column make model type mpg_city mpg_highway invoice;
    define make / group;
    run;
    ods tagsets.excelxp close;
    ods listing;

    • Jane Eslinger
      Jane Eslinger on

      Brooke,

      The sheet_ suboptions can be confusing. The sheet_interval option controls when new sheets are generated, it has nothing to do with the naming convention. The sheet_label option prepends the default name. When you have both sheet_name and sheet_label specified, sheet_name wins.

      Someone from our marketing team will reach out to you via email for your mailing information where to send the copy of my book.

      Jane

  3. A tip relating to multi-tab workbooks with ODS EXCEL destination is that in 9.4M3, these sheets will be "grouped", which causes all kinds of problems if you users start to filter or edit data in Excel. You can manually ungroup them in Excel. This is fixed in 9.4M4, see http://support.sas.com/kb/56/878.html. Hopefully I win a book ? : )

    • Jane Eslinger
      Jane Eslinger on

      Quentin,
      Yes, Excel will mark sheets generated from a BY variable as 'grouped'. Thankfully the development time fixed this problem.

      Thank you for your comments. Unfortunately, you were the third person to reply to the blog and we only have two copies to give away. Our Marketing Team will be doing more promotions such as this, keep an eye on the blogs for another chance to win.

      Jane

      • Drat, missed it by 49 minutes! Thanks for the blog post and congrats on the book, I guess I'll have to buy my copy. : )

  4. This is a great tip; Thank you!
    When I run the sample code I receive the following note - "NOTE: Groups are not created because the usage of Model is DISPLAY. To avoid this note, change all GROUP variables to ORDER
    variables." However after the change I still receive the same note even though the groups are created. Perhaps I am doing something incorrect?

    • Jane Eslinger
      Jane Eslinger on

      Hi Al,
      By default, a character variable is defined as a DISPLAY. A DISPLAY means that every row will be printed out. However, there is also a GROUP defined in the PROC REPORT code. Group, by definition, means to consolidate the values to the lowest common level, much like a PROC SUMMARY. When there is a DISPLAY and a GROUP in the code, PROC REPORT behind the scenes will treat GROUP as ORDER and prints out the note of ‘Groups are not created because the usage of Y is DISPLAY.’

      The same results should be produced without generating the note in the log if you use this DEFINE statement:
      define make / order;

      Is that the change you made?

      Jane

  5. Wang Yajun on

    Hi,Jane

    This is really a great enhancement to the ODS EXCELXP statement, and I think that the inclusion of SAS add-in for Excel(See the Paper : Tips and Techniques for Automating the SAS® Add-In for Microsoft Office with Visual Basic for Applications) may also improve the exhibition and customization of report.

Leave A Reply

Back to Top