BookmarkSubscribeRSS Feed
FD
Calcite | Level 5 FD
Calcite | Level 5

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

2 REPLIES 2
art297
Opal | Level 21

Have you tried it with the excelcs engine?

FD
Calcite | Level 5 FD
Calcite | Level 5

Thanks Excelcs engine worked.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 2 replies
  • 860 views
  • 0 likes
  • 2 in conversation