• Ekta Aggarwal

EXPORTING multiple datasets in one excel in different sheets in SAS

In this tutorial we shall learn about how to export various datasets in a single excel (each data in a single sheet) in SAS.

Let us first set our filename using the following code: (My directory name is myfile) and specify the location and file name where the file needs to get saved.

filename myfile "/home/u50132927/My_datasets/Multiple_tab_file.xml";

In this tutorial we are aiming to save SAS' inbuilt datasets: SASHELP.SHOES and SASHELP.CARS in a single excel file. As a result of the following code our file will get saved as an XML file which we can later on covert manually to an excel.

In SHEET_NAME we will specify the sheet name which we want in our output file.

TITLE is optional.

We print our dataset using PROC PRINT.

ODS tagsets.excelxp body=myfile STYLE=HTMLBLUE OPTIONS(SHEET_NAME="Shoes_data");
proc print data=SASHELP.SHOES NOOBS;

Similarly we specify our other datasets (SASHELP.CARS) as follows:

ODS tagsets.excelxp OPTIONS(SHEET_NAME="Cars_data");
TITLE "CARS_data";
proc print data=SASHELP.CARS NOOBS;

Finally we close our ODS TAGSETS AND TITLE command,

ODS tagsets.excelxp CLOSE;