topic writing on excel in Microsoft Integration with SAS

writing on excel

mcadena — Wed, 15 Oct 2014 15:23:14 GMT

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;

Re: writing on excel

art297 — Wed, 15 Oct 2014 15:46:30 GMT

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;

Re: writing on excel

mcadena — Wed, 15 Oct 2014 16:13:07 GMT

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.

Re: writing on excel

art297 — Wed, 15 Oct 2014 17:23:01 GMT

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.

Re: writing on excel

RamKumar — Thu, 16 Oct 2014 07:16:51 GMT

what does this line x = "x"; does in your code?

Re: writing on excel

mcadena — Thu, 16 Oct 2014 07:24:54 GMT

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.

Re: writing on excel

mcadena — Thu, 16 Oct 2014 07:25:50 GMT

x = "x"; defines variable x to be written in excel.

Re: writing on excel

Kurt_Bremser — Thu, 16 Oct 2014 08:05:35 GMT

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!