BookmarkSubscribeRSS Feed
wcp_fnfg
Obsidian | Level 7

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?

4 REPLIES 4
Chevell_sas
SAS Employee

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
Fluorite | Level 6

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
SAS Employee

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
Calcite | Level 5

Yes I am.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 2112 views
  • 0 likes
  • 4 in conversation