To use DDE, the application to which DDE is communicating must be open and the file to which DDE is communicating must be open within the application. To accomplish this, you can open the application and the file manually. To dynamically accomplish this, use an X statement to issue the appropriate operating system command to open the application and a subsequent DATA step to open the file.

For example, to open Excel and the file C:\test.xls, issue the following statements from SAS:

options noxwait noxsync;
/* Open Excel */
x "'C:\Program Files\Microsoft Office\OFFICE11\excel.exe'"; 
filename cmds dde 'excel|system';
/* Open C:\test.xls in Excel */
data _null_; 
  file cmds; 
  put '[open("C:\test.xls")]'; 
run;

 

Documentation on the specific functions available when using Excel with DDE can be downloaded at the following URL:

http://support.microsoft.com/kb/128185/en-us

Then double click on C:\Program Files\Microsoft Office\macrofun.hlp

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Excel must be open when the code below is run. See the Details tab for details.

---

/* Write to the workbook that is opened by default with Excel */
filename foo dde 'excel|[book1]sheet1!r1c1:r30c2' notab; 
data _null_;
  set sashelp.class;
  file foo;
  put name '09'x age;
run;

/* Save the workbook to a specific file and close the workbook */
filename cmds dde 'excel|system';
data _null_;
  file cmds;
  put '[SAVE.AS("c:\test.xls")]';
  put '[close]';
run;

/* Open a specific file in excel */
data _null_; 
  file cmds; 
  put '[open("C:\test.xls")]'; 
run;
/* Read the file that was opened above into a SAS data set */ 
filename foo dde 'excel|[test.xls]sheet1!r1c1:r30c2' notab; 
data new;
  infile foo;
  input name $ age;
run;

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

This example illustrates the functions used in DDE to write, save, close, open, and read Excel files from SAS.

Type:	Sample
Topic:	Third Party ==> Information Exchange ==> DDE (Dynamic Data Exchange)

Date Modified:	2007-03-29 03:02:51
Date Created:	2007-03-29 03:02:51

Operating System and Release Information

Product Family	Product	Host	SAS Release
			Starting	Ending
SAS System	Base SAS	Microsoft® Windows® for 64-Bit Itanium-based Systems	6.12	n/a
		Windows	6.12	n/a