cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Ronein
Onyx | Level 15 Ronein
Onyx | Level 15
Go to Solution

ODS excel

Posted 05-09-2025 07:53 AM (2017 views)

Hello

Here are 2 example of export sas reports in Excel using ODS excel.

Example1:

This example working 100% and results are as I wish.

I want to export-2 reports into Sheet1, 1 Report into Sheet2, 1 Report into Sheet 3

ods excel file="/usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/q13.xlsx"
    options(sheet_name="Sheet1" sheet_interval="none"   embedded_titles='yes');
	/*Sheet1*/
	Title 'Table1-In Sheet1';
	proc print data=sashelp.class(obs=3) noobs;
	run;
	Title 'Table2-In Sheet1';
	proc print data=sashelp.shoes(obs=3) noobs;
	run;
ods excel options(sheet_name="Sheet2" sheet_interval="table"   embedded_titles='yes');
	/*Sheet2*/
	Title 'Tables3--In Sheet2';
  proc report data=sashelp.prdsale;
    column country region actual predict;
	define country / group;
	define region / group;
	break after country / summarize;
  run;
ods excel options(sheet_name="Sheet3" sheet_interval="table"   embedded_titles='yes');
	/*Sheet3*/
  Title 'Table4-In Sheet3';
  proc print data=sashelp.heart(obs=10) noobs;
  run;
ods excel close;

Example2: 

This example is not working well and I dont get results as I wish.

I want to export- 1 report into sheet1, 2 reports into sheet 2, 1 report into sheet3.

The problem is that I want 2 reports in sheet 2 but i get one report in sheet2a and one in sheet 2b.

Please note that NOW option is not in my sas version so i cannot use it

ods excel file="/usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/q14.xlsx"
    options(sheet_name="Sheet1" sheet_interval="none"   embedded_titles='yes');
	/*Sheet1*/
	Title 'Table1-In Sheet1';
	proc print data=sashelp.class(obs=3) noobs;
	run;


	ods excel options(sheet_name="Sheet2" sheet_interval="table"   embedded_titles='yes');
	/*Sheet2*/
	Title 'Table2-In Sheet2';
	proc print data=sashelp.shoes(obs=3) noobs;
	run;
	Title 'Tables3--In Sheet2';
  proc report data=sashelp.prdsale;
    column country region actual predict;
	define country / group;
	define region / group;
	break after country / summarize;
  run;


ods excel options(sheet_name="Sheet3" sheet_interval="none"   embedded_titles='yes');
	/*Sheet3*/
  Title 'Table4-In Sheet3';
  proc print data=sashelp.heart(obs=10) noobs;
  run;
ods excel close;

 

 

 

 

0 Likes
Reply
1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26 PaigeMiller
Diamond | Level 26

Re: ODS excel

Posted 05-09-2025 08:02 AM (2006 views)  |   In reply to Ronein

Long discussion on this topic which you initiated, and for which you have marked an answer as correct.

https://communities.sas.com/t5/SAS-Programming/Export-to-XLSX-Excel-2-sheets-and-3-tables-in-each-sh...

--
Paige Miller

View solution in original post

Reply
3 REPLIES 3
PaigeMiller
Diamond | Level 26 PaigeMiller
Diamond | Level 26

Re: ODS excel

Posted 05-09-2025 08:02 AM (2007 views)  |   In reply to Ronein

Long discussion on this topic which you initiated, and for which you have marked an answer as correct.

https://communities.sas.com/t5/SAS-Programming/Export-to-XLSX-Excel-2-sheets-and-3-tables-in-each-sh...

--
Paige Miller
Reply
Ronein
Onyx | Level 15 Ronein
Onyx | Level 15

Re: ODS excel

Posted 05-09-2025 08:29 AM (1974 views)  |   In reply to PaigeMiller

Great great!!

Now I understand the trick of  ods none and as i understand need to apply it only in case of -

multiple reports in sheet_K  and also multiple reports in sheet_K+1

 

/***Example1: Sheet1-one report, Sheet2-One report, Sheet3-one report, Sheet4-one report, Sheet5-one report, sheet6-one report***/
/***Example1: Sheet1-one report, Sheet2-One report, Sheet3-one report, Sheet4-one report, Sheet5-one report, sheet6-one report***/
/***Example1: Sheet1-one report, Sheet2-One report, Sheet3-one report, Sheet4-one report, Sheet5-one report, sheet6-one report***/
/***Example1: Sheet1-one report, Sheet2-One report, Sheet3-one report, Sheet4-one report, Sheet5-one report, sheet6-one report***/
ods excel file="/usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/Ex1.xlsx"
options(embedded_titles='yes' sheet_name="SH1" sheet_interval="proc");
title;
title 'Report_a_BMW';
proc print data=SASHELP.CARS(WHERE=(compress(make)='BMW')) noobs;
run;
ods excel options(embedded_titles='yes'  sheet_name="SH2" sheet_interval="proc");
title1 '';
title2 'Report_b_Jaguar';
proc print data=SASHELP.CARS(WHERE=(compress(make)='Jaguar')) noobs;
run;
ods excel options(embedded_titles='yes'  sheet_name="SH3" sheet_interval="proc");
title1 '';
title2 'Report_c_Lincoln';
proc print data=SASHELP.CARS(WHERE=(compress(make)='Lincoln')) noobs;
run;
ods excel options(embedded_titles='yes'  sheet_name="SH4" sheet_interval="proc");
title;
title 'Report_d_Sedan';
proc print data=SASHELP.cars(WHERE=(compress(Type)='Sedan' )) noobs;
run;
ods excel options(embedded_titles='yes'  sheet_name="SH5" sheet_interval="proc");
title1 '';
title2 'Report_ae_Sports';
proc print data=SASHELP.cars(WHERE=(compress(Type)='Sports' )) noobs;
run;
ods excel options(embedded_titles='yes'  sheet_name="SH6" sheet_interval="proc");
title1 '';
title2 'Report_f_SUV';
proc print data=SASHELP.cars(WHERE=(compress(Type)='SUV' )) noobs;
run;
ods excel close;

/***Example2: Sheet1-3 report2, Sheet2-3 reports**/
/***Example2: Sheet1-3 report2, Sheet2-3 reports**/
/***Example2: Sheet1-3 report2, Sheet2-3 reports**/
/**sheet_interval='none'   say stay in the same sheet**/
ods excel file="/usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/Ex2.xlsx"
options(embedded_titles='yes' sheet_name="SH1" sheet_interval="none");
/**Sheet1**/
title;
title 'Report_a_BMW';
proc print data=SASHELP.CARS(WHERE=(compress(make)='BMW')) noobs;
run;
title1 '';
title2 'Report_b_Jaguar';
proc print data=SASHELP.CARS(WHERE=(compress(make)='Jaguar')) noobs;
run;
title1 '';
title2 'Report_c_Lincoln';
proc print data=SASHELP.CARS(WHERE=(compress(make)='Lincoln')) noobs;
run;

/*just dummy code, would not print anything due to "ODS SELECT NONE;"*/
/*use for resetting options "Sheet_interval=" ,and make it reactive "Sheet_interval=none" again**/
ods select none;
ods excel options( sheet_interval="proc");
proc print data=sashelp.class;run;
ods select all;

/**Sheet2**/
ods excel options(embedded_titles='yes'  sheet_name="SH2" sheet_interval="none");
title;
title 'Report_d_Sedan';
proc print data=SASHELP.cars(WHERE=(compress(Type)='Sedan' )) noobs;
run;
title1 '';
title2 'Report_e_Sports';
proc print data=SASHELP.cars(WHERE=(compress(Type)='Sports' )) noobs;
run;
title1 '';
title2 'Report_f_SUV';
proc print data=SASHELP.cars(WHERE=(compress(Type)='SUV' )) noobs;
run;
ods excel close;



/***Example3: Sheet1-1 report , Sheet2-2 reports, sheet3-2 reports, sheet4-1 report**/
/***Example3: Sheet1-1 report , Sheet2-2 reports, sheet3-2 reports, sheet4-1 report**/
/***Example3: Sheet1-1 report , Sheet2-2 reports, sheet3-2 reports, sheet4-1 report**/
ods excel file="/usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/Ex3.xlsx"
/**Sheet1-one report**/
options(embedded_titles='yes' sheet_name="SH1" sheet_interval="proc");
title;
title 'Report_a_BMW';
proc print data=SASHELP.CARS(WHERE=(compress(make)='BMW')) noobs;
run;


ods excel options( embedded_titles='yes' sheet_name="SH2"   sheet_interval="none");
/**Sheet2-two reports**/
title1 '';
title2 'Report_b_Jaguar';
proc print data=SASHELP.CARS(WHERE=(compress(make)='Jaguar')) noobs;
run;
title1 '';
title2 'Report_c_Lincoln';
proc print data=SASHELP.CARS(WHERE=(compress(make)='Lincoln')) noobs;
run;


/*just dummy code, would not print anything due to "ODS SELECT NONE;"*/
ods select none;
ods excel options( sheet_interval="proc");
proc print data=sashelp.class;run;
ods select all;


/**Sheet3-two reports**/
ods excel options(embedded_titles='yes'  sheet_name="SH3" sheet_interval="none");
title;
title 'Report_d_Sedan';
proc print data=SASHELP.cars(WHERE=(compress(Type)='Sedan' )) noobs;
run;
title1 '';
title2 'Report_e_Sports';
proc print data=SASHELP.cars(WHERE=(compress(Type)='Sports' )) noobs;
run;


/**Sheet3*-one report*/
ods excel options( embedded_titles='yes' sheet_name="SH2"   sheet_interval="proc");
title1 '';
title2 'Report_f_SUV';
proc print data=SASHELP.cars(WHERE=(compress(Type)='SUV' )) noobs;
run;
ods excel close;



/***Example4: Sheet1-6 reports**/
/***Example4: Sheet1-6 reports**/
/***Example4: Sheet1-6 reports**/
ods excel file="/usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/Ex4.xlsx"
/**Sheet1-one report**/
options(embedded_titles='yes' sheet_name="SH1" sheet_interval="none");
title;
title 'Report_a_BMW';
proc print data=SASHELP.CARS(WHERE=(compress(make)='BMW')) noobs;
run;
title1 '';
title2 'Report_b_Jaguar';
proc print data=SASHELP.CARS(WHERE=(compress(make)='Jaguar')) noobs;
run;
title1 '';
title2 'Report_c_Lincoln';
proc print data=SASHELP.CARS(WHERE=(compress(make)='Lincoln')) noobs;
run;
title;
title 'Report_d_Sedan';
proc print data=SASHELP.cars(WHERE=(compress(Type)='Sedan' )) noobs;
run;
title1 '';
title2 'Report_e_Sports';
proc print data=SASHELP.cars(WHERE=(compress(Type)='Sports' )) noobs;
run;
title1 '';
title2 'Report_f_SUV';
proc print data=SASHELP.cars(WHERE=(compress(Type)='SUV' )) noobs;
run;
ods excel close;

 

 

 

0 Likes
Reply
Kathryn_SAS
SAS Employee Kathryn_SAS
SAS Employee

Re: ODS excel

Posted 05-09-2025 10:19 AM (1941 views)  |   In reply to Ronein

There is also a SHEET_INTERVAL='NOW' option as described below:

 

https://support.sas.com/kb/57/766.html

 

0 Likes
Reply

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • ‎05-09-2025 07:53 AM
  • 2018 views
  • 1 like
  • 3 in conversation