• Print |
• E-mail  |
• Bookmark

Overview

This note shows how to download and install the latest ExcelXP tagset for SAS and Microsoft Excel. However, you can use these techniques and best practices for any tagset that you or others create.

The ExcelXP tagset was shipped with other tagsets beginning with SAS 9.0. This tagset is actively developed, and therefore enhancements and fixes can be obtained by downloading an updated tagset from our Web site at support.sas.com/rnd/base/ods/odsmarkup/. Other tagsets' updates are also found at this Web site.

The Basics: What Is This File, and How Do I Compile It?

When you save a file from the above site, the file contains SAS code that must be compiled. If you open the file, you can see a TEMPLATE procedure and a DEFINE TAGSET statement. In some cases, the file is zipped together with examples.

The file that you download can have a .tpl, .sas, or .txt extension. The extension can be changed if you want, because it's just a text file that contains SAS code:

• If you save the file to your computer with a .sas extension, then on Windows you can right-click on the file and run it in batch.
• If you are running SAS interactively, then copy the SAS code into your program editor and submit it.
• You can save the file to your computer and then use an %INCLUDE statement from any ODS program when you want to submit the PROC TEMPLATE code. In this case, it is recommended to save the file with a .tpl extension so that everyone knows it contains PROC TEMPLATE code.

When you run the PROC TEMPLATE code, using any of the three techniques described above, SAS creates or updates the tagset in an item store.

Where Should I Store the Compiled Tagset?

SAS handles tagsets similarly to templates. The tagsets that are shipped with SAS are located in the SASHELP.TMPLMST item store. When you create or update tagsets, they are saved by default to the SASUSER.TEMPLAT item store.

These two default item stores differ in their level of access:

sasuser.templat(update) sashelp.tmplmst(read)

Notice that only the SASUSER location has update access. If you want to write the tagset to the SASHELP location, you must modify the default path with an ODS PATH statement. If you do not modify the default path, an error occurs and the tagset is not saved.

Two common errors can prevent ODS from writing the templates to the SASUSER location. One error is having the -RSASUSER option specified, which makes SASUSER read only. Another error, when using PC SAS, is having multiple SAS sessions open. The first open session has exclusive access to SASUSER.

The best practice for storing tagsets depends on your needs. Here are some suggestions:

One or a Small Number of Users

If you have a limited number of users, then each individual can run the PROC TEMPLATE code or submit an %INCLUDE statement. The tagset is stored by default in the individual's SASUSER location.

   %include "c:\myfiles\excltags.tpl";

Large Number of Users

If you need to roll out the tagset to a lot of users, you (we will call you the administrator) need to make it accessible to everyone. To make tagsets accessible, modify the ODS PATH statement, which changes the default location where you read and write tagsets. You can either store the PROC TEMPLATE file in a location where everyone has access, or store the compiled tagset in the SASHELP location.

Large Number of Users: ODS PATH with PREPEND

In this example, the location that all users have access to is "d:\templib". The administrator submits a LIBNAME statement with this pathname. The ODS PATH statement with PREPEND references the xxx libref, so "d:\templib" becomes the default location. The %INCLUDE step compiles the updated tagset in the xxx library. The item store is in the xxx library instead of in SASUSER.

   libname  xxx "d:\templib"
   ods path(prepend) xxx.templat(update);
   %include "c:\myfiles\excltags.tpl";

Then the user submits the following code. The LIBNAME statement points to the location of the compiled tagset, and the ODS PATH statement sets the default. If you add these lines to an autoexec file, then your user can omit them and just reference the xxx libref.

   libname  xxx "d:\templib";
   ods path(prepend) xxx.templat(read);

Large Number of Users: Stored Processes

If you want to make the ExcelXP tagsets available to your stored processes, and enable your BI servers to locate them, then under SASMain is a good location to store the tagsets. The administrator creates a new directory within the SASEnvironment folder such as the pathname here:

   libname temp "...\Lev1\SASMain\SASEnvironment\Tagsets";
   ods path(prepend) temp.templat(update);
   %include temp.templat(update);

The user submits the following code. For simplicity, this code can be part of the stored process, or it can be placed in an autoexec file.

   libname temp "...\Lev1\SASMain\SASEnvironment\Tagsets";
   ods path(prepend) temp.templat(read);

Large Number of Users: SASHELP

To update the ExcelXP tagset in the SASHELP location, you first need to make a backup of the SASHELP location. If this is not a local installation on PC SAS, you need admin or root authority to update this SASHELP location. The administrator submits the ODS PATH statement, which changes the access mode from read to update for the SASHELP location. The user does not have to do anything special to access the updates. The administrator must be sure that all users are logged out of SAS when updating the SASHELP library.

     ods path sashelp.tmplmst(update);
     %include "c:\myfiles\excltags.tpl";
     

Operating System and Release Information