Tag Archives: replace

3 Ways to Export Your Data to Excel

SAS offers many different ways to export data to Excel, including Excel ODS output. The following alternatives to ODS are also available:

The File Export Wizard

This is an easy way to export your data without having to write any code. To use this wizard:

  • Select File>>Export Data
  • Choose the library and SAS data set name
  • Select a data source from the dropdown list (in this case, the version of Excel you’d like to export to – other types of data sources are also available here)
  • Browse to the folder where you’d like to create your Excel Workbook and type a name for the file
  • You then have the option to save the Proc Export statements generated by the Wizard to a SAS program file

Proc Export

If you chose to use the File Export Wizard to save your Proc Export statements, your code may look something like this:

PROC EXPORT DATA=sasdatasetname
OUTFILE=”\\path\filename.xls”
DBMS=EXCEL REPLACE;
SHEET=”sheetname”;
RUN;

  • The data set you’d like to export goes after DATA=
  • The file path goes after OUTFILE=
  • You are exporting to Excel, so DBMS=EXCEL
  • If you wish to replace any existing workbook or worksheet with the same name, use the REPLACE option.
  • The SHEET= statement is optional, but if you wish to create a multi-sheet workbook, it is useful. For your next PROC EXPORT statement, just specify a different file and a new SHEET name.

The Libname Statement

This is a newer option for exporting to Excel which does not support the REPLACE option, so if you already have a file of the same name you are trying to create, you will have to delete or rename the older version first. Also, while the libname is assigned, you will not be able to access the Excel document you have created; you will first have to exit out of SAS or clear the libname. It is easy to create multiple sheets using this method, though, as shown below.

libname myxls \\path\filename.xls;

DATA myxls.sheetname1;
SET sasdataset1;
DATA myxls.sheetname2;
SET sasdataset2;
RUN;

PROC SQL;
Create table myxls.sheetname3 as
SELECT *
from sasdataset3;
QUIT;