SAS7BDAT to MDB-table -- Speeding up?

[RFLinnenbank]

Hello

Most of you will probably have noticed dat (at least with default-settings) SAS is quite slow at placing data in Access 97 MDB-files.

This goes for PROC EXPORT as much as PROC SQL and DATA STEP solutions.

As a matter of fact it is usually faster with bigger files to export the files in a DBF-format first and then have Access 97 import those!

But this appears to take up just a bit more space than the direct approach, and this tiny bit more space unfortunately means that in the end the MDB crosses that happy 1G border that Access97 MDB files have going for them. (Yay)

I can off course solve this from the Access side as well, and I'm working on that, but if there is a way to use SAS to get those tables into their MDBs in less than 6 hours time (for +/- 890MB) that would off course help greatly as well. (as that would reduce the number of steps required, and prevent the necessity for some manual respeccing of fieldtypes)

Got any ideas or questions regarding this, then let me know!

Raoul

Edit:

As an alternative, to reduce the overhead in fieldtypes; is there a way to tell SAS to make a field into an integer, float, double upon export?

[LinusH]

Is there any particular reason for storing the data in MS Access?

Event though I'm sure that Access have been improved over the years, I'm still thinking of it as a toy database.

Without knowing the purpose of your data base, one suggestion could be to keep the data in SAS, and access from your application via ODBC or OLEDB.

Or load it to SQL Server if you want to have that data in the MS world.

About file types - not sure about this particular case: SAS usually uses format to determine what target database type to use. The DBTYPE options lets you specify specific data types when creating tables in the target DBMS.

/Linus

[RFLinnenbank]

Thanks allready for your answer regarding DBTYPE; that might be practical.

The reason for Access (and an old fogey like 97 even) is definitely not because of the desire to do so.

Unfortunately this is about quite a lot of work in the past made using Access 97 due to lacking other tools for the job. Since pretty much all of these will become obsolete in the coming two years and there were a huge amount of 'm it's not really an option to change all of those.

Exporting in a format that allows me to export using an index that 'll be picked up by an Access link would also work. It is a horrid solution indeed, but the (unfortunate) goal is a linked table in those mini-mdbs that refers to a fixed file on a hard drive.

The entire office package here is (still) 97 even though over a decade has passed so conversion to at least a more sensible Access version or maybe Excel is not an option.

Raoul

[polingjw]

I don't think that the problem you are reporting is typical.  I sometimes export data to Access 2003 from SAS and tables that are about 150-200MB take only a couple of minutes on my very modest PC using the default options.  I typically create the table structure using Access and then use the ACCESS LIBNAME engine with PROC SQL INSERT statements to load the data into the tables.

[RFLinnenbank]

For up to 200 to 300 mb that is correct, but the general progress of writing to access appears to work as follows (over here):

Export to Access starts of really fast, but slows down ridiculously. 200MB takes approximately 10 minutes on one of the smaller ones, but the full required 699MB takes 3 hours. SAS starts taking heaps of memory and writing slows down tot 12 mb per couple of minutes.

The bigger ones at around 850 to 930 MBs take up to seven hour per piece..

It's like it is trying to sort all the records anew after inserting new ones or something like that.

This is identical in speed for all the mentioned options (PROC SQL, PROC EXPORT as wel as DATA STEP).

Edit: An export test to export to newer versions of access (even if those can't be opened here) did reveal that exporting to the newer Access versions slows down significantly less than the older versions.

[Doc_Duke]

The slowdown over time indicates that you need to drop any indices and referential integrity on the table before doing the data transfer.

[RFLinnenbank]

Wel, there should be no index on the source table, unless SAS secretly places those itself.

Not too sure what you mean by Referential Integrity in this situation?

Raoul

[Doc_Duke]

In MS Access, Referential Integrity is usually accomplished by labelling a field as unique.  If  you have a unique field, then any insert is preceeded by checking all previous rows for a duplicate.

[RFLinnenbank]

I left the creation of the table in Access in this example to SAS as well. (Maybe this becomes a part of the problem though?)

So unless SAS automagically adds Referential Integrity to either standard PROC SQL results (where the original tables came from) or export results there should not be any involved here.