topic Re: ODS EXCEL sheet naming with multiple tables from a proc in ODS and Base Reporting

ODS EXCEL sheet naming with multiple tables from a proc

snoopy369 — Tue, 13 Jun 2017 22:40:21 GMT

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;

Re: ODS EXCEL sheet naming with multiple tables from a proc

ballardw — Tue, 13 Jun 2017 23:34:46 GMT

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;

Re: ODS EXCEL sheet naming with multiple tables from a proc

snoopy369 — Wed, 14 Jun 2017 15:28:35 GMT

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.

Re: ODS EXCEL sheet naming with multiple tables from a proc

SuzanneDorinski — Thu, 15 Jun 2017 00:33:28 GMT

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;

Re: ODS EXCEL sheet naming with multiple tables from a proc

snoopy369 — Thu, 15 Jun 2017 14:09:07 GMT

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!