SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Export to Excel 07

Reply
New Contributor FD
New Contributor
Posts: 2

Export to Excel 07

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  */

Esteemed Advisor
Posts: 7,296

Export to Excel 07

Have you tried it with the excelcs engine?

New Contributor FD
New Contributor
Posts: 2

Export to Excel 07

Thanks Excelcs engine worked.

Ask a Question
Discussion stats
  • 2 replies
  • 303 views
  • 0 likes
  • 2 in conversation