BookmarkSubscribeRSS Feed
CMC
Calcite | Level 5 CMC
Calcite | Level 5

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

3 REPLIES 3
TomKari
Onyx | Level 15

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

Tom

ballardw
Super User

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.

CMC
Calcite | Level 5 CMC
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 3 replies
  • 1500 views
  • 0 likes
  • 3 in conversation