Desktop productivity for business analysts and programmers

ODS Excel Output

Reply
Occasional Contributor
Posts: 7

ODS Excel Output

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

Esteemed Advisor
Esteemed Advisor
Posts: 7,190

Re: ODS Excel Output

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.  

Occasional Contributor
Posts: 7

Re: ODS Excel Output

Thanks for the reply.

Our actual requirement is to print multiple proc outputs into a single sheet.
Esteemed Advisor
Esteemed Advisor
Posts: 7,190

Re: ODS Excel Output

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.

Occasional Contributor
Posts: 7

Re: ODS Excel Output

Sorry for the confusion.

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

Esteemed Advisor
Esteemed Advisor
Posts: 7,190

Re: ODS Excel Output

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");
Grand Advisor
Posts: 17,311

Re: ODS Excel Output

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

Ask a Question
Discussion stats
  • 6 replies
  • 115 views
  • 0 likes
  • 3 in conversation