As the first step in the decommissioning of the site has been converted to read-only mode.

Here are some tips for How to share your SAS knowledge with your professional network.

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,
				 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"
	%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"
	x "rm /usr/tmp/temp&SYSUSERID..xls";
%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.