topic How do I load microsoft access data into Linux SAS server (BI 9.2) in SAS Procedures

How do I load microsoft access data into Linux SAS server (BI 9.2)

Arlan — Fri, 24 Jun 2011 23:18:33 GMT

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.

How do I load microsoft access data into Linux SAS server (BI 9.2)

andreas_lds — Mon, 27 Jun 2011 11:02:00 GMT

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.

How do I load microsoft access data into Linux SAS server (BI 9.2)

Doc_Duke — Mon, 27 Jun 2011 13:34:43 GMT

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

How do I load microsoft access data into Linux SAS server (BI 9.2)

Arlan — Tue, 28 Jun 2011 20:56:08 GMT

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.

How do I load microsoft access data into Linux SAS server (BI 9.2)

Arlan — Tue, 28 Jun 2011 20:58:17 GMT

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.

How do I load microsoft access data into Linux SAS server (BI 9.2)

Ksharp — Wed, 29 Jun 2011 02:01:23 GMT

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

proc setinit;run;

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

Ksharp

How do I load microsoft access data into Linux SAS server (BI 9.2)

Arlan — Thu, 30 Jun 2011 17:06:03 GMT

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.