ODS TAGSETS.EXCELXP and Title for Contents

[Makkena]

Hello,
I am generating some reports in SAS with ODS TAGSETS.EXCELXP. Question is: How would I get a user defined content title in the contents tab. At the moment it's showing sheet_name. I thought sheet_label would work. What I am missing heer? Any thoughts?

ods tagsets.excelxp
file="test.xls" style=sasweb
options(orientation='landscape' embedded_titles='yes' fittopage='yes'
frozen_rowheaders='yes' frozen_headers='5' index='yes'
rowcolheadings='no' print_header='none'
fittopage='yes' row_repeat='1-5'
AUTOFIT_HEIGHT='yes' embedded_titles='yes' embedded_footnotes='yes' zoom='100'
ABSOLUTE_COLUMN_WIDTH='6, 8, 15, 18, 9, 9, 9, 9, 15, 15' sheet_name='Table 1'
sheet_label='Table 1: This is Table 1');
proc print data=sashelp.class;
run;

proc print data=sashelp.cars;
run;
ods tagsets.excelxp close;
Regards,
Ramesh

[Peter_C]

with a search of the Forums, you should come up with suggestions, like
ods procLabel 'dfghjk' ;
proc print data=sashelp.class contents="first" ;
will work fine in the contents= of an HTML destination, but to make them take effect in excelXP, you'll need the excelXP options item
contents='yes'
(tested in 1.114 and 1.86 of tagsets.excelXP on win-xp-pro on SAS913)

good luck
peterC

[Makkena]

Thanks Peter. It did work.
ods tagsets.excelxp
file="test.xls" style=sasweb
options(orientation='landscape' embedded_titles='yes' fittopage='yes'
frozen_rowheaders='yes' frozen_headers='5'
rowcolheadings='no' print_header='none' contents='yes'
fittopage='yes' row_repeat='1-5'
AUTOFIT_HEIGHT='yes' embedded_titles='yes' embedded_footnotes='yes' zoom='100'
ABSOLUTE_COLUMN_WIDTH='6, 8, 15, 18, 9, 9, 9, 9, 15, 15' sheet_name='Table 1'
sheet_label='Table 1: This is Table 1');
proc print data=sashelp.class contents="This is Table 1" ;
run;
ods procLABEL " ";
proc print data=sashelp.cars contents="This is Table 2" ;

run;
ods tagsets.excelxp close;

[Eric_SAS]

The sheet_label option is the prefix portion of the sheet name. Setting it to ' ' will often get rid of the parts of the worksheet name that you don't want.

The sheet name is generated in the sheet_name event in the tagset. The worksheet naming varies depending upon the setting of the sheet_interval option.

The core of that event looks like this.


/*--------------------------------------------------------------------------eric-*/
/*-- Try to create a reasonable worksheet label based --*/
/*-- on the type of sheet interval we are using. --*/
/*--------------------------------------------------------------------4Jul 03-*/
set $worksheetName $sheet_label ' ' /if $sheet_label;
do /if cmp($sheet_interval, 'none');
set $worksheetName 'Job ' /if ^$sheet_label;
set $worksheetName $worksheetName $numberOfWorksheets ' - ' $label;

else /if cmp($sheet_interval, 'proc');
do /if $proc_label;
set $worksheetName $proc_label /if ^$sheet_label;
else;
set $worksheetName 'Proc ' /if ^$sheet_label;
set $worksheetName $worksheetName total_Proc_count ' - ' $label;
done;

else /if cmp($sheet_interval, 'page');
set $worksheetName 'Page ' /if ^$sheet_label;
set $worksheetName $worksheetName total_page_count ' - ' $label;

else /if cmp($sheet_interval, 'bygroup');
do /if ^$byval_name;
do /if $sheet_label;
set $worksheetName $label ' ' $byGroupLabel;
else;
set $worksheetName $byGroupLabel;
done;
else;
do /if $sheet_label;
set $worksheetName $sheet_label ' ' $last_byval;
else;
set $worksheetName $worksheetName $byval_name '=' $last_byval;
done;
done;

do /if ^$worksheetName;
set $worksheetName 'Table ' /if ^$sheet_label;
set $worksheetName $worksheetName $numberOfWorksheets ' - ' $label;
done;

else /if cmp($sheet_interval, 'table');
set $worksheetName 'Table ' /if ^$sheet_label;
set $worksheetName $worksheetName $numberOfWorksheets ' - ' $label;
done;