SAS cannot export bit .mdb file

[Gateux]

I have a table with 87 columns and 3,924,060 rows, which I am trying to export as MDB Access File with PROC EXPORT (DBMS = ACCESS REPLACE).

The PROC EXPORT settings should be correct as the code works correctly with other tables, however, with this one, SAS stops with this error:

ERROR: Execute: Unable to insert row
WARNING: File deletion failed for _IMEX_.MyDataTable.DATA.
ERROR: Export unsuccessful.  See SAS Log for details.

The .mdb file produced is little more than 2gb in size, with exactly 2,621,000 rows. I can see that some variables, which are obviously integers, are exported as double. This can be the cause that file is larger than it should be.

Also, I don't see a reason why should have SAS problem with exporting big table into ACCESS.

I should note that I work with 32bit version of SAS 9.4, Windows 7, MS Office 2013. (Problem persists also with 64bit SAS).

So I have two questions:

* Can I force SAS to export variable into Access as Integer, not as double?

* Why does SAS in some point stop exporting the data?

Thank you

[RW9 · Accepted solution]

Access itself has limitations (its what comes from using a mickey mouse database):
http://webcheatsheet.com/SQL/access_specification.php

Most likely your hitting one of those limits.  For that kind of data I would strongly advise you to use another datasource/database.

[Kurt_Bremser]

---

@Gateux wrote:

I have a table with 87 columns and 3,924,060 rows, which I am trying to export as MDB Access File with PROC EXPORT (DBMS = ACCESS REPLACE).

The PROC EXPORT settings should be correct as the code works correctly with other tables, however, with this one, SAS stops with this error:

ERROR: Execute: Unable to insert row
WARNING: File deletion failed for _IMEX_.MyDataTable.DATA.
ERROR: Export unsuccessful.  See SAS Log for details.

The .mdb file produced is little more than 2gb in size, with exactly 2,621,000 rows. I can see that some variables, which are obviously integers, are exported as double. This can be the cause that file is larger than it should be.

Also, I don't see a reason why should have SAS problem with exporting big table into ACCESS.

I should note that I work with 32bit version of SAS 9.4, Windows 7, MS Office 2013. (Problem persists also with 64bit SAS).

So I have two questions:

* Can I force SAS to export variable into Access as Integer, not as double?

* Why does SAS in some point stop exporting the data?

Thank you

---

From https://www.mrexcel.com/forum/microsoft-access/687514-access-32bit-vs-64bit.html I take it that 32-bit Access has a filesize limitation of 2GB.

For data interchange, I always (and most of the senior users her also do) prefer a text-based file format, either some kind of character-separated, or fixed column width. Files like that can be read by any application, and you don't have to rely on how good one piece of software understands the quirks of another.

[Gateux]

Thank you for suggestions. Regarding the form of the output - I am limited by the company process, so .mdb must be preserved.

I have found that I can shrink the size of the output by adjusting the formats of the variables (obviously, when the format of numeric variable is for example BEST12., it is read into Access as 'double', but after converting format to 8., Access reads it as 'integer').