Hi,
I found a piece of code on the net that exports data from SAS to
Excel:
%macro ods_sas2xl(
code =,
master_excel_path =,
master_excel_name =,
sheetname =
);
options noxwait noxsync mprint;
* -- ODS DEFINED HERE;
ods listing close;
ods html body="&master_excel_path.temp.xls";
* -- CODE EMBEDED HERE;
&code.
ods html close;
ods listing;
* -- START EXCEL PROCESS;
x '"c:\Program Files\Microsoft Office\office\EXCEL.EXE"';
* -- TILL THE EXCEL PROCESS STARTS KEEP THE SAS SESSION WAITING;
data _null_;x = sleep(8);run;
* -- HANDLE : TO EXCEL SESSION;
filename handle dde 'excel|system';
filename xl_file "&master_excel_path.&master_excel_name.";
data _null_;
file handle;
* -- CHECK IF THE EXCEL FILE TO BE CREATED IS EXISTING OR NOT;
%if %sysfunc(fexist(xl_file)) %then %do;
put "[open(""&master_excel_path.&master_excel_name."")]";
%end;
%else %do;
* -- CREATE THE EXCEL FILE IF IT DOESNT EXIST;
put "[new(1)]";
put "[save.as(""&master_excel_path.&master_excel_name."")]";
%end;
* -- USE A TEMPORARY EXCEL FILE "temp.xls" TO WRITE THE PROCEDURE
OUTPUT TO temp.xls;
put "[open(""&master_excel_path.temp.xls"")]";
/* put "[workbook.next()]";*/
/* put "[workbook.insert(1)]";*/
* -- inserting a macro sheet;
put "[workbook.insert(3)]";
* -- reposition the macro sheet to the first postion in temp.xls;
put "[workbook.move(""macro1"",""temp.xls"",1)]";
run;
* --- Macro sheet is created above Now the next step is feed commands
in that macro sheet;
filename xlmacro dde 'excel|macro1!r1c1:r100c1' notab lrecl=300;
data _null_;
file xlmacro;
* -- ods html creates the output sheet with the same name as the
excel file so in the
present case the name is "temp" hence we rename it before copying to
the master
excel file we want the output to come in ;
put "=workbook.name(""temp"",""&sheetname."")";
put "=halt(true)";
* -- copying the sheet (coming from ODS) to the master excel file;
put "=workbook.copy(""&sheetname."",""&master_excel_name."",1)";
put "=halt(true)";
put "!dde_flush";
x=sleep(10);
run;
* -- executing the macro;
data _null_;
file handle;
* -- executing the first command in the macro sheet;
put "[run(""macro1!r1c1"")]";
put "[run(""macro1!r3c1"")]";
put "[save()]";
put "[close()]";
put "[error(false)]";
put "[workbook.delete(""macro1"")]";
put "[workbook.activate(""temp.xls"")]";
put "[save()]";
put "[quit()]";
put "[error(false)]";
run;
%mend;
%ods_sas2xl(
code = %str(proc freq data=sashelp.class; table name;run; title
'test';),
master_excel_path = %str(E:\Thomas\),
master_excel_name = multiple_sheet.xls,
sheetname = procFreq
);
The code works fine but it does not seem to execute the macros in the
macro1 sheet (the formulas are there). The following is the message
from the log:
NOTE: The file HANDLE is:
DDE Session,
SESSION=excel|system,RECFM=V,LRECL=256
ERROR: DDE session not ready.
FATAL: Unrecoverable I/O error detected in the execution of the DATA
step program.
Aborted during the EXECUTION phase.
NOTE: 5 records were written to the file HANDLE.
The minimum record length was 8.
The maximum record length was 20.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.14 seconds
cpu time 0.00 seconds
I have set the security to low on the macro options and Excel is open
when i run the code. I am using SAS 9.2 and Excel 2003.
Any suggestions on what to do? I've tried the ODS way - but would like
to use DDE.
/Mats
