About sascis

sascis · ‎10-29-2025

That is exactly my conundrum! If my data were smaller, which is stored on disk as a CSV, my users could simply open it in Excel and do what they need with the raw data. But the pivot table idea is the compromise that we've come up with to give access to all the data from Excel, and by choosing their own definition, the data can be displayed and summarized however each user wants. The piece that's missing is figuring out the ODS coding to be able to include AMO connection/definition components. I have a bunch of different datasets that need this "solution", so trying to automate the creation is the ultimate goal.

sascis · ‎10-28-2025

Good idea - but not the solution I need. My sole purpose for creating the pivot table is because I need entire granular dataset contents available to non-SAS users who do the analytics. I've found that I can create a pivot table - sourced using Add-In for Microsoft Office from a SAS dataset residing in OneDrive - manually, then the analysts can get all the source data pulled in however they want to see it at the moment. Planning ahead for all those possible investigative summaries is not really an option. And I can do all that manually - my question is centered on "how can I do it programmatically". The code above will create the pivot table I need, but it also puts the entirety of the data into a separate tab in the same worksheet - and in many cases, my data is far too big for Excel to handle. So the code execution just spins, and then eventually fails because the sheet cannot handle the data size. But if I can find the correct options for either the TableEditor or MSOffice2K** tagsets that can define a source using AMO, then I should be able to use my time more efficiently.

sascis · ‎10-24-2025

I am trying to use the TableEditor tagset to create an Excel Pivot Table - and so far it's working fine, EXCEPT it's putting all the data into a sheet in the workbook (which I'm guessing is default behavior). However, the reason I want the pivot table in the first place is because my full data will not fit into a worksheet (I'm using a tiny subset to create the pivot table to verify structure). I'd like to be able to programmatically tell ODS to use a SAS dataset (probably through some sort of AMO API) - can I do that?? I've looked through the definitions of both the TableEditor and MSOffice2K** tagsets to see if any of the events/options look like they'd work, but I'm not having much success. Here's what I do have working: ods tagsets.tableeditor file="c:\temp\premsum&q2y4..html" options ( auto_excel = "yes" excel_autofilter = "yes" sheet_name = "Prem &q2y4" pivotrow = "state,product_type" pivotcol = "qtr_year" pivotdata = "totalep" pivotdata_fmt = "[blue] $#,###" excel_save_file = "c:\\temp\\premsum&q2y4..xlsx" quit="NO" ); title1 "Premiums &q2y4."; proc print data=MYPREM.PREMSUM&q2y4.(where=(state in ('NC','SC','GA','VA','TN') and cal_year GE 2023)); run; title1; ods tagsets.tableeditor close; ( @Chevell_sas ??)

Online Status	Offline
Date Last Visited	‎10-31-2025 09:55 AM

Re: ODS TableEditor tagset: programmatically creating a pivot table wh...

Re: ODS TableEditor tagset: programmatically creating a pivot table wh...

ODS TableEditor tagset: programmatically creating a pivot table whose ...

Re: ODS TableEditor tagset: programmatically creating a pivot table wh...

Re: ODS TableEditor tagset: programmatically creating a pivot table wh...

ODS TableEditor tagset: programmatically creating a pivot table whose ...