BookmarkSubscribeRSS Feed
Tommy1
Quartz | Level 8

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

4 REPLIES 4
svh
Lapis Lazuli | Level 10 svh
Lapis Lazuli | Level 10

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. 

Tommy1
Quartz | Level 8

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!

LinusH
Tourmaline | Level 20
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.
Data never sleeps
Tommy1
Quartz | Level 8

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!

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1388 views
  • 0 likes
  • 3 in conversation