03-17-2016 09:33 AM
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
ods tagsets.tableeditor file="&path\enroll.html"
button_text = "PivotTable"
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"
proc print data=enroll noobs;
ods tagsets.tableeditor close;
03-17-2016 11:44 AM
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.
03-17-2016 12:39 PM
03-17-2016 01:37 PM
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).
03-17-2016 03:50 PM
03-21-2016 09:45 AM - edited 03-23-2016 04:30 AM
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
After inserting the pivot I convert my xml to a native xlsx file
one way to do this can be found here:
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 -