topic Re: ODS Excel Sheet_name in SAS Enterprise Guide

ODS Excel Sheet_name

DavidPhillips2 — Thu, 11 Jul 2019 15:25:18 GMT

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;

Re: ODS Excel Sheet_name

PaigeMiller — Thu, 11 Jul 2019 15:29:47 GMT

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.

Re: ODS Excel Sheet_name

ballardw — Thu, 11 Jul 2019 16:42:58 GMT

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.

Re: ODS Excel Sheet_name

DavidPhillips2 — Thu, 11 Jul 2019 17:02:18 GMT

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

Re: ODS Excel Sheet_name

DavidPhillips2 — Thu, 11 Jul 2019 17:14:26 GMT

Found a good example:

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

Re: ODS Excel Sheet_name

Tom — Thu, 11 Jul 2019 17:03:41 GMT

@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.

Re: ODS Excel Sheet_name

ballardw — Thu, 11 Jul 2019 17:26:27 GMT

@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

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

<proc report 3

by somevariable

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

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

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

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.

Re: ODS Excel Sheet_name

varmadi — Sat, 13 Jul 2019 05:55:10 GMT

Thanks a lot