SAS put data to Excel via DDE

1
votes

I want to write some data to Excel via DDE and have the following code:

option noxwait noxsync;
x call "C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.EXE";

%let delay=5;
data _null_;
    rc=sleep(&delay); 
run;

filename random dde 'excel|Tabelle1!r1c1:r100c3';
data _null_;
    set sashelp.class;
    file random;
    put name sex age;
run;

Excel opens successfully however the sheet remains empty. The log tells me that 19 records were written to the file RANDOM.

Any suggestions why the data is not written to my Excel sheet? Could it be connected with my language settings (German) in Excel?

2
excelsasdde
Have you tried adding a 5-second delay between opening excel and running your data step? – user667489
Yes, please find my edit in my question. – zuluk
I would reconsider the use of DDE at all. Try using ods excel instead, especially if you're simply dumping a table to a new worksheet. – Robert Penridge
@RobertPenridge: This suits not with my requirements. I have an Excel template file and want to add some data. In other cases you are right. – zuluk

2 Answers

2
votes

NOXSYNC is a mistake here.

Basically, what happens when I run this at least is it won't work if NOXSYNC is set, because it tries to write to the excel sheet before Excel is ready for it. You need XSYNC to have SAS wait for the X command to complete.

If XSYNC is something you can't deal with, then you will need to add a manual delay.

I'd also verify that you don't have Excel open before running this, as if you do it may be writing to a different Excel workbook than the one it's opening.

2
votes

Very mad story from my end: When I changed my language settings from German to English it works fine with the shown code.

Update: It also works with German language settings, however I have to translate all statements, e.g. the range written to:

English Excel

filename random dde 'excel|Sheet1!r1c1:r100c3';

German Excel use z(eile) and s(palte) instead of r(ow) and c(olumn)

filename random dde 'excel|Tabelle1!z1s1:z100s3';