DDE Examples

This section provides several examples of using DDE with SAS under Windows. These examples use Microsoft Excel and Microsoft Word as DDE servers, but any application that supports DDE as a server can communicate with SAS.

Before you run these examples, you must first invoke Microsoft Excel and Microsoft Word, and open the spreadsheet or document used in the example.

A DDE server application can be opened using the X command within SAS code. The XWAIT and XSYNC options must be turned off.

options noxwait noxsync;
x '"c:program files\microsoft office\office14\excel.exe"'; 

Double quotation marks are required around the path if the path contains a space. The single quotation marks are for the X command.

The first example sends data from a SAS session to an Excel spreadsheet. The target cells are rows 1 through 100 and columns 1 through 3. To send the data, submit the following program:

/* The DDE link is established using   */
/* Microsoft Excel SHEET1, rows 1      */
/* through 100 and columns 1 through 3 */
filename random dde
   'excel|sheet1!r1c1:r100c3';
data random;
   file random;
   do i=1 to 100;
      x=ranuni(i);
      y=10+x;
      z=x-10;
      put x y z;
   end;
run;

This example sends a text string to a Microsoft Word document at a given bookmark. Note the difference between using DDE with Microsoft Word and Microsoft Excel.

filename testit dde 'winword|"c:\temp\testing.doc"
                     !MARK' notab;

data _null_;
   file testit;
   put 'This is a test.';
run;

You can also use DDE to read data from an Excel application into SAS, as in the following example:

/* The DDE link is established using   */
/* Microsoft Excel SHEET1, rows 1      */
/* through 10 and columns 1 through 3  */
filename monthly
  dde 'excel|sheet1!r1c1:r10c3';
data monthly;
   infile monthly;
   input var1 var2 var3;
run;
proc print;
run;

This example reads data from a Microsoft Word document at a given bookmark.

filename testit dde 'winword|"c:\temp\testing.doc"
                     !MARK' notab;

libname workdir 'c:\temp';

/* Get ready to read the first bookmark.  */

data workdir.worddata;
   length wordnum $5;
   infile testit;
   input wordnum $;
run;

proc print;
run;

You can issue commands to Excel or other DDE-compatible programs directly from SAS using DDE. In the following example, the Excel application is invoked using the X command; a spreadsheet called SHEET1 is loaded; data are sent from SAS to Excel for row 1, column 1 to row 20, column 3; and the commands required to select a data range and sort the data are issued. The spreadsheet is then saved and the Excel application is terminated.

/* This code assumes that Excel       */
/* is installed on the current        */
/* drive in a directory called EXCEL. */

options noxwait noxsync;
x '”c:program files\microsoft office\office14\excel.exe”';

/* Sleep for 60 seconds to give */
/* Excel time to come up.       */

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

/* The DDE link is established using   */
/* Microsoft Excel SHEET1, rows 1      */
/* through 20 and columns 1 through 3  */

filename data
   dde 'excel|sheet1!r1c1:r20c3';
data one;
   file data;
   do i=1 to 20;
      x=ranuni(i);
      y=x+10;
      z=x/2;
      put x y z;
   end;
run;

/* Microsoft defines the DDE topic */
/* SYSTEM to enable commands to be */
/* invoked within Excel.           */

filename cmds dde 'excel|system';

/* These PUT statements are       */
/* executing Excel macro commands */

data _null_;
   file cmds;
   put '[SELECT("R1C1:R20C3")]';
   put '[SORT(1,"R1C1",1)]';
   put '[SAVE()]';
   put '[QUIT()]';
run;

SAS expects to see a TAB character placed between each variable that is communicated across the DDE link. Similarly, SAS places a TAB character between variables when data are transmitted across the link. When the NOTAB option is placed in a FILENAME statement that uses the DDE device-type keyword, SAS accepts character delimiters other than tabs between variables.

The NOTAB option can also be used to store full character strings, including embedded blanks, in a single spreadsheet cell. For example, if a link is established between SAS and the Excel application, and a SAS variable contains a character string with embedded blanks, each word of the character string is normally stored in a single cell. To store the entire string, including embedded blanks in a single cell, use the NOTAB option as in the following example:

/* Without the NOTAB option, column1  */
/* contains 'test' and column2        */
/* contains 'one'.                    */

filename test
  dde 'excel|sheet1!r1c1:r1c2';
data string;
   file test;
   a='test one';
   b='test two';
   put a $15. b $15.;
run;

/* You can use the NOTAB option to store   */
/* each variable in a separate cell. To    */
/* do this, you must force a tab           */
/* ('09'x) between each variable, as in    */
/* the PUT statement.                      */
/* After performing this DATA step, column1*/
/* contains 'test one' and column2         */
/* contains 'test two'.                    */

filename test
  dde 'excel|sheet1!r2c1:r2c2' notab;
data string;
   file test;
   a='test one';
   b='test two';
   put a $15. '09'x b $15.;
run;

If the HOTLINK option is specified, the DDE link is activated every time the data in the specified spreadsheet range are updated. In addition, DDE enables you to poll the data when the HOTLINK option is specified to determine whether data within the range specified have been changed. If no data have changed, the HOTLINK option returns a record of 0 bytes. In the following example, row 1, column 1 of the spreadsheet SHEET1 contains the daily production total. Every time the value in this cell changes, SAS reads in the new value and outputs the observation to a data set. In this example, a second cell in row 5, column 1 is defined as a status field. Once the user completes data entry, typing any character in this field terminates the DDE link:

/* Enter data into Excel SHEET1 in   */
/* row 1 column 1. When you          */
/* are through entering data, place  */
/* any character in row 5            */
/* column 1, and the DDE link is     */
/* terminated.                       */

filename daily
  dde 'excel|sheet1!r1c1' hotlink;
filename status
  dde 'excel|sheet1!r5c1' hotlink;
data daily;
   infile status length=flag;
   input @;
   if flag ne 0 then stop;
   infile daily length=b;
   input @;

   /* If data have changed, then the */
   /* incoming record length         */
   /* is not equal to 0.             */

   if b ne 0 then
      do;
         input total $;
         put total=;
         output;
      end;
run;

It is possible to establish multiple DDE sessions. The previous example uses two separate DDE links. When the HOTLINK option is used and there are multiple cells referenced in the item specification, if any one of the cells changes, then all cells are transmitted.

Unless the HOTLINK option is specified, DDE is performed as a single one–time data transfer. The values currently stored in the spreadsheet cells at the time that the DDE is processed are values that are transferred.

DDE also enables you to program when the DDE buffer is dumped during a DDE link. Normally, the data in the DDE buffer are transmitted when the DDE link is closed at the end of the DATA step. However, the special string '!DDE_FLUSH' issued in a PUT statement instructs SAS to dump the contents of the DDE buffer. This function allows you considerable flexibility in the way DDE is used, including the capacity to transfer data dynamically through the DATA step. The following example creates a Macro Sheet in Microsoft Excel. Commands are then written to the Macro Sheet, which will rename Sheet1 to NewSheet. After writing these commands, through the use of !DDE_FLUSH , the Excel Macro can be executed in the same DATA Step as it is written.

filename cmds dde 'excel|system';
data _null_;
file cmds;
/* Insert an Excel Macro Sheet */
put '[workbook.insert(3)]';
run;

/* Direct the Output to the Newly created Macro Sheet */
filename xlmacro dde 'excel|macro1!r1c1:r5c1' notab;

data _null_;
file xlmacro;
put '=workbook.name("sheet1","NewSheet")';
put '=halt(true)';
/* Dump the contents of the buffer, allowing us to both write and */
/* execute the macro in the same DATA Step                        */
put '!dde_flush';
file cmds;
/* Run Macro1 */
put '[run("macro1!r1c1")]';
put '[error(false)]';
/* delete the Macro Sheet */
put '[workbook.delete("macro1")]';
run;

This example illustrates the use of a Macro Variable to issue a command to Microsoft Excel. In the example, the Macro Variable, excelOne, is being used in place of the Excel Workbook location C:\test.xls. Since macro triggers such as ampersands and percents are treated as text within single quotes, a Macro quoting function must be used. %STR is used to mask each individual apostrophe separately. Anytime you have an unmatched apostrophe or parenthesis then it must be preceded by a percent sign and since each apostrophe needs to be treated independently of each other the percents are needed. Once %STR has hidden the apostrophe the macro variable &excelOne resolves. %UNQUOTE is then used to remove what %STR has done and restores each apostrophe around the resolved value leaving the result as:

'[open("C:\test.xls")]'

options mprint symbolgen;
filename cmds dde 'excel|system';

%let excelOne=C:\test.xls;

data _null_;
file cmds;
put %unquote(%str(%'[open("&excelOne")]%'));
run;

This example illustrates reading missing data from an Excel spreadsheet called SHEET1. This example reads the data in columns 1 through 3 and rows 10 through 20. Some of the data cells can be blank. Here is an example of what some of the data look like:

...
10   John    Raleigh      Cardinals
11   Jose    North Bend   Orioles
12   Kurt    Yelm         Red Sox
13   Brent                Dodgers
...

Here's the code that can read these data correctly into a SAS data set:

filename mydata
  dde 'excel|sheet1!r10c1:r20c3';
data in;
   infile mydata dlm='09'x notab
          dsd missover;
   informat name $10. town $char20.
            team $char20.;
   input name town team;
run;
proc print data=in;
run;

In this example, the NOTAB option tells SAS not to convert tabs that are sent from the Excel application into blanks. Therefore, the tab character can be used as the delimiter between data values. The DLM= option specifies the delimiter character, and '09'x is the hexadecimal representation of the tab character. The DSD option specifies that two consecutive delimiters represent a missing value. The default delimiter is a comma. For more information about the DSD option, see SAS System Options: Reference. The MISSOVER option prevents a SAS program from going to a new input line if it does not find values in the current line for all the INPUT statement variables. With the MISSOVER option, when an INPUT statement reaches the end of the current record, values that are expected but not found are set to missing.

The INFORMAT statement forces the DATA step to use modified list input, which is crucial to this example. If you do not use modified list input, you receive incorrect results. The necessity of using modified list input is not DDE specific. You would need it even if you were using data in a CARDS statement, whether your data were blank- or comma-delimited.