topic Re: Closing Excel Workbook after ODS is utilized to create report in ODS and Base Reporting

Closing Excel Workbook after ODS is utilized to create report

ssitharath0420 — Wed, 17 Mar 2021 22:19:02 GMT

Hello,

I am creating a report with ODS Excel. I used proc report and it generate an excel output. The Excel open but doesn't automatically closed. I have to close out the excel manually. Is there a command to close the excel automatically once the ODS Proc Report is generated?

Re: Closing Excel Workbook after ODS is utilized to create report

Cynthia_sas — Wed, 17 Mar 2021 23:28:45 GMT

Hi:
This is what I tell my students...if you open it, you have to close it. This ALWAYS works for me:
ods excel file='c:\temp\makereport.xlsx';
proc report data=sashelp.shoes(obs=5);
run;
ods excel close;

So I'm not sure what you mean when you say "The Excel open but doesn't automatically closed". Can you post your code or your log?

Cynthia

Re: Closing Excel Workbook after ODS is utilized to create report

ballardw — Thu, 18 Mar 2021 00:24:36 GMT

You don't say which interface you are using.

In Foudation (or Base SAS) there is a system setting that says "View results as generated". If you turn that off then the Excel file, or other destinations, won't open automatically.

Re: Closing Excel Workbook after ODS is utilized to create report

ssitharath0420 — Thu, 18 Mar 2021 21:32:52 GMT

@Cynthia_sas ,

This is the code I am using.

ods excel file="\\fenrir\DEVL\sam_s\Standard Opioid ODS Conversion\Summary1.xlsx" options(start_at="1,13"
frozen_headers="5"
frozen_rowheaders="3"
/*autofilter="1-5"*/
sheet_name="Summary"
row_repeat="1"
embedded_titles="yes");

proc report split='~' data=Table1_Final out=Table1_Final_1 spanrows style(header)=[fontweight=bold background=lightblue foreground=black]
style(column)={font_face='Calibri' fontsize=11pt};
column Status P4 P3 P2 P1;
define Status / Display "";
define P1 / Analysis "7/1/20 -~ 12/31/2020" style(column)={cellwidth=15% textalign=center just=decimal};
define P2 / Analysis "4/1/20 -~ 09/30/2020" style(column)={cellwidth=15% textalign=center just=decimal};
define P3 / Analysis "1/1/20 -~ 06/30/2020" style(column)={cellwidth=15% textalign=center just=decimal};
define P4 / Analysis "10/1/19 -~ 03/31/2020" style(column)={cellwidth=15% textalign=center just=decimal};
title1 '# of Members Filling Opioids';
rbreak after / summarize;
compute status;
if _break_ in ('_RBREAK_') then do;
Status = 'Total';
end;
endcomp;
run;

ods excel close;

It is supposed to open the excel and close but for some reason the excel output would not closed.

Re: Closing Excel Workbook after ODS is utilized to create report

Cynthia_sas — Fri, 19 Mar 2021 16:21:05 GMT

Hi:
Without your data, no one can test the code. However you say that "IT is supposed to open the excel and close..." but I am confused by what you mean when you say "it". Do you think that SAS is supposed to launch Excel when the output XLSX file is created? Whether Excel opens or not really depends on your interface -- how you are submitting the code.

As mentioned by @ballardw, if you are creating output using the standard Windowing Environment (or Display Manager) then this is controlled by a setting that you (or your Administrator) sets. If you are using Enterprise Guide or SAS Studio, then Excel output does NOT automatically open within those interfaces.

Cynthia

Re: Closing Excel Workbook after ODS is utilized to create report

ssitharath0420 — Fri, 19 Mar 2021 21:02:48 GMT

@Cynthia_sas , thank you I got it to work by using ODS NORESULT;