The SAS Output Delivery System and reporting techniques

ODS EXCEL sheet naming with multiple tables from a proc

Reply
Regular Contributor
Posts: 244

ODS EXCEL sheet naming with multiple tables from a proc

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;
Super User
Posts: 10,500

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

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;
Regular Contributor
Posts: 244

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

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.

Contributor
Posts: 65

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

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;
Regular Contributor
Posts: 244

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

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!

Ask a Question
Discussion stats
  • 4 replies
  • 216 views
  • 1 like
  • 3 in conversation