topic Re: ods excel -multiple sheets in SAS Programming

ods excel -multiple sheets

Ronein — Mon, 18 Feb 2019 09:18:56 GMT

Hello

I am using ODS excel to export multiple tables to multiple sheets in one excel file.

I want to create 3 sheets: "one" "two" "three".

In the result I get only one sheet.

What is wrong?

ods excel file="/path/Ex3.XLSX" 
    options(sheet_name="one" sheet_interval='none');
	/*Sheet one*/
	Title 'Table1';
	proc print data=sashelp.class(obs=3) noobs;
	run;
	Title 'Table2';
	proc print data=sashelp.shoes(obs=3) noobs;
	run;
ods excel options(sheet_name="two" sheet_interval='none');
	/*Sheet two*/
	Title 'Tables3';
  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='none');
	/*Sheet three*/
  Title 'Table4';
  proc print data=sashelp.heart(obs=10) noobs;
  run;
ods excel close;

Re: ods excel -multiple sheets

RW9 — Mon, 18 Feb 2019 09:20:52 GMT

You have told it not to use multiple sheets with:

sheet_interval='none'

Re: ods excel -multiple sheets

Ronein — Mon, 18 Feb 2019 12:29:46 GMT

I run this code and still dont get expected result.

In this result each table appear in a different sheet but I want that table+table2 will be in sheet one.

Tables 3 will be in sheet two

Table4 will be in sheet 4

ods excel file="/path/Ex7.XLSX" 
    options(sheet_name="one" );
	/*Sheet one*/
	Title 'Table1';
	proc print data=sashelp.class(obs=3) noobs;
	run;
	Title 'Table2';
	proc print data=sashelp.shoes(obs=3) noobs;
	run;
ods excel options(sheet_name="two" );
	/*Sheet two*/
	Title 'Tables3';
  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 three*/
  Title 'Table4';
  proc print data=sashelp.heart(obs=10) noobs;
  run;
ods excel close;

Re: ods excel -multiple sheets

RW9 — Mon, 18 Feb 2019 12:40:54 GMT

Have you tried setting the option at the right moment:

ods excel file="/path/Ex7.XLSX" 
    options(sheet_name="one" sheet_interval="none");
	/*Sheet one*/
	Title 'Table1';
	proc print data=sashelp.class(obs=3) noobs;
	run;
	Title 'Table2';
	proc print data=sashelp.shoes(obs=3) noobs;
	run;
ods excel options(sheet_name="two" sheet_interval="table");
	/*Sheet two*/
	Title 'Tables3';
  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="table");
	/*Sheet three*/
  Title 'Table4';
  proc print data=sashelp.heart(obs=10) noobs;
  run;
ods excel close;

Re: ods excel -multiple sheets

Ronein — Tue, 19 Feb 2019 04:35:40 GMT

Thank you for your reply.

However, the result of the code that you sent is not giving the desired result.

In this code there are 3 sheets :two two2 two3 and I want to create one sheet called "two" with 3 tables (from proc report)

Joe

Re: ods excel -multiple sheets

Ronein — Tue, 19 Feb 2019 05:08:49 GMT

I found the solution.

Need to use a dummy table.

I really dont know what this code is doing but it solve the problem and get the desired result

ods excel file="/path/Ex7.XLSX"
    options(sheet_name="Sh1" sheet_interval="none");
/*Sheet1-2 tables*/
	Title 'Table1';
	proc print data=sashelp.class(obs=3) noobs;
	run;
	Title 'Table2';
	proc print data=sashelp.shoes(obs=3) noobs;
	run;
/*Sheet2-multiple tables created by proc report*/
/*Add dummy table */
ods excel options(sheet_interval="table");
ods exclude all;
data _null_;
file print;
put _all_;
run;
ods select all;
ods excel options(sheet_name="Sh2"  sheet_interval="none");
  proc report data=sashelp.prdsale;
    column country region actual predict;
	define country / group page;
	define region / group;
	break after country / summarize page;
  run;
 /*Sheet3-one table*/
/*Add dummy table */
ods excel options(sheet_interval="table");
ods exclude all;
data _null_;
file print;
put _all_;
run;
ods select all;
ods excel options(sheet_name="Sh3"  sheet_interval="none");
  proc print data=sashelp.heart(obs=10) noobs;
  run;
ods excel close;

Re: ods excel -multiple sheets

RW9 — Tue, 19 Feb 2019 08:58:52 GMT

The code I presented works perfectly well:

ods excel file="&_sasws_./test.xlsx" options(sheet_name="Sh1" sheet_interval="none");
title 'Table1';
proc print data=sashelp.class(obs=3) noobs;
run;
title 'Table2';
proc print data=sashelp.shoes(obs=3) noobs;
run;
ods excel options(sheet_interval="table");
proc print data=sashelp.cars;
run;
ods excel close;

This will print class and shoes to sh1, and cars to sh2 - it is tested working. Your "Correct" post, does nothing further than what I present here!

Re: ods excel -multiple sheets

Ronein — Tue, 19 Feb 2019 09:06:11 GMT

Can you also Add the proc report output to another sheet and check if you get it in one sheet?

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

Re: ods excel -multiple sheets

RW9 — Tue, 19 Feb 2019 09:10:16 GMT

When I run this code:

ods excel file="&_sasws_./test.xlsx" options(sheet_name="Sh1" sheet_interval="none");
title 'Table1';
proc print data=sashelp.class(obs=3) noobs;
run;
title 'Table2';
proc print data=sashelp.shoes(obs=3) noobs;
run;
ods excel options(sheet_interval="table");
proc print data=sashelp.cars;
run;
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 close;

I get tabs for Sh1 1 = Class and Shoes,

Sh1 2 = Cars

SH1 3-5 = the three summarised tables from the proc report, each on their own tab as three component tables are created - as clearly programmed to do this by the statement:

   break after country / summarize page;