SAS tips: converting the data between SAS data sets and Excel

Converting SAS data sets to Excel Book

 In clinical trials, the database may be stored in SAS data set format. Sometimes, there is a need to convert multiple SAS data sets into an excel book. The following program can be easily modified to serve this purpose. With the small macro using Proc Export, SAS data sets can be converted into Excel book with multiple tabs (each tab is corresponding to a SAS data set). 

libname aa "c:\Data\CRF Data\Final Data\";

%macro export(dst=);
PROC EXPORT DATA= aa.&dst

            OUTFILE= "c:\Data\CRF Data\Final Data\excel\ExcelData.xls"

            LABEL DBMS=xls REPLACE;  

   SHEET="&dst";
RUN;
%mend;

%export(dst=IE);
%export(dst=DM);
%export(dst=MH);
%export(dst=VS);
%export(dst=PE);
%export(dst=CLAB);
%export(dst=PREG);
%export(dst=DRUG);
%export(dst=AE);
%export(dst=CM);
%export(dst=COM);

In the macro above, the keyword 'LABEL' is important. With 'LABEL', the SAS variable label will be used as the column header. Without 'LABEL', the SAS variable name will be used as the column header. The keyword 'LABEL' must be placed before the keyword 'DBMS' in order to be effective.
DBMS=xls indicates that the output data file will be an excel book (no version number is needed). other options for DBMS are csv,  dlm, tab, jmp. Check SAS manual for detail. 

If you run into an error due to the Excel version issue, you may try to use xlsx engine.

%macro export(dst=);
PROC EXPORT DATA= aa.&dst

            OUTFILE= "c:\Data\CRF Data\Final Data\excel\ExcelData.xlsx"

            LABEL DBMS=xlsx REPLACE; 

   SHEET="&dst";
RUN;
%mend;

Converting Excel Book to SAS data sets

The opposite way is to convert the Excel book (with multiple table) into different SAS data sets. The program below can be modified to fulfill this task.

libname aa "c:\temp\";

%macro import(dst=);
PROC IMPORT DATAFILE= "C:\Dengc\ExcelData.xls"  OUT= aa.&dst
            DBMS=xls REPLACE;
     SHEET="&dst";
     GETNAMES=YES;
RUN;
%mend;

%import(dst=primary);
%import(dst=secondary);
%import(dst= tertiary);

In the macro above, GETNAMES=YES indicates that the first row from excel spreadsheet will be used as the SAS variable name.


Other References:

• How do I read/write Excel files in SAS?
• SAS Proc Import
• SAS Proc Export