ExcelXP Tagset Macro

From sasCommunity
Jump to: navigation, search

by Don Gallogly


Below is macro code created by Don Gallogly, Oregon Department of Consumer and Business Services. It can be used to invoke the ExcelXP tagset (OpenSSXP), create a new tab in a spreadsheet (NewTabXP) or close the ExcelXP tagset (CloseSSXP).

/***************************************************************************************/
/*	This macro uses ods to create an Excel2007 spreadsheet on the SAS server and opening
/*	it for writing.  Following execution of this macro, anything written to the output
/*	window will be echoed in the file referenced as tab1.  Use macro CloseSS to finish.
/*
/*	Donald Gallogly - DCBS IMD
/***************************************************************************************/
 
%Macro OpenSSXP (Style=default, AutoFilter=None, Sheet_Name=, Sheet_Label=, 
				 Sheet_Interval=, Orientation=Portrait, FitToPage=No, 
				 Embedded_Titles=Yes, Embedded_Footnotes=No,
				 Print_Footer=%NRStr(&L&Z&F&R&D),
                                 Frozen_Headers=none, 
				 Row_Repeat=none, Absolute_column_width="", Doc=);
 
	filename tab1 "/usr/tmp/temp&SYSUSERID..xls";
 
	ods tagsets.ExcelXP Style=&Style
						options(AutoFilter = "&AutoFilter"
						Sheet_Name = "&Sheet_Name"
						Sheet_Label = "&Sheet_Label"
						Sheet_Interval = "&Sheet_Interval"
						Orientation = "&Orientation"
						FitToPage="&FitToPage"
						Embedded_Titles="&Embedded_Titles"
						Embedded_Footnotes="&Embedded_Footnotes"
				 		Print_Footer="&Print_Footer"
						Frozen_Headers="&Frozen_Headers"
				 		Row_Repeat="&Row_Repeat"
						Absolute_column_width=&Absolute_column_width
						Doc="&Doc") 
		file=tab1;
 
	%let Doc = str(' ');
 
%Mend OpenSSXP;




/***************************************************************************************/
/* This macro, when placed between two procedures that produce output, will cause the  
/* second one to appear on a new tab. Only works with OpenSSXP for Excel2007 workbooks.
/* 
/* Donald Gallogly - DCBS IMD
/***************************************************************************************/
%Macro NewTabXP;
 
	ods tagsets.ExcelXP options (Sheet_Interval='None');
 
%Mend NewTabXP;




%Macro CloseSSXP(location);
/***************************************************************************************/
/*	This macro uses ods to close an Excel2007 spreadsheet on the SAS server, download to
/* 	another location, and delete the temporary file.  The parameter 'location' is any
/*	valid path and filename.  Use macro OpenSS to create and open the file.
/*
/* 	Donald Gallogly - DCBS IMD
/***************************************************************************************/
	ods tagsets.ExcelXP close;
 
	proc download infile ="/usr/tmp/temp&SYSUSERID..xls"
	     outfile=&location;
	run;
 
	x "rm /usr/tmp/temp&SYSUSERID..xls";
	run;
 
%Mend CloseSSXP;

Presented at a State of Oregon SAS Users Group (SOSUG) meeting.

Find more SOSUG presentations, as well as other tips, tricks, and tools created by or recommended by SOSUG members here.