sas excel worksheet name

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

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!

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?

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 .

About Author

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.

Related Posts

Generate random uniform points on an ellipse

Python Integration to SAS® Viya® - Part 21 - Impute Missing Values

Bootstrap predicted means by using PROC GLMSELECT

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 .

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?

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.

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;

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.

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.

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

Back to Top

• Stack Overflow Public questions & answers
• Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
• Talent Build your employer brand
• Advertising Reach developers & technologists worldwide
• Labs The future of collective knowledge sharing
• About the company

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

SAS: ODS EXCEL (how to name different sheets)

I want to export a table generated by PROC TABULATE . My code goes like this:

This creates me an excel file with different sheets:

Unfortunately, the sheets do not have the names of the different crimes (theft, assault, …) but are called "CRIME TYPE 1", "CRIME TYPE 2" and so forth ( SHEET_NAME="CRIME TYPE" ).

Does anyone know how to name the sheets according to the values of the variable crime_type ?

2 Answers 2

If you want to name the sheets using values of crime_type variable, you can use options(sheet_name='#byval1') instead (options sheet_name="CRIME TYPE")

try this solution from SAS support https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-Excelxp-PROC-TABULATE-multiple-sheets/td-p/359181

Your Answer

Sign up or log in, post as a guest.

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct .

Not the answer you're looking for? Browse other questions tagged sas proc ods tabulate or ask your own question .

• The Overflow Blog
• Fighting comment spam at Facebook scale (Ep. 602)
• What it’s like being a professional workplace bestie (Ep. 603)
• Featured on Meta
• Moderation strike: Results of negotiations
• Our Design Vision for Stack Overflow and the Stack Exchange network
• Temporary policy: Generative AI (e.g., ChatGPT) is banned
• Discussions experiment launching on NLP Collective
• Call for volunteer reviewers for an updated search experience: OverflowAI Search

Hot Network Questions

• Probability approximation and computation given Compound Poisson random variable
• When in Rome... or perhaps Ankh-Morpork?
• Where do I report ISO income with a subsequent disqualifying disposition, and what taxes are due on it?
• Super slow deployments when using Solana CLI 16.x
• What RPG had space travel, star gates, and an organization maybe called "Brothers of Battle"?
• Compile TailwindCSS to CSS
• Would it be possible to make a custom zoomable world map (kinda like google maps)?
• Outlining the boundary of a shape defined by intersections in TikZ
• Is there any way to find the distance covered by a train in a particular rail route between two stations?
• TV-series with wizards and swords, with fat companion
• Do vampires gain exhaustion during a chase?
• What are the balance implications of removing spell lists?
• may(=possibility) vs. can(=possibility)
• Which companions are longterm compatible for recruitment?
• 13 puzzles I like
• Which airline is liable for compensation in case of missed connection?
• Note to African American colleague - targeted crime
• Is quadrature still considered part of numerical analysis?
• What are all the possible codes for {{A\\B}}?
• Is there a socially-optimal way to drive on a busy interstate?
• If Weird Al Yankovic Wrote Riddles
• So, too, may be the fate of his seed
• I'm liking to take it easy on the weekend
• How to remove and replace wire pins from axle?

Your privacy

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy .