ODS EXCEL sheet naming with multiple tables from a proc

[snoopy369]

I'm trying to use ODS EXCEL to do the following:

1.  Print something, and put that on a sheet named something specific.

2.  Run a PROC COMPARE, and make two sheets, the main Dataset sheet and the Variables comparison, and name them something intelligent.

Either of these is not a problem separately; I like the automatic names you get from SAS by default for 2, and for 1 I know how to use SHEET_NAME.  But when I do them - in this order - it fails.

data class;
  set sashelp.class;
  drop name;
run;

ods excel file="e:\temp\test.xlsx" options(sheet_name="test" sheet_interval="table");
proc print data=sashelp.class;
run;
ods exclude compareSummary;
title1 "STEP 1: Compare contents of files";  
proc compare base=sashelp.class compare=class listvar novalues ;
run;
ods excel close;

I end up with "test 2" and "test 3" as sheet names, which is no good.  I can use

options(sheet_name=' ')

and get "Sheet 1" and "Sheet 2", which is not really that much better.  If I use Sheet_Label I don't get much better; obviously something sees that sheet_name has _ever_ been set, and refuses to forget that it was.

I have a workaround using PROC DOCUMENT that works fine, but I'm curious if there's a more integrated way to do this.  Something like ODS PROCLABEL but for the tables maybe?

Thanks.

data class;
  set sashelp.class;
  drop name;
run;

ods excel file="e:\temp\test.xlsx" options(sheet_name="test" sheet_interval="table");
proc print data=sashelp.class;
run;

ods document name=conts;
ods exclude compareSummary;
ods excel exclude all;
title1 "STEP 1: Compare contents of files";  
proc compare base=sashelp.class compare=class listvar novalues ;
run;
ods document close;
ods exclude none;

proc document name=conts;
ods excel options(sheet_name="Compare Datasets");
replay \Compare#1\CompareDatasets#1;
run;
ods excel options(sheet_name="Compare Variables");
replay \Compare#1\CompareVariables#1;
run;
quit;

ods excel close;

[ballardw]

See if this gives you a hint:

ods excel file="e:\temp\test.xlsx" 
    options(sheet_name="Print" sheet_interval="table");
proc print data=sashelp.class;
run;
ods exclude compareSummary;
ods excel options(sheet_name="Compare" );
title1 "STEP 1: Compare contents of files";  
proc compare base=sashelp.class compare=class listvar novalues ;
run;
ods excel close;

[snoopy369]

Not sure what it's supposed to be hinting at?  That produces "Compare" and "Compare 2" as sheet names, which isn't what I want; I want to individually name those two tabs.

[SuzanneDorinski]

This is a way to do it without using PROC DOCUMENT.

data class;
	set sashelp.class;
	drop name;
run;

ods excel file="e:\temp\test.xlsx" options(sheet_name="test" sheet_interval="proc");

proc print data=sashelp.class;
run;

ods excel options(sheet_name="Compare Datasets" sheet_interval="table");

ods select compareDatasets;
title1 "STEP 1: Compare contents of files";

proc compare base=sashelp.class compare=class listvar novalues;
run;

ods excel options(sheet_name="Compare Variables");

ods select compareVariables;

proc compare base=sashelp.class compare=class listvar novalues;
run;

ods excel close;

[snoopy369]

Thanks - that was a consideration as well, trying to avoid two PROC COMPAREs of course but since we're not doing value comparisons it's not exactly expensive.  Thanks for the suggestion!