SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

writing on excel

Reply
Occasional Contributor
Posts: 8

writing on excel

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 '"cSmiley Tonguerogram files\microsoft office\office14\excel.exe"';

filename random dde

  'excel|sheet1!r1c1:r1c1';

data random;

  file random;

x = "x";

put x;

run;

Esteemed Advisor
Posts: 7,299

Re: writing on excel

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;

Occasional Contributor
Posts: 8

Re: writing on excel

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.

Esteemed Advisor
Posts: 7,299

Re: writing on excel

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.

Occasional Contributor
Posts: 8

Re: writing on excel

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.

Regular Contributor
Posts: 165

Re: writing on excel

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

Occasional Contributor
Posts: 8

Re: writing on excel

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

Esteemed Advisor
Posts: 6,698

Re: writing on 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!

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 7 replies
  • 1580 views
  • 0 likes
  • 4 in conversation