topic Re: SAS 9.4 M5 ODS Excel output into multiple worksheets not working in New SAS User

SAS 9.4 M5 ODS Excel output into multiple worksheets not working

JerryK60 — Mon, 12 Nov 2018 22:28:04 GMT

After the recent upgrade to SAS 9.4 M5 the following code does not produce an excel workbook with the output on separate sheets. Prior to the upgrade this worked fine.

ods excel file= "outputfile.xlsx" options (sheet_name= "Screen Fails" sheet_interval= "none);

proc report data= screenfails;

...

run;

ods excel options (sheet_name= "A5 Subjects" sheet_interval= "none");

proc report data= a5subjects;

...

run;

ods excel options (sheet_name= "A6 Subjects" sheet_interval= "none");

proc report data= a6subjects;

...

run;

ods excel close;

Thank you for assisting me.

Re: SAS 9.4 M5 ODS Excel output into multiple worksheets not working

VDD — Mon, 12 Nov 2018 22:35:46 GMT

this

ods excel file= "outputfile.xlsx" options (sheet_name= "Screen Fails" sheet_interval= "none);

should be this

ods excel file= "outputfile.xlsx" options (sheet_name= "Screen Fails" sheet_interval= "none");

Re: SAS 9.4 M5 ODS Excel output into multiple worksheets not working

JerryK60 — Mon, 12 Nov 2018 22:36:45 GMT

I made a typo. That is not the problem I'm trying to solve.

Re: SAS 9.4 M5 ODS Excel output into multiple worksheets not working

Cynthia_sas — Mon, 12 Nov 2018 22:41:13 GMT

Hi:
I assume the missing quote in the first Options was a typing error?

I believe that with 9.4M5, you need to use sheet_interval="now", as shown below:

ods excel file='c:\temp\testsubopt_now.xlsx'
    options(sheet_name="one" sheet_interval='none');

	proc print data=sashelp.class(obs=3);
	run;

	proc print data=sashelp.shoes(obs=3);
	run;

ods excel options(sheet_name="two" sheet_interval="now");

  proc report data=sashelp.prdsale;
    column country region actual predict;
	define country / group page;
	define region / group;
	break after country / summarize page;
  run;

ods excel options(sheet_name="three" sheet_interval="now");

  proc print data=sashelp.heart(obs=10);
  run;
ods excel close;

Hope this helps,

cynthia

Re: SAS 9.4 M5 ODS Excel output into multiple worksheets not working

Reeza — Mon, 12 Nov 2018 22:39:59 GMT

We can only see exactly what you post, and it’s not clear to me what the issue is? None means that all output is on one sheet, is that not happening? If you want separate sheets, you should be using proc as the interval. If your previous version was below M3, it was still in pre-production/testing so it is possible the behaviour has changed.

Re: SAS 9.4 M5 ODS Excel output into multiple worksheets not working

JerryK60 — Mon, 12 Nov 2018 22:41:10 GMT

It was, and much regretted.

Re: SAS 9.4 M5 ODS Excel output into multiple worksheets not working

djh — Sat, 03 Jul 2021 02:32:49 GMT

I encounter this when using ods excel to export multiple datasets to a workbook but without contens after set "ods select noe;"

Try to use the following the options, it works for me.

ods exclude all;

ods exclude none;

Re: SAS 9.4 M5 ODS Excel output into multiple worksheets not working

Cynthia_sas — Sat, 03 Jul 2021 21:52:15 GMT

Hi:
You've added your posting onto a 3 year old post and I'm not sure that your current post is related to older one. I would recommend that you start a new posting and be sure to explain your desired output and the problem you're having. Also it's a good idea to make up a sample program using SASHELP datasets to illustrate the issue you're having.
Cynthia

Re: SAS 9.4 M5 ODS Excel output into multiple worksheets not working

Goldfish1 — Thu, 09 Sep 2021 17:22:44 GMT

This is really help for multiple tables on multiple sheets!