BookmarkSubscribeRSS Feed
sas_lak
Quartz | Level 8

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

3 REPLIES 3
MadhuKorni
Quartz | Level 8


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;


sas_lak
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 3864 views
  • 1 like
  • 3 in conversation