04-09-2012 09:35 AM
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....
04-09-2012 05:54 PM
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.
04-09-2012 06:15 PM
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...