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
Meteorite | Level 14 Ronein
Meteorite | Level 14
Go to Solution

ODS excel

Posted a week ago (1370 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 a week ago (1359 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 a week ago (1360 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
Meteorite | Level 14 Ronein
Meteorite | Level 14

Re: ODS excel

Posted a week ago (1327 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 a week ago (1294 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

sas-innovate-white.png

Missed SAS Innovate in Orlando?

Catch the best of SAS Innovate 2025 — anytime, anywhere. Stream powerful keynotes, real-world demos, and game-changing insights from the world’s leading data and AI minds.

 

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
  • a week ago
  • 1371 views
  • 1 like
  • 3 in conversation