Desktop productivity for business analysts and programmers

Best Practice - export data from EG to EXCEL PIVOT

Reply
Frequent Contributor
Posts: 90

Best Practice - export data from EG to EXCEL PIVOT

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.

SAS Super FREQ
Posts: 271

Re: Best Practice - export data from EG to EXCEL PIVOT

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

Grand Advisor
Posts: 10,210

Re: Best Practice - export data from EG to EXCEL PIVOT

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.

Ask a Question
Discussion stats
  • 2 replies
  • 297 views
  • 0 likes
  • 3 in conversation