How to Export Data from SAS to Excel (With Examples)


You can use proc export to quickly export data from SAS to an Excel file.

This procedure uses the following basic syntax:

/*export data to file called my_data.xlsx*/
proc export data=my_data
    outfile="/home/u13181/my_data.xlsx"
    dbms=xlsx
    replace;
    sheet="First Data";
run;

Here’s what each line does:

  • data: Name of dataset to export
  • outfile: Location to export Excel file
  • dmbs: File format to use for export
  • replace: Replace the file if it already exists
  • sheet: Name to display on sheet in Excel workbook

The following examples show how to use this function in practice.

Example 1: Export One Dataset to One Excel Sheet

Suppose we have the following dataset in SAS:

/*create dataset*/
data my_data;
    input A B C;
    datalines;
1 4 76
2 3 49
2 3 85
4 5 88
2 2 90
4 6 78
5 9 80
;
run;

/*view dataset*/
proc print data=my_data;

We can use the following code to export this dataset to an Excel file called my_data.xlsx:

/*export dataset*/
proc export data=my_data
    outfile="/home/u13181/my_data.xlsx"
    dbms=xlsx
    replace;
    sheet="First Data";
run;

I can then navigate to the location on my computer where I exported the file and view it in Excel:

The data in Excel matches the dataset from SAS and the sheet in the Excel workbook is called “First Data” just like I specified in the proc export statement.

Example 2: Export Multiple Datasets to Multiple Excel Sheets

Suppose we have two datasets in SAS:

/*create first dataset*/
data my_data;
    input A B C;
    datalines;
1 4 76
2 3 49
2 3 85
4 5 88
2 2 90
4 6 78
5 9 80
;
run;

/*create second dataset*/
data my_data2;
    input D E F;
    datalines;
1 4 90
2 3 49
2 3 85
4 5 88
2 1 90
;
run;

We can use the following code to export both datasets to the same Excel file in different sheets:

/*export first dataset to first sheet in Excel*/
proc export data=my_data
    outfile="/home/u13181/my_data.xlsx"
    dbms=xlsx
    replace;
    sheet="First Data";
run;

/*export second dataset to second sheet in Excel*/
proc export data=my_data2
    outfile="/home/u13181/my_data.xlsx"
    dbms=xlsx
    replace;
    sheet="Second Data";
run;

I can then navigate to the location on my computer where I exported the file and view it in Excel.

The first sheet titled “First Data” contains the first dataset:

And the second sheet titled “Second Data” contains the second dataset:

Additional Resources

The following tutorials explain how to perform other common tasks in SAS:

How to Normalize Data in SAS
How to Rename Variables in SAS
How to Remove Duplicates in SAS
How to Replace Missing Values with Zero in SAS

Leave a Reply

Your email address will not be published.