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.
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:
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. If you have problems compiling the downloaded file on a mainframe computer, try saving the file with Windows end-of-line format (CRLF) before you run it.
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:
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:
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.
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.
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);
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 "c:\myfiles\excltags.tpl";
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);
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";
|Product Family||Product||System||SAS Release|
|SAS System||Base SAS||z/OS||9.1 TS1M3 SP4|
|Microsoft® Windows® for 64-Bit Itanium-based Systems||9.1 TS1M3 SP4|
|Microsoft Windows Server 2003 Datacenter 64-bit Edition||9.1 TS1M3 SP4|
|Microsoft Windows Server 2003 Enterprise 64-bit Edition||9.1 TS1M3 SP4|
|Microsoft Windows XP 64-bit Edition||9.1 TS1M3 SP4|
|Microsoft® Windows® for x64||9.1 TS1M3 SP4|
|Microsoft Windows 2000 Advanced Server||9.1 TS1M3 SP4|
|Microsoft Windows 2000 Datacenter Server||9.1 TS1M3 SP4|
|Microsoft Windows 2000 Server||9.1 TS1M3 SP4|
|Microsoft Windows 2000 Professional||9.1 TS1M3 SP4|
|Microsoft Windows NT Workstation||9.1 TS1M3 SP4|
|Microsoft Windows Server 2003 Datacenter Edition||9.1 TS1M3 SP4|
|Microsoft Windows Server 2003 Enterprise Edition||9.1 TS1M3 SP4|
|Microsoft Windows Server 2003 Standard Edition||9.1 TS1M3 SP4|
|Microsoft Windows XP Professional||9.1 TS1M3 SP4|
|Windows Vista||9.1 TS1M3 SP4|
|64-bit Enabled AIX||9.1 TS1M3 SP4|
|64-bit Enabled HP-UX||9.1 TS1M3 SP4|
|64-bit Enabled Solaris||9.1 TS1M3 SP4|
|HP-UX IPF||9.1 TS1M3 SP4|
|Linux||9.1 TS1M3 SP4|
|Linux on Itanium||9.1 TS1M3 SP4|
|OpenVMS Alpha||9.1 TS1M3 SP4|
|Tru64 UNIX||9.1 TS1M3 SP4|
|Topic:||SAS Reference ==> ODS (Output Delivery System)|
Data Management ==> Data Sources ==> External Databases ==> MS Excel
|Date Modified:||2008-08-14 11:32:30|
|Date Created:||2008-06-09 16:27:34|