Hello, 1992 called. They want their DDE Excel automation back.
Perhaps the title of this article is too pessimistic. Of course your SAS programs that use DDE (dynamic data exchange) can still work perfectly, as long as you situate your SAS software and its DDE "partner" (usually Microsoft Excel) to run on the same Windows PC. DDE is still documented and supported in SAS 9.4.
But for the growing number of users who modernize to a centralized SAS environment, the legacy of DDE is a big challenge to bring forward. Your new environment might include the benefits of SAS Grid Computing, SAS Visual Analytics, stored processes, and more manageable security. You might run your SAS programs using SAS Enterprise Guide or even a web browser. But with all of that shiny new tech and its distributed architecture, the simple local arrangement that allows DDE to function...well, it falls apart.
Let's look at how DDE works. This data exchange relies on two consenting Windows processes, both running on a single machine, to communicate with each other using special Windows messages. In SAS programs, this usually takes the form of SAS spawning Microsoft Excel, sending a command to reference a particular cell or range of cells in a sheet, and poking in some values that were computed in SAS. If your SAS session is now running on a remote machine (often a non-Windows machine), then SAS cannot use DDE to talk to your local Microsoft Excel application. As the man says, "what we've got here...is failure to communicate."
For those customers who have a lot invested in DDE (some have hundreds of programs!) and no time/budget to adjust processes away from it, I usually recommend a simple stop-gap approach: maintain a designated machine with SAS for Windows to do your DDE grunt work, even as you move other processes to an enterprise SAS environment.
But going forward, I encourage customers to look at the alternatives supported by new features in SAS that are more in line with today's topology (SAS on a remote server, SAS Enterprise Guide or other local client on the PC). These features can replace many (but not all) of the legacy DDE uses that are out there.
DDE is still supported in Microsoft Excel. We cannot say for how long, as Microsoft has put more emphasis on VBA, .NET, or PowerShell for Excel automation instead. But since DDE works only between two Windows processes (SAS and Excel in this case) on the same machine, it's an outmoded approach that's incompatible with many IT setups.
Some DDE alternatives
The SAS Add-In for Microsoft Office offers the most flexible method for complete control over the format and structure of your spreadsheet. It turns the problem on its head: instead of relying on a batch SAS program to push content into Excel, you simply use Excel to pull your SAS content into the spreadsheet, exactly where and how you want it. You can use SAS stored processes to encapsulate whatever SAS operation you need.
Within the SAS Add-In, you can also use specific cells and ranges as input into stored processes; it's just as flexible to inject your Excel content into SAS.
You can use Office scripting to automate the process (like a batch job), so the process can happen unattended. The consumers of your Excel documents do not need the SAS Add-In for Microsoft Office in order to view the results.
I know that not everyone is lucky enough to have the SAS Add-In. For batch SAS users, ODS tagsets.ExcelXP -- or its SAS 9.4 descendant ODS EXCEL -- can be used to place formatted report content into an Excel spreadsheet. Here's an example use for ODS EXCEL, which works as a "one-way" push from SAS into Excel.
Also, PROC EXPORT now supports adding sheets to existing files, or replacing entire sheets in place. That's flexibility that didn't exist before, when some users turned to DDE to fill the gap.
Aside from these SAS-centric approaches, creative programmer types can use script (VBS, PowerShell, and other) to plug their SAS data results into a spreadsheet as a post-process (which many customers spawn within their SAS programs).
Thinking of DDE as technical debt
Weaning yourself off of DDE is easier said than done, I'll admit. Twenty years ago DDE was a boon for SAS developers who needed to deliver Excel content to picky constituents. I played my part: I wrote the SAS Companion for Microsoft Windows (Release 6.11!) and crafted some of the examples that still exist in the documentation.
However, I now consider DDE programs to be a form of "technical debt" that organizations will have to pay off sooner or later.
If your "DDE bill" hasn't come due yet, you're fortunate. But if you're planning to write another SAS program that relies on DDE, consider the future generations. Today's kids are writing SAS programs in iPads and web browsers, and DDE is about as hip as a rotary-dial phone. (Yes, it still works, but it does make your fingers tired.)