Chevell Parker, SAS Institute
Tableeditor.tpl is a custom tagset that is created by the TEMPLATE procedure. The tableeditor tagset uses HTML, CSS, JavaScript, and the Markup language. You can download the tableeditor.tpl file and examples as a ZIP file (Updated May 2020).
The tableeditor tagset enables you to create HTML output with the same kind of functionality that you would get from Visual Basic .Net (VB.NET). You can easily export the output to Excel or other filetypes. Note, currently, the tagset supports Internet Explorer only.
For the full list of options and functionality, please see the readme file. Here some features you get from the tagset:
You can modify colors of your output in the following ways: Alternating row colors can be specified by using the BANNER_COLOR_EVEN= and BANNER_COLOR_ODD= options. Alternating columns can be generated by using the COL_COLOR_EVEN= and COL_COLOR_ODD= options. The FBANNER_COLOR_EVEN= and FBANNER_COLOR_ODD= options specify foreground colors for alternate rows. The background color of the body of the table can be specified with the BACKGROUND_COLOR= option. The scrollbar can be specified using the SCROLLBAR_COLOR= option. A mouseover color can be specified using the HIGHLIGHT_COLOR= option. The header background color can be specified using the HEADER_BCOLOR= option. Gridline colors can be modified using the GRIDLINE_COLOR= option and can be removed using the GRIDLINE="NO" option and more.
ods tagsets.tableeditor file="c:\temp.html"
options(banner_color_even="lightblue"
banner_color_odd="pink");
proc print data=sashelp.class;
run;
ods tagsets.tableeditor close;
The above example code produces the following output:
ods tagsets.tableeditor file="c:\temp.html"
style=styles.mystyle
options(col_color_odd="pink"
col_color_even="lightblue"
banner_color_even="teal"
fbanner_color_even="white"
);
proc print data=sashelp.class;
title "Alternating column and row colors";
run;
ods tagsets.tableeditor close;
The above example code produces the following output:
ods tagsets.tableeditor file="c:\combo.html"
style=styles.mystyle
options(banner_color_even="teal"
fbanner_color_even="white"
col_color_even="lightpink"
col_color_odd="lightblue"
scrollbar_color="teal"
background_color="brown"
);
proc print data=sashelp.class(obs=10);
title c=white " Combination of color options";
run;
ods tagsets.tableeditor close;
The above example code produces the following output:
Images or logos can be applied by using the IMAGE_PATH= option. The background image can be specified by using the BACKGROUND_IMAGE= option. The image can also be justified by using the IMAGE_JUST= option.
ods tagsets.tableeditor file="c:\temp.html"
options(background_image="somepath.gif"
image_path="somepath.gif"
image_just="left"
);
proc print data=sashelp.class;
run;
ods tagsets.tableeditor close;
The above example code produces the following output:
The below example uses dynamic positioning and specifies the page height and the page width by using the PAGEHEIGHT= and the PAGEWIDTH= options. This allows you to manage wide and long pages. If the output goes beyond the height and the width specified, scrollbars are added to the table. For a single table, the column headers and row headers can be frozen like in Excel when the the FROZEN_ROWHEADERS= and FROZEN_HEADERS= options are specified. These options can be added in conjunction with the PAGEHEIGHT= and the PAGEWIDTH= to generate a explicit page width and the page height. If these options are not specified, the page width and page height will be generated dynamically which will be the max width and height of the viewable area of the browser. For multiple tables, the FROZEN_HEADERS_ALL= can be specified. The FROZEN_ROWHEADERS= option should only be applied to wide and not the very long tables. If you print the file, you get the complete table with the headers on each printed page.
ods tagsets.tableeditor file="c:\temp.html"
options(pageheight="300"
pagewidth="35%"
frozen_headers="yes"
frozen_rowheaders="yes"
);
proc print data=sashelp.class;
run;
The above example code produces the following output:
The below example modifies the printing properties such as the headers and footers and the orientation (which typically cannot be done programmatically) by using the PRINT_HEADER=, PRINT_FOOTER=, and ORIENTATION= options. The page breaks can be removed using the PAGEBREAK= option, and the printed scale of the output can be specified using the PRINT_ZOOM= option. The PAGEBREAK= option can also specify how many tables to place on a page.
To prevent truncation or to get more information on a page, use either the ORIENTATION="LANDSCAPE" option, the PRINT_ZOOM= option, or the FIT2PAGE= option. The FIT2PAGE="YES" option scales the output dynamically to fit on a printed page whether it is landscape or portrait. The page header is repeated automatically when the table breaks on a page. The margin options can also be specified. FIT2PAGE="YES" prevents truncation without having to specify a value. Special codes can be used for the headers and footers which allow you modify the presentation. The PAGEBREAK_TOGGLE= option enables you to modify the page breaks dynamically. Here is the code available for the headers and footers in Internet Explorer:
| Header | Code |
|---|---|
| Window title | &w |
| Page address (URL) | &u |
| Date in short format | &d |
| Date in long format | &D |
| Time in the format specified by Regional settings from the Control Panel | &t |
| Time in 24-hour format | &T |
| Current page number | &p |
| Total number of pages | &P |
| Right-aligned text (following &b) | &b |
| Centered text (between &b&b) | &b&b |
| A single ampersand (&) | && |
ods tagsets.tableeditor file="c:\fit2page.html"
style=styles.mystyle
options(print_header="&b testing header"
print_footer="testing footer"
/*print_zoom="50%" */
orientation="landscape"
pagebreak="no"
left_margin=".5"
right_margin=".5"
top_margin="1"
bottom_margin="1"
fit2page="yes"
print_dialog="yes"
);
proc print data=sashelp.orsales(obs=20);
run;
ods tagsets.tableeditor close;
The above example code produces the following output:
The ZOOM= option scales the output based on the value specified. the TABLE_ZOOM= option specifies the zoom property for individual tables. In the below example, table 1 is scaled at 50%, table 2 is 75% and table 3 is 200%. ZOOM_TOGGLE= option enables you to zoom the page interactively.
ods tagsets.tableeditor file="c:\temp.html"
options(/* zoom="100%" */
zoom_table="50%, 75%, 200%"
zoom_toggle="no"
);
proc print data=sashelp.class(obs=5);
run;
proc report data=sashelp.class(obs=5) nowd;
run;
proc print data=sashelp.air(obs=5);
run;
ods tagsets.tableeditor close;
The above example code produces the following output:
The SORT= option enables you to click on the column headers to sort the columns. The SORT_ARROW_COLOR= option allows you change the up and down arrow color. You can use the SORT_IMAGE= and the SORT_UNDERLINE= options to notify the user that the column headers can be sorted.
The DESCRIBE= option allows you to display the type of the field, whether character, numeric, or date. This allows you to see how this column will be sorted. In SAS 9.1, the REPORT procedure shows character for all of the columns; this has been enhanced for SAS 9.2. So if you are using PROC REPORT in SAS 9.1, you must set the data type to modify the default sorting for all fields as a character string. This can be done by using the DATA_TYPE= option, specifying the desired type for each column. You can also set the type of the field within the procedure manually by using the TAGATTR= attribute. The valid types are Number, String, Date, None and Numberx (numbers formatted with the dollar sign , comma or percent).
ods tagsets.tableeditor file="c:\temp.html"
options(data_type="String,String,Number,Number,Number"
sort="yes"
sort_arrow_color="green"
sort_image="temp.jpg"
sort_underline="no"
describe="yes"
);
proc print data=sashelp.class;
run;
proc report data=sashelp.class nowd;
/* define _numeric_ / style(header)={tagattr="type='String'"};*/
run;
proc print data=sashelp.air;
run;
ods tagsets.tableeditor close;
The above example code produces the following output:
Summaries can be excluded by specifying HTMLCLASS="NoFilter" along with EXCLUDE_SUMMARY="YES". This prevents the grand total or the summary from sorting in PROC REPORT.
ods tagsets.tableeditor file="c:\exclude_summary.html"
options(sort="yes"
sort_arrow_color="blue"
exclude_summary="yes"
)
style=styles.mystyle;
proc report data=sashelp.class(obs=5) nowd;
rbreak after / summarize style={htmlclass="NoFilter"};
title "Summary Excluded";
run;
ods tagsets.tableeditor close;
The above example code produces the following output:
Autofilters can be added using the AUTOFILTER="YES" option. The width for the filters can be modified by using the AUTOFILTER_WIDTH= option. By default, the filter is as wide as the cell. Specific tables can be filtered by using the AUTOFILTER_TABLE= option. An ending range on columns can be specified using the AUTOFILTER_ENDCOL= option. To filter specific columns, the FILTER_COLS= option can be specified with the list of columns separated by commas. The below code adds filters to the first table and gives it a default width so that it is not the width of the entire cell.
ods tagsets.tableeditor file="c:\temp.html"
options(autofilter="yes"
autofilter_width="7em"
autofilter_table="1"
) style=styles.mystyle ;
proc print data=sashelp.orsales(obs=100);
run;
ods tagsets.tableeditor close;
The above example code produces the following output:
The tableeditor tagset also generates a compact table of contents for you by default. If you are not using the style STYLE.MYSTYLE, use the below PROC TEMPLATE code, which generates a collapsed table of contents. The plus or minus signs enable you to navigate down to the specific table or graph. The PROC TEMPLATE code defines the level of expansion. You also have the ability to add your own images using the OPEN_IMAGE_PATH=, CLOSED_IMAGE_PATH=, and the LEAF_IMAGE_PATH= options. The background color can be specified using the TOC_BACKGROUND= option. The output can be expanded using the TOC_EXPAND= option, and a "Print" button added by using the TOC_PRINT= option.
ods tagsets.tableeditor
file="c:\temp.html"
contents="c:\temp1.html"
frame="c:\temp1.html"
style=styles.mystyle
options(/*open_image_path="file.gif"
closed_image_path="file.gif"
leaf_image_path="file.gif"*/
toc_background="white"
toc_expand="yes"
toc_print="yes"
);
proc print data=sashelp.class;
run;
proc report data=sashelp.class nowd;
run;
ods tagsets.tableeditor close;
The above example code produces the following output:
Interactive options include PAGEBREAK_TOGGLE=, which enables you to toggle the page breaks interactively within the document. Also, there is the ZOOM_TOGGLE= option which enables you to specify the zoom interactively. The STYLE_SWITCH= option can be specified along with the CSS files of your choice. Any file that contains the name DEFAULT will be used as the default style; however, you then have the ability to choose from any of the others.
ods tagsets.tableeditor file="c:\temp.html"
style=styles.mystyle
options(style_switch="yes"
pagebreak_toggle="yes"
zoom_toggle="yes"
)
stylesheet=(url="default.css
astronomy.css
analysis.css
sasweb.css");
proc print data=sashelp.class;
run;
proc report data=sashelp.class nowd;
run;
ods tagsets.tableeditor close;
The above example code produces the following output:
These options are more informative in nature. They enable you to name the window by using the WINDOW_TITLE= option, add a message when the page is loading by using the LOAD_MSG= option, include another file (for example, PDF, RTF, HTML) to the document with the INCLUDE= option, or specify alert text or window status text.
ods tagsets.tableeditor file="c:\temp.html"
options(window_title="the table editor"
load_msg="yes"
include="c:\temp.pdf"
alert_text="Confindential"
window_status="Wow! Now that's your style!"
);
proc print data=sashelp.class;
run;
proc print data=sashelp.air;
run;
ods tagsets.tableeditor close;
A "Save As" button can be generated by specifying the SAVEAS= option. This adds a button on the page which allows the current HTML page to be saved. The DEFAULT_FILE= option can specify a default filename to be used.
ods tagsets.tableeditor file="c:\temp.html"
options(saveas="yes"
default_file="c:\\temp.xls"
);
proc print data=sashelp.class;
run;
ods tagsets.tableeditor close;
The above example code produces the following output:
The EXCEL_SAVE_FILE= option saves the file as native a XLS, CSV, XML, TXT, DOC, or SLK file. Specify the appropriate extension of the file with the FILE_FORMAT= option. You can also specify whether you want Excel to open this file or save it in the background. To save this file in the background without opening Excel, use the OPEN_EXCEL="NO" option. There is also the EXCEL_SAVE_PROMPT= option, which prompts the user for a file to save. The EXCEL_SAVE_DIALOG= option opens a dialog box to save the file.
ods tagsets.tableeditor file="c:\\temp.html"
options(excel_save_file="c:\\temp122.csv"
file_format="csv"
open_excel="no"
quit="yes"
);
proc print data=sashelp.class;
run;
ods tagsets.tableeditor close;
The above example code produces the following output:
The EXCEL_AUTOFILTER option enables autofilters. The EXCEL_FROZEN_HEADERS= option freezes the column headers in the Excel file. The EXCEL_ORIENTATION= option controls the orientation of the sheet. You can name the sheets with the SHEET_NAME= option. The table can be zoomed or scaled for the printed output by using the EXCEL_ZOOM= or EXCEL_SCALE= option. The AUTO_FORMAT= option is used to supply one of Excels build-in formats
ods tagsets.tableeditor file="c:\temp.html"
style=styles.mystyle
options(excel_zoom="90"
excel_scale="90"
excel_autofilter="yes"
excel_frozen_headers="yes"
excel_orientation="landscape"
sheet_name="first"
auto_format="color1"
excel_save_file="c:\\temp.xls"
);
proc print data=sashelp.class;
run;
proc report data=sashelp.class nowd;
run;
proc print data=sashelp.air;
run;
ods tagsets.tableeditor close;
The above example code produces the following output:
Tables from a web page can be inserted into a workbook as a worksheet using the Update_Target= option which is responsible for the opening the file that will have the sheet inserted. The Sheet_Name= option can be added to name tables moved from the web page into existing workbooks.
ods tagsets.tableeditor file="temp.html"
options(update_target="c:\\temp\\temp.xls"
sheet_name="Table 1"
);
proc print data=sashelp.class;
run;
ods tagsets.tableeditor close;
Pivot tables can be generated by specifying the range of the data to create the pivot table from using using the PTSOURCE_RANGE= option. The pivot table will be write and the PTDEST_RANGE= options. The pivot rows, columns, data, and page can be specified using the below options. To supply the position of more than one field to an option, separate the column number by commas.
ods tagsets.tableeditor file="c:\temp.html"
style=styles.mystyle
options(excel_orientation="landscape"
sheet_name="test"
auto_format="color2"
ptsource_range="b1:i5"
pivotrow="5"
pivotcol="2,3"
pivotdata="7"
pivotpage="1"
);
proc print data=sashelp.orsales(obs=5);
var year Quarter Product_Line Product_Category Product_Group
Quantity Profit Total_Retail_Price;
title ;
run;
ods tagsets.tableeditor close;
The above example code produces the following output: