The SAS Output Delivery System and reporting techniques

SAS to an EXCEL Pre-formatted Template

Reply
Contributor
Posts: 73

SAS to an EXCEL Pre-formatted Template

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.
SAS Employee
Posts: 95

Re: SAS to an EXCEL Pre-formatted Template

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.
N/A
Posts: 0

Re: SAS to an EXCEL Pre-formatted Template

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.
SAS Employee
Posts: 95

Re: SAS to an EXCEL Pre-formatted Template

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.
N/A
Posts: 0

Re: SAS to an EXCEL Pre-formatted Template

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?
SAS Employee
Posts: 95

Re: SAS to an EXCEL Pre-formatted Template

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.
Contributor
Posts: 73

Re: SAS to an EXCEL Pre-formatted Template

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?
SAS Employee
Posts: 286

Re: SAS to an EXCEL Pre-formatted Template

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
N/A
Posts: 0

Re: SAS to an EXCEL Pre-formatted Template

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!
N/A
Posts: 0

Re: SAS to an EXCEL Pre-formatted Template

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
Ask a Question
Discussion stats
  • 9 replies
  • 360 views
  • 0 likes
  • 4 in conversation