BookmarkSubscribeRSS Feed
Makkena
Calcite | Level 5
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
3 REPLIES 3
Peter_C
Rhodochrosite | Level 12
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
Calcite | Level 5
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
SAS Employee
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 3 replies
  • 1684 views
  • 0 likes
  • 3 in conversation