Hi All,
I am having a problem with exporting sas datasets to Excel. I am using SAS 9.2 on windows 7 64 bits and MS 2007. I understand that SAS does not export to MS 2007. So, I used the old excel format and the files generated were corrupted. Below is the code I am using to export multiple datasets into one excel file with each dataset in a different tab. Any help would be highly appreciated.
thanks
options mprint macrogen symbolgen mlogic;
%macro exportexcel9(mylibrary=, mylibref=, myexcel=);
libname &mylibref &mylibrary;
proc sql;
create table tablemems as
select * from dictionary.tables
where libname="&mylibref";
run; quit;
data _null_; set tablemems end=last;
call symput('sheetm'||left(_n_),trim(memname));
if last then call symput('counter',_n_);
run;
%put _all_;
run;
%do i= 1 %to &counter;
proc export data=&mylibref..&&sheetm&i outfile=&myexcel dbms=xls replace;
sheet="&&sheetm&i";
run; quit;
%end;
%mend exportexcel9;
run;
ods tagsets.excelxp options(default_column_width="7.5, 7.5, 5, 7.5, 7.5" width_fudge='0.75');
%exportexcel9(mylibrary="C:\Users\export", /* library where the sas datasets live */
mylibref=EXPORT, /* libref for the library use UPPERCASE */
myexcel="C:\Users\data.xls"); /* location of the excel file */
