Excel

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 */
 style=pearl
 options(
  /* for multiple procs/sheet */
  sheet_interval="none" 
  /* 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;
run;
 
/* and a graph */
ods graphics / height=400 width=800 noborder;
proc sgplot data=sashelp.cars;
histogram msrp;
run;
 
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:

odsexcel

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 […]
2月 122014
 
Last week, I showed how the Excel Add-In for JMP can bring more value to Excel spreadsheets for what-if analysis and optimization. Today, we’ll look at how using that same data from within JMP alone is more elegant. First, let’s look at the Excel spreadsheet from last week's post (see [...]
2月 052014
 
Over the past few weeks, we’ve looked at the rise of Excel’s usage and popularity, and I've noted that Excel has made forays into analytics. Today, we’ll look at one type of analysis: scenario-based planning and optimization. Using Excel add-ins, you can create what-if analysis and scenario-based planning using Monte [...]
1月 232014
 
Last week, I shared some of the many stories about errors in Excel spreadsheets that led to misinformation and to a  path toward incorrect decisions. Today, we’ll explore why such errors can be potentially pervasive. The power of the spreadsheet has always been its interactive ease of use. It allows [...]
1月 152014
 
In 2010, esteemed Harvard professors Carmen Reinhart and Kenneth Rogoff published the paper “Growth in a Time of Debt.” It soon became powerful supporting evidence for those who argued against growing government spending, even in times of needed economic stimulation. The paper looked at the debt levels of many different [...]
1月 062013
 

Hate it or not (Yes I do), SAS programmers can’t just get rid of Microsoft Office Excel in their life.

Now my turn (with Tagsets.ExcelXP, I can at least get rid of DDE)…

0. SAS Templates window ODSTEMPLATES

A visual way to browse all ODS templates is to use windows command “ODSTEMPLATES” to invoke a SAS template window.

1.  Check the current ExcelXP tagset version

filename temp temp;
ods tagsets.ExcelXP file=temp options(doc=’help’);
ods tagsets.ExcelXP close;

Scroll down the Log window to the end and you get something like:

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.122, 01/04/2011).

2. Get the source codes of current ExcelXP tagset and library it’s stored

proc template;
    source Tagsets.ExcelXP;
run;

The Log window shows the template codes:

15   proc template;
16       source Tagsets.ExcelXP;
define tagset Tagsets.ExcelXP;
   parent = Tagsets.ExcelBase;
end;
NOTE: Path ‘Tagsets.ExcelXP’ is in: SASHELP.TMPLMST.

Note Tagsets.ExcelXP is subject to Tagsets.ExcelBase.

The template store path uses a two level name in which SASHELP is a SAS library and TMPLMST, a name assigned to distinguish a set of templates. You can also store your customized templates in SASHELP.mytpl1, WORK.forTLF and such.

If want a the source codes in a separated file rather than in Log window, try

proc template;

source Tagsets.ExcelXP/file="a:\test\ExcelXP.tpl";
run;

The extension doesn’t matter and you can use anything you like (including .SAS).

3. List all ODS template store path

ods path show;

Log:

Current ODS PATH list is:

1. SASUSER.TEMPLAT(UPDATE)
2. SASHELP.TMPLMST(READ)

This order also indicates the ODS template searching priority, first SASUSER.TEMPLAT then SASHELP.TMPLMST. If two templates with same name exist in both library, the one in SASUSER.TEMPLAT will be used.

Templates in SASHELP.TMPLMST can’t be modified(read only).

4. Install latest version in SASUSER library

The last version of ExcelXP tagset is v1.127 by far according to SAS ODS MARKUP portal:

filename excltags url ‘http://support.sas.com/rnd/base/ods/odsmarkup/excltags.tpl’;
%include excltags;

log:

NOTE: TAGSET ‘Tagsets.ExcelBase’ has been saved to: SASUSER.TEMPLAT
NOTE: Overwriting existing template/link: Tagsets.Config_debug
NOTE: TAGSET ‘Tagsets.Config_debug’ has been saved to: SASUSER.TEMPLAT
NOTE: TAGSET ‘Tagsets.ExcelXP’ has been saved to: SASUSER.TEMPLAT

Since there is already an ExcelXP tagset in SASHELP.TMPLMST and it can’t be overwritten, this new added ExcelXP tagset is thrown to SASUSER.TEMPLAT by adding a STORE option (check the source code) and will be used when statement “ODS tagsets.ExcelXP” invoked.

5. List all templates

proc template;
    list / store=sasuser.templat;
    list / store=sashelp.tmplmst;
run;

Get output:

sasuser_templat2

If only type “Tagset” need (to save space!):

proc template;
list tagsets/store=sasuser.templat;
run;

 

6. Delete template

proc template;
    delete Tagsets.ExcelBase;
    delete Tagsets.ExcelXP;
run;

It’s always safe to perform delete statements since items in SASHELP.TMPLMST are read only.  In this case, the ExcelXP tagset in SASUSER.TEMPLAT will be deleted.

log:

8617   proc template;
8618       delete Tagsets.ExcelBase;
NOTE: ‘Tagsets.ExcelBase’ has been deleted from: SASUSER.TEMPLAT
8619       delete Tagsets.ExcelXP;
NOTE: ‘Tagsets.ExcelXP’ has been deleted from: SASUSER.TEMPLAT

check again following step #5 and get what’s in SASUSER.TEMPLAT (no ExcelXP tagset anymore):

sasuser_templat

7 Install in other specified library

Install ExcelXp tagset in a other library. It will be deleted if current SAS session is killed:

libname excltags ‘a:\test’;
ods path (prepend) excltags.templat(update);
filename excltags url ‘
http://support.sas.com/rnd/base/ods/odsmarkup/excltags.tpl’;
%include excltags;

log:

NOTE: Overwriting existing template/link: Tagsets.ExcelBase
NOTE: TAGSET ‘Tagsets.ExcelBase’ has been saved to: EXCLTAGS.TEMPLAT
NOTE: Overwriting existing template/link: Tagsets.Config_debug
NOTE: TAGSET ‘Tagsets.Config_debug’ has been saved to: EXCLTAGS.TEMPLAT
NOTE: Overwriting existing template/link: Tagsets.ExcelXP
NOTE: TAGSET ‘Tagsets.ExcelXP’ has been saved to: EXCLTAGS.TEMPLAT

show path as step #3:

Current ODS PATH list is:

1. EXCLTAGS.TEMPLAT(UPDATE)
2. SASUSER.TEMPLAT(UPDATE)
3. SASHELP.TMPLMST(READ)

If apply step #6 to delete ExcelXP tagset, the items in EXCLTAGS.TEMPLAT will be deleted(as same as the searching priority).

8. Hello World

Adopted from one of Vince DelGobbo’s papers(2012):

title ‘The CLASS Data Set’;
footnote ‘(From the SASHELP Library)’;

*Set some "global" tagset options that affect all worksheets;

ods tagsets.ExcelXP path="a:\test\" file=’MyWorkbook.xml’ style=Printer
                    options(embedded_titles=’yes’
                            embedded_footnotes=’yes’
                            print_header=’&C&A&RPage &P of &N’
                            print_footer=’&RPrinted &D at &T’
                            autofilter=’2′);

%macro doit;

proc sort data=sashelp.class (keep=sex) out=class nodupkey;
    by sex;
run;

data _null_;
    set class end=eof;
    II=left(put(_n_,2.));
    call symputx(‘sex’||II,compress(sex));
    if eof then call symputx(‘total’,II);
run;

%do i=1 %to &total;
    ods tagsets.ExcelXP options(sheet_name="Students_&&sex&i");

    proc print data=sashelp.class noobs style(Header)=[just=center];
      where (sex eq "&&sex&i");
      var name age      / style(Column)=[background=#99ccff];
      var height weight / style(Column)=[background=#99ccff tagattr='format:#.0'];
    run;

%end;

%mend doit;

%doit

ods tagsets.ExcelXP close;

This .XML file can be opened by Office Excel:

SAS_ExcelXP

9. Get Help

As a new comer, I found the following materials useful and accessible: