BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasmaverick
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

Reeza
Super User

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

 

 

sasmaverick
Obsidian | Level 7
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. -##
Reeza
Super User

@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. 

sasmaverick
Obsidian | Level 7

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,

LinusH
Tourmaline | Level 20

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
sasmaverick
Obsidian | Level 7

Hi Reeza,

 

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

 

Thanks

Reeza
Super User

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

dhowser98
Calcite | Level 5

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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