topic Re: EXPORT SAS DDE doesn't communicate with windows 10 Excel 2016 in SAS Programming

EXPORT SAS DDE doesn't communicate with windows 10 Excel 2016

Marwa_Se — Thu, 26 Nov 2020 13:11:39 GMT

Hello everybody,

I'm working with the DDE export to generate a dashboard, i used to work with this code below to open my excel file before Moving to Windows 10

SYSTASK COMMAND "Start Excel";

Data _NULL_;
CALL SLEEP (5,1);
RUN;

/* Open excel */
filename xc dde 'excel|system';
DATA _null_ ;
 file xc;
  put '[ouvrir("file\Challenge.xlsx")]' ;
   t=sleep(5) ;
RUN ;

Now since we moved to Windows 10 even when i'm using the same PC SAS version 9.4 but Excel 2016. When I run the same code with the same dataset, the PC SAS gets hung for an eternity after a couple of seconds and then I have to just kill the process. Nothing happens to the open excel file. I can't even see the log since the PC SAS gets hung. Please suggest a solution for this

thank u in advance!

Marwa

Re: EXPORT SAS DDE doesn't communicate with windows 10 Excel 2016

JOL — Thu, 26 Nov 2020 17:24:34 GMT

DDE is a legacy method, and SAS does not encourage using DDE. Instead, consider using the SAS Add-In to MS Office or alternatives listed below.

SAS programs that use DDE can still work, as long as you situate your SAS software and Microsoft Excel to run on the same Windows PC.

Here are some alternatives to DDE:

SAS Add-In for Microsoft Office

Use Excel to pull your SAS content into the spreadsheet. You can use SAS stored processes to encapsulate whatever SAS operation you need.

Office Scripting

You can use Office scripting to automate the process like a batch job, so that the process can run unattended. Those who use your Excel documents do not need to install the SAS Add-In for Microsoft Office in order to view the results.

PROC EXPORT

supports adding sheets to existing files or replacing entire sheets.

ODS EXCEL

For batch SAS users, ODS tagsets. ExcelXP and ODS EXCEL can be used to place formatted report content into an Excel spreadsheet. See ODS EXCEL for an example.

Scripts

enables the use of scripts like VBS and PowerShell to plug SAS data results into a spreadsheet as a post-process, which users can spawn within their SAS programs.

Re: EXPORT SAS DDE doesn't communicate with windows 10 Excel 2016

ballardw — Thu, 26 Nov 2020 21:13:13 GMT

In addition to @JOL's very useful information the communications links that DDE use can be interfered with by other newer applications.

I discovered that Cisco Jabber for one, if running, disables DDE even when I had Excel installed locally. So to use DDE I had to KILL the Jabber process (not stop the application, go into Task Manager and KILL the process). If you have other applications that interfere you may not have the option of killing them to allow DDE to run.

Luckily the reason I was using DDE was replaced by ODS options.

Re: EXPORT SAS DDE doesn't communicate with windows 10 Excel 2016

ChrisNZ — Fri, 27 Nov 2020 00:29:30 GMT

> DDE is a legacy method, and SAS does not encourage using DDE

Just to add:

DDE is a legacy method, and Microsoft does not encourage using DDE. It's not a stance by SAS.

It only remains enabled, with no support, for compatibility purposes.

See this devblog entry from 2007 https://devblogs.microsoft.com/oldnewthing/?p=27863