Creating Output Files with ODS for Use by Excel - Exchanging Data Between SAS and Microsoft Excel - page 109

Of course, this sample of pseudo code will not execute anywhere and to be useful the “ODS” commands
must be provided in the correct SAS syntax for the task you wish to accomplish. The important thing to
remember here is that prior to SAS 8.2 and SAS 7 (for the few people who used it) every non-HTML file
used to transport data had to be coded in a DATA step. It is important to realize that the ability to process
tagsets is a huge step forward for the ability to generate formatted SAS output. SAS software has long had
users wanting output that was easy to transport to other systems. Tagsets crossed that gap. This chapter will
discuss the following ODS tagsets and destinations because they have a direct relationship with making the
transfer of data from SAS to Excel easier for you as a programmer. The tagsets and destinations listed
reduce your coding effort from pages of SAS code to lines of code defining the output file, locations, and
structures.

Table 8.1: ODS Options That Create Files That Can Be Opened by Microsoft Excel.

ODS Option Destination or Tagset Output

CSV Both Create a text file with values

separated by a delimiter

CSVALL Both Create a text file with values

separated by a delimiter

MSOFFICE2K Both Create a text file with HTML

formatted data values

HTML Both Create a text file with HTML

formatted data values

EXCELXP Tagset Create a text file with XML

formatted data values

EXCEL Destination Create a Microsoft Excel file in

*.xlsx native format

The SAS ODS destinations and tagsets listed in Table 8.1 allow SAS to write delimited or formatted files
without interrupting the flow of the job, thereby enabling programmers to do more work in less time.
Simple commands turn the feature on and off, and most outputs in between are written to the output file.
The capabilities of ODS have been steadily increasing ever since the release of SAS 7. The power of
tagsets has increased from writing text-based (*.txt, *.csv, *.tab) files to writing Hyper Text Mark-up
Language (HTML) files and then to outputting Extensible Mark-up Language (XML)-formatted files. The
new experimental ODS destination EXCEL will actually write native Excel 2007 and later formatted files.
As with all of the experimental SAS products, the final product options may change.

8.3 An Introduction to SAS Tagset Templates That Create Files for Excel
As I mentioned in earlier, tagsets are text files stored for use by the Output Delivery System. Many of the
tagsets are delivered with, and are part of, Base SAS. PROC TEMPLATE prepares SAS code to be used as
a tagset. The syntax for coding a tagset is slightly different from that of regular SAS code. There are also
specific methods in place to store an access tagset templates. Tagsets delivered with your SAS product are
usually stored in a location that is Write protected (see Figure 8.1), so the contents cannot be easily
overwritten. Other storage levels are easily defined and are used to store changes to original tagsets. The
tagset names and the concatenated list of storage directories allow changes to be applied without
overwriting the original files. Tagsets can be updated by SAS and are stored on the SAS web site. You can
also make changes to a tagset and install the changes on your system.