topic Re: Best Practice - export data from EG to EXCEL PIVOT in SAS Enterprise Guide

Best Practice - export data from EG to EXCEL PIVOT

hellind — Wed, 22 Jul 2015 03:39:39 GMT

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.

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

CaseySmith — Wed, 22 Jul 2015 04:20:42 GMT

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

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

ballardw — Wed, 22 Jul 2015 15:17:08 GMT

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.