BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
aamoen
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

7 REPLIES 7
ballardw
Super User

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
Obsidian | Level 7

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
Super User
Did you make sure to reset the title before the next proc/output?

Title; will delete the previous title.
aamoen
Obsidian | Level 7
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
Super User
You really need to show your code....
aamoen
Obsidian | Level 7

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
Obsidian | Level 7

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 3377 views
  • 1 like
  • 3 in conversation