How to Export SAS Dataset to Excel (2 Easy Ways)

How to Export SAS Dataset to Excel (2 easy ways)

Users need to know how to export SAS dataset to Excel because some data edit, and analyses are more convenient in Excel than in SAS. While analyzing data in Excel, users can use the user-friendly interface of Excel. SAS users can easily export SAS data set to Excel by using either PROC EXPORT or LIBNAME statement. Users can customize the labels and headers in EXPORT procedure whereas LIBNAME gives more options for sub-setting data and creating new columns. Users can also define the name of the sheet in Excel in which the data will be exported. In this section, we discuss, with example codes, how to create a new data, see the data in SAS and finally export the created new data from SAS to Excel.

Are you interested to know? How to import Text File into SAS?

Export SAS data to MS Excel files (in .xlsx format) with easy SAS code. No special setup is required.

1. The Generic PROC EXPORT Statement to Export SAS data in Excel

The Generic PROC EXPORT Statement to export 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 new data set in SAS using DATA STEP

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:

We are done with the creation of new data set in SAS. Now, we will see how to export this data set to Excel. We now know to create SAS dataset and will now explain how to export SAS dataset to Excel.

3. How 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.

We can find that the data has been properly exported to Excel. Look at the sheet “GRADE”. The sheet name has also been given by SAS.

4. Export Multiple SAS Datasets 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.

The Excel Grade_data

Grade1 in the first sheet:

Grade2 in the second sheet:

Grade3 in Sheet3:

5. How to Export SAS Dataset using LIBNAME

We can also export SAS dataset to Excel using LIBNAME reference:

/* TO Create a New 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;

/* To Print the dataset Grade3 */

proc print data=grade3;
run;

/* To assign LIBNAME */

libname mygrade 'C:\wikitekkee\Learning SAS\mygrade.xlsx';

/* To Export grade3 to mygrade Excel File */
data mygrade.sheet1;
    set work.grade3;
run;

The log file:

For library reference, you learn from expert. The link is here

In this article, we explained how to export SAS dataset to Excel. Happy analysis.

More related reading

How to import Excel File into SAS

How to import STATA Files into SAS

9 Examples to import Text Files into SAS