| |
"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).
|
|