DATA Step, Macro, Functions and more

ODS HTML - Multiple Excel Sheets

Reply
Contributor
Posts: 46

ODS HTML - Multiple Excel Sheets

Hi All,

I have 10 data sets for county Segment and 10 data sets for state segment wise.

Need to export all datasets into 1 Excel file through ODS HTML according to segment wise on sheets.

Earlier using below code: (creating multiple excel files instead of Sheets);

/*COUNTRY WISE STATS*/

options nodate pageno=1 linesize=100 pagesize=100;

ods html file="D:\xxxxx\yyyyy\Country_Stats.xls";

proc sql;

title1 " Country - 1 Segment DQ Points ";

Title2 " ";

select * from Country_1_segment;

quit;

proc sql;

title1 " Country - 2 Segment DQ Points ";

Title2 " ";

select * from Country_2_segment;

quit;

proc sql;

title1 " Country - 3 Segment DQ Points ";

Title2 " ";

select * from Country_3_segment;

quit;

ods html close;

/*STATE WISE STATS*/

ods html file="D:\xxxxx\yyyyy\State_Stats.xls";

proc sql;

title1 " STATE - 1 Segment DQ Points ";

Title2 " ";

select * from STATE_1_segment;

quit;

proc sql;

title1 " STATE - 2 Segment DQ Points ";

Title2 " ";

select * from STATE_2_segment;

quit;

proc sql;

title1 " STATE - 3 Segment DQ Points ";

Title2 " ";

select * from STATE_3_segment;

quit;

ods html close;

Looking for Country wise information in one sheet, State wise information is on another sheet IN ONE EXCEL FILE

Contributor
Posts: 65

Re: ODS HTML - Multiple Excel Sheets


ods listing close;


ods tagsets.ExcelXP path=" " file = " "

options(sheet_interval = "none" sheetname = "Country ");


----

----

----

options(sheet_interval = "none" sheetname = "State");


---

---

---

ods tagsets.ExcelXP close;

ods listing;


Contributor
Posts: 46

Re: ODS HTML - Multiple Excel Sheets

Thank You,

From your code, every select query output created a new sheet in excel file, but I need 10 select quires under Country into one sheet and remain 10 select query output

into another. (20 select statements create 20 sheets instead of 2 sheets).

Super User
Super User
Posts: 7,401

Re: ODS HTML - Multiple Excel Sheets

Have you tried changing the options for the ods tagsets.excelxp?  MadhuKorni has given you your answer:

options(sheet_interval="none");

Then just proc report your data out and it should all appear on the same page.

Ask a Question
Discussion stats
  • 3 replies
  • 356 views
  • 1 like
  • 3 in conversation