DATA Step, Macro, Functions and more

SAS DDE to Excel 2013 freezes SAS

Accepted Solution Solved
Reply
Contributor
Posts: 64
Accepted Solution

SAS DDE to Excel 2013 freezes SAS

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;


Accepted Solutions
Solution
‎08-10-2016 03:43 PM
Super User
Posts: 19,861

Re: SAS DDE to Excel 2013 freezes SAS

Posted in reply to sasmaverick

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

 

 

View solution in original post


All Replies
Super User
Super User
Posts: 7,988

Re: SAS DDE to Excel 2013 freezes SAS

Posted in reply to sasmaverick

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.  

Solution
‎08-10-2016 03:43 PM
Super User
Posts: 19,861

Re: SAS DDE to Excel 2013 freezes SAS

Posted in reply to sasmaverick

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

 

 

Contributor
Posts: 64

Re: SAS DDE to Excel 2013 freezes SAS

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. -##
Super User
Posts: 19,861

Re: SAS DDE to Excel 2013 freezes SAS

Posted in reply to sasmaverick

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. 

Contributor
Posts: 64

Re: SAS DDE to Excel 2013 freezes SAS

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,

Super User
Posts: 5,437

Re: SAS DDE to Excel 2013 freezes SAS

Posted in reply to sasmaverick

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
Data never sleeps
Contributor
Posts: 64

Re: SAS DDE to Excel 2013 freezes SAS

Hi Reeza,

 

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

 

Thanks

Super User
Posts: 19,861

Re: SAS DDE to Excel 2013 freezes SAS

Posted in reply to sasmaverick

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

Occasional Learner
Posts: 1

Re: SAS DDE to Excel 2013 freezes SAS

Posted in reply to sasmaverick

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 1129 views
  • 7 likes
  • 5 in conversation