How to create Excel Pivot tables from EG

[Vish33]

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

[robby_beum]

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

[Haikuo]

Hi,

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

Haikuo

[Vish33]

can you please write a sample  for that.

[Haikuo]

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

[Cynthia_sas]

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

[Priya_18]

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.

[Vish33]

Thank you for the helpful answers Haikuo and Cynthia..