8月 302014

The ODS ExcelXP tagset has served us well over the years. It provides a reliable method to get formatted SAS output into Microsoft Excel workbooks, where the business world seems to like to live. And it's available in Base SAS, which means that you don't need SAS/ACCESS to PC Files and any extra setup to begin using it.

In SAS 9.4 Maintenance 1, ODS EXCEL was introduced as an experimental feature. Even though it still has the "experimental" label in the recently released SAS 9.4M2, I've found it to be a useful addition to the many ways I can create Excel content from SAS. ODS EXCEL offers a couple of big advantages over ExcelXP:

ODS EXCEL produces a native XLSX file. Users of the ExcelXP tagset know that Excel complains about the file format as you open it. And the XML Spreadsheet format that is uses is uncompressed, resulting in potentially very large files.

ODS EXCEL supports SAS graphics in the output. ExcelXP users have come up with creative methods to insert graphs "after the fact", but it's not as convenient as a "once and done" SAS program. With ODS EXCEL, graphics from SAS procedures are automatically included in output.

Syntax-wise, ODS EXCEL is similar to ODS TAGSETS.ExcelXP. Chevell Parker shares many of those details in his SAS Global Forum 2014 paper; in absence of official doc for this experimental feature, Chevell's paper is essential.

Let's dive into an example. The following program looks similar to how you might use the ExcelXP tagset, but can you see the key differences?

ods excel file="c:projectsoutputexample.xlsx" 
 /* will apply an appearance style */
  /* for multiple procs/sheet */
  /* name the sheet tab */
  sheet_name="CARS summary"
/* add some formatted text */
ods escapechar='~';
ods text="~S={font_size=14pt font_weight=bold}~Cars Summary and Histogram";
/* tabular output */
proc means data=sashelp.cars;
var msrp invoice;
/* and a graph */
ods graphics / height=400 width=800 noborder;
proc sgplot data=sashelp.cars;
histogram msrp;
ods excel close;

Note how I've targeted an XLSX file directly, instead of going through an XML intermediary file to placate Excel with the file format. And I included a graph with no problem. Like other ODS output destinations, I can add apply styles and special formatting as needed. Here's a screen shot of the resulting spreadsheet:


I have a few additional observations from playing with this feature (did I say it was experimental?):

  • ODS EXCEL doesn't perform well with large detailed output. That is, if I use ODS EXCEL and PROC PRINT a data set with lots of columns and many thousands of rows, it's going to be slow -- and might hit a wall with memory usage. For that use case, PROC EXPORT DBMS=XLSX works better (though that requires SAS/ACCESS to PC FILES).
  • ODS EXCEL overwrites any existing Excel file that you target. That means that you aren't going to use this method to poke new values into an existing spreadsheet, or add sheets to an existing workbook. Compare that to PROC EXPORT DBMS=XLSX, which allows you to update an existing workbook by targeting just one sheet.
  • My example code works perfectly on the SAS University Edition! Simply change the destination file to be a location that SAS can write to -- for example, file="/folders/myfolders/example.xlsx".

If you want to learn more about the new ODS destinations in SAS 9.4 (which include HTML5, EPUB, POWERPOINT, and EXCEL), watch my interview and demo with David Kelley, R&D manager for ODS at SAS:

tags: excel, excel 2007, ods, ODS EXCEL, SAS 9.4, SAS University Edition
4月 182014
In writing the second edition of SAS Programming in the Pharmaceutical Industry, I knew that I wanted to replace the device-driven SAS/GRAPH figures with the new ODS template-driven graphics procedures. The latest developments in SAS graphics involve the template-driven procedures and tools found in SAS ODS graphics (i.e., ODS Graphics […]
4月 162014

When you run a program or task in SAS Enterprise Guide, the application wraps your job in an "ODS sandwich", the colloquial term we use for the ODS statements necessary to create output that can be viewed in your project.

That's convenient for exploring and refining your program, but at some point you might want to use your own ODS statements and options to control the format and appearance of the output. You might find that the default ODS sandwich that SAS Enterprise Guide generates can get in your way.

For example, suppose that you want to create a PDF output with landscape orientation and Legal paper size. The default SAS Enterprise Guide options might be in conflict with some of the options you need. The best approach? Squelch the "automatic" options, and code up your own exactly how you need them. Here's how.

First, change the properties of your SAS program.. Right-click on the program node in your flow and select Properties, or click the Properties icon at the top of the editor window:


In the Properties window, select the Results tab. Then select the Customize results formats [...] option, and uncheck all of the built-in formats.

Next, add the ODS statements and options necessary to produce the results that you want. For example, if you want a PDF file that has a particular format, use something like this:

options orientation=landscape papersize=legal;
ods noproctitle;
/* using ID= to control each ODS dest */
ods pdf (id=custom) 
  /* this file path is on the SAS workspace */
  columns=2 style=journal;
  title "Types by Cylinders and MSRP stats";
proc freq data=sashelp.cars;
  table Type * Cylinders;
proc means data=sashelp.cars;
  class Origin;
  var msrp;
/* print-friendly image options */
ods pdf (id=custom) dpi=300 startpage=no;
ods graphics / 
  noborder scale=on
  height=2in width=2.5in;
title "Distribution";
proc sgplot data=sashelp.cars;
	histogram msrp;
	density msrp;
ods pdf (id=custom) close;

Sample result:
Link to PDF
Some notes about this program:

  • The FILE= option on the ODS PDF statement refers to the file that you want to create in the file system of your SAS workspace, not your local machine. SAS Enterprise Guide will offer to download this file for you to view, but if you want complete control over where it lands on your local PC, use the Copy Files task to download it.
  • If you run this program in SAS Enterprise Guide without turning off the other Results formats, the final PDF output won't have all of the attributes you expect. For example, the graph might look slightly different. So if this PDF is your ultimate objective, be sure to suppress the automatic results from SAS Enterprise Guide.
  • Use this same technique to create custom HTML output, or ODS POWERPOINT (new in 9.4), ODS EPUB (new in 9.4) or even ODS HTML5 (hey! new in 9.4).

For a minimalist, fast-running SAS program, you can use this technique to suppress the fancy (bulkier) ODS results and rely on the old-school LISTING format. In the Results properties for your program, uncheck all output destinations except for Text. If your program generates only text-based output that you want to view quickly, this will turn SAS Enterprise Guide into a speedy-quick results-generating machine...just like you might be accustomed to from your old PC SAS days.

See also

Destination Known: Programmatically Controlling Your Output in SAS Enterprise Guide, by Aaron Hill
[VIDEO] New Destinations in ODS: PowerPoint, HTML5, EPUB, EXCEL
Tip Sheet for ODS PDF

tags: ods, SAS 9.4, SAS Enterprise Guide, SAS programming
1月 072014

Because I began my SAS career in the Publications division, I like to think that I have a keen eye when it comes to SAS documentation. When I first visited the SAS 9.4 online documentation, I immediately noticed that it had a different look. Examine the image below; can you see what I mean? (Click on the image to see a larger version.)

Aside from a few layout enhancements and more helpful details, I noticed the use of a different typeface for the text. It's a font that I didn't recognize, and I wondered how it had been installed on my system.

It turns out that this font face (named "Lato") is not installed on my system; it's a web font. A web font is downloaded and rendered "on the fly" by your web browser. This gives the web designer more control over the exact appearance of the text, even across different operating systems and devices that share only a small subset of generic font styles.

There are different sources for web fonts. Some are free to use, while others require a nominal licensing fee. If you have a particular typeface that is important to your company brand, it might be worth a licensing fee to ensure that this typeface is used consistently in all of your web content. However, the SAS documentation (and many other sites) use the free Google Fonts.

After I learned all of this I wondered: how can I use web fonts in SAS ODS output?

Specifying a web font in ODS HTML

There are three ways to "import" a web font in your HTML content:

  1. using a <link> tag to reference a directive from an external style
  2. using an @import directive from within a CSS file or <style> tag
  3. using JavaScript to dynamically insert a web font style reference into the page.

The Google Font web site provides code snippets for each of these in HTML.

Once you import the font, you must then reference the font name in CSS "font-family" style attributes for the different element classes that you want to affect. For example, if you want ODS tabular data to use the Lato font, you must change the "data" class to include it:

.data {
  font-family: 'Lato', sans-serif;

I decided that the <link>-tag approach was the simplest method to import the web font. I copied the <link>-tag directives from the Google Fonts entry for Lato and Droid Sans, and then "injected" them into the ODS HTML output by using the HEADTEXT option. Then I used PROC TEMPLATE to modify the style attributes for specific ODS-related style classes; these attributes will translate into CSS when SAS creates your HTML. Here's the program:

/* These snippets copied from http://www.google.com/fonts facility    */
/* Macro for HEADTEXT option, since the value cannot exceed 256 chars */
%macro ods_html_webfont;
<link href='http://fonts.googleapis.com/css?family=Lato:400,700' 
  rel='stylesheet' type='text/css'>
<link href='http://fonts.googleapis.com/css?family=Droid+Sans:400,700' 
  rel='stylesheet' type='text/css'>
proc template;
  define style webfont;
    /* for this example, inherit existing style elements from HTMLBLUE */
  style header from header /
    fontfamily="'Droid Sans', sans-serif";
  style data, body from _self_ /
    fontfamily="'Lato', sans-serif";
  class titlesandfooters /
    fontfamily="'Lato', sans-serif";
ods html (id=wf)
   file="c:\temp\wf_test.html"(title="Web Fonts Test") 
title "A new look for my report";
proc means data=sashelp.cars;
ods html (id=wf) close;

Here's the result as seen in my Chrome browser:

(Want to compare this to a version that doesn't use web fonts?)

Limitations of web fonts

Before you consider using web fonts in all of your SAS-generated content, there are a few restrictions that you should review:

  • Web fonts can be used only in HTML output -- output that you intend to display in a web browser. The browser will download and render the font based on CSS or JavaScript directives. This means that this technique won't work for RTF or PDF output.
  • Web fonts can apply to textual content only, and not to images that are generated by SAS graphical procedures. SAS graphical output is usually rendered into an image file (such as a PNG file) within your SAS session. The appearance is controlled by SAS styles that are defined in your SAS session, and any referenced fonts must be accessible to SAS.
  • Because web fonts must be downloaded by the browser as the HTML page loads, this can have an impact on how quickly the page is rendered. Each Google web font provides some guidance about this potential impact. For the best response, include references to the minimum number of typefaces that you need for the content.
  • And of course, for a web font to download you must be connected to the Internet. It's a good idea to always specify a fallback font family (ex: sans-serif) in your styles so that even if your web font can't load, your style still provides some cue for how to render the text.
tags: CSS, ods, PROC TEMPLATE, SAS 9.4, SAS tips, web fonts
1月 042014
Ron Cody starts things off right with our first SAS tip of 2014. If you resolve to become a better statistical programmer this year, Ron's book SAS Statistics by Example is a great place to start. After you take a look at this week's free excerpt from the book, head over to Ron's author page. You'll [...]
11月 052013
1. If you have SAS Enterprise Guide installed

Try one of Chris Hemedinger’s EG plug-ins, “Data Set to DATA Step”. Chris also wrote a post for it, Turn your data set into a DATA step program.

2. If you need to embed the dataset to SQL Clauses

Use one of Eric Gebhart’s ODS tagsets, “SQL Tagset”.

3. If you want to make your hands dirty…

You can also take a bite of some SAS base codes by Rick Langston. Check out his 2011 paper, Recreating SAS Data Sets via SAS Code Generation.

10月 122013
This week's SAS tip is from Michael Tuchman and his innovative book PROC DOCUMENT by Example Using SAS. Michael's book fills an important gap. For instance, in her review of the book, SAS user and senior statistical analyst Wendi Wright said: "Currently, I believe PROC DOCUMENT is a highly underused procedure and after reading [...]