How to Export SAS Data to Excel (with Examples)

In this article, we will show how to export data from SAS to Excel file, along with examples.

PROC EXPORT is used to export data from SAS to an Excel file.

Syntax of PROC EXPORT for Excel Files

The following code exports data from SAS to Excel using PROC EXPORT.

proc export data=sas-dataset-name
  outfile='/path/to/output/filename.xlsx'
  dbms=xlsx
  replace;
  sheet="My Sheet";  
run;

Explanation of PROC EXPORT Syntax

data=sas-dataset-name: SAS dataset you want to export.
outfile: Specifies the desired location and name of the output Excel file.
dbms=xlsx: Indicates that the destination file format is XLSX.
replace: Replaces the Excel file if it already exists. It is optional argument.
sheet: Sheet name to be used in the exported Excel file. It is optional argument.

Sample SAS Dataset

Let's create a sample SAS dataset that will be used to export it to Excel File.

data data1;
  input Company$ Origin$ Sales;
  datalines;
Unilever UK 453
Tesla USA 636
Amazon USA 829
;
run;

The following code exports the dataset data1 to an Excel file named "companies.xlsx" with the sheet name "data1".

proc export data=data1
  outfile='/home/deepanshu88us0/Files/companies.xlsx'
  dbms=xlsx
  replace;
  sheet="data1";
run;

How to Export Multiple SAS Datasets to Excel

The following code exports two datasets to two different sheets in the same excel file. We are using PROC EXPORT twice and specifing both the datasets and sheet names in the data= and sheet= options.

data data1;
  input Company$ Origin$ Sales;
  datalines;
Unilever UK 453
Tesla USA 636
Amazon USA 829
;
run;

data data2;
  input Company$ Industry$;
  datalines;
Unilever FMCG
Tesla Auto
Amazon Tech
;
run;

proc export data=data1
  outfile='/home/deepanshu88us0/Files/companies.xlsx'
  dbms=xlsx
  replace;
  sheet="data1";
run;

proc export data=data2
  outfile='/home/deepanshu88us0/Files/companies.xlsx'
  dbms=xlsx
  replace;
  sheet="data2";
run;

SAS Macro to Export Multiple SAS Datasets to an Excel File

The following macro exports multiple SAS datasets to an Excel file. Each dataset is exported to a separate sheet in the Excel file, with the sheet name being the same as the dataset name.

%macro export_to_excel(filepath, data_names);
    %let data_count = %sysfunc(countw(&data_names));
    %do i = 1 %to &data_count;
        %let data_name = %scan(&data_names, &i);
        proc export data=&data_name
            outfile= &filepath.
            dbms=xlsx
            replace;
            sheet="&data_name";
        run;
    %end;
%mend export_to_excel;

%export_to_excel("/home/deepanshu88us0/Files/companyData.xlsx", data1 data2);

filepath The full path to the output Excel file.
data_names A space-separated list of dataset names to export.

About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

While I love having friends who agree, I only learn from those who don't
Let's Get Connected Email LinkedIn