Best Practice - export data from EG to EXCEL PIVOT

[hellind]

SAS EG/Access 2007

Our company uses SAS EG 9.2

We then export large data-sets to ACCESS. Records = 1 million.

EXCEL PIVOT tables then connect to this ACCESS.

Problem: SAS EG export to access is very slow. I have Googled and the only results returned is a Aug'2008 article at SAS.com about exporting to access 10x slower than exporting to txt.

I just wonder how most of you do in such situations? I think most people still use Excel pivot for dashboards, and SAS for data-manipulation.

If you export to txt first and then import to access, it is too manual and prone to error.

[CaseySmith]

If you have SAS/ACCESS to PC Files licensed, I think you'll have better performance using it to export larger data sources to Microsoft Access, rather than the EG export.  For example:

/* MS Access database already exists */
LIBNAME mylib ACCESS 'C:\temp\Demo.accdb';

/* write new table to the database */
data mylib.myclass;
set sashelp.class;
run;

See SAS/ACCESS(R) 9.4 Interface to PC Files: Reference, Third Edition

Casey

[ballardw]

For what it is worth, I have never built a dashboard in Excel. SAS GKPI makes all the graphics I need for a dashboard and avoids any headaches with data interchange.