topic Re: Pivot Tables without HTML? in ODS and Base Reporting

Pivot Tables without HTML?

wcp_fnfg — Mon, 04 Aug 2014 18:14:49 GMT

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?

Re: Pivot Tables without HTML?

Chevell_sas — Mon, 04 Aug 2014 20:29:21 GMT

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";

Re: Pivot Tables without HTML?

jacqelynl — Tue, 12 Aug 2014 17:24:32 GMT

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!

Re: Pivot Tables without HTML?

Chevell_sas — Wed, 13 Aug 2014 13:55:55 GMT

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?

Re: Pivot Tables without HTML?

jacqelynl49 — Thu, 08 Jan 2015 18:17:34 GMT

Yes I am.