topic Re: How do I include titles in ODS Excel output on first tab only. in SAS Programming

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

aamoen — Tue, 11 May 2021 14:12:10 GMT

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!

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

ballardw — Tue, 11 May 2021 14:35:56 GMT

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>;

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

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.

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

Reeza — Tue, 11 May 2021 14:44:35 GMT

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

Title; will delete the previous title.

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

aamoen — Tue, 11 May 2021 14:46:29 GMT

Thank you.

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

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

aamoen — Tue, 11 May 2021 14:50:43 GMT

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

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

Reeza — Tue, 11 May 2021 14:54:28 GMT

You really need to show your code....

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

aamoen — Tue, 11 May 2021 14:56:11 GMT

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;

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

aamoen — Tue, 11 May 2021 14:56:46 GMT

updated code

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

run;