So, I've got 3 fairly large datasets (~ 1.5mil records each. 670mb total) that I need to be exported from SAS to Access as tables in a new database. We have code that is part of a weekly automated process, and like every other week the code bombs out with this error: ERROR: CLI execute error: [Microsoft][ODBC Microsoft Access Driver] System resource exceeded. Unfortunately, I have to use Access instead of a real database (it needs to be accessible to many other people who don't have/don't know other software), so I can't just upgrade to MySQL or anything. Any suggestions on how to work with/around this error? I'd be hugely thankful as this has been a pretty consistent thorn in my side!
It might help to provide the code you are using to connect SAS to Access.
Are your writing into an existing table or is SAS creating one from scratch? How many tables are already in the database when the SAS bit starts? At 670MB, assuming no expansion of fields on transfer to Access, you are already at nearly one-third of the allowed size of the database.
You don't mention the number of variables involved. If you are pushing the 255 variable limit occasionally that would be a concern as well.
Write to a text file and link the Access DB to the text file as the main source.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.