ODS excel

Ronein — Fri, 09 May 2025 11:53:33 GMT

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;

Re: ODS excel

PaigeMiller — Fri, 09 May 2025 12:02:42 GMT

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-sheet/m-p/770191

Re: ODS excel

Ronein — Fri, 09 May 2025 13:05:57 GMT

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;

Re: ODS excel

Kathryn_SAS — Fri, 09 May 2025 14:19:29 GMT

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

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

topic Re: ODS excel in SAS Programming

ODS excel

Re: ODS excel

Re: ODS excel

Re: ODS excel