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;
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.
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).
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";
- Cheers -
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.