SAS® Related Publications & Codes

 

 

"Using Dynamic Data Exchange to Pour SAS® Data into Microsoft Excel." Proceedings of the 18th SAS European Users Group International Conference, 2000.
Abstract:
Version 6.12 of the SAS® System under Windows® provides a number of methods to export data from a SAS® data set into a Microsoft Excel worksheet. To name but a few: the point-and-click SAS® Export Wizard is satisfactory if one only needs it occasionally and if one doesn't care too much about how the data will be formatted in the resulting worksheet. Improved results may be obtained by taking the programming approach and tinkering with proc dbload, although one will still run into limitations easily. In contrast, the use of Dynamic Data Exchange (DDE) allows full programmatic control over where exactly in the worksheet data are being inserted, and how the worksheet-cells are formatted. The %sastoxl macro pours SAS® data into either a new blank Excel workbook, or into an existing one. Furthermore, it demonstrates some key DDE functionality by allowing the user to specify the name of the workbook-worksheet that is to receive the data, choose the upper-left cell of the data-block, and decide whether to apply a certain set of common formatting options like font, font-size, column-width auto-fit, and freeze-panes for the label row.
Download: paper (PDF, 164K) code included.

"Using Dynamic Data Exchange to Export Your SAS® Data to MS Excel - Against All ODS, Part I." Proceedings of the 26th SAS Users Group International Conference, 2001.
Abstract:
Of all the different ways in which the SAS® System allows data export into a Microsoft Excel spreadsheet, Dynamic Data Exchange (DDE) is the only technique providing total control over the Excel output. As is often the case however, this high level of control comes at a price ... The DDE formalism can appear quite daunting at times, even downright obscure! The purpose of this paper, liberally sprinkled with recyclable Working Code™, is to provide a detailed walkthrough of how to programmatically create and customize an Excel workbook containing SAS® data. More or less in order of decreasing obviousness, we will cover the following specific topics: firing up Excel from within a SAS® session; loading and saving an Excel workbook; inserting SAS® data into a given worksheet; formatting a range of spreadsheet cells; adding an Excel macro sheet to a workbook; renaming a worksheet; finding out the names of existing worksheets in an Excel workbook. As a bonus, a SAS® macro is presented, harnessing the power of DDE in an easy-to-use one-liner!
Download: paper (PDF, 116K) macro code (TXT, 40K) conference package (ZIP, 596K), includes presentation, demo code, and demo Excel file.

"Using Dynamic Data Exchange to Export Your SAS® Data to MS Excel - Against All ODS, Part I." Proceedings of the 27th SAS Users Group International Conference, 2002.
(a repeat performance of the above...)

"Creating Custom Excel Workbooks from Base SAS with Dynamic Data Exchange: a Complete Walkthrough." Proceedings of the 27th SAS Users Group International Conference, 2002.
Abstract:
If you find yourself besieged by hordes of business users and analysts, all clamoring for SAS output in nicely formatted Excel workbooks, then you'll need to go beyond what proc export offers ... Dynamic Data Exchange (DDE) will do the job for you. DDE is a communication protocol that lets pc-based applications talk to each other. In particular, DDE allows a SAS session to take control of the Excel application, and perform manipulation of cells, spreadsheets and entire workbooks just as if you were doing so manually. The purpose of this workshop is to provide a complete walkthrough for building good-looking Excel workbooks from scratch, all from within Base SAS code. As you run the provided sample code and observe what it accomplishes, you'll learn how to create, rename and move spreadsheets, fill them with SAS data, apply Excel formatting to selected cells, change fonts and colors, center titles, enter Excel formulas, specify header and footer, and much more. More than enough in fact to keep those hordes at bay!
Download: paper (PDF, 96K) workshop code (ZIP, 10K).

"Fancy MS Word Reports Made Easy: Harnessing the Power of Dynamic Data Exchange - Against All ODS, Part II." Proceedings of the 28th SAS Users Group International Conference, 2003.
Abstract:
For many years, the SAS System for OS/2 and Windows has had the ability to talk to other pc applications by means of Dynamic Data Exchange. DDE enables the creation of fully customised MS Word documents straight from within a base SAS program, and is therefore ideal for automating high quality output from the SAS System. Using a set of easy-to-use SAS macros, we show how to insert and format SAS data, graphs, and tables into a Word report. As a further demonstration of the myriad possibilities, a DDE-driven Word mail-merge application is constructed.
Download: paper (zipped PDF, 74K) conference package (ZIP, 732K), includes presentation, demo code, Word template documents.

"Excel Exposed: Using Dynamic Data Exchange to Extract Metadata from MS Excel Workbooks" Proceedings of the 10th South-Eastern SAS Users Group Conference, 2003.
Abstract:
Dynamic Data Exchange (DDE) can be used on the Windows platform to create fully customized MS Excel and MS Word files, all from within a Base SAS program. But it also comes in handy for gathering useful metadata about your Excel files! When automating the reading from, or writing to an Excel workbook, life becomes a lot easier if you have access to certain facts about the workbook you're manipulating. Such as the names of the worksheets it comprises; the type of each worksheet -- data, graphics, or macros; the number of rows and columns that are in use on each data-sheet; whether a given column contains numerical, character, or mixed type values; and so forth. In this tutorial we will go through the necessary DDE-movements that allow the extraction of such Excel workbook metadata. Wrapping up, a SAS macro is shown to load these metadata into a SAS data set for easy reference in the manner of a dictionary table.
Download: paper (zipped PDF, 157K) (Still working on readying the code and macros for general consumption... They'll be here soonish).

 

 

 

 


 

Main Site Portal