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;
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.
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.
why there are two && used in the macro?
ReplyDeleteIn this case it was not needed. I corrected it. Thanks!
Delete