ODS Excel Output

[aneeshv]

Team,

I am trying to generate Excel report with multiple sheet. for this i am using the below given code. My problem is even though i am specifying multiple sheet name in the code, All are coming in a single sheet.

ods excel file="Operationsreport.xls" ;

ods excel OPTIONS (

embedded_titles="yes"

sheet_interval="none"

sheet_name="Account Opening"

)

proc sql;

select item_desc ,field1 ,field2

from table1

order by item_code;

proc sql;

select item_desc ,field1 ,field2

from table2

order by item_code;

quit;

run;

ods excel OPTIONS (

embedded_titles="yes"

sheet_interval="none"

sheet_name="Modification Process"

);

　

proc sql;

select item_desc ,field1 ,field2

from table3

order by item_code;

proc sql;

select item_desc ,field1 ,field2

from table4

order by item_code;

quit;

run;

ods _all_ close;

The sheet_interval="none" option is not working i guess.

Please guide.

regards,

Aneesh

[RW9]

Sheet_interval="none" means that there will be no sheet_intervals, i.e. all output appears on one sheet.  I would guess (and haven't time to check) that you really want sheet_interval="proc" or something like that so each procedures output appears on a new sheet.  

[aneeshv]

Thanks for the reply.

Our actual requirement is to print multiple proc outputs into a single sheet.

[RW9]

Sorry, I am confused.  You initial post:

---

I am trying to generate Excel report with multiple sheet. for this i am using the below given code. My problem is even though i am specifying multiple sheet name in the code, All are coming in a single sheet.

---

Is the exacy opposite of what you post now?  If you want all in one sheet, then sheet_interval="none" and don't specify sheet name options in each block.  If you want separate output sheets, then sheet_interval="proc" and specify sheet_name in each instance.

[aneeshv]

Sorry for the confusion.

My actual requirement is multiple sheet with different tables in each sheet.

[RW9]

Just change to:

ods excel file="Operationsreport.xls"  options(
  embedded_titles="yes"
  sheet_interval="proc"
  sheet_name="Account Opening")

And only set it once at the top.  Each further change in sheet name should only look like:

ods excel options(sheet_name="Modification Process");

[Reeza]

There is a bug in ODS EXCEL that controls how pages are generated. 

If you search on here I posted a workaround, it's also on an blog post from Chris Hemedinger