Export Data from SAS to Excel: A Step-by-Step Guide

APPLIED REGRESSION ANALYSIS 3ED (PB 2019)

(39)

6% Off ₹1,295.00 ₹1,211.00

Exporting data from SAS to Excel has never been easier, thanks to the straightforward PROC EXPORT procedure.

This powerful tool allows you to export your datasets into an Excel file and organize them neatly across multiple sheets.

Export Data from SAS to Excel

In this guide, we will break down how to export data effectively, provide practical examples, and ensure your datasets are visually represented in Excel just as they appeared in SAS.

Basic Syntax of PROC EXPORT

To get started, here’s a simple syntax for exporting data to an Excel file:

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

Explanation of Each Parameter:

• data: The name of the dataset you want to export.
• outfile: The path where your new Excel file will be saved.
• dbms: Specifies the file format for export. In this case, we are using Excel’s xlsx format.
• replace: This option will overwrite any existing file with the same name.
• sheet: The name of the worksheet in the Excel file.

Example 1: Exporting One Dataset to One Excel Sheet

Let’s say you have a dataset named my_data. Here’s how you would create the dataset and subsequently export it to an Excel file:

/* 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;

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

After running the export code, navigate to the specified location on your computer. You will find the Excel file my_data.xlsx, and the first sheet titled “First Data” will display the dataset just as it appeared in SAS.

Example 2: Exporting Multiple Datasets to Multiple Excel Sheets

Now, let’s explore how to export multiple datasets into different sheets within the same Excel file. Imagine you have another dataset, my_data2, in addition to my_data.

/* 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;

/* Export first dataset to the 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 the second sheet in Excel */
proc export data=my_data2
    outfile="/home/u13181/my_data.xlsx"
    dbms=xlsx
    replace;
    sheet="Second Data";
run;

Once you have executed the code, you can open my_data.xlsx in Excel. The first sheet, labeled “First Data,” contains the first dataset, while the second sheet, titled “Second Data,” displays the second dataset.

Conclusion

Using SAS’s PROC EXPORT procedure is an efficient way to move your data into Excel for further analysis or reporting.

With just a few lines of code, you can effortlessly export single or multiple datasets and organize them in a structured manner in an Excel workbook.

This functionality not only streamlines your data handling processes but also enhances your overall productivity when working with datasets in SAS.

Now you’re equipped with the knowledge to export your SAS datasets to Excel with ease! Whether you’re dealing with a single dataset or multiple datasets, the process is straightforward and efficient.

Try it out, and enjoy seamless data management between SAS and Excel.

SAS Archives » FINNSTATS

(1342)

28% Off ₹799.00 (₹2,159.46 / Kilograms) ₹571.79 (₹1,545.38 / Kilograms)