topic Re: How to export to Excel or to Access in Developers

How to export to Excel or to Access

deleted_user — Wed, 09 Jan 2008 16:57:23 GMT

I have a quite large dataset that a user wants to hve exported to MS Access. The dataset has 216 fields and contains 6.75 million reocrds. Why this user wants such a huge amount of data ported into MS Access is beyond me, but when users make a request......

I tried using Proc SQL and the MS Add-in to process data into MS Excel, from where I could then put the data into MS Access. However, even with an observations limit of 100,000, a simple Proc SQL and Proc Print took FOREVER.

Is there a more efficient way to get my data into MS Access, even if I have to do it a chunk at a time?

Re: How to export to Excel or to Access

Cynthia_sas — Thu, 10 Jan 2008 13:25:38 GMT

Hi:
Generally, this is not something I would do in a stored process for one thing it's possible that the time involved to execute and run a stored process and return results (PROC PRINT) with this many records will most likely exceed even the most liberal of timeout limits. I think your best bet for figuring out how to get the file to your users would be to investigate a batch program that wrote the exported file to a server location and then have your user download the file they want. Do the users want all 6.7 M records or only a subset? Why do the records need to be in Access? Is it just because of the 65K limit in Excel? Are you aware that using the SAS Add-in for Microsoft Office, you can work with large files and get Excel to page through the file a certain number of obs at a time?

Dealling with large files raises a set of challenges that Tech Support is best equipped to handle. You might consider checking with them to see what they recommend as an approach.

cynthia

Re: How to export to Excel or to Access

deleted_user — Thu, 10 Jan 2008 20:17:12 GMT

Hi, Cynthia,

Thanks for replying! Yes, the users say they want ALL records. And, the records have to be in MS Access format since that is what the users have specified.

I know what you mean about the time factor.... very inexpedient to process a dataset this large.

Using the SAS Add-in, how would I page through the file a certain number of obs at a time?

Re: How to export to Excel or to Access

Cynthia_sas — Fri, 11 Jan 2008 13:22:57 GMT

Hi:
I am not on my machine (in a hotel) at the moment, so you'll have to experiment with the click path, but it is fairly intuitive once you get there.

1) use Microsoft Excel -- under SAS --> Options, on the DATA tab, there's a choice for how many obs to "page" by. I believe the default is 1500?? change that as needed (I suggest NOT picking 65K)
2) go to sas menu
3) open data source
4) point to data source (must be in metadata repository or defined with an information map)
5) use data toolbar tool icons to page thru the file (look like standard single arrow or arrow with a line to go to the top or bottom of the file)

Of course, this will NOT work with PowerPoint or Word, as they do not have "data" viewing capabilities.

cynthia

Re: How to export to Excel or to Access

478850 — Tue, 01 Apr 2008 13:56:34 GMT

Hi, I am just curious how you made out with exporting this number of records. I attempted to export to access a dataset with only 1.8 million rows and 39 columns. I exported as a step in a SAS EG project and it just ran the export continuously in a loop and never finished nor gave me an error. I cannot find information on whether there is a size limit for exporting.

Thanks

Re: How to export to Excel or to Access

Cynthia_sas — Tue, 01 Apr 2008 14:13:13 GMT

Hi:
This Tech Support note lists one possibility for failure:
http://support.sas.com/kb/6/318.html

To find this note, I went to support.sas.com and typed
limit export Access

into the search box and this note was the first hit. There are others.

cynthia

Re: How to export to Excel or to Access

478850 — Tue, 01 Apr 2008 14:20:55 GMT

Thanks Cynthia. I will search by this criteria to see if I can locate an answer for this. I recvd no SASerrors and the file size is much smaller than 1 gig so there is no apparent reason for this not to complete exporting.