topic Re: ODS Excel Output in SAS Enterprise Guide

ODS Excel Output

aneeshv — Thu, 04 May 2017 09:32:32 GMT

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

Re: ODS Excel Output

RW9 — Thu, 04 May 2017 09:44:22 GMT

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.

Re: ODS Excel Output

aneeshv — Thu, 04 May 2017 10:55:51 GMT

Thanks for the reply.

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

Re: ODS Excel Output

RW9 — Thu, 04 May 2017 11:04:28 GMT

Sorry, I am confused. You initial post:

---

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.

Re: ODS Excel Output

aneeshv — Thu, 04 May 2017 11:21:45 GMT

Sorry for the confusion.

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

Re: ODS Excel Output

RW9 — Thu, 04 May 2017 11:32:52 GMT

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");

Re: ODS Excel Output

Reeza — Thu, 04 May 2017 14:41:59 GMT

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