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:
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
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.
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/.
Thanks for sharing the valuable information,This is useful information for online learners
SAS Online Training
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.
it is very good article and very useful for SAS learners we also provide SAS Online Training for more info Click here
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!
Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site.
sas certification courses
It is a very useful blog and very important information about SAS.
Online SAS Training
Post a Comment