topic Re: Access Size Limitation in SAS Data Management

Access Size Limitation

Raj21 — Sat, 23 Jul 2016 03:33:58 GMT

I have been trying to export using proc export for a table that has approximately 20 variables and 15 million observations. Every time i try to write an access database, i am running into the 2GB size limitation. The file is already being compressed by SAS automatically. Is there a way that i can write to one access database and then tell SAS to essentially stop and create another access database. I would just want this to keep going until it is done and then I can link all of these databases and hook up my software application to ping against all of these.

Access SAS export

Raj21 — Sat, 23 Jul 2016 03:41:38 GMT

I am running into size limitations with proc export to access. I was wondering if there is any way that i could write up to the 2GB limitation and then start a new access database immediately. I would just want this to keep going until everything is written and then i will link the access databases after that.

I am trying to create one database that the software that i am utilizing can ping over and over to gather the necessary information.

Re: Access Size Limitation

Reeza — Sat, 23 Jul 2016 03:44:45 GMT

You can split your file and export it a few times. There isn't an automatic way to do that but it should be relatively easy to create a partitioned file.

Do you have any idea how many records fit in 2GB? Then you can split it efficiently.

Is there any other format your software can use to avoid this, obvious question I know.

The 2GB is an Access limitation.

Re: Access Size Limitation

Raj21 — Sat, 23 Jul 2016 03:54:53 GMT

I have not partitioned an access database before, but i can read up on this to figure it out. Would i essentially be telling SAS to write 1-1000000 observation and then 1000001-2000000 would be the next set?

Re: Access Size Limitation

Raj21 — Sat, 23 Jul 2016 03:58:48 GMT

Also when you use the insert buff command like so.

PROC EXPORT DATA=WORK.xxxxxxxx

OUTTABLE='xxxxxxxx'

DBMS=ACCESSCS REPLACE;

DATABASE='C:\xxxxxx\xxxxx\xxxxxxxxx.mdb';

SERVER='xxxxxxx';

PORT=xxxx;

DBDSOPTS='INSERTBUFF=15';

RUN;

if i increase this to 100, could this speed up the writing of the database?

Re: Access Size Limitation

Reeza — Sat, 23 Jul 2016 03:58:58 GMT

Essentially yes, but you can use FIRSTOBS and OBS options in your export statement.

proc export data=firstpart(obs=100000) outfile=... dbms=access;run;

proc export data=secondpart(firstobs=100001 obs=200000) outfile=... dbms=access;run;

Re: Access Size Limitation

Raj21 — Sat, 23 Jul 2016 04:07:48 GMT

This is so awesome Reeza, thank you so much! My other question is this. I posted it wrong before. Still learning how to do stuff on this community. 🙂

Also when you use the insert buff command like so.

PROC EXPORT DATA=WORK.xxxxxxxx

OUTTABLE='xxxxxxxx'

DBMS=ACCESSCS REPLACE;

DATABASE='C:\xxxxxx\xxxxx\xxxxxxxxx.mdb';

SERVER='xxxxxxx';

PORT=xxxx;

DBDSOPTS='INSERTBUFF=15';

RUN;

if i increase this to 100, could this speed up the writing of the database?

Re: Access Size Limitation

Reeza — Sat, 23 Jul 2016 04:26:27 GMT

No idea about the buffer part. You could test it on a data set with 500k records and see what happens.

You can also examine your data structure to see if you can reduce the size of database. If you have text fields for example make sure there the shortest length required.

Re: Access Size Limitation

Raj21 — Sat, 23 Jul 2016 04:28:53 GMT

Thank you again very much for your assistance and I will test that!

Re: Access Size Limitation

LinusH — Sat, 23 Jul 2016 06:39:00 GMT

The fact that there is a 2GB limitation tells you something. Don't use Access for large data sets.
What is the application?

Re: Access Size Limitation

Raj21 — Sat, 23 Jul 2016 15:44:20 GMT

I wouldn't use it but unfortunately the software is requiring that. It is transportation modeler supplied by JDA. I'm still in the process of investigating if I can use something else instead of access.