BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Arlan
Calcite | Level 5

I am attempting to load MS Access data onto a Linux SAS server (BI 9.2) but running into road blocks except when handled by EG.  Would really like to schedule these loads outside of EG.

1)  This works.  EG 4.2 allows me to load each of the tables using my local windows ODBC driver.  EG then allows me to insert into/update a SAS dataset.

2)  Attempted Proc Import out=work.activities dbms=access table="activities"; database="/var/sasextract/activity.mdb"

     This returns:  ERROR: DBMS type ACCESS not valid for import.

     I am running on a Linux server.  Found one hint for Windows 64 but not Linux with this error.

3)  Attempted server based ODBC connection.  Currently do not have a unixODBC driver for MS Access loaded on the Linux server.  This option works when used with MySQL ODBC driver and SAS/Access for ODBC.

4)  I also have access to DIS but it also appears to need a MS Access ODBC driver for Unix.

1 ACCEPTED SOLUTION

Accepted Solutions
Doc_Duke
Rhodochrosite | Level 12

The way I have done this with a Solairs SAS installation is a bit of a kluge, but it works.

1) Use EG to convert all the Microsoft Access tables to SAS datasets.

2) Set up the scheduler to run the project.

3) If you have a shared file space on the windows and Linux (Samba, say), then run your cron'd LinuxSAS programs a little while after the EG runs.

3a) If no, use FTP to move the files to the Linux server and run the LinuxSas programs.

The only place that this has tripped me up is that I have to remember to leave the PC running EG turned on at night.

Doc Muhlbaier

Duke

View solution in original post

6 REPLIES 6
andreas_lds
Jade | Level 19

1) EG has build-in ODBC support.

2) The import procedure requires SAS Access to PC File Formats, but with SAS on Linux a PC File Server on a Windows machine is also required.

3) + 4) You have already found the solution to your problem: installing unixODBC driver for MS Access.

Arlan
Calcite | Level 5

Thanks Andreas

I have tried the Proc Import and also the Libname options for accessing the database and SAS come back with the following

Proc Import out=work.activities dbms=ACCESScs table="activities";

         database="\\10.10.10.13\activity.mdb";

         replace table='activity';

         port=8621;

         server="my.web.server.com";

ERROR: DBMS type ACCESSCS not valid for import.

I also tried DMBS=ACCESS and received the same message.  We are using the 64 bit SAS (accesscs).

Run across another option for accessing data and tried the following

libname myref pcfiles server="my.web.server.com"

30         port=8621 path="activity/activity.mdb";

ERROR: The PCFILES engine cannot be found.

ERROR: Error in the LIBNAME statement.

So it appears my Linux install did not come with engines like a Windows 32 bit box would.  It appears I'm at a dead end with this option.

Option 3)  unixODBC for MS Access.  Do you/anyone have a recommended unixODBC for MS Access?  This appears to be my best option for automation.

Ksharp
Super User

Can save your access file as a xls file then use proc import again?

Or

proc setinit;run;

to see whether your site has product SAS/Access for ODBC.

Ksharp

Arlan
Calcite | Level 5

We will have lots of MS Access databases containing multiple tables, so it will be a time consuming manual step to convert each one, nightly to xls and then to SAS.  EG appears to be a more direct option and I have been able to load MS Access tables in the past.

Yes, we have SAS/Access Interface for ODBC.  Its on a Linux box which doesn't give me the MS Access ODBC driver by default.

Doc_Duke
Rhodochrosite | Level 12

The way I have done this with a Solairs SAS installation is a bit of a kluge, but it works.

1) Use EG to convert all the Microsoft Access tables to SAS datasets.

2) Set up the scheduler to run the project.

3) If you have a shared file space on the windows and Linux (Samba, say), then run your cron'd LinuxSAS programs a little while after the EG runs.

3a) If no, use FTP to move the files to the Linux server and run the LinuxSas programs.

The only place that this has tripped me up is that I have to remember to leave the PC running EG turned on at night.

Doc Muhlbaier

Duke

Arlan
Calcite | Level 5

Thanks Doc,

I missed seeing the scheduler in the past, so may have a option there.  If I get the EG project running on a VM, I may be able leave EG open so it can run as scheduled.  Will give it a try if I can't get the unixODBC option going.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 2999 views
  • 0 likes
  • 4 in conversation