topic Re: ExcelXP Tagsets - Embed_titles_once option in ODS and Base Reporting

ExcelXP Tagsets - Embed_titles_once option

deleted_user — Tue, 01 Mar 2011 10:31:06 GMT

Hi,

I'm trying to use the Embed_titles_once option but the titles will always appear before every table no matter where I

I tried the sample code in an earlier thread (appended below) & the titles also appeared before the table.

ods tagsets.ExcelXP file="sample_report_v7_sql.xls" style=sasweb ;

*Create First/Report Fake Title Page;
ods tagsets.ExcelXp options(sheet_name='Title Page' sheet_interval='none');
proc report data=sashelp.class nowd noheader; where name = 'Alfred';
column name age height weight;run;

*Create second tab;
ods tagsets.ExcelXp options(embedded_titles='yes' EMBED_TITLES_ONCE='yes'sheet_name='2.4.1.2 Business Unit HA'sheet_interval='none');

proc report data=sashelp.class nowd; title 'My Second Sheet';
where sex = 'F';run;
proc report data=sashelp.class nowd;
where sex = 'M';run;

*Create third and fourth tab;
ods tagsets.ExcelXp options(embedded_titles='yes' EMBED_TITLES_ONCE='yes' sheet_name='Consumer Business Unit HA' sheet_interval='page');
proc report data=sashelp.shoes nowd; title 'Slippers'; where product = 'Slipper' and region in ('Pacific', 'Western Europe');
define region / group; break after region /page;run;

ods _all_ close;
Is anyone able to advise where or how we can use the Embed_titles_once option?

Re: ExcelXP Tagsets - Embed_titles_once option

Cynthia_sas — Tue, 01 Mar 2011 15:41:44 GMT

Hi:
If you change sheet_interval='none' to sheet_interval='table', then the embed_titles_once works as advertised. My guess is there's some interaction between embed_titles_once and sheet_interval='none' -- what happens is that ODS builds all of the first table and then builds all of the second table and then puts the 2 tables together in 1 bigger table. Part of that building the tables before putting them together must include the title. You'd have to work with Tech Support for a definitive explanation.

For the 2 PROC REPORT scenario, you can get rid of the title on the second output, by simply issuing a NULL TITLE statement as shown in the (simplified) code below.

cynthia
[pre]
ods tagsets.ExcelXP file="c:\temp\sample_report_v7_sql.xls" style=sasweb
options(embedded_titles='yes' EMBED_TITLES_ONCE='yes' sheet_name='Something' sheet_interval='none');

proc report data=sashelp.class nowd;
title 'My Sheet title';
where sex = 'F';
run;

title; /* reset title statement */

proc report data=sashelp.class nowd;
where sex = 'M';
run;

ods tagsets.Excelxp options(sheet_interval='table');

title 'A Sheet for Every Age';
proc report data=sashelp.class nowd;
column age name sex height weight;
define age / order;
break after age / page;
run;

ods _all_ close;
[/pre]

Re: ExcelXP Tagsets - Embed_titles_once option

deleted_user — Wed, 02 Mar 2011 12:04:07 GMT

thanks for the quick response cynthia!