EZ Study

Actuarial Biology Chemistry Economics Calculators Confucius Engineer

Click to solve: How to create excel pivot table in SAS

Question: As statistician, we often got the question "Can we get that in excel?" For example, can we get the pivot table from SAS, rather than manually clicking to generate the pivot table. Also can we do some sas coding to get subtotal, or some conditional formatting in the pivot table output?

Answer: SAS provides tableeditor tagset to accomplish those tasks. With the help from this tagset, you can actually do more tasks than just generating pivot tables:
        • Add logos and background images.
        • Alternate odd and even colors for rows or columns or both
        • Repeat headers or footers on every page
        • Apply filters to the table of choice.
        • Change orientation from portrait to landscape.
        • Create pivot tables; Generate and run PowerPoint slides.

How to install TableEditor tagset in SAS?
To create PivotTables from SAS you only need Base SAS and the TableEditor tagset; since the TableEditor tagset is not included in the SAS distribution you will need to download it from the SAS website. If you don't install TableEditor tagset first, and simply run the following code, then you will get some error/warning in the log:

To check which template/tagset you have installed, run the following sas code and check the log output:
        proc template; list tagsets; run;
if you can find TableEditor in the list, then you already installed; otherwise install the TableEditor tagset. Click this link to download the installation file, or click this link for
all updated tagset installation files. Once the TableEditor file is downloaded you can open and submit it in SAS. But how?

Once you have downloaded the installation file, you may realize it's a zipped file; unzipped it, then you will find some css, html etc. files. Most importantly, it should have a file called: Tableeditor.tpl, you need to include that template file to your SAS. There are several ways to load that template into SAS. An relatively easy one is using the following code:
        %include "direcotry\tableeditor\tableeditor.tpl";
        /*the directory is the folder that contains your unzipped template file*/
Or you can simply copy paste that file into sashelp.templt folder.

Here are the sample code you can use for various purpose:
        ods tagsets.tableeditor file="c:\temp\temp.html" style=styles.mystyle
        options(print_header="testing header" print_footer="testing footer"

        frozen_headers="yes" /* forzen header*/
        frozen_rowheaders="yes" /*forzen the row header*/

        background_image="somepath.gif" /* insert a graph as background */
        image_path="somepath.gif" image_just="left"
        pageheight="300" pagewidth="35%" print_dialog="yes"
        orientation="landscape" pagebreak="no" /*print_zoom="50%" */

        left_margin=".5" right_margin=".5" top_margin="1" bottom_margin="1"
        fit2page="yes" /* rescale the page print to fit */

        zoom_table="50%, 75%, 200%" /* zoom different tables */
        zoom_toggle="no" /* not zoom the page interactively */

        col_color_odd="pink" col_color_even="lightblue"
        banner_color_even="teal" fbanner_color_even="white"
        scrollbar_color="teal" background_color="brown"

        sort="yes" sort_arrow_color="green"
        sort_image="temp.jpg" sort_underline="no" describe="yes"

        exclude_summary="yes" /* exclude summary in the output */

        autofilter="yes" autofilter_width="7em" autofilter_table="1"
        /* or use options AUTOFILTER_ENDCOL=... or FILTER_COLS=...*/
        saveas="yes" default_file="c:\\temp.xlsx" /* save or export to excel file */

        excel_zoom="90" excel_scale="90" excel_autofilter="yes"
        excel_frozen_headers="yes" excel_orientation="landscape"
        update_target="c:\\temp\\temp.xls" /* or update an excel tab */
        sheet_name="first" auto_format="color1" excel_save_file="c:\\temp.xls"

        excel_orientation="landscape" sheet_name="test"
        auto_format="color2" ptsource_range="b1:i5" /*select pivot table data*/

        button_text = "Create PivotTable" auto_excel = "yes"
        pivotrow = "product_line" pivotcol = "quarter"
        pivotdata = "profit" pivotpage = "year"
        excel_save_file="c:\example1.xlsx" quit="NO"

        pivotdata_stats="Sum,Average,Max" format the pivot output
        pivotdata_fmt="[blue] #,###~[red] $#,###.##~$#,###.##" );

        proc print data=sashelp.orsales(obs=20); run;
        ods tagsets.tableeditor close;

Acknowledgement: read more about Tableeditor from this link.

Related links:
Continue to: Automation for Data Exploring in data mining   SAS tutorial home
Back to: How to format Excel file by SAS DDE?   Statistics tutorial home