topic Re: Suppress ODS output from Results without affecting ODS Excel in ODS and Base Reporting

Suppress ODS output from Results without affecting ODS Excel

AI_Vonnegut — Fri, 29 Nov 2019 14:27:53 GMT

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:

ODS EXCLUDE ALL; --> Indeed suppresses output to Results, but it also excludes data from the Excel sheets (blank)
ODS _ALL_ CLOSE; --> Produces a warning: "No output destinations active"
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.

Re: Suppress ODS output from Results without affecting ODS Excel

Reeza — Fri, 29 Nov 2019 15:52:46 GMT

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

ODS HTML CLOSE;

or

ODS LISTING CLOSE;

Where listing is the other output.

Re: Suppress ODS output from Results without affecting ODS Excel

Tom — Fri, 29 Nov 2019 16:09:32 GMT

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;

Re: Suppress ODS output from Results without affecting ODS Excel

AI_Vonnegut — Fri, 29 Nov 2019 16:42:07 GMT

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; ?

Re: Suppress ODS output from Results without affecting ODS Excel

Tom — Fri, 29 Nov 2019 18:27:41 GMT

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;

Re: Suppress ODS output from Results without affecting ODS Excel

Reeza — Fri, 29 Nov 2019 19:55:03 GMT

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.

Re: Suppress ODS output from Results without affecting ODS Excel

AI_Vonnegut — Fri, 29 Nov 2019 20:04:23 GMT

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

Re: Suppress ODS output from Results without affecting ODS Excel

AI_Vonnegut — Fri, 29 Nov 2019 20:06:49 GMT

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.

Re: Suppress ODS output from Results without affecting ODS Excel

Reeza — Fri, 29 Nov 2019 20:17:28 GMT

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;

Re: Suppress ODS output from Results without affecting ODS Excel

AI_Vonnegut — Fri, 29 Nov 2019 20:18:06 GMT

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

Re: Suppress ODS output from Results without affecting ODS Excel

Reeza — Fri, 29 Nov 2019 20:21:32 GMT

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.

Re: Suppress ODS output from Results without affecting ODS Excel

Tom — Fri, 29 Nov 2019 20:24:33 GMT

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.

Re: Suppress ODS output from Results without affecting ODS Excel

AI_Vonnegut — Fri, 29 Nov 2019 20:38:08 GMT

That code produces:

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

Re: Suppress ODS output from Results without affecting ODS Excel

AI_Vonnegut — Fri, 29 Nov 2019 20:42:48 GMT

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.

Re: Suppress ODS output from Results without affecting ODS Excel

Reeza — Fri, 29 Nov 2019 21:48:57 GMT

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

Re: Suppress ODS output from Results without affecting ODS Excel

Reeza — Fri, 29 Nov 2019 21:50:18 GMT

Why would that even matter though if all the output was needed? Maybe I'm not understanding the requirements then.
If you turned off other destinations/results which are open by default and have only ODS EXCEL open all the results should go there by default.

Re: Suppress ODS output from Results without affecting ODS Excel

Tom — Fri, 29 Nov 2019 21:56:56 GMT

@Reeza wrote:
Why would that even matter though if all the output was needed? Maybe I'm not understanding the requirements then.
If you turned off other destinations/results which are open by default and have only ODS EXCEL open all the results should go there by default.

How does the subprogram that is just trying to use ODS EXCEL the dump some data know what other destinations it needs to close and how to open them again?

Can you show example that turns off all other destinations AND turns that back on again? Works with the goofy things that SAS/Studio sets up? etc.

Re: Suppress ODS output from Results without affecting ODS Excel

Reeza — Fri, 29 Nov 2019 22:02:13 GMT

ODS _all_ close should close all other destinations. Turning them on again is usually the issue. But EG doesn't have all open, it typically only has whatever's specified and turning them off is usually fairly easy.

This seems to work previously:
https://communities.sas.com/t5/SAS-Enterprise-Guide/Supressing-results-in-EG/td-p/342038

Re: Suppress ODS output from Results without affecting ODS Excel

Reeza — Fri, 29 Nov 2019 22:06:55 GMT

This works fine in SAS Studio. Studio resets the settings so you can just turn it off, it turns them on automatically after.

ods _all_ close;
ods excel file='/home/fkhurshed/Demo1/x21.xlsx';

proc report data=sashelp.class; run;
ods excel close;

No results in the Results window, running proc print after goes to results window without the need to reset anything.

Just tested on SAS Academics on Demand, which is 9.4 M6

Re: Suppress ODS output from Results without affecting ODS Excel

Tom — Fri, 29 Nov 2019 22:24:39 GMT

@Reeza wrote:

This works fine in SAS Studio. Studio resets the settings so you can just turn it off, it turns them on automatically after.
ods _all_ close;
ods excel file='/home/fkhurshed/Demo1/x21.xlsx';

proc report data=sashelp.class; run;
ods excel close;
No results in the Results window, running proc print after goes to results window without the need to reset anything.

Just tested on SAS Academics on Demand, which is 9.4 M6

SAS/Studio does not have any function I can call to turn them on again. It might in the next block of code that is submitted but nothing I have seen that I can trigger after I am done dumping my data to excel and want to continue with the rest of my program.