SAS University Edition

11月 262014
Having worked in analytics for over 25 years, I’ve never really felt part of the ‘cool gang’. However that’s changing and all of a sudden, at long last, it is "chic to be geek!" Research published by SAS UK and the Tech Partnership reveals that from 2013 to 2020, the […]
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;
var msrp invoice;
/* and a graph */
ods graphics / height=400 width=800 noborder;
proc sgplot;
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
6月 272014
This blog post teaches you how to import an Excel spreadsheet into the free SAS University Edition, so you can further analyze and graph the data. First, you need to create a folder on your local computer (such as C:\\SASUniversityEdition\\myfolders\\ ), and then then set that up as a Shared […]
6月 262014
If you're wanting to become a 'data scientist' then you should probably learn SAS/STAT ... and this blog shows you the basics of how to run a statistical analysis in the free SAS University Edition. In my previous blog posts, you learned how to install SAS University Edition, and how to […]