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.
21 Comments
how do you create a name for only the first page of your excel output file?
Hi Kat,
It sounds like you want to give a specific name to only the first sheet but let all of the others revert to the default name. You can do this by "clearing out" the sheet_name value.
In this code I gave a specific name for the sheet that will have the sashelp.class data. Then I reset the sheet name prior to the PROC REPORT with sashelp.cars.
ods excel file='example.xlsx';
ods excel options(sheet_name='myfirstsheet');
proc report data=sashelp.class;
run;
ods excel options(sheet_name=" ");
proc report data=sashelp.cars(obs=5);
column make model type mpg_city mpg_highway invoice;
run;
ods excel close;
If you want only the first BY-value to provide a sheet name and all of the BY-values to not have sheet names, you will need to run two different steps. You cannot use #byval1 like the example in the blog does.
Regards,
Jane
Can we give the sheet names in the excel as "1012", "1018" ...... ?? Sas is not able to read this.
Yes, you can create sheet names that are numbers. For example,
ods excel file='test.xlsx' options(sheet_name='1012');
proc print data=sashelp.class;
where sex='F';
run;
ods excel options(sheet_name='1018');
proc print data=sashelp.class;
where sex='M';
run;
ods excel close;
I’m not sure what you mean by SAS not being able to read the numbers. For further help I recommend that you contact SAS Technical Support.
Thanks,
Jane
Can anyone tell me how this might be accomplished with proc freq (there is no by group processing in my proc freq statements, so the above won't work). For example, I have:
ods excel file="Output1.xls" style=printer;
proc surveyfreq data=surveydata missing nosummary;
stratum stratum;
weight FinalWt;
tables cohort*(q0001 q0002)/cl nostd;
run;
ods excel close;
This will produce two excel sheets: one with the output of cohort*q0001 and one with the output of cohort*q0002. How can I gain control over the sheet names since both sheets are produced with the same procedure?
Without a BY variable or a macro variable, you will need to run the procedure twice to gain control of the sheet name. For example,
ods excel file="Output1.xlsx" style=printer options(sheet_name='q0001');
proc surveyfreq data=surveydata missing nosummary;
stratum stratum;
weight FinalWt;
tables cohort*(q0001 )/cl nostd;
run;
ods excel options(sheet_name='q0002');
proc surveyfreq data=surveydata missing nosummary;
stratum stratum;
weight FinalWt;
tables cohort*( q0002)/cl nostd;
run;
ods excel close;
Hi Jane,
This is great. I'm doing something similar but using months of a year instead of car make. I have to use year and month number so that the tabs are sorted correctly but the label for each tab is the month number instead of the month name. How can I get the month name to show instead?
Hi Randy,
Is your date variable a SAS date variable and do you have a format associated with it? If I use a SAS date variable as my BY variable the tabs are in chronological order. When I associate the monyy5. format with the date variable the tab names are JAN18, FEB18, and so on .
Regards,
Jane
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.
Thanks for the great tip, Jane. This would be a great tip of the day on sasCommunity.org if you were willing to submit it.
I would also encourage you to make a page for your SAS Global Forum paper. See here for instructions.
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?
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
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 ? : )
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. : )
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;
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
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.
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
Your tip did not work as expected for me. My sheet names looks like "Acura Detailed or ..." instead of Acura. Any idea why?
Sorry my mistake. I had sheet_label, not sheet_name. It is fine. Thanks