Updating the ExcelXP Tagset via the URL Access Method

Every now and then I need to update the version of the ExcelXP tagset for a SAS® 9.1.3 platform installation.

SAS programmers use the SAS Output Delivery System (ODS) and ExcelXP tagset to generate good looking multi-sheet Microsoft Excel files (in XML format). This tagset has gone through a number of versions so the version you have installed could be an old one. The installation I saw today had v1.28 whereas the latest available version is v1.86 (as of today).

The options available for updating the version of the ExcelXP tagset are documented in SAS Usage Note 32394: Installing and Storing Updated Tagsets for ODS MARKUP at http://support.sas.com/kb/32/394.html.

I usually modify the main SASHELP.TMPLMST so the updated version is available for the entire SAS platform installation. This involves manually downloading the latest ExcelXP tagset PROC TEMPLATE code from http://support.sas.com/rnd/base/ods/odsmarkup/excltags.tpl and then running the downloaded code using a small %include wrapper as shown in the usage note.

Today it occurred to me that I could combine the steps, modifying the %include wrapper to automatically download and execute the latest tagset code from the SAS web site using the URL access method.

Here is the code I used to automate the tagset update process:
filename xlxpurl url "http://support.sas.com/rnd/base/ods/odsmarkup/excltags.tpl";
ods path sashelp.tmplmst(update);
%include xlxpurl;
ods path reset;
filename xlxpurl clear;

Further information about the URL access method (for SAS 9.1.3) can be found in the SAS OnlineDoc page for the FILENAME Statement, URL Access Method at http://support.sas.com/onlinedoc/913/getDoc/en/lrdict.hlp/a000223242.htm.

Whilst this code removes the manual step of downloading the code and providing the download location in the %include wrapper, there are a few things you might want to consider before using it:

  1. Automatically downloading and running code from an internet site using the URL access method with %INCLUDE is potentially very dangerous. Whilst the URL in the code above currently points to a valid SAS web site location for the latest ExcelXP tagset code this may not always be the case. Consider what you might be %include-ing if the ExcelXP tagset code is ever moved (a 404 error page?) or the code at that location changes (such as in an unlikely worst case scenario where your providers DNS is compromised and support.sas.com is directed elsewhere!). Of course these concerns also exist with manually downloaded code but with manually downloaded code you do have the opportunity to review the code before it is executed – assuming you take that opportunity :)
  2. The server or workstation you run the code on needs to have internet access. If you need to use a proxy server you will need to specify additional options on the initial filename statement.
  3. Are you sure the version you are downloading and installing is the version that is required (and has been tested with any existing ODS code)?

After having read the caveats above you might decide that although this might have been an interesting exercise, it may not be appropriate for you to use. I fully understand any such reservations. Although I will probably use this code again myself, I will only do so after first checking the URL provides valid ExcelXP tagset code from the machine it is going to run on.

Finally, if you want to find out which version of the tagset you have installed you can use the following code:
filename xlxptmp temp;
ods tagsets.excelxp file=xlxptmp;
ods tagsets.excelxp close;
filename xlxptmp clear;

After I had run the automated update the version check code generated the following log fragment:
NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.86, 04/15/08). Add options(doc='help') to the ods
statement for more information.

SAS Forum Australia & New Zealand 2010

I’m excited about the SAS Forum Australia & New Zealand 2010 event in Sydney on 12 August 2010.

I will be presenting Best Practices with SAS® 9 Metadata Security in the Technical Stream, and my company, Metacoda, is a Collateral Sponsor at the event too.

It looks like there will be several presentations of interest to SAS platform administrators.

What is platformadmin.com?

This is my brand new blog and I thought I’d better start by explaining who I am, why I created it, and what I am going to be using it for.

My name is Paul Homes and I have been working with SAS® software for over 20 years. In that time I have worked for a variety of companies in a variety of roles. These days I spend my time doing short term SAS consulting projects, teaching the occasional course and creating plug-ins that integrate with SAS software.

I should have been blogging ages ago, but always wondered what I would blog about. Most of the work I do these days is related to SAS platform administration and working with SAS metadata, so I thought that would be a good place to start.

I created platformadmin.com as a place where I could jot down my thoughts, notes, handy techniques, code fragments and other general stuff related to managing SAS software installations and SAS metadata. There is a wealth of useful 3rd party materials online too, including SAS documents, SAS usage notes and other people’s blogs. I often find myself revisiting the same places time and time again but, having forgotten to bookmark them in the first place, I end up searching for them anew each time, so now I will link to them from this blog.

Although I created this blog primarily for my own purposes, to jog my memory and help me find stuff again, I hope that other people working with SAS metadata or as SAS platform administrators will also find it useful. If you have any comments, questions or suggestions, please let me know by either commenting on the articles or sending me an email or tweet.

The types of topics I plan to cover in this blog include:

  • SAS platform administration
  • SAS metadata security
  • Working with SAS metadata and the SAS Open Metadata Interface
  • Operating system topics (Linux, UNIX, Windows) that might be useful for SAS platform administrators
  • Using Metacoda software for SAS platform administration

I work for Metacoda where I develop plug-ins to integrate with SAS software for the purpose of enhancing metadata access. I also use Metacoda software in SAS platform admin consulting work, so I plan to blog about how I find Metacoda software helps me in that role.