The SAS® METALIB Procedure, available in SAS 9.3, 9.2 and 9.1, is used to keep logical table metadata in sync with the physical tables that the metadata describes. It can also be used to report on any differences or discrepancies between the physical table and logical metadata descriptions. This post shows how ODS OUTPUT can be used in combination with PROC METALIB to capture its report output as SAS tables for further analysis and processing. Continue reading “SAS PROC METALIB and ODS OUTPUT”
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);
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:
- 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 :)
- 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.
- 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.