SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Creating Pivot Tables with ODS

Reply
Occasional Contributor CMC
Occasional Contributor
Posts: 7

Creating Pivot Tables with ODS

Hi all, I have to work with datasets in the 5-10 million record range, I roll the data up as much as possible to get the datasets in the 2-3 million records range, which I currently bridge to MS Excel pivot tables via MS Access or the MS Addin (my preferred methodology).  In our current environment, I can't use the scheduler in the AMO to kick the job off, and need to manually run the bridging process from SAS to Excel.  My manager asked me to look into running this process using ODS. I tried using the methodology outlines by Parker in Paper 003-2010, but run out of memory before even 100,000 records transfer over.

Does anybody know how to dump 2 million plus records into Excel using ODS?  Any other recommendations?  Thanks for your help....

Trusted Advisor
Posts: 1,028

Re: Creating Pivot Tables with ODS

Try using the CSV destinations. I don't think there is a performance penalty for using them with large datasets.

Tom

Grand Advisor
Posts: 9,682

Re: Creating Pivot Tables with ODS

I would at this point discuss with the manager whether he wants an Excel file with 2 million plus records or the resulting the table(s).

It is very likely that the pivot tables could be made in SAS and then use ODS to export the resulting tables, without all of the data, to Excel. The various SAS report tools such as PROC REPORT and TABULATE can do much more than Excel pivot tables in my opinion.

Occasional Contributor CMC
Occasional Contributor
Posts: 7

Re: Creating Pivot Tables with ODS

There is more to the story, I'm aware of the power of PROC REPORT and TABULATE, but our senior management likes reports with 15-20 different dropdown (with up to 20 cuts per box) boxes so they can drill-down into the data, hence my need to have access to all the data represented in the Excel file. Hope I'm being clear, thanks for your imput...

Post a Question
Discussion Stats
  • 3 replies
  • 757 views
  • 0 likes
  • 3 in conversation