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

Chapter 11: Using DDE to Read and Write to Excel
Workbooks
11.1 Introduction............................................................................................... 167
11.2 Purpose ..................................................................................................... 167
11.3 Basic Concept of the DDE Client-Server Environment .............................. 168
11.3.1 How the DDE Client-Server Relationship Works .................................................168
11.3.2 General DDE Syntax and Options..........................................................................168
11.4 List of User-Written SAS Macros That Can Enhance DDE Processing ....... 171
11.4.1 SAS Macro to Start Excel .......................................................................................171
11.4.2 SAS Macro to SAS to Issue Commands to Excel ................................................172
11.4.3 SAS Macro to Define a Range of Excel Cells for Processing ............................172
11.4.4 SAS Macro to Save the Contents of an Excel Workbook ...................................174
11.4.5 SAS Macro to Close Excel Workbook...................................................................174
11.4.6 SAS Macro to Write All or Selected Variables to an Excel Output Workbook .175
11.5 List of Examples........................................................................................ 177
11.6 Examples................................................................................................... 177
Example 11.6.1 The Hello World Project..........................................................................177
Example 11.6.2 The Hello World Project When the Excel Workbook Is Closed..........179
Example 11.6.3 The Hello World Project Using NOTAB and LRECL= Options ...........180
Example 11.6.4 Writing “Hello World” to an Excel File Using DDE Macros.................182
Example 11.6.5 Writing a SAS Dataset to an Excel File Using the SAS_2_EXCEL DDE
Macro ...................................................................................................................................184
11.7 Conclusion ................................................................................................ 187
11.1 Introduction
Dynamic Data Exchange (DDE) has been around for a long time. DDE is a feature of the Microsoft Office
operating system and is available only if your computer is running Microsoft products. This is a method of
trading data between clients and servers. In this case, SAS is the client and Excel is the server. The magic
of DDE is that it boils down to using SAS as a way to enter data directly into an open Excel spreadsheet.
You can also write to Microsoft Word and many other products. Note that the Excel spreadsheet must be
open, and SAS must be running, because the data transfer is a “real time” “do it right now” type of a data
transfer. But, never fear, the SAS application can open the Excel program and close it when you issue
simple “X” commands.
11.2 Purpose
DDE is the oldest of the automated data transfer methods. The roots of DDE date back to Excel 4. Many
programmers and companies still use this method today, but it seems to work best only if you know exactly
where the input and output Excel data cells are within the worksheets. You as a programmer need to make
your programs keep track of exactly which Excel worksheet cells are to be read from or written to.