SAS to Excel: Save SAS data to Excel (7 Easy Example Codes!)

SAS to Excel: Save SAS data to Excel (Easy Example Codes!)

SAS users sometimes need to know “SAS to Excel: Save SAS data to Excel” because some analyses are more convenient in Excel than in SAS. If you want to filter data, for example, it is easy and handy in Excel than in SAS. SAS dataset can be saved or exported to Excel in two ways: (1) using PROC EXPORT and (2) using Libname. While the EXPORT procedure allows to include column labels and header names, the LIBNAME statement allows sub-setting the dataset. In addition to the SAS Communities post on how to export SAS dataset to Excel, this article provides easier examples.

1. PROC EXPORT to Export SAS dataset to Excel

The GENERIC code for exporting SAS dataset to Excel is as follows:

/* Generic PROC EXPORT Statement to export SAS data to Excel */

proc export 
data=student_grade
outfile="Location where to export\FileName.xlsx"
DBMS=xlsx 
REPLACE;
sheet=grade; /*Give the name of the sheet in which the data will be exporeted */
run;

Explanation of the Statement:

• PROC EXPORT: Start the Statement with the proc export.
• data: The name of the data set to be exported.
• outfile: This is the location of the folder where the Excel File will be exported.
• DBMS: This is the format of the file. Here we use the Excel file extension XLSX
• REPLACE: If users already have a file with the same name, use REPLACE to replace the existing file.
• sheet: Give a name of the sheet in Excel where the SAS data will be exported.

Now let’s start with creation of a new data set:

2. Create a dataset to be Exported to Excel

We can directly apply the PROC EXPORT statement to export SAS data in Excel; however, it is better to know how to create a new data set by using DATA STEP and then know how to export the data set in Excel.

Example: Create a new data set in SAS:

To create a new data set, use the following code:

/*To create data set using DATA STEP*/

data student_grade;
input Name$ Math Stat Science;
datalines;
Sanam 90 95 94
Alex 84 85 88
Rich 92 93 94
Craig 88 78 72
Robert 72 74 68
run;

/* To print the data set in SAS*/

proc print data=student_grade;
run; 

The SAS data is as follows:

3. Example PROC EXPORT code to Export SAS dataset to Excel

To export SAS data set to Excel File, the code is as follows:

/* To export SAS Data set to Excel File */

proc export 
data=student_grade
outfile="C:\wikitekkee\Learning SAS\student_grade.xlsx"
DBMS=xlsx REPLACE;
sheet=grade;
run;

Check the Excel File:

After running the above code, we want to check whether the data set has been properly exported to Excel. To do so, we go to the file location and find the following Excel file.

4. PROC EXPORT to Export Multiple SAS dataset to Multiple Excel Sheets

To export multiple SAS datasets to multiple Excel Sheets, users need to write the same PROC EXPORT statements. Users can save time by using multiple sheets.

Examples:

Let’s create three data sets and then we EXPORT them to Excel Sheets in one Excel Workbook.

/* To Create dataset Grade1*/

data grade1;
    input Name$ accounting math stat;
    datalines;
Alex 80 71 94
Bob 74 75 78
Rich 86 88 98
Craig 75 85 88
Ling 82 84 84
;
run;


/*To Create dataset Grade2*/

data grade2;
    input Name$ finance calculus mgt;
    datalines;
Sanam 80 71 94
Ruby 74 75 78
John 86 88 98
Pavel 75 85 88
Safa 82 84 84
;
run;

/*To Create dataset Grade3*/

data grade3;
    input Name$ biology math computer;
    datalines;
Alxe 80 71 94
Akash 74 75 78
Boby 86 88 98
Sofia 75 85 88
Yafi 82 84 84
;
run;

Export the New datasets to a single Excel Workbook but in different sheets:

/*To Export "grade1" dataset to first sheet in Excel*/

proc export data=grade1
    outfile="C:\wikitekkee\Learning SAS\grade_data.xlsx"
    dbms=xlsx
    replace;
    sheet="grade1";
run;

/*To Export "grade2" dataset to second sheet in Excel*/

proc export data=grade2
    outfile="C:\wikitekkee\Learning SAS\grade_data.xlsx"
    dbms=xlsx
    replace;
    sheet="grade2";
run;

/*To Export "grade3" dataset to third sheet in Excel*/

proc export data=grade3
    outfile="C:\wikitekkee\Learning SAS\grade_data.xlsx"
    dbms=xlsx
    replace;
    sheet="grade3";
run;

After executing the above codes, we can now check the specified folder whether the datasets have been properly exported.

Grade 1 in the first Excel sheet:

Grade2 in the second Excel Sheet:

Grade3 in the third Excel Sheet:

In this article, we have discussed SAS to Excel: Save SAS data to Excel. Users can also use LIBNAME statement. You can also watch a video on SAS export to Excel.

5. More related readings:

Getting Started with data in SAS

To start with SAS data analysis, you first have to have a dataset. This article explains how to create and import data in SAS.

Infile Statement to Import Data into SAS

Advertisements

How to import Excel, Text, and STATA files into SAS

How to Import STATA Files into SAS

9 Examples to Import Text Files into SAS

Export SAS data as CSV Files