BookmarkSubscribeRSS Feed
chris_e
Obsidian | Level 7

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

 

 

4 REPLIES 4
ballardw
Super User

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

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
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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?

 

 

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
  • 4 replies
  • 1673 views
  • 0 likes
  • 4 in conversation