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!