topic Create Linked Table in MS Access From SAS Dataset? in SAS Programming

Create Linked Table in MS Access From SAS Dataset?

Tommy1 — Wed, 11 Jul 2018 19:54:27 GMT

Hi SAS Community,

I am trying to create a linked table in Microsoft Access that references a SAS data set. Through the SAS/Access engine I am able to pull in data from MS Access into SAS, but I can't figure a way to have it go the other way around. I want to create a linked table in Access because MS Access has data limits of 2GB, and the SAS dataset that I am working with is about 6GB.

If anyone knows a way that I would be able to do this it would be very helpful.

Thanks for the help,

Tom

Re: Create Linked Table in MS Access From SAS Dataset?

svh — Thu, 12 Jul 2018 18:55:36 GMT

You first need to create the empty Access database somewhere on your computer/network share. You will need the path and name of database for the next step.

You can then create an Access database from tables in a SAS library with code like this:

libname your_data ACCESS 'C:/PATH/test.accdb';

Next, you can copy some tables from a pre-defined library into your library that is actually the Access database.

proc copy in=library_name out=your_data;
 select table_for_database;
 run;

However, the Access database will not linked to your SAS library in the true sense of the word because if someone is using the database, and you make changes to the tables in SAS, the user will not automatically see the changes. In addition, Access will not support views that you may create via the CREATE VIEW statement in PROC SQL.

Re: Create Linked Table in MS Access From SAS Dataset?

Tommy1 — Thu, 12 Jul 2018 20:21:56 GMT

Thank you for answering. This is not exactly what I am trying to do, but this is a clever solution that I may be able to utilize for another analysis in the future. As you said in your post, the Access database is not linked to the SAS library like I had hoped I would be able to do. This is basically just creating a table in Access. Unfortunately as I said, my data is over 6GB while Access can only hold up to 2GB. Thank you so much for trying to help!

Re: Create Linked Table in MS Access From SAS Dataset?

LinusH — Fri, 13 Jul 2018 06:03:42 GMT

I don't think that SAS supports this feature.
SAS has ODBC and OLEDB drivers though you could use to access SAS data. But if the MS Access and SAS resides on different hosts additional licences are required.

Re: Create Linked Table in MS Access From SAS Dataset?

Tommy1 — Fri, 13 Jul 2018 17:47:35 GMT

Thanks for letting me know that SAS may not support this.

Is there a way using the ODBC driver to create an ODBC data base because there is a possibility I might be able to connect to that. I don't really understand how ODBC works. I am trying to read the documentation and Google what is happening in the background, but I still am confused and can't figure out how everything interacts together. It may not be possible as you said, but I was just trying to read up on the ODBC connection just in case that was a possibility.

Thanks for all the help!