Suppress ODS output from Results without affecting ODS Excel

[AI_Vonnegut]

I want to exclude the PROC REPORT (very long) from the Results window without affecting the export into Excel (Enterprise Guide 7.15, Office 365, Window 10). After trying numerous methods, I'm stuck and thought perhaps I'm missing something obvious. Here is the general code:

ODS NORESULTS;
ODS EXCLUDE ALL;
ODS EXCEL FILE="&OUT.TEST.xlsx";
ODS EXCEL OPTIONS(AUTOFILTER="1-8" SHEET_NAME="ABC");

PROC REPORT DATA=OUT.TEST NOWD HEADSKIP SPANROWS;
	COLUMN OBS T1 T2;
	DEFINE OBS / DISPLAY "Obs";
RUN;

ODS EXCEL CLOSE;
ODS EXCLUDE NONE;
ODS RESULTS;

I have tried all these ODS options, with the following results:

1. ODS EXCLUDE ALL; --> Indeed suppresses output to Results, but it also excludes data from the Excel sheets (blank)
2. ODS _ALL_ CLOSE;  --> Produces a warning: "No output destinations active"
3. ODS NORESULTS;    --> No effect

I've tried  different combinations of the above codes and also different placement within the code, with no success. The NOPRINT option within PROC REPORT is also inapplicable since it also excludes data from the Excel export. The goal is to wrap this report within an option to localize the output suppression since it is surrounded by other code in the program where I do want the output. Any tips would be appreciated.

[Reeza]

close the other desitnations. If it's HTML it would be

ODS HTML CLOSE;

or

ODS LISTING CLOSE;

Where listing is the other output.

[Tom · Accepted solution]

Looks like a bug to me. You would expect that:

ods excel exclude none;

would do the trick.

By find that I need to explicitly select what outputs I want EXCEL to produce. So using ODS TRACE I see that PROC REPORT's output is named REPORT.  So adding 

ods excel select report;

did the trick.

Test code:

ods show;
ods exclude all ;
ods excel file='C:\downloads\x2.xlsx';
ods show ;
ods excel show;
ods excel select report;
ods show ;
ods excel show;
proc report data=sashelp.class; run;
ods excel close;
ods exclude none;

[AI_Vonnegut]

Tom, that worked indeed. However, I'm still not sure how you tracked down the issue with ODS TRACE + SHOW. I only see generic "exclude all" outputs in the log that don't help to pinpoint the problem.

I don't know if you're saying this is a SAS bug. But if instead of proc report I had proc print, would the solution then become ods excel select print; ?

[Tom]

Use ODS TRACE to see what the names are for the ODS outputs that a step produces.

965   ods trace on;
966   proc print data=sashelp.class; run;


Output Added:
-------------
Name:       Print
Label:      Data Set SASHELP.CLASS
Data Name:  ProcPrintTable
Path:       Print.Print
-------------
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


967   ods trace off;

[AI_Vonnegut]

Got it, thanks. I had to run TRACE on proc report in isolation to get a clear output.

[Reeza]

There's no bug, you have multiple destinations open so the output goes to all by default. Turn off the destinations you don't want and then the output would go only to ODS EXCEL.

[AI_Vonnegut]

I have not opened any other ODS sessions. In either case, ODS HTML CLOSE, ODS LISTING CLOSE, and ODS _ALL_ CLOSE still do not fix the issue. I don't know what else I would need to close for this method to work.

[Reeza]

What version of SAS do you have? If this is a bug it would likely be documented already.

EDIT: you can find your version by running the following, it should be something like : SAS 9.4 TS1M5 

ODS EXCEL is only production version after 9.4 TS1M3.

proc product_status;run;

And can you test the following, post the log and let us know if the file is created with the data set and if any other output is generated.

ods _all_ close;

ods excel file='C:\demo.xlsx' ;

proc print data=sashelp.class;
run;

ods excel close;

ods html; 

[AI_Vonnegut]

EG is 7.15 as mentioned.
SAS Foundation: 9.4M3 (/STAT 14.1)

[Reeza]

EG can be different Base versions, ergo my question.

9.4 M3 does have a bug within ODS EXCEL primarily with creating and switching sheets, but not with controlling other output AFAIK.

[AI_Vonnegut]

That code produces:

ERROR: Insufficient authorization to access C:\WINDOWS\system32\sashtml.htm.
ERROR: No body file. HTML output will not be created.

[Reeza]

Change the path to one that's valid for your computer...

[Tom]

The BUG is that you cannot use ODS EXCEL EXCLUDE NONE or ODS EXCEL SELECT ALL to override the ODS EXCLUDE ALL.

Instead you have to use list specific outputs in the ODS EXCEL SECECT statement.

[AI_Vonnegut]

I have access to another computer with SAS 9.4M6 (15.1) - the issue still exists there. The only method that works is ODS EXCEL SELECT, as you mentioned.