BookmarkSubscribeRSS Feed
Raj21
Calcite | Level 5

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. 

10 REPLIES 10
Reeza
Super User

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. 

Raj21
Calcite | Level 5

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? 

Reeza
Super User

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;

 

Raj21
Calcite | Level 5

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?

Reeza
Super User

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. 

Raj21
Calcite | Level 5
Thank you again very much for your assistance and I will test that!


Raj21
Calcite | Level 5

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?

Raj21
Calcite | Level 5

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. 

LinusH
Tourmaline | Level 20
The fact that there is a 2GB limitation tells you something. Don't use Access for large data sets.
What is the application?
Data never sleeps
Raj21
Calcite | Level 5
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 10 replies
  • 1478 views
  • 0 likes
  • 3 in conversation