DDE Gets Stuck Exporting to Excel

[chris_e]

I have a SAS macro I created a while a go that creates a whole lot of tables in SAS from SQL tables, then exports them into specific places Excel, which has formulas set up to take that data, perform calculations and present it in a user friendly format.

It opens a template spreadsheet, pasting the data using DDE into specific places in that spreadsheet then saves the spreadsheet under a new name. I like it because I just need to put in a few details of the SQL tables and press go for it to build a whole spreadsheet for me reporting a number of metrics of interest.

However recently I tried to use it and it seems to get stuck I have run it in parts and identified the code causing it to get stuck below

options noxwait noxsync;
x "&dir.\Template.xlsx";

/*Export occupancy*/
filename sas_out dde 'excel|prim_mods!r44c2:r300c4';

/*Main data you want checked*/
/*THE BELOW CODE WHEN RUN GETS STUCK*/
data _null_;
	set work.occ;
		file sas_out;
		put occscheme occtype expo2;
run;

If anyone know how I can fix this or another way of sending tables to Excel in a similar fashion it would be appreciated?

Thanks

[ballardw]

You may have to provide more detail of what you mean by "gets stuck".

Note that there are multiple other programs that interfere with DDE. I discovered this when the only program I used with DDE started misbehaving (not creating any output and seemingly running). In my case the culprit was Cisco Jabber which requires the process to be killed from Task Manager not just closing the active window for DDE to run properly.

If your "user friendly" sheet isn't actually used by people to CHANGE values after created I would suggest moving all of the calculations into SAS and then create a populated output file.

Nearly 10 years ago Microsoft suggested everyone stop using DDE  https://blogs.msdn.microsoft.com/oldnewthing/20070226-00/?p=27863

[chris_e]

Thanks, ballardw.

I have experimented with closing down some programs and believe it is the my new version of Microsoft Outlook. When that is closed down it seems to work.

Do you know of any other way in SAS of pasting tables like that into Excel, so I don't have to shutdown Outlook every time I run this?

Thanks

[Reeza]

You can use the macro here. 

It allows you to specify the Range you need and to use a template if necessary.

http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

[RW9]

I am not surprised your having issues, support for DDE was stopped 10 years or more ago, and in several scenarios doesn't work at all.

I would ask why you don't directly link the SQL into the spreadsheet itself?  Saves sending it via SAS.

The next question would be, why not just do the processing in SAS, and produce an output file with the nicely presented data, save using a Excel template?