How do I include titles in ODS Excel output on first tab only.

[aamoen]

I'm trying to include a proc title to my ODS excel output using the embedded_titles='Yes' option, but I want the title to be included in the first tab of my excel workbook only.  Not all every tab of the output file (my workbook has 3 tabs).  Is there an option to include the title on the first tab only?

Thank you!

[ballardw · Accepted solution]

You need to show the code for the entire output.

IF you have separate procedures creating the output then you can add an additional ODS EXCEL statement that would turn of the embedded titles.

Skeleton:

Ods excel options(embedded_titles='Yes') <other stuff>;

<first procedure that creates output>

ods excel options(embedded_title='No'); <do not add a file name here or the output gets overwritten>

<next procedure that creates output>

<next output>

ods excel close;

This is the general approach to changing anything set with the Options. You can change them with additional ODS excel statements while the same document is open and change the options for different output. Usually.

If the first procedure creates all the sheets then you need to change how the output is generated so the titles only appear on the first, options alone won't suffice.

[aamoen]

Thank you. 

Adding the second "ods excel options(embedded_title='No')" between my first and second procedure created the result I was looking for. 

[Reeza]

Did you make sure to reset the title before the next proc/output?

Title; will delete the previous title.

[aamoen]

yes, the title was still populating in all of the output tabs though. Including the second "ods excel options(embedded_title='No')" did work.

[Reeza]

You really need to show your code....

[aamoen]

Initial code

ods _all_ close;
ods excel file=&B17file;
ods excel options(sheet_name="B.17" embedded_titles='yes');
ods noproctitle;
proc print data=work.B17_by_GL noobs;
var March;
title "B.17";
title2 'Certificates of Deposit';
run;
title;

ods excel options(sheet_name="GL balances");
proc print data=work.b17essbase noobs;

run;

ods excel options(sheet_name="XRI Map");
proc report data=work.xri329 (where=(reported in('CDAB4', 'CDAH3', 'CDAB2', 'CDAR1', 'REAR1', 'CDASR', 'CDASV', 'REAST', 'CDAH4', 'CDAF5', 'CDAH5')));
column reported eom_bal;
define reported/group;
define eom_bal / analysis sum;
rbreak after / summarize;
format eom_bal comma18.2;
run;
ods excel close;

[aamoen]

updated code

ods _all_ close;
ods excel file=&B17file;
ods excel options(sheet_name="B.17" embedded_titles='yes');
ods noproctitle;
proc print data=work.B17_by_GL noobs;
var March;
title "B.17";
title2 'Certificates of Deposit';
run;
title;

ods excel options(sheet_name="GL balances" embedded_titles='no');
proc print data=work.b17essbase noobs;

run;

ods excel options(sheet_name="XRI Map");
proc report data=work.xri329 (where=(reported in('CDAB4', 'CDAH3', 'CDAB2', 'CDAR1', 'REAR1', 'CDASR', 'CDASV', 'REAST', 'CDAH4', 'CDAF5', 'CDAH5')));
column reported eom_bal;
define reported/group;
define eom_bal / analysis sum;
rbreak after / summarize;
format eom_bal comma18.2;
run;
ods excel close;