SAS to an EXCEL Pre-formatted Template

[JohnH]

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.

[Eric_SAS]

The easiest way would be to use ODS CSV. Then link the template to the external CSV file with the external data menu option on the worksheet.

From then on, if the CSV updates anyone reading the template will be notified and the worksheet will update if they want it to.

[deleted_user]

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.

[Eric_SAS]

He did say he wanted to use his templates.

Of course if you don't want to use the templates then using the Excelxp tagset to recreate the workbooks is a perfectly good solution. It's a better solution than DDE. Far less klunky.

If you truly want DDE there is a DDE tagset that can also be used. It's better than having a custom data step for every piece of data.

[deleted_user]

I was using old NT with old Excel (pre 19??), thus forced to use DDE. I believe but haven't ventured yet that xml is a more modern way to go?

[Eric_SAS]

Yes. If you are targeting any version of Excel previous to 2002 you are stuck
with DDE, HTML, CSV, or some combination. Maybe even external data.

Excel creates so much work for us. I guess that's a good thing.

[JohnH]

But, is there any way of reading all of the style elements from the template into SAS and then getting SAS to produce the whole thing using the EXCEL tagset?

[Vince_SAS]

JohnH-

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"

http://www2.sas.com/proceedings/sugi27/p174-27.pdf

"A Beginner's Guide to Incorporating SAS® Output into Microsoft Office Applications"

http://www2.sas.com/proceedings/sugi28/052-28.pdf

Additionally, this paper contains some good general references for Excel Web Queries.

Good luck.

Vince DelGobbo
SAS R&D

[deleted_user]

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!

[tgleghorn]

i tried using excelxp, it works, except I am not able to apply a different format for each line statement.

[ballardw]

Since this thread is nearly 18 years old I suggest that you start your own thread. In that thread describe what you are attempting to do, the code that you used, a data step to create some example data like yours and since this involves specific ODS output the ODS destination statement with all the options you used and what your active ODS Style at the time you run the code. You may need to provide an example of the Excel template as well.

There has been a lot of changes related to ODS output in the 18 years since this thread was posted. So many options were not available.

[deleted_user]

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.

Regards,
LeRoy Bessler