Office of Planning and Institutional Research How To Create Presentation Quality Reports with SAS Proc TABULATE and ODS Part II: Using ODS ExcelXP Tagset Karen Egypt and Althea Oenga NEAIR 37th Annual Conference November 15, 2010 Georgetown UNIVERSITY ExcelXP TAGSET An eXtended Markup Language (XML) that enables SAS users to create SAS output that can be read in Microsoft Excel 2002 or later. This presentation uses ExcelXP tagset version 1.86, 04/15/08 which is available at http://support.sas.com/rnd/base/ods/odsmarkup. General Usage Syntax ODS TAGSETS.EXCELXP file-specifications <option(s)>; (Your SAS Procedures) ODS TAGSETS.EXCELXP CLOSE; Creates a list of available options in the SAS Log. Common Usage Syntax ODS TAGSETS.EXCELXP file-specifications STYLE=style-definition Options(DOC=‘help’|<suboption(s)>); (Your SAS Procedures) ODS TAGSETS.EXCELXP CLOSE; OPIR SAS Code Example data factbook; set fall fall09; run; dumvar=0; proc format; picture pct (round) low - high = '0009.99%'; run; ods listing close; ods noresults; options missing=' ' leftmargin = .7in rightmargin = .7in topmargin = .7in bottommargin = .7in; ods tagsets.excelxp file= "R:\OPIR\enroll2010neair2.xml" style=factbkrv options(orientation='portrait' sheet_name='Table 2-3' embedded_titles='yes’ absolute_column_width='11,7,7,6.5,7,7,6.5' scale='93'); PROC TABULATE DATA=factbook ORDER=FORMATTED missing; where level = 'U'; CLASS term ftpt; sex /descending; format sex $sex. ftpt $ftpt. ; Table (term=''), ((sex=''*(ftpt='' all))*rowpctn=''*f=pct.) /box='Undergraduate' TITLE1 'ENROLLMENT by Gender and by Academic Load'; Title2 'Table 2.3'; run; ; ods tagsets.excelxp options(sheet_name='Table 2-4' orientation='landscape' absolute_column_width='19,7.5,7.5,7.5,7.5,7.5,10' scale='98'); PROC TABULATE DATA=factbook ORDER=FORMATTED missing; where level = 'U'; CLASS class term; format class $class. ; var dumvar; *Dummy variable to pass formula to be read by MS Excel; TABLE (class='' all), term=''*N=''*[style={tagattr='format:###,##0'}] dumvar='Percent Change'*Sum='4 Year'*[style={tagattr='formula:(RC[-1]-RC[-3])/RC[-1]'}]*f=pct. /box='Undergraduate' ; TITLE1 'ENROLLMENT by Academic and Class Level'; Title2 'Table 2.4'; run; ods tagsets.ExcelXP close; ods listing; ods results; OPIR SAS Example Results 1 OPIR SAS Example Results 2 OPIR SAS Example Coding Pt 1 Ln Code 1 ods listing close; 2 ods noresults; 3 options missing=' ' leftmargin = .7in rightmargin = .7in topmargin = .7in bottommargin = .7in; 4 ods tagsets.excelxp file= "R:\OPIR\enroll2010 neair2.xml" style=factbkrv options(orientation='portrait' sheet_name='Table 2‐3' embedded_titles='yes‘ absolute_column_width='11,7,7,6.5,7,7,6.5' scale='93'); General Explanation – How to Create SAS Example Results 1 Line 1 closes the listing destination so that no listing output is created. Line 2 prevents output objects from being sent to the Results window. The SAS OPTIONS statement (Line 3) specifies that missing numeric values should appear as blanks and defines the margins of the output. The ODS TAGSETS.EXCELXP statement (Line 4) creates an XML file using the requested pre‐defined style and options. The above options indicated in Line 4 set the page orientation to portrait, name the worksheet “Table 2‐3,” place the SAS procedure titles inside the worksheets, set the column width for the first 7 columns, and change the page scaling to 93%. OPIR SAS Example Coding Pt 2 Ln Code 5 PROC TABULATE DATA=factbook ORDER=FORMATTED missing; 6 where level = 'U'; 7 CLASS term ftpt; sex /descending; format sex $sex. ftpt $ftpt. ; 8 Table (term=''), ((sex=''*(ftpt='' all))*rowpctn=''*f=pct.) /box='Undergraduate' ; 9 TITLE1 'ENROLLMENT by Gender and by Academic Load'; Title2 'Table 2.3'; 10 run; proc format; picture pct (round) low - high ='0009.99%'; Run; General Explanation Lines 5‐10 involve a standard tabulate procedure that includes invoking a picture format (named “pct”) within the Table statement of Line 8. This picture format provides a template for displaying percentages that is readable by MS Excel. See SAS Example Results 1. OPIR SAS Example Coding Pt 3 Ln Code 11 ods tagsets.excelxp options(sheet_name='Table 2‐4' orientation='landscape' absolute_column_width='19,7.5,7.5,7.5,7.5,7.5,10' scale='98'); 12 PROC TABULATE DATA=factbook ORDER=FORMATTED missing; 13 where level = 'U'; CLASS class term; format class $class. ; 14 var dumvar; *Dummy variable to pass formula to be read by MS Excel; 15 TABLE (class='' all), term=''*N=''*[style={tagattr='format:###,##0'}] dumvar='Percent Change'*Sum='4 Year'*[style={tagattr='formula: (RC[‐1]‐RC[‐3])/RC[‐1]'}]*f=pct. /box='Undergraduate' ; 16 TITLE1 'ENROLLMENT by Academic and Class Level'; Title2 'Table 2.4'; run; 17 ods tagsets.ExcelXP close; ods listing; ods results; General Explanation P1 of 2 – How to Create SAS Example Results 2 Line 11 modifies options that were in effect during the previous ODS TAGSETS.EXCELXP statement . Line 17 closes the XML file and reinitiates the listing destination and the Results window. OPIR Ln Code SAS Example Coding Pt 4 12 PROC TABULATE DATA=factbook ORDER=FORMATTED missing; 13 where level = 'U'; CLASS class term; format class $class. ; 14 var dumvar; *Dummy variable to pass formula to be read by MS Excel; 15 TABLE (class='' all), term=''*N=''*[style={tagattr='format:###,##0'}] dumvar='Percent Change'*Sum='4 Year'*[style={tagattr='formula: (RC[‐1]‐RC[‐3])/RC[‐1]'}]*f=pct. /box='Undergraduate' ; 16 TITLE1 'ENROLLMENT by Academic and Class Level'; Title2 'Table 2.4'; run; General Explanation P2 of 2 Within the second tabulate procedure, Line 14 identifies a dummy analysis variable created in an earlier data step that will be used to pass a formula to MS Excel. Line 15 includes two tag attribute (“tagattr”) style overrides to pass a format and a formula to MS Excel. The Formula uses relative cell syntax rather than the absolute syntax (ex. A2, B8) commonly used by Excel. OPIR SAS Example Coding Pt 5 Ln Code 15 TABLE (class='' all), term=''*N=''*[style={tagattr='format:###,##0'}] dumvar='Percent Change'*Sum='4 Year'*[style={tagattr='formula: (RC[‐1]‐RC[‐3])/RC[‐1]'}]*f=pct. /box='Undergraduate' ; OPIR Style=factbkrv Ln 4 Code ods tagsets.excelxp file= "R:\OPIR\enroll2010 neair2.xml" style=factbkrv options(sheet_interval='proc' orientation='portrait' sheet_name='Table 2‐3' embedded_titles='yes'); General Explanation “Factbkrv” is a stand alone style template that I created to customize the appearance of the SAS output. Using the template procedure, you can specify default colors, fonts, and other style attributes of the SAS output. To see SAS built‐in style templates, type “odstemplates” in the SAS dialog box, click Sashelp.Tmplmst and click on the Styles folder. OPIR Proc Template Example: Factbkrv Proc template; define style factbkrv; style data / background=white foreground=black font_face="Times New Roman" font_size=12pt; style table / background=white foreground=CX0000A1 font_face="Times New Roman" font_size=12pt; style header / background=white foreground=CX0000A1 font_face="Times New Roman" font_weight=bold font_size=14pt; style systemtitle / background=white color=dark blue just=left font_face="Georgia“ font_weight=bold font_style=italic font_size=16pt; *Systemtitle controls formats of Proc Tabulate "Title and Title1"; style systemtitle2 / background=white color=dark blue just=right font_face="Georgia“ font_weight=bold font_size=14pt; *Systemtitle2 controls formats of Proc Tabulate "Title2"; style systemtitle3 / background=white color=dark blue font_face="Georgia“ font_weight=bold font_style=italic font_size=14pt; *Systemtitle3 controls formats of Proc Tabulate "Title3"; style footer from data / font_size=9pt; end; run; OPIR Output Example Systemtitle Systemtitle2 Header Data OPIR Resources SAS Support: http://support.sas.com SAS Institute Inc. 2008. “Using Style Elements in the REPORT and TABULATE Procedures”. Andrews, Rick. “Printable Spreadsheets Made Easy: Utilizing the SAS Excel XP Tagset,” NEAIR (2008). DelGobbo, V. “Creating and Importing Multi‐Sheet Excel Workbooks the Easy Way with SAS”. SUGI (2006). Gebhart, Eric S. “ODS ExcelXP: Tag Attr IS It! Using and Understanding the TAGATTR=Style Attribute with the ExcelXP Tagset,” SAS Global Forum (2010). See our presentations tomorrow at 10:05 am. OPIR How To Access Today’s Presentation https://gushare.georgetown.edu/xythoswfs /webui/_xy‐7071421_2‐t_sSA9fBeV Above link expires December 23, 2010. Karen M. Egypt, Assistant Director Office of Planning & Institutional Research Georgetown University, 303 Maguire [email protected] 202‐687‐3587 OPIR Ex – How Table Could Look Like PROC TABULATE DATA=permtabl.factbook ORDER=FORMATTED missing; where level = 'U' and term ne 'FALL 2001'; CLASS class term; var dumvar; format class $class. ; TABLE (class all), term dumvar /box='Undergraduate' ; TITLE1 'ENROLLMENT by Academic and Class Level'; Title2 'Table 2.4'; run; OPIR