topic Re: How to create Excel Pivot tables from EG in SAS Enterprise Guide

How to create Excel Pivot tables from EG

Vish33 — Thu, 26 Jul 2012 11:51:14 GMT

Hi,

I want to create Excel pivot table from EG 4.2, which will consists of autofilters. I tried with ODS tagsets.tableeditor, but its not working in EG.

Can anyone suggest on this.

Thanks,

Vishnu

Re: How to create Excel Pivot tables from EG

robby_beum — Thu, 26 Jul 2012 13:18:39 GMT

There seem to be many examples out there, hopefully they will help shed some light on the topic:

http://www2.sas.com/proceedings/forum2007/055-2007.pdf

http://www2.sas.com/proceedings/sugi31/035-31.pdf

http://support.sas.com/resources/papers/proceedings11/077-2011.pdf

Re: How to create Excel Pivot tables from EG

Haikuo — Thu, 26 Jul 2012 13:39:58 GMT

Hi,

Not familiar with EG. But by using tagsets.excelxp, Autofilters and other features are easily done on BASE platform.

Haikuo

Re: How to create Excel Pivot tables from EG

Vish33 — Thu, 26 Jul 2012 14:56:14 GMT

can you please write a sample for that.

Re: How to create Excel Pivot tables from EG

Haikuo — Thu, 26 Jul 2012 15:27:37 GMT

Sure.

ods listing close;

ods tagsets.excelxp

file="c:\yourfolder\yourfilename.xml"

style=statistical;

title "Excel table with autofilters";

ods tagsets.excelxp

options ( sheet_name="Map"

embedded_titles ='yes'

autofilter = 'yes'

frozen_headers='3');

proc print data=sashelp.class;

run;

ods tagsets.excelxp close;

ods listing;

HTH,

Haikuo

Re: How to create Excel Pivot tables from EG

Cynthia_sas — Thu, 26 Jul 2012 16:48:56 GMT

Hi:

TAGSETS.TABLEEDITOR should work to create a pivot table in Excel, if you got the suboptions correct. when you say it "doesn't work in EG" my guess is that 1) you still have the SASReport format turned on, so you're NOT seeing the output in Excel, you are seeing it inside the EG Results tab, as SASReport output; and/or 2) EG doesn't always like to open output that is aimed at Excel. So, if you do something like this:

ods _all_ close;

ods tagsets.tableeditor file='c:\temp\myreport.xls'

.... more options and suboptions ....;

procedure code goes here

ods tagsets.tableeditor close;

And then using Windows Explorer (outside of EG) go to c:\temp and find the file, you should be able to RMB on it and click Open with Excel. OR, you should be able to do a File-->Open from within Excel to navigate to c:\temp and find the file. Otherwise, if those suggestions do not work for you, I'd suggest that you open a track with Tech Support. TAGSETS.TABLEEDITOR is the method by which you can create pivot tables.

cynthia

Re: How to create Excel Pivot tables from EG

Vish33 — Mon, 30 Jul 2012 15:06:09 GMT

Thank you for the helpful answers Haikuo and Cynthia..

Re: How to create Excel Pivot tables from EG

Priya_18 — Wed, 19 Dec 2018 03:36:46 GMT

When I Tried this method my excel file was throwing error as "the file format or file extension is not valid. Verify that the file is not been corrupted and that the file extension matches the format of the file". I have tried with both the extensions as xls and also as xlsx.