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

Figure 2.10: SAS Code Export Data to an XLS File with Uppercase Sheet Names.
* Write sheet called MY_PAGE, the sheet name will be all capital letters;
* Old output file will be deleted and the new file will have one sheet;
PROC EXPORT DATA=sashelp.shoes
OUTFILE='C:\My_Files\shoes.xls'
DBMS=EXCEL
REPLACE;
SHEET=My_Page;
NEWFILE=YES;
DBDSOPTS='keep=region sales returns inventory';
RUN;
Example 2.4 PROC EXPORT Using the DBMS=EXCELCS Option
This code was executed on a Windows 64-bit operating system using 64-bit SAS 9.3, but the Excel
program was a 32-bit based copy of Excel. Since this computer uses 64-bit hardware, PROC EXPORT
requires the use of SAS/ACCESS to PC Files Server software. The option NEWFILE= is not valid in that
configuration. Windows 64-bit operating systems and remote connections both require the use of the PC
Files Server to execute. This code uses the DBMS=EXCELCS and the SERVICE=SASPCFILE statements.
Also note that SAS 9.2 and SAS 9.3 use different SAS PC Files Server executable modules, but maintain
the same names and are therefore not compatible with each other. They also use different defaults ports
when the processing occurs. See the documentation for your version of SAS software for complete details.
In addition, be aware that some of these features may not be available in SAS versions prior to the third
maintenance release of SAS 9.2. The PROC EXPORT DBMS=EXCELCS statement will write to *.xls,
*.xlsb, and *.xlsx files but not to *.xlsm file formats.
Figure 2.11: SAS Code Export Data to an xls file with Uppercase Sheet Names.
* Write a sheet called MY_PAGE, the sheet name will be all capital;
* letters since the sheet name entered on the SHEET= statement is not;
* quoted;
* The PROC EXPORT DBMS=EXCELCS statement will write to *.xls, *.xlsb, ;
* *.xlsx but not to *.xlsm file formats.;
PROC EXPORT DATA=sashelp.shoes
OUTFILE='C:\My_Files\shoes.xlsb'
DBMS=EXCELCS
REPLACE;
SHEET=My_Page;
SERVICE=SASPCFILE;
RUN ;
Figure 2.12a: The Result of the PROC EXPORT Execution.
1 PROC EXPORT DATA=sashelp.shoes
2 OUTFILE='C:\My_Files\shoes.xlsb'
3 DBMS=EXCELCS
4 REPLACE;
5 SHEET=My_Page;
6 SERVICE=SASPCFILE;
7 RUN;
NOTE: "MY_PAGE" range/sheet was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 12.43 seconds
cpu time 0.64 seconds