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.)
26 Comments
Hello Chris - great article, highlighting the fact that no process is "forever" and must be routinely reviewed, hopefully validated, but also updated to keep track with changes in surrounding technology.
At my SAS/Excel presentation at SGF this year - explaining lots of different ways how SAS & Excel co-exist (including DDE) - I dared to suggest that "DDE is dead". Needless to say, the "DDE gang" were waiting for me offstage for a showdown...
Thanks Andrew. You're braver than I am -- I wouldn't be so bold to say that "DDE is dead" to a group of long-time SAS programmers. I'd say it's more like a loyal pet that has served us well over the years. But as we contemplate our move out of the basement and into our fancy new downtown apartments...well, there's just no room for good ol' DDE. It might be time for DDE to "move out to the farm", where it can run and be free (as far as we know).
Chris, That is very nice post on why not to use DDE anymore.
The reactions Andrew got are from SAS insiders wanting to protect it. There are other groups around blaming SAS is old fashioned still using DDE and get rid all of that together. ==Politics==
Am more interesting question is the missing connection to the Open Office standards published at OASIS and being used by Microsoft with extensions. Yep that is the xlsx type structure.
Being that well documented and that long around it is annoying SAS missed the vision the adopt all those as evolved newer interface formats.
Jaap, thanks for the comment. DDE is a blessing and a curse. It's incredibly flexible for SAS interaction with Excel, but customers with a high investment in DDE-specific processes encounter challenges when trying to move their SAS processes into a modern environment.
SAS does support XLSX output, in Base SAS, via the new ODS EXCEL destination. It also supports flexible input/output options using PROC IMPORT/EXPORT with DBMS=XLSX (using SAS/ACCESS to PC Files). As you say, these are leveraging the Open Office standards. (And ODS also supports the PPTX format via the ODS POWERPOINT destination -- so it's not just Excel.).
I worked a lot with automating office from Sas. In a Windows environment, I'd say the best and easiest (and cheapest) way is using OLE. It's built into office and Windows. You get error messaging between the automated apps. You get eventing. You can also automate via DCOM and run client / server apps on separate machines. Or - If you want to be up to date, embed com-components into net interops, or use .Net as is. No need for sas addins. Just put the calling code in the scl language (proc display ) or use Scripting. You expose ALL properties and methods that office have to offer, into sas. You can also naturally make your own components and in these you can automate any app or program logic you have invented yourself. /Axel
Axel -- yep, using OLE (COM) to automate Office is slightly better than DDE, in that Microsoft offers good documentation of the Office object model. (Fun fact: I also documented the OLE integration with SAS/AF and SCL back in the day (Release 6.11 Changes and Enhancements doc for SAS under Windows.)
However, SCL and PROC DISPLAY still bind you to a single machine, where SAS and the Office apps must coexist. So for those who must move to a central SAS environment for security/maintenance/scalability reasons, the direct OLE/SAS integration will be limiting.
Yes, right you are. But with .Net remoting or the still working, but obsolete, DCOM model you can divide processing on any number of machines. All Im saying is that there are alternatives. Anyway, good article. Thanks /Axel
Axel, yes, .NET remoting is feasible. DCOM is difficult because of its many security issues, and Microsoft has closed many of the holes that, in turn, has the effect of disabling meaningful DCOM processing.
Thanks for being creative in your use of SAS with other processes!
Hi Chris,
I want to copy a file(o/p) from SAS(9.3) server to Windows folder location. I can't do it in Copy file task since i need to schedule this job in Scheduler which runs the job in the server remotely when certain conditions satisfies.
I tried with the X command and all other stuffs but i'm not able to succeed. Need your help in this.
Thanks in advance.
Regards,
Ram
Ram, perhaps this copy file method could help? It does the work completely in SAS code. However, your SAS session still needs access to your Windows folder somehow, either through a network share or mapped drive.
And the sources for why your programs using DDE may suddenly quit working are expanding. I have one job that used DDE that is run once a year that didn't work. It took me most of a day to determine that one of the applications our IT department had added to all of our machines, Cisco Jabber interfered. It required not only closing Jabber but going into Task Manager and killing the process.
DDE is still viable for co-located Windows applications to talk to each other, and a messaging app like Jabber might rely on this to integrate with other applications in a line-of-business (such as a CRM application). But your example illustrates how the presence of such an application can interfere with other "chatter" on the DDE channels.
Interesting article on exporting data to EXCEL from SAS using the Add-ins and tag sets, but not all of us use DDE processes to export data. What's an efficient way to import data replacing an old DDE process?
PROC IMPORT brings an entire EXCEL file into SAS temp space (depending on the EXCEL spreadsheet setup the columns can be formatted on not). So this method is very inefficient when you only want column number 10,15, and 45 out of a spreadsheet with 100 columns and 12,000 rows. Combine that with a macro to pull in multiple spreadsheets of the same layout and space is now at a premium.
Using EG's fine import wizard, it appears that SAS converts an EXCEL spreadsheet to a text/CSV file and then creates datalines/cards statement to read in the data. Again, seems inefficient when I only want certain columns and I have a lot of spreadsheets to churn through. Also, I don't want to do this manually.....
DDE may be an antiquated way of doing things, but it had its uses and still does. I realize SAS needs to compete with the Tableaus and the Qlikviews of the world for slick ways to extract and present data, but you don't throw out the baby with the bath water.
O.T.S.U.,
Thanks for the comment! DDE still works and is supported as long as you have SAS for Windows and Microsoft Excel on the same desktop. Increasingly, that arrangement is disappearing at many SAS sites due to centralization, modernization, etc.
I agree that if you want to pluck just a few values out of a spreadsheet, importing the entire kit-and-caboodle is overkill. But most code libraries that read the new XLSX format must parse through an entire sheet (which is XML internally) to get to the data. Even when using DDE, you're communicating with a Microsoft Excel process that has loaded the sheet into memory. In that case, the burden is on the Excel process and not in your SAS session. While I'm sure that Excel is very efficient at reading its own files, the content of the file is still loaded into memory somewhere. DDE aside, you could use VB Script or PowerShell with Excel automation to surgically retrieve the values you want -- but I imagine EXCEL.EXE will still load the worksheet into memory.
For SAS programmers, you could use the LIBNAME EXCEL (or PCFILES) engine to access the data like a typical library, and use POINT= and KEEP= to get just the values you want. While this makes the code appear to be surgical, I'd guess the spreadsheet is still read in completely behind the scenes.
The Import Data task in SAS Enterprise Guide does its work by processing all of the data locally before passing it to SAS. This typically results in a two-pass process, but then you don't need any additional software (like SAS/ACCESS to PC Files or PC Files Server) to get your data into the flow.
Blasphemer!!!! ... just kidding
well written
i do still use DDE extensively (major debt?) but as others have mentioned, there's now many other apps that "interfere" w/ the DDE messaging that i, too, have to remember to close before starting any DDE sessions
also - for those who may remember the 60's ... there's something very "trippy" about sitting there watching your empty Excel spreadsheet instantly fill w/ tons of data when i batch my SAS DDE jobs
later
Thanks William! It was fun to watch DDE at work -- like a million little fingers typing away at invisible keyboards.
Interesting points.
I know there are different ways to get data from SAS into Excel, but right now I don´t know a different way to just fill data into an existing(!) Excel template, maybe to start some VBA macros and save.
We have quite some reports which are based on this approach and I would be happy to learn an easy-to-use and reliable alternative.
Regards,
Chris
Chris, if you have the SAS Add-In for Microsoft Office, then you could drive the SAS content into Excel from within Excel. Absent that, you could export the content you need from SAS (into a text file, for example) and then use PowerShell or VB Script to automate Excel and add it into your Excel sheet. There are many more options, but these are just a couple of ideas. Again: DDE still works as long as SAS and Excel are co-located, but when that situation changes, you'll have to get creative with a new approach.
Could I ask for a suggestion then, please: We use DDE to place values into specific cells, so, say, we want to put in the number 12 into cell C42.
What would be a good SAS, non-DDE way of doing that task ?
Thanks,
JB
Jørgen,
Other than using LIBNAME XLSX to completely replace a sheet, I think the method you need to use involves some sort of post-processing on the Excel file. You can use SAS to generate a script (VB Script, PowerShell) that automates Excel to place the value exactly where you need it, and then use X or SYSTASK to run that script as the last part of your SAS job. All of this, of course, requires that your SAS output (including the script) are on the same machine where Excel is.
When that's not the case -- when running through EG -- you could use SAS to create the script file, use the Copy Files task to copy the script file to the local PC, then use the Command Prompt task to execute. All of this can be captured in the process flow. The Copy Files task and the Command Prompt task are both available for download from this blog.
Thanks Chris, I did a quick test and the VBS method would be a viable option if DDE was ever removed
Hello All.
Much has been said about the good and the bad about DDE.
Among all the other alternatives, DDE is the only tool that provides cell level manipulation (pulling or putting the data at a specific location in the spreadsheet).
Consider the case of an organization still running SAS 9.2 on Windows 7 (Both 64 bit) and Office 32 bit. (and PC file server not installed. Why? Admin rights on Desktop). The best recourse is DDE.
Pingback: Using LIBNAME XLSX to read and write Excel files - The SAS Dummy
Pingback: How do I export from SAS to Excel files: Let me count the ways - The SAS Dummy
Hallo all together,
actually we have a lot of pre-formatted Excel workbooks from the subject area (is that appropriate to say? I am part of the IT and customers from Marketing, Controlling, etc requests reports) and we need to fill there Excel pattern. This is what we need DDE commands for. There is not a comparable way that I read recently.
The idea with the post-processing scripts (VBS, Powershell, etc.) comes closest to what we want.
Is there any other alternative to fill pre-formatted Excel workbooks?
Thank you so far,
Michael Schmidt
Yes, PowerShell or other scripting is the only solution I can think of for automating local Excel.