Pivot Tables without HTML?

[wcp_fnfg]

I've read a few articles about tagset.tableeditor to create pivot tables in excel, but they don't work for me.  Pressing the Export button causes a crash, plus having the unnecessary step of loading IE before I can get my output is much less than ideal.  Does anyone have an alternative?

[Chevell_sas]

Another approach that you can take is to use the TableEditor tagset to update an existing file with the pivot table.  This could be any file type that could be opened by Excel  such as CSV, XML, HTML, XLSX.. With the version of the TableEditor tagset that I am pointing you to, there is a new option which is the OUTPUT_TYPE which you can set to Script. This will create a JavaScript file that you can simply execute using the X statement which will update the worksheet without having to create the intermediate HTML file.  You will either have to compile the tagset first or compile it by adding this on a %include statement.

ftp://ftp.sas.com/techsup/download/base/SGF2014_examples.ZIP.

/* Create file to update */

ods tagsets.Excelxp file="c:\temp.xml"  options(sheet_name="temp") ;

proc print data=sashelp.class;;

run;

ods tagsets.Excelxp close;

/* update the file with a simple pivot table  by creating a javascript file that we execute */

options xnowait noxsync;

ods noresults;

ods tagsets.tableeditor file="c:\testing_source.js"

                                 options(output_type="script"

                                             update_target="c:\\temp.xml"

                                             sheet_name = "temp"

                                             pivotrow = "age,sex"

                                             pivotdata = "height,weight"

                                             pivotdata_tocolumns = "yes" );

data _null_;

file print;

put "test";

run;

ods tagsets.tableeditor close;

/* execute Script file */

x "c:\testing_source.js";

[jacqelynl]

Hi,

I was just searching for some help with exactly this issue and I have tried it.  For the most part it works.  One question has come up:  My javascript file keeps giving me a sheet_name of temp even though I specify another.  I am updating an excel file.  I would appreciate any help you can give me.

Thank you!

[Chevell_sas]

I have not been able to replicate this yet. Just to verify, when running this example or similar example, you are specifying the same sheet_name= in the tableEditor tagset options list which matches the sheet name in the worksheet?

[jacqelynl49]

Yes I am.