| |
"Using Dynamic Data Exchange to Pour SAS® Data into Microsoft
Excel." Proceedings of the 18th SAS European Users Group International
Conference, 2000.
Author(s): Koen Vyverman
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
"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.
Author(s): Koen Vyverman
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, macro code, conference
package (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.
Author(s): Koen Vyverman
(essentially 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.
Author(s): Koen Vyverman
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, workshop
code
"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.
Author(s): William W. Viergever & Koen Vyverman
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, conference
package (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.
Author(s): Koen Vyverman
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
"Hash Component Objects: Dynamic Data Storage and Table Look-Up" Proceedings of the
29th SAS Users Group International Conference, 2004.
Author(s): Paul M. Dorfman & Koen Vyverman
Abstract:
Searching for data being one of the most important and frequently performed data processing operations, SAS supports it with a roster of built-in techniques, such as merges, joins, formats, indexes, operators, and functions. However, the fastest look-up methods based on hashing algorithms had not been offered as encapsulated modules until the advent of Version 9.0. This gap had been partially filled by SAS programmers crazy enough to code their own hash-based schemes in the SAS Language. At last, in Version 9.0 direct-addressed data storage and look-up was implemented as a Data Step Component Interface object. Easily controlled via the object-dot syntax, the hash object renders all standard table operations — insertion, search, and deletion — in constant time and much faster than any other built-in SAS look-up structure. A real breakthrough, it represents the first ever truly dynamic, run-time, memory-resident data step structure. By adding even more features and methods to the hash object, Version 9.1 has made its programming potential nothing short of mind-boggling. We will get a taste of this new exciting SAS tool and reveal at least a few splendid things it makes possible.
Download: paper
"Publishing Jack Vance: The SAS® System as a Tool for Literary Analysis" Proceedings of the
30th SAS Users Group International Conference, 2005.
Author(s): Koen Vyverman
Abstract:
The Vance Integral Edition (VIE) is a non-profit organization that aims to publish the entire works of the American author Jack Vance in a limited, durable, and definitive edition. As each of Vance’s 136 novels and short stories progresses through the VIE workflow — from scanning and digitizing, via restoration and proofing, all the way through to typesetting and printing — the SAS System has proved to be invaluable. SAS software has been used for comparing and analyzing the contents of the various file formats that are involved (flat-file, Word document, RTF), for keeping track of text changes, for reporting in Word and Excel formats, and for providing analytical insights into some prickly questions of textual and stylistic integrity. This paper discusses the major VIE processes where SAS came to the rescue, with ample segments of code and sample output.
Download: paper
"A Matter of Presentation: Generating PowerPoint Slides from Base® SAS using Dynamic Data Exchange" Proceedings of the
30th SAS Users Group International Conference, 2005.
Author(s): Koen Vyverman
Abstract:
The creation of PowerPoint slides with SAS content using DDE in a Base SAS environment has long been considered impossible. Unlike MS Word and MS Excel, the PowerPoint application does not come with a scripting language like WordBasic or the Excel 4 macro language that would allow DDE to talk to it in a client/server fashion. The job can be done, though, by using DDE to Excel as an intermediate agent to pull the strings of PowerPoint. A set of easy SAS macros is introduced to perform a number of basic PowerPoint operations. As a sample application, a SAS catalog of graphs is exported to a stand-alone PowerPoint presentation. No specific technical knowledge is required from the reader, at least not beyond a basic understanding of the SAS macro language. A slight degree of familiarity with DDE to Excel concepts should prove enlightening, though.
Download: paper
"Data Step Hash Objects as Programming Tools" Proceedings of the
30th SAS Users Group International Conference, 2005.
Author(s): Paul M. Dorfman & Koen Vyverman
Abstract:
In SAS® Version 9.1, the hash table — the very first object introduced via the Data Step Component Interface in Version 9.0 — has become robust and syntactically stable. The philosophy and application style of the hash objects differs quite radically from any other structure ever used in the Data step previously. The most notable departure from the tradition is their run-time nature. The hash objects are instantiated and/or deleted and acquire memory, if necessary, at the run-time. It is intuitively clear that such traits should make for very interesting and flexible programming having not seen in the Data step code of yore. Although some propaedeutics will be provided in the paper, the talk is intended for SAS programmers already somewhat familiar with the basic ideas and syntax behind the hash objects. Instead of teaching hash basics — which by now has been rehashed enough! — live code examples will be used to demonstrate a number of programming techniques, which would be utterly unthinkable before the advent of the canned hash objects in SAS. Imagine using “data _null_” to write a SAS data set, whose name depends on a variable. Or fancy sorting a huge temporary array rapidly and efficiently without the need
for sophisticated hand-coding. In other words, you are in for a few intriguing SAS tunes from the hash land.
Download: paper
"Data Step Hash Objects as Programming Tools" Proceedings of the
31st SAS Users Group International Conference, 2006.
Author(s): Paul M. Dorfman & Koen Vyverman
(essentially a repeat performance of the above...)
Download: paper
"Hash Crash and Beyond" Proceedings of the
SAS Global Forum Conference, 2008.
Author(s): Paul M. Dorfman, Lessia S. Shajenko, Koen Vyverman
Abstract:
In SAS® Version 9.1, the hash table — the very first object introduced via the DATA Step Component Interface in Version 9.0 — has finally become robust and syntactically stable. The philosophy and application style of the hash objects is quite different from any other structure ever used in the DATA step before. The most notable departure from the tradition is their run-time nature. Hash objects are instantiated, deleted, allocate memory, and get updated all at the run-time. Intuitively, it is clear that such traits should make for very inventive and flexible programming unseen in the DATA step of yore. Still better, Version 9.2 has added new methods, attributes, and parameters. This paper includes both hash propaedeutics and material intended for programmers already familiar with SAS hashigana at basic to very advanced levels. A number of truly dynamic programming techniques utterly unthinkable before the advent of the canned hash objects in SAS are explored and explained using live SAS code samples.
Download: paper
"Ludicrously Large Numbers — Using Arbitrary Precision Arithmetic in SAS® Applications" Proceedings of the
SAS Global Forum Conference, 2008.
Author(s): Patrick Tan & Koen Vyverman
Abstract:
The SAS language knows but a single numeric data type, with a maximum length of 8 bytes. This imposes a limit on the maximum precision that a number can have in SAS. Sometimes, more precision is needed to correctly store and handle very large or very tiny numbers, in which case you typically need to resort to seriously arcane coding. In this paper we propose to take some of the tedium out of this, by using the Java arbitrary-precision libraries and calling these via the Data Step Component Interface for Java Objects.
Download: paper
"Put Your Customers on the Map: Integrating SAS/GRAPH® and Google Earth" Proceedings of the
SAS Global Forum Conference, 2008.
Author(s): Daniël Kuiper & Koen Vyverman
Abstract:
Ever fancied creating a Google Earth map from SAS data? Or, conversely, loading Google Earth placemarks into a SAS data set? We propose an XML-based interface between SAS and Google Earth, allowing easy exchange of data between the two applications. We show how a folder of Google Earth placemarks can be plotted on a SAS/GRAPH map by means of an XML Map created with SAS® XML Mapper. Going the other way, we transform a SAS/GRAPH customer distribution plot into an interactive Google Earth satellite view.
Download: paper
"The DOW-Loop Unrolled" Proceedings of the
SAS Global Forum Conference, 2009.
Author(s): Paul M. Dorfman & Koen Vyverman
Abstract:
The DOW-loop is a nested repetitive DATA step programming structure, intentionally organized in order to allow for programmatically and logically natural isolation of DO-loop instructions related to a certain break-event from actions performed before and after the loop, and without resorting to superfluous conditional statements. Readily recognizable in its basic and most well-known form by the DO UNTIL (LAST.ID) construct, which naturally lends itself to control-break BY-processing of grouped data, the DOW-loop, however, is much more morphologically diverse and general in nature. In this talk, we aim to examine the internal logic of the DOW-loop and use the power of example to reveal its aesthetic beauty and pragmatic utility. To some industries, for example, pharma, where “flagging” every observation in a group based on conditions within the group is ubiquitous, the DOW-loop lends itself as an ideal logical vehicle by greatly simplifying the alignment of stream-of-consciousness and SAS® code.
Download: paper
"The SAS® Hash Object in Action" Proceedings of the
SAS Global Forum Conference, 2009.
Author(s): Paul M. Dorfman & Koen Vyverman
Abstract:
In SAS® Version 9.1, the hash table — the very first object introduced via the DATA Step Component Interface in Version 9.0 — has finally become robust and syntactically stable. The philosophy and application style of the hash objects is quite different from any other structure ever used in the DATA step before. The most notable departure from the tradition is their run-time nature. Hash objects are instantiated, deleted, allocate memory, and get updated all at the run-time. Intuitively, it is clear that such traits should make for very inventive and flexible programming unseen in the DATA step of yore. Still better, Version 9.2 has added new methods, attributes, and parameters. This paper includes both hash propaedeutics and material intended for programmers already familiar with SAS hashigana at basic to very advanced levels. A number of truly dynamic programming techniques utterly unthinkable before the advent of the canned hash objects in SAS are explored and explained using live SAS code samples.
Download: paper
|
|