I am using code provided by SAS to write from SAS to Excel, but finally SAS does not write on Excel. Does someone know why ? Thanks in advance.
Here an example of code:
options noxwait noxsync;
x '"c:program files\microsoft office\office14\excel.exe"';
filename random dde
'excel|sheet1!r1c1:r1c1';
data random;
file random;
x = "x";
put x;
run;
I can think of two problems. First, are you running 64 or 32 bit Excel. I have 32 bit, thus had to change Excel's location to program files (x86). Second, you probably have to add a data _null_ step with a sleep function to give Excel a chance to open before attempting to write to the file. e.g.:
options noxwait noxsync;
x '"c:\program files (x86)\microsoft office\office14\excel.exe"';
filename random dde 'excel|sheet1!r1c1:r1c1';
data _null_;
slept= sleep(1);
run;
data random;
file random;
slept= sleep(5);
x = "x";
put x;
run;
Thank you for the answer, but I do not have any of those problems.
About the sentence
x '"c:\program files (x86)\microsoft office\office14\excel.exe"';
that is only to open excel, but even that can de done manually. Executing only
filename avg1 dde 'excel|US!r235c15:r235c15';
data _null_;
file avg1;
x = "x";
put x;
run;
SAS log shows:
NOTE: The file AVG1 is:
DDE Session,
SESSION=excel|US!r235c15:r235c15,RECFM=V,
LRECL=256
NOTE: 1 record was written to the file AVG1.
The minimum record length was 1.
The maximum record length was 1.
NOTE: DATA statement used (Total process time):
real time 0.32 seconds
cpu time 0.03 seconds
apparently record was written, but cell (235;15) is still empty.
Further, I do not use a sleep function.
Try it by adding the data step with the sleep function like I did in my example. That STILL could easily be a problem that you do have, namely that Excel isn't ready to accept what you are trying to write to it.
I tried it but it does not work. However I succed to write in excel using range names !
The problem is that I need to define a number of range names in my excel template.
what does this line x = "x"; does in your code?
x = "x"; defines variable x to be written in excel.
After having tried it, I would strongly advise to use ods tagsets.excelxp in the future. Works on all platforms (does not need any crappy piece of MS SW), therefore WORKS!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!