| This post was kindly contributed by From a Logical Point of View » SAS - go there to comment and to read the full post. |
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 
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:
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):
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:
9. Get Help
As a new comer, I found the following materials useful and accessible:
| This post was kindly contributed by From a Logical Point of View » SAS - go there to comment and to read the full post. |