Use PROC EXPORT to Write SAS Data to External Files and Excel Workbooks - Exchanging Data Between SAS and Microsoft Excel - page 26

Step One - Write an Excel worksheet with the sheet name in Excel converted to
capital letters.
Write the file 'c:\My_files\shoes.xls' with only four variables from the SHOES dataset in the SASHELP
library. If this Excel workbook exists and the worksheet “MY_PAGE” also exists, the worksheet will be
overwritten. If the workbook does not exist, it will be created.
Figure 2.8: SAS Code Export Data to an XLS File.
* Write the MY_PAGE sheet, the sheet name is all capital letters;
* since the sheet name entered on the SHEET= statement is not quoted;
* only variables region, sales, returns, and inventory will be output;
PROC EXPORT DATA=sashelp.shoes
OUTFILE='C:\My_Files\shoes.xls'
DBMS=EXCEL
REPLACE;
SHEET=My_Page;
DBDSOPTS='keep=region sales returns inventory';
RUN;
Step Two - Write an Excel worksheet with a mixed-case sheet name and spaces
converted to underscores by SAS for Excel.
There are two differences between step one and step two which involve the output Excel worksheet name.
The first is that the sheet name contains mixed case letters and underscores when viewed in the Excel file.
The second difference is there will be two sheets in the output Excel workbook.
Figure 2.9: SAS Code Export Data to an XLS File with Mixed-Case Sheet Names.
* Write a sheet called My_New_Page, the sheet name will be mixed case ;
* and the sheet name has underscores instead of spaces in the ;
* name because SAS generated sheet names do not have spaces however ;
* when a sheet name is created or renamed by Excel you can place a ;
* space into the sheet name. A sheet name generated by SAS can contain ;
* a single quote if the sheet name is enclosed in double quotes ;
PROC EXPORT DATA=sashelp.shoes
OUTFILE='C:\My_Files\shoes.xls'
DBMS=EXCEL
REPLACE;
SHEET='My New Page';
DBDSOPTS='keep=region sales returns inventory';
RUN;
Step Three - Overwrite an existing Excel Workbook and output one sheet with a
name in uppercase letters.
Step two and step three have different results when you output an Excel workbook. The first is that the
sheet name contains uppercase letters. The second difference is that the old Excel workbook will be
replaced and the new workbook will have one worksheet.