Which exporting method is best for this situation?

[deleted_user]

I have the common task of needing to export SAS data to Excel. I have 50 or so formatted table shells in Excel and had previously been using DDE to export the data into the empty shells, which worked perfectly. Now, I am being forced to execute SAS remotely, and DDE and SAS Connect are conflicting. So, I switched to using Proc Export, and keeping the table shells as one worksheet, exporting the output data sets to another worksheet in the same Excel workbook, and linking the cells in the shell worksheet to the appropriate ones in the output data set worksheet. This is for a quarterly report, so I need to be able to overwrite those output data set worksheets, keeping the same worksheet name, so that my links to the table shell worksheet will remain intact (so that once I do the linking once I should not have to do it again). This seems to be working with Proc Export, with one exception. My table shells contain text with superscripts, and when I run the SAS program with the Proc Export, those superscripts turn into regular-sized text. Since these table shells are then linked to a MS Word document which becomes the final report, I'd really like it if they remained superscripts, so that I don't have to manually fix 50 tables 4 times per year.

I have been looking to see if maybe ODS or another method (Excel libname engine) would do the same thing and not cause the same problem, but it is looking like I would run into even worse problems, i.e. the whole excel workbook would be overwritten in the process of exporting (therefore losing my table shell sheets), or the output data set cannot be overwritten/replaced (therefore I cannot update the sheets each quarter). Am I missing something, or is Proc Export as close as I can get? And does anyone have a solution to my superscript problem? Thanks!

[JohnH]

I also use DDE to send SAS numbers to pre-formatted EXCEL tables. As a short term fix, I would first look into resolving the conflict between DDE and SAS Connect. Perhaps it is one or two settings to get right. Give the SAS Techies a call. They are very helpful.
We are now looking into insourcing the whole publication process into SAS, using ODS and the Proc Templates to create the style sheets according to our specs. I would recommend trying to insource all of it into SAS.

[Eric_SAS]

One choice is to use the excelxp tagset. It cannot write to existing workbooks or worksheets. But it can create them with formatting and style so it should be possible to get your Excel files to be the way you want straight from SAS. It does not currently handle superscripts. But if we know what the xml should look like we can make it do superscripts. Providing that Microsofts XML definition supports them. The Excel tagset does support links so it is possible create drill down reports like what you want.

By default it creates multiple worksheets, one per table. But that can also be controlled. Take a look at the various resources on the odsmarkup page.

http://support.sas.com/rnd/base/topics/odsmarkup/

[deleted_user]

Thanks to you both for your suggestions. Unfortunately, we have contacted SAS about the DDE/Connect conflict and they said it could not be resolved. I don't quite get that, but I definitely do not know much about how either functions, so it is hard for me to question.

Perhaps I should work to move towards exporting in one of the ways you described. One of my concerns is making things too complicated. I work with several folks with limited SAS skills, and such, I liked using DDE and/or a simple PROC EXPORT, because it involved little more than basic data step programming. It might be worth it though, if I can get it to create a perfect table. I will check it out. THanks again.