topic ODS HTML - Multiple Excel Sheets in SAS Programming

ODS HTML - Multiple Excel Sheets

sas_lak — Tue, 25 Aug 2015 07:58:43 GMT

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

Re: ODS HTML - Multiple Excel Sheets

MadhuKorni — Tue, 25 Aug 2015 09:00:48 GMT

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;

Re: ODS HTML - Multiple Excel Sheets

sas_lak — Tue, 25 Aug 2015 10:17:13 GMT

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).

Re: ODS HTML - Multiple Excel Sheets

RW9 — Tue, 25 Aug 2015 10:43:31 GMT

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.