topic Re: SAS DDE to Excel 2013 freezes SAS in SAS Programming

SAS DDE to Excel 2013 freezes SAS

sasmaverick — Tue, 09 Aug 2016 08:13:15 GMT

I am using SAS 9.4 in a server environment in Windows 7. I was able to use DDE to write to excel for the first time, but subsequently, the excel file opens up but empty and SAS freezes.

Below is the code I am using:

options noxsync noxwait;

proc freq data=sashelp.class;
tables age/out=age_freq nocum;
tables height/out=height_freq nocum;
tables weight/out=weight_freq nocum;
run;

data age_freq;
set age_freq (drop=count);
run;

data height_freq;
set height_freq (drop=count obs=6);
run;

data weight_freq;
set weight_freq (drop=count obs=6);
run;

x '"E:\testfolder\test.xlsx"';

filename example dde "excel|sheet1!r3c2:r8c3" notab;

data _null_;
set age_freq;
file example dlm="09"x;
put age percent;
run;

Re: SAS DDE to Excel 2013 freezes SAS

RW9 — Tue, 09 Aug 2016 08:25:27 GMT

I am not in the least bit surprised, it doesn't work at all on our setup. Basically DDE is very old, well over 15 years old and has been discontinued for 10 years or more. I would advise that you look into creating your excel report using libname excel or tagsets.excelxp, far more control and uses the latest technology.

Re: SAS DDE to Excel 2013 freezes SAS

Reeza — Tue, 09 Aug 2016 08:55:25 GMT

Does Excel open with any warnings? If so, that'll lock up the process. Try just opening Excel file and ending program there.

Re: SAS DDE to Excel 2013 freezes SAS

sasmaverick — Tue, 09 Aug 2016 09:02:50 GMT

Yes. Actually the MS Office on server is expired and opens with warning. Is
there a workaround to this?

##- Please type your reply above this line. Simple formatting, no
attachments. -##

Re: SAS DDE to Excel 2013 freezes SAS

sasmaverick — Tue, 09 Aug 2016 09:39:41 GMT

Actually I was able to use DDE for the first time. And it does work every now and then but not consistently.

What would be the best way to control which row and which cell (specify a range) I write to in excel. Note: I do not have a SAS ACCESS license at my workplace.

Regards,

Re: SAS DDE to Excel 2013 freezes SAS

LinusH — Tue, 09 Aug 2016 10:12:27 GMT

Pushing data to specific fields in Excel will be risky business regardless of technique.

So I would prefer a pull strategy:

Write an VB or similar application in Excel to retrieve data from a SAS server - would require some sort of SAS server license
Use SAS Add-in for MS Office - separate licensed

Re: SAS DDE to Excel 2013 freezes SAS

Reeza — Tue, 09 Aug 2016 10:31:24 GMT

@sasmaverick wrote:
Yes. Actually the MS Office on server is expired and opens with warning. Is
there a workaround to this?

##- Please type your reply above this line. Simple formatting, no
attachments. -##

Get a valid copy of MS Office?

Google "Rich Poor Proc Export" for a custom macro that uses VB that some users have written. It allows you to export to specific ranges/cells if that's all your using DDE for.

Re: SAS DDE to Excel 2013 freezes SAS

dhowser98 — Tue, 09 Aug 2016 20:08:05 GMT

I also use SAS 9.4 (workstation) and use DDE to interact with Excel 2013. I find the same problem as the OP, but typically this only occurs when either Word or Internet Explorer (I can't remember which) is also open on the same machine. Try closing these programs and re-running.

-Doug Fuller

Senior Research Analyst

Arbor Research Collaborative for Health

Re: SAS DDE to Excel 2013 freezes SAS

sasmaverick — Wed, 10 Aug 2016 19:44:16 GMT

Hi Reeza,

I had to keep the Excel file open all the time and then run the code, which worked for me!

Thanks

Re: SAS DDE to Excel 2013 freezes SAS

Reeza — Wed, 10 Aug 2016 19:46:30 GMT

That adds a manual step into the process...which is usually what DDE/automation is intended to avoid