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:
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.
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;
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;
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;
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;
That code produces:
ERROR: Insufficient authorization to access C:\WINDOWS\system32\sashtml.htm.
ERROR: No body file. HTML output will not be created.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.