topic Re: How to correctly format Pivot Tables and create multiple sheets? in SAS Programming

How to correctly format Pivot Tables and create multiple sheets?

TashaBee — Thu, 17 Mar 2016 13:33:00 GMT

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;

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

ballardw — Thu, 17 Mar 2016 15:44:04 GMT

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.

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

TashaBee — Thu, 17 Mar 2016 16:39:21 GMT

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.

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

ballardw — Thu, 17 Mar 2016 17:37:42 GMT

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).

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

LinusH — Thu, 17 Mar 2016 19:50:28 GMT

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.

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

Oligolas — Wed, 23 Mar 2016 08:30:50 GMT

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