Tuesday, June 12, 2012

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:

9 comments:

Anonymous said...

I like the valuable info you provide in your articles. The written skill is so good . I am very impress to this post.Thanks to share this blog with us . I will keep share in future.
website design

21cssIndia said...

sas clinical online training| sas clinical training| call us+ ...
www.21cssindia.com/courses/sas-clinical-online-training-153.html
SAS clinical Online Training, SAS clinical training, SAS clinical course contents, SAS clinical , call us: +919000444287,dharani@21cssindia.com.

Unknown said...

Well, Thanks for your article.
Its impressive to know something about your note on SAS CLINICAL Course. Please do share your articles like this your articles for our awareness. Mostly we do also provide Online Training on SAS CLINICAL Course. for more info Contact-Us at@ online-training-sas.com/.

Unknown said...


Thanks for sharing the valuable information,This is useful information for online learners

SAS Online Training

Unknown said...

This information which you provided is very much useful for us.It was very interesting and useful for SAS online training.We also providing sas online training institute in USA.

Unknown said...

it is very good article and very useful for SAS learners we also provide SAS Online Training for more info Click here

Susique said...

This is awesome! Why can't I get this to work? Is there a particular version of EXCEL that this works on? with SAS 9.2?

ERROR: DBMS type XLS not valid for export. I tried xlsx too!

Techtoolsinnovation said...

Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site.
sas certification courses

Clinnovo said...

It is a very useful blog and very important information about SAS.
Online SAS Training