SAS How To's

How to Export Data from SAS to Microsoft Excel

SAS Example Code

As a Data Analyst it is common to create a dataset in SAS and carry out further analysis in Microsoft Excel. But, how do you export data from SAS to Excel?

Exporting data from SAS to Microsoft Excel can be done with the EXPORT procedure and the LIBNAME statement. The EXPORT procedure provides flexibility when it comes to including column labels or header names. Whereas exporting data with a LIBNAME statement gives you more options for subsetting data and creating new columns.

In this article, we discuss both methods in more detail and answer common questions such as:

  • How to define a sheet name?
  • How to export column labels?
  • How to create an Excel file without a header?

Do you know? How to Import an Excel File

Sample Data

In this article, we use a sample dataset to export to Excel (work.my_data). This dataset is based on the CARS dataset from the SASHELP library, but with fewer columns.

work.my_data

Export data from SAS to Excel with PROC EXPORT

The EXPORT Procedure

An easy way to export a table from SAS to Excel is with the EXPORT procedure (PROC EXPORT). This procedure is easy-to-understand and provides many options.

Syntax of PROC EXPORT

To export data from SAS to Excel with PROC EXPORT you need to define at least three parameters:

  • DATA=-option to specify the SAS dataset you want to export. For example, DATA=work.my_data.
  • OUTFILE=-option to define the output location and the file name. For instance, OUTFILE=”/folders/myfolders/export/cars.xlsx”
  • DBMS=-option to specify the file extension such as DBMS=xlsx.

DATA=-option

The DATA=-option specifies the SAS dataset you want to export. You can either use a one- or two-level SAS name (i.e., with or without libref). When you export a dataset to Excel, you can use dataset options such as KEEP, RENAME, and WHERE in the DATA=-option.

OUTFILE=-option

The OUTFILE=-option specifies the location and file name of the exported dataset. The file name includes the extension. Keep in mind that the complete path (location and file name) can’t exceed 201 characters.

DBMS=-option

The DBMS=-option specifies the type of file the EXPORT procedure creates (e.g., .xlsx or .xls). It depends on the file type how much data SAS can export. For example, if you export data to Excel, the SAS dataset cannot exceed the maximum of 1,048,576 rows and 16,384 columns.

Examples

The SAS code below exports the work.my_data dataset to Excel and creates a file called cars.xlsx.

proc export data=work.my_data
    outfile="/folders/myfolders/export/cars.xlsx"
    dbms=xlsx;
run;

Note 1: If you want to create an XLS-file with PROC EXPORT, you need to change the file extension in the OUTFILE=-option and use DBMS=xls.

proc export data=work.my_data
    outfile="/folders/myfolders/export/cars.xls"
    dbms=xls;
run;

Note 2: You need the SAS/ACCESS Interface to PC Files license to export data from SAS to Excel. To check if this license is available on your SAS installation, you can use the PROC SETINIT statement.

proc setinit;
run;

How to Overwrite Existing Files

By default, PROC EXPORT doesn’t overwrite existing files. If you try to do this, SAS will write an error to the log and will stop executing. To change this default behavior, you need the REPLACE option. If you use the REPLACE option, you can export and overwrite an existing file.

This is an example of how to use the REPLACE option.

proc export data=work.my_data
    outfile="/folders/myfolders/export/cars.xlsx"
    dbms=xlsx
    replace;
run;

Do you know? How to Archive Multiple Excel Files in one ZIP File from SAS

How to Define an Excel Sheet Name

By default, PROC EXPORT creates an Excel file where the sheet name is equal to the filename. However, you can overwrite this default behavior and define the sheet name with the SHEET=-option. You need to write the sheet name between quotation marks, and it cannot exceed 31 characters.

In the example below, we create the Excel file cars.xlsx and call the sheet All Cars.

proc export data=work.my_data
    outfile="/folders/myfolders/export/cars.xlsx"
    dbms=xlsx
    replace;
    sheet="All Cars";
run;
Change the Sheet Name with the SHEET=-option
Change the Sheet Name with the SHEET=-option

How to Export an Excel File with Multiple Sheets

Many Excel workbooks have multiple worksheets. Nevertheless, PROC EXPORT creates only one file with one sheet. So, how do you create an Excel file with multiple sheets using SAS?

You can create an Excel file with multiple sheets by submitting one PROC EXPORT statement for each sheet. In each PROC EXPORT statement, you change the DATA=-option and the SHEET=-option. But, the OUTFILE=-option and the DBMS=-option remain unchanged.

In the example below, we create an Excel file with two sheets, namely All Cars and Ford Cars. As you can see, we use dataset options, such as KEEP, RENAME, and WHERE in the DROP=-option.

/* One Excel File with Multiple Sheets */
/* Create a Sheet with All Cars */
proc export data=work.my_data
    outfile="/folders/myfolders/export/cars.xlsx"
    dbms=xlsx
    replace;
    sheet="All Cars";
run;
 
/* Create a Sheet with only Ford Cars */
proc export data=work.my_data 
    (where=(Make="Ford")
    keep=Make Model
    rename=(Model = Ford_Model))
    outfile="/folders/myfolders/export/cars.xlsx"
    dbms=xlsx
    replace;
    sheet="Ford Cars";
run;
Create Multiple Sheets

How to Export a Table to Excel with SAS Labels

SAS datasets have column names and, optionally, column labels. Column labels can contain special characters such as blanks and percentage signs. For this reason, column labels are more elegant and are frequently used in reports. However, by default, PROC EXPORT exports the column names instead of the column labels. So, how do you export the column labels with PROC EXPORT?

You use the LABEL option to export a sheet with the column labels instead of the column names. You place this option after the REPLACE option. If a column doesn’t have a label, then PROC EXPORT exports the column name.

With the SAS code below, we export the column labels instead of the column names.

/* Export the column labels */
proc export data=work.my_data
    outfile="/folders/myfolders/export/cars.xlsx"
    dbms=xlsx
    replace
    label;
    sheet="All Cars";
run;
Show column labels instead of column variables

With a PROC CONTENTS statement, you can check whether a column has a label. For instance, some of the columns of our sample data work.my_data have labels. 

proc contents data=work.my_data;
run;

Do you know? How to Change Variable Labels

How to Export a Table to Excel without Header (Column Names)

Typically, you want your Excel file to have a header (i.e., column names). However, sometimes you need to create a file without one. So, how do you export data from SAS to Excel with a header?

You can prevent SAS from exporting the header to Excel by setting the PUTNAMES=-option to No. By default, this option is set to Yes. In contrast to other options, you write the word NO without quotation marks.

In the example below, we use PUTNAMES=NO to prevent SAS from adding column names to the Excel file.

proc export data=work.my_data
    outfile="/folders/myfolders/export/cars.xlsx"
    dbms=xlsx
    replace;
    sheet="All Cars";
    putnames=NO;
run;
PROC EXPORT without header (column names)

Do you know? How to Save SAS Output as a PDF file

Export data from SAS to Excel with the LIBNAME Statement

The LIBNAME Statement

A less known, but also very efficient way to export data from SAS to Excel is by creating a library with the LIBNAME statement.

Normally, you use the LIBNAME statement to create a library to store your SAS datasets. However, with the LIBNAME statement’s ENGINE option, you can also create libraries to import and export Excel files. Because of this option, it becomes very convenient to save the output of a DATA step or a SAS procedure directly as an Excel file.

Syntax

You create a library to export data to Excel with the LIBNAME statement. The statement starts with the LIBNAME keyword, followed by the libref and the engine type, and finally, the location and Excel file name.

LIBNAME libref XLSX "path";
  • libref: The libref is the name of the library. A libref is at most 8 characters long, starts with a letter or an underscore, and can only include letters, numbers, or underscores.
  • engine: The engine provides SAS with instructions on how to treat the data. The default engine is BASE for SAS tables. However, SAS treats the data in the library as an Excel file with the XLSX engine.
  • path: The location and the file name (including the file extension).

A normal library (without specifying the engine) contains one or more SAS datasets. However, if you use the XLSX engine, then a library is one Excel file where the “datasets” are the worksheets of the file.

Do you know? 10 FAQs about SAS Libraries

Example

With the LIBNAME statement below, we create the library my_xlsx that refers to the Excel file cars.xlsx.

libname my_xlsx xlsx '/folders/myfolders/export/cars.xlsx';

Note: You can only use the LIBNAME statement to export (and import) Excel files if you use SAS version 9.04.01M2 or higher. With PROC SETINIT or the global macro variable &sysvlong you can check your SAS version.

How to Use a LIBNAME Statement to Export Data to Excel

After you have created a library that refers to an Excel file, you can use this library directly to export data as a worksheet. For example, with the code below, we export the work.my_data dataset and create the worksheet all_cars in the cars.xlsx file.

data my_xlsx.all_cars;
    set work.my_data;
run;

To create an Excel file with multiple sheets, you just need to submit another DATA step. (Don’t change the libref.)

For example:

data my_xlsx.only_ford_cars;
    set work.my_data (where=(Make = "Ford"));
run;

Caution: When you use this method to export data to Excel, SAS replaces without warning existing data. While with PROC EXPORT, you need to explicitly specify to overwrite existing data with the REPLACE option.

Advantages

An advantage of this method is that it also works for SAS procedures like PROC SQL or PROC SORT.

For example, with the code below, we directly export the result of the SORT procedure to an Excel sheet.

proc sort data=work.my_data out=my_xlsx.all_cars_sorted;
    by EngineSize;
run;

Disadvantages

A drawback of this method is that you can’t export column labels. It always exports the column headers. Nor can you create a worksheet without a header.

Did you know? How to use the LIBNAME statement to Import an Excel file

Similar Posts