I am given an EXCEL pre-formatted template for a table, complete with row and column titles, footnotes, etc. The cells that contain the stats for the tables are blank. I presently use the Dynamic Data Exchange (DDE) system to get the stats from SAS to EXCEL WITHOUT DISTURBING THE FORMATTING. This is old technology. But, all of the new technology overwrites the format. Can anyone suggest the best way to get the stats into the cells without disturbing the format? By the way, I need to automate this procedure for about 1,000 tables.
Alternatively – the above from Eric@SAS (no disrespect intended) would mean setting up 1000 linked tables? - JohnH if you are using DDE, did you know that you can reproduce every excel function from DDE (bar one)? Thus starting from a new blank spreadsheet you can build your workbook from scratch with all the formatting that is required. The one function that does not work is renaming a worksheet (see below for my solution). And by all the formatting I mean fonts, text sizes, numeric field formats, underlining, bolds, you name it, you got it.
I had an exercise (not as big as yours), but one that involved manual formatting of data, which typically took around one day to complete. I had to use the group function on around 50 fields and 600 rows, grouping columns by year / quarter / month / day and the rows by a predetermined hierarchy, across 22 worksheets. OK it took me a week to set up the program, but then the program took an impressive 20 seconds to build the workbook – an obvious time saver for the future and its really impressive when running - reminds me of that robot in I Robot - what's his name? - drawing the picture.
I couldn't use a template which drew the data across as the columns were expanding to the right with time from a fixed point.
Look for DDE on SAS help online – there are lots of papers showing the techniques involved.
I got around the problem of renaming the worksheets creating 22 CSV files from SAS; opening a new blank workbook and using the move/copy command (DDE call from SAS), the move all the data into the workbook. The sheet comes across with the name of the CSV file (shortened to 32 chars). It is interesting and helpful that once you have moved the only sheet out of a file opened in Excel, Excel automatically closes the file without saving, and without annoying dialog question boxes either! You can turn the dialog box off as well with an error function call. If you’re interested in some sample code, I’ll only be able to provide tomorrow – I got it at home.
Given the size of your project and the Excel template requirement, I think the best course of action is to stay with your DDE solution. Is there something wrong with that, or are you just looking for something more "up-to-date"?
Perhaps going forward, you could look into Eric's solution of bringing ODS CSV (or HTML) output into a separate sheet in the workbook using an Excel Web Query or similar method. In the pre-formatted worksheet, use standard Excel cell references to point to the cells in the worksheet that contains the ODS output. This will populate the cells in the pre-formatted worksheet with the appropriate ODS data.
If you use SAS/IntrNet or SAS Stored Processes in conjunction with the Excel Web Query, you can add dynamic update capability (refresh the ODS output on demand).
For information on using Excel Web queries with SAS, refer to these papers:
"Techniques for SAS® Enabling Microsoft Office Applications in a Cross-Platform Environment"
best experience with ODS -> HTML. EXCEL in all versions I know seems to get HTML very good. Even the usage of the extention .XLS instead of .HTML works very good, so it is not necessary to change the defaults for a double-click. All things, including formats, colors, fonts are nearly as in a web-browser. Try it out!
Dynamic Data Exchange (DDE) is the solution that I have used over and over for this type of application, including as recently as last month. See, e.g., paper http://www2.sas.com/proceedings/sugi31/022-31.pdf and the references cited therein. A zip file of 60 macros, 26 sample programs (with substantial comments), and supporting files is available via email from the author. These resources may not explicitly address your specific situation, but they should give you tools and techniques that you can adapt.