export multiple data sets into multiple sheets in excel

[Ronein]

Hello

I have a big data set with 1 million rows.

I need to export it to multiple sheets in Excel file (By value of group variable).

What is the best way to do it?

Is it possible to create CSV file with multiple sheets?

The users should use it in Excel so any format that applicable in excel is okay: XMLS, CSV, or another format

[PaigeMiller · Accepted solution]

You can use ODS EXCEL with the option SHEET_INTERVAL='BYGROUP'

Example:

proc sort data=sashelp.class out=class;
    by sex;
run;
ods excel file="d:\myfolder\class.xlsx" options(sheet_interval='BYGROUP');
proc print data=class;
    by sex;
run;
ods excel close;

 There is no such thing as a CSV with multiple sheets.

Editor's note: Thanks for PaigeMiller's answer and Reeza's additional context on sheet_name. 

[mss2am]

This is probably the cleanest and easiest-to-understand codeS I have ever seen!!!

I would love to learn more from you about how to become a better coder using SAS

[Ronein]

/****Way1-Export 3 data sets to 3 excel sheets***/
/****Way1-Export 3 data sets to 3 excel sheets***/ 
/****Way1-Export 3 data sets to 3 excel sheets***/
PROC EXPORT DATA=sashelp.cars (where=(Origin="USA"))
  outfile= "/path/Way1.xlsx"
  dbms=xlsx;
  sheet="USA";
run;
 PROC EXPORT DATA=sashelp.cars (where=(Origin="Europe"))
  outfile= "/path/Way1.xlsx"
  dbms=xlsx;
  sheet="Europe";
run;
 PROC EXPORT DATA=sashelp.cars (where=(Origin="Asia"))
  outfile= "/path/Way1.xlsx"
  dbms=xlsx;
  sheet="Asia";
run;


/****Way2-Export 3 data sets to 3 excel sheets***/
/****Way2-Export 3 data sets to 3 excel sheets***/ 
/****Way2-Export 3 data sets to 3 excel sheets***/
ods _all_ close;
ods excel file="/path/Way2.xlsx";
proc report data=sashelp.cars (where=(Origin="USA"));
columns _all_;
run;

proc report data=sashelp.cars (where=(Origin="Europe"));
columns _all_;
run;

proc report data=sashelp.cars (where=(Origin="Asia"));
columns _all_;
run;
ods excel close;
/*Question: How to control sheet name????*/
/*Question: How to create 3 sheets????*/



/****Way3-Export 3 data sets to 3 excel sheets***/
/****Way3-Export 3 data sets to 3 excel sheets***/ 
/****Way3-Export 3 data sets to 3 excel sheets***/
PROC EXPORT DATA=sashelp.cars (where=(Origin="USA"))
  outfile= "/path/Way3.csv"
  dbms=csv;
  sheet="USA";
run;
 PROC EXPORT DATA=sashelp.cars (where=(Origin="Europe"))
  outfile= "/path/Way3.csv"
  dbms=csv;
  sheet="Europe";
run;
 PROC EXPORT DATA=sashelp.cars (where=(Origin="Asia"))
  outfile= "/path/Way3.csv"
  dbms=csv;
  sheet="Asia";
run;
/***Is it possible to  create multiple sheets in CSV file??????**/


 

/****Way4-Export 3 data sets to 3 excel sheets***/
/****Way4-Export 3 data sets to 3 excel sheets***/ 
/****Way4-Export 3 data sets to 3 excel sheets***/
ods _all_ close;/*To prevent print on screen*/

ods path work.temptemp(update) sasuser.templat(update) sashelp.tmplmst(read); 
ods path show; 

ods tagsets.excelxp file= "/path/Way4.XLS" style=htmlblue;
 
ods tagsets.excelxp options(embedded_titles='yes' sheet_interval='none' sheet_name='USA');
proc print data=sashelp.cars (where=(Origin="USA"))  noobs label;
run;
 
ods tagsets.excelxp options(embedded_titles='yes' sheet_interval='none' sheet_name='Europe');
proc print data=sashelp.cars (where=(Origin="Europe"))  noobs label;
run;
 
ods tagsets.excelxp options(embedded_titles='yes' sheet_interval='none' sheet_name='Asia');
proc print data=sashelp.cars (where=(Origin="Asia"))  noobs label;
run;
 
ods tagsets.excelxp close;
ods _all_ close;
 


/****Way5-Export 3 data sets to 3 excel sheets***/
/****Way5-Export 3 data sets to 3 excel sheets***/
/****Way5-Export 3 data sets to 3 excel sheets***/
/*XLSX engine libname*/
libname RRR xlsx "/path/Way5.xlsx";
data RRR.asia;
 set sashelp.cars (where=(Origin='Asia'));
run;
data RRR.usa;
 set sashelp.cars (where=(Origin='USA'));
run;
data RRR.Europe;
set sashelp.cars (where=(Origin='Europe'));
run;

[ballardw]

If you are using arbitrary procs to generate output for a single ODS Excel destination then you set the SHEET_NAME and SHEET_INTERVAL in a ods excel options before each procedure.

Something like:

ods excel file="/path/Way2.xlsx" options(sheet_interval="PROC");

ods excel options(sheet_name="USA");
proc report data=sashelp.cars (where=(Origin="USA"));
columns _all_;
run;

ods excel options(sheet_name="Europe");
proc report data=sashelp.cars (where=(Origin="Europe"));
columns _all_;
run;

ods excel options(sheet_name="Asia");
proc report data=sashelp.cars (where=(Origin="Asia"));
columns _all_;
run;
ods excel close;

The SHEET_INTERVAL here means each procedure generates a single "tab" of output, there are other options, Read the Friendly Manual; then Sheet_name before each step that generated ODS output sets the name of the sheet (Data steps can create ODS output ). The SHEET_LABEL option can also affect the names of the sheets. There are interactions between these three options that are not going to be answered in a short paragraph.

[Reeza]

WAY3 is not correct and will not work. 

WAY4 is outdated and generates an XML file that causes an issue within Excel so you should really be using ODS EXCEL instead, if you have 9.4M3+ (M4 is my recommendation, M3 is still buggy). 

PROC COPY is another method with the libname option, especially if you have the three data sets already split. 

@PaigeMiller is the simplest so far as all your other options are not dynamic - if you add another origin group the code will fail. You can control the name of the sheet with other options - sheet_label for example. 

[PaigeMiller]

---

@Reeza wrote:

WAY3 is not correct and will not work. 

WAY4 is outdated and generates an XML file that causes an issue within Excel so you should really be using ODS EXCEL instead, if you have 9.4M3+ (M4 is my recommendation, M3 is still buggy). 

PROC COPY is another method with the libname option, especially if you have the three data sets already split. 

@PaigeMiller is the simplest so far as all your other options are not dynamic - if you add another origin group the code will fail. You can control the name of the sheet with other options - sheet_label for example. 

---

Yes, SAS has already done the hard work to make the code dynamic, and so that it works on whatever BY group values are in the data set. 

[Reeza]

Yup. And you can control the labels on the page name by using the SHEET_NAME option and specifying the #byval1. This works with either PROC REPORT or PRINT.

ods excel file="/home/fkhurshed/my_courses/cars.xlsx" options(sheet_interval='BYGROUP' sheet_name= '#byval1');
proc print data=cars;
by origin;
run;
ods excel close;