BookmarkSubscribeRSS Feed
mcadena
Calcite | Level 5

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;

7 REPLIES 7
art297
Opal | Level 21

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;

mcadena
Calcite | Level 5

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.

art297
Opal | Level 21

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.

mcadena
Calcite | Level 5

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.

RamKumar
Fluorite | Level 6

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

mcadena
Calcite | Level 5

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

Kurt_Bremser
Super User

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 7 replies
  • 2361 views
  • 0 likes
  • 4 in conversation