SAS DDE Error

[stc200896]

Hi All,

I'm trying desperately to determine what is invalid in this code.  I have an excel template .xlsb already created and I'm trying to export my output into it.  I keep getting "invalid device type, error in the FILENAME statement" and it appears to be the only thing that's keeping the rest of the code from running.  I'm using SAS9.3, 64bit version.  Excel is the 32bit version.  Could someone help point me in the right direction?

options mprint;
x '"C:\Program Files (x86)\Microsoft Office\Office14\excel.exe"';

data _null_;
x=sleep(30);
run;

data _null_;
file sas2x1;
put '[Open("C:\My Documents\TEMPLATE.xlsb")]';
run;

filename sas2x1 dde 'excel|WHSE_10!r7c1:r150c57' lrecl=1024;

data _null_;
set Staci.whse_10;
file sas2x1;
put var1 '09'x var2 '09'x var3 '09'x var4 '09'x var5;
run;

data _null_;
file sas2x1;
put '[error(false)]';
cmnd = '[save.as("C:\My Documents\Testing_DDE.xlsb"".xlsb")]';
put cmnd;
put '[file.close(true)]';
put '[quit()]';
run;

On another note, when I try running the x command below, I get errors saying the first two are unrecognized SAS option names:

options noxwait noxsync mprint;
x '"C:\Program Files (x86)\Microsoft Office\Office14\excel.exe"';

Thanks, Staci B

[Reeza]

Post your exact log please. 

Does Excel open when you run that code?

---

@stc200896 wrote:

Hi All,

I'm trying desperately to determine what is invalid in this code.  I have an excel template .xlsb already created and I'm trying to export my output into it.  I keep getting "invalid device type, error in the FILENAME statement" and it appears to be the only thing that's keeping the rest of the code from running.  I'm using SAS9.3, 64bit version.  Excel is the 32bit version.  Could someone help point me in the right direction?

options mprint;
x '"C:\Program Files (x86)\Microsoft Office\Office14\excel.exe"';

data _null_;
x=sleep(30);
run;

data _null_;
file sas2x1;
put '[Open("C:\My Documents\TEMPLATE.xlsb")]';
run;

filename sas2x1 dde 'excel|WHSE_10!r7c1:r150c57' lrecl=1024;

data _null_;
set Staci.whse_10;
file sas2x1;
put var1 '09'x var2 '09'x var3 '09'x var4 '09'x var5;
run;

data _null_;
file sas2x1;
put '[error(false)]';
cmnd = '[save.as("C:\My Documents\Testing_DDE.xlsb"".xlsb")]';
put cmnd;
put '[file.close(true)]';
put '[quit()]';
run;

On another note, when I try running the x command below, I get errors saying the first two are unrecognized SAS option names:

options noxwait noxsync mprint;
x '"C:\Program Files (x86)\Microsoft Office\Office14\excel.exe"';

Thanks, Staci B

---

[stc200896]

No, I didn't realize until after I posted that excel wasn't opening.  I get an error saying excel.exe not found.  I've reached out to my IT team but haven't heard back yet.  I've verified it's in that location.

NOTE: Remote submit to MIRAGE commencing.

REMOTE(MIRAGE): /bin/ksh: C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE: not found
2412 x '"C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE"'; ;
2413
2414 data _null_;
2415 x=sleep(30);
2416 run;

NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.00 seconds

2417
2418 data _null_;
2419 file sas2x1;
2420 put '[Open("C:\My Documents\TEMPLATE.xlsb")]';
2421 run;

NOTE: 1 record was written to the file SAS2X1.
The minimum record length was 68.
The maximum record length was 68.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

2422
2423 filename sas2x1 dde 'excel|WHSE_10!r7c1:r150c57' lrecl=1024;
ERROR: Invalid device type.
ERROR: Error in the FILENAME statement.
2424
2425 data _null_;
2426 set Staci.whse_10;
2427 file sas2x1;
2428 put var1 '09'x var2 '09'x var3 '09'x var4 '09'x var5
2429 run;

NOTE: 15042 records were written to the file SAS2X1.
The minimum record length was 1.
The maximum record length was 256.
NOTE: There were 8420 observations read from the data set STACI.WHSE_10.
NOTE: DATA statement used (Total process time):
real time 0.13 seconds
cpu time 0.13 seconds

2430
2431
2432
2433 data _null_;
2434 file sas2x1;
2435 put '[error(false)]';
2436 cmnd = '[save.as("C:\My Documents\Testing_DDE.xlsb")]';
2437 put cmnd;
2438 put '[file.close(true)]';
2439 put '[quit()]';
2440 run;

NOTE: 4 records were written to the file SAS2X1.
The minimum record length was 8.
The maximum record length was 54.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

NOTE: Remote submit to MIRAGE complete.

[Reeza]

I noticed you're using remote submit. That means the code goes to a server and does't have access to your C drive or Excel and you very likely cannot do remote submission to use DDE. That would require having Excel and your file on the server.

---

@stc200896 wrote:

No, I didn't realize until after I posted that excel wasn't opening.  I get an error saying excel.exe not found.  I've reached out to my IT team but haven't heard back yet.  I've verified it's in that location.

NOTE: Remote submit to MIRAGE commencing.

REMOTE(MIRAGE): /bin/ksh: C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE: not found
2412 x '"C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE"'; ;
2413
2414 data _null_;
2415 x=sleep(30);
2416 run;

NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.00 seconds

2417
2418 data _null_;
2419 file sas2x1;
2420 put '[Open("C:\My Documents\TEMPLATE.xlsb")]';
2421 run;

NOTE: 1 record was written to the file SAS2X1.
The minimum record length was 68.
The maximum record length was 68.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

2422
2423 filename sas2x1 dde 'excel|WHSE_10!r7c1:r150c57' lrecl=1024;
ERROR: Invalid device type.
ERROR: Error in the FILENAME statement.
2424
2425 data _null_;
2426 set Staci.whse_10;
2427 file sas2x1;
2428 put var1 '09'x var2 '09'x var3 '09'x var4 '09'x var5
2429 run;

NOTE: 15042 records were written to the file SAS2X1.
The minimum record length was 1.
The maximum record length was 256.
NOTE: There were 8420 observations read from the data set STACI.WHSE_10.
NOTE: DATA statement used (Total process time):
real time 0.13 seconds
cpu time 0.13 seconds

2430
2431
2432
2433 data _null_;
2434 file sas2x1;
2435 put '[error(false)]';
2436 cmnd = '[save.as("C:\My Documents\Testing_DDE.xlsb")]';
2437 put cmnd;
2438 put '[file.close(true)]';
2439 put '[quit()]';
2440 run;

NOTE: 4 records were written to the file SAS2X1.
The minimum record length was 8.
The maximum record length was 54.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

NOTE: Remote submit to MIRAGE complete.

---

[ballardw]

When getting errors or warnings copy from the log containing the CODE and the Error message and paste into a code box opened with the forum's {I} icon to preserve the formatting of the error messages.

There are additional issues arising just attempting to use DDE arising from age. There are some programs that have "hijacked" part of the system area that DDE uses rendering DDE useless. I found that out when Cisco Jabber was installed on my computer. And it is not sufficient to turn off the program, the actual service process has to be KILLED using task manager. Other programs may have the same impact but which ones may be hard to tell.

You may want to double check the path for My Documents. It likely actually reside in something like c:\users\yourid\documents

Also, IIRC, the DDE "save as" command requires a file type parameter which is an integer after the filename.