Using DDE to Read and Write to Excel Workbooks - Exchanging Data Between SAS and Microsoft Excel - page 182

Example 11.6.4 Writing “Hello World” to an Excel File Using DDE Macros
In general, computer programs have the following steps:

1. Start running.

a.

Open input files.

b. Open output files.

i. Read input values.
ii. Calculate values.
iii. Output values.
iv. Repeat until there is no more input.

c. Close output files.

d. Close input files.

2. End.

A SAS DATA step usually takes care of most of these operations for you. Therefore, many SAS
programmers rarely do more than start a DATA step (which names an output file), declare a SET statement
(which names an input file), and calculate the values needed. At the end of the DATA step, a RUN or a
PROC statement outputs the data records. When SAS runs out of input data, it closes the input and output
files for you. When you use DDE, you have to open and close the files yourself. You also have to find a
way to determine where the input and output data exists in your Excel files.

Examples 11.6.4 and 11.6.5 will use the macros described in Section 11.4 and explain how to automate
some of the input/output processed. This will give you a framework upon which to build your own DDE
interface tools. Before we examine the next example, you might want to review Section 11.4 to better
understand the macros presented in this chapter and stored on my SAS Author's page. They are used to
move data from SAS to Excel.

In Example 11.6.4, the five macros (start_Excel, Open_cmd, out_range, save_Excel, and Close_Excel)
defined in Section 11.4 above will be used to write two sets of data to an Excel workbook. The commands
used to do the writing are exactly the same, but the DDE-triplet defined by the macro is different. The
NOTAB option is set differently for each of the DDE-triplets. The data will be written to the same Excel
workbook, but in different locations on the same worksheet. We will start with a blank Excel workbook.
The following SAS macro calls invoke the macros described in Section 11.4 above. The implementation of
the code is stored on the SAS website for my Author's page. More elaborate and feature-rich macros exist
to do the similar DDE functions, but these are good enough to get you started.

http://support.sas.com/publishing/authors/benjamin.html

Figure 11.6.9: SAS DDE Code to Write “Hello World” to the Output Excel File Twice.

***********************************************************************;
** Example 4 - Simple Execution Code Example **;
***********************************************************************;

*======================================================================;
* Define the file name *;
*======================================================================;
%let my_file = "C:\Excel_Files\Book1.xls";

*======================================================================;
* Start Excel and open the file defined above as My_file *;
*======================================================================;
% start_excel (file_name=&my_file,wait= 5 ); /* Excel output file name */

*======================================================================;
* Set up a filename to use to issue commands to Excel via DDEs *;
*======================================================================;
% Open_cmd (file_ref=commands,wait= 5 ); /* command stream excel fileref*/