How to correctly format Pivot Tables and create multiple sheets?

Reply
Contributor
Posts: 24

How to correctly format Pivot Tables and create multiple sheets?

I decided to stop while I am ahead. My goal is to create a workbook that has a few mulitple pivot tables and also format the numbers. When I attempt the process for formating, the data part is blank. I do not get the mulitple worksheets. I am new to this approach so I am sure that I missed something.

 

Here is my snippet for the code:

 

filename temp url

'http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/tableeditor.tpl';

%include temp;

 

 

ods tagsets.tableeditor file="&path\enroll.html"

options(

pivot_series="yes"

button_text = "PivotTable"

AUTO_EXCEL="Yes"

pivotpage = "county_nm,race_desc | county_nm,race_desc | county_nm,race_desc"

pivotrow = "PROGRAM_AREA_DESC | CURRICULUM_NM | DEGREE_PURSUED"

pivotcol = "reporting_term,gender_code | reporting_term,gender_code | reporting_term,gender_code"

pivotdata = "student_cnt | studednt_cnt | student_cnt"

pivotdata_stats = "sum | sum | sum"

/*pivotdata_fmt="#,###"*/

excel_save_file="&path\enroll.xls"

/*SHEET_NAME="Program Area"*/);

proc print data=enroll noobs;

run;

ods tagsets.tableeditor close;

Grand Advisor
Posts: 10,210

Re: How to correctly format Pivot Tables and create multiple sheets?

I don't use the tableeditor tagset so can't make a targeted response. We would need some example data, please provide as a data step we can run as importing from an Excel file is likely not to duplicate your data in terms of type and other characteristics.

 

One thing that you should look at if the example code was copy and paste: you have a variable studednt_cnt that I suspect you meant to be student_cnt.

I have no idea what a mis-spelled word does in the tagset but I doubt it is going to do what you intend.

Contributor
Posts: 24

Re: How to correctly format Pivot Tables and create multiple sheets?

That spelling error was typed in quickly because I had initially removed it. Is there another way to pivot tables with tableeditor? I took a class and that was used.
Grand Advisor
Posts: 10,210

Re: How to correctly format Pivot Tables and create multiple sheets?

I don't use pivot tables unless someone threatens me so I'm not very helpful with that.

 

Actually I haven't actually done an Excel pivot table in something like seven years. With SAS I determine with my report customers what information they want and generate a table that looks that way using one of the SAS report procedures generally Print or Tabulate and occasional Report.

Then output can go to ODS RTF, PDF or tagsets.Excelxp (or all at the same time if needed).

 

Esteemed Advisor
Posts: 5,198

Re: How to correctly format Pivot Tables and create multiple sheets?

I haven't used tastes either, and haven't heard of the possibility to do pivoting directly from it. Not saying it couldn't be done...
For pivots I think that either SAS Addin for MS Office or use SAS data as a data source (OLEDB or ODBC) are more widely used.
Data never sleeps
Frequent Contributor
Posts: 102

Re: How to correctly format Pivot Tables and create multiple sheets?

[ Edited ]

Hi,

 

I didn't manage yet to get the tableeditor work correctly for me, fulfilling all the requirements I had. So I chose to work with ODS Tagset ExcelXp to output my data and added some vbs

 

This paper helped me to program my pivot table directly from SAS, works like a charm

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

 

After inserting the pivot I convert my xml to a native xlsx file
one way to do this can be found here:
http://support.sas.com/kb/43/496.html

 

Finally i noticed that I had to refresh the xlsx. Although it can be done with 3 clics in excel, I chose to do this directly from SAS

 

The main vbs code you need to do this (using the proceedings described in the above paper) is:

 

         put " Dim oXL ";
         put " Dim objWorkBook";
         put " Set oXL = CreateObject(""Excel.Application"")";

         %*Open xlsx file, refresh (pivot) Table and save;
         put " Set objWorkBook = oXL.Workbooks.Open(""&xlsxFileName."")";

         put " objWorkBook.RefreshAll";
         put " oXL.ActiveWorkBook.SaveAs ""&xlsxFileName."",51";
         put " oXL.DisplayAlerts = True";
         put " oXL.ActiveWorkBook.Close SaveChanges = True";
         put " oXL.Quit";
________________________

- That still only counts as one -

Ask a Question
Discussion stats
  • 5 replies
  • 363 views
  • 0 likes
  • 4 in conversation