ODS Excel Sheet_name

[DavidPhillips2]

I'm trying to dynamically set the Excel tab names for a series of proc reports.  I'm looking through a set of Departments using a macro to control the loop.  I was hoping that I could use the title of the proc report as the tab name but this does not seem to be an option.  If I could do something like

sheet_name = &title1 then it would work.  I tried feeding a macro variable to the sheet name but it looks like since the macro variable changes throughout the loop it doesn't work.

My structure is 

ods Excel OPTIONS (embedded_titles='yes' ) ;
ods escapechar='~';
%ShowUniversityLevel();
%callEmployeeBody();
%DepartmentsDisplay();

ods Excel close;
ods Excel(id=VCU_Report_Printing1) close;

[PaigeMiller]

I think it should work, I'm pretty sure I have done this.

ods Excel OPTIONS (embedded_titles='yes' sheet_name="&title1") ;

There is probably a limitation on the number of characters that  can go into the sheet name.

[DavidPhillips2]

Not sure if I can use it.  "WARNING: Apparent symbolic reference TITLE2 not resolved. "

[Tom]

---

@DavidPhillips2 wrote:

Not sure if I can use it.  "WARNING: Apparent symbolic reference TITLE2 not resolved. "

---

You cannot use a macro variable you have not defined.

[ballardw · Accepted solution]

You would need a separate ODS EXCEL OPTIONS statement prior to each sheet of output.

Depending one what else is going on you likely need to be changing the sheet interval as well if you are expecting to have one tab per department with multiple report tables on each.

Since you don't show the looping code it is pretty hard to be more precise.

[DavidPhillips2]

Found a good example:

https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-excel-multiple-sheet/td-p/225377

[ballardw]

---

@DavidPhillips2 wrote:

Are you saying the idea is to close and open the ODS Excel output to the process?

---

No.

I don't have convenient examples as I generate little complicated ODS EXCEL output.

Ods excel file="something";

ods excel options=(sheet_interval='NONE'); to attempt to place multiple reports on one tab

<proc report 1>

<proc report 2>

ods excel options=(sheet_interval='BYGROUP'); One tab per by value

<proc report 3

   by somevariable

>

ods excel options=(sheet_interval='Table');

<something that creates multiple tables, get one per tab>

ods excel close;

is a basic example.

To make groups related output put you might want something like

ods excel options=(sheet_interval='NONE'); to attempt to place multiple reports on one tab

<proc report 1>

<proc report 2>

ods excel options=(sheet_interval='TABLE); to force a "change" so that then NEXT interval is a different tab

ods excel options=(sheet_interval='NONE'); to attempt to place multiple reports on next tab

<proc report 3>

<proc report 4>

ods excel options=(sheet_interval='TABLE); to force a "change"

I am not sure whether table or page might work "better" as the dummy change or not.

Of course each of those interval changes would likely be where to change the sheet_label or sheet_name as well.

[varmadi]

Thanks a lot