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;
Does Excel open with any warnings? If so, that'll lock up the process. Try just opening Excel file and ending program there.
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.
Does Excel open with any warnings? If so, that'll lock up the process. Try just opening Excel file and ending program there.
@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.
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,
Pushing data to specific fields in Excel will be risky business regardless of technique.
So I would prefer a pull strategy:
Hi Reeza,
I had to keep the Excel file open all the time and then run the code, which worked for me!
Thanks
That adds a manual step into the process...which is usually what DDE/automation is intended to avoid
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.