Import/Transfer Ms Access tables directly into SAS

[Anita_n]

Hi all, 

I have an access DB and I wish to write a sas macro which allows the user to directly import the data from access into sas. Is this possible at all?

I saw this code but its not working. I will appreciate any help

PROC IMPORT OUT=chemodaten
            TABLE='mySASTABLE'
            DBMS=ACCESSCS REPLACE;
   DATABASE="myAccessdb.accdb";
   RUN;

*I get this error message:;

ERROR: Failed to connect to the Server: &server.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.70 seconds
      cpu time            0.56 seconds

[Kurt_Bremser]

Your code is for use in conjunction with the SAS PC Files Server.

If SAS runs on Windows, and Access is installed there, and the accdb also resides on the same PC, then you can use DBMS=ACCESS.

[Anita_n]

I tried using DBMS=Access but it returns this error:  

 Unable to open file mypath\&myAccessdb.accdb. It does
       not  exist or it is already opened exclusively by another user, or you need permission to view its data.

[Kurt_Bremser]

If your SAS runs on a server, it may not have access to the same resources that you "see" from your desktop.

[Anita_n]

It doesn't run on a server. I don't really know if it's possible to import directly from access to sas

[andreas_lds]

---

@Anita_n wrote:

It doesn't run on a server. I don't really know if it's possible to import directly from access to sas

---

It is, if you have SAS Access To PC Files installed (and configured).

[Kurt_Bremser]

Then make sure that no MS Access instance is running when you try to import the accdb file, and that the path to the file is correct.

[Anita_n]

I tried that again but I get this error

PROC IMPORT OUT=myoutdata
           DATATABLE='myAccesstable'
           DBMS=ACCESS REPLACE;
    DATABASE="myAccessdb.accdb";
    RUN;

ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.
ERROR: Connection Failed. See log for details

[Kurt_Bremser]

This points to a bitness issue between MS Office and SAS (32 vs. 64), or the fact that no Office(Access) is installed on the SAS host. From your previous posts I take it that Access to PC Files is installed and licensed, but you can verify that by running PROC SETINIT and PROC PRODUCT_STATUS.

The most robust method to transfer data between applications is the use of a textual interface file format. CSV (comma-separated), tab-separated, fixed column width are all superior to the proprietary file formats.

[Anita_n]

Okay, I think is the issue of 64 and 32 bytes, its probably better to import using textual method

Thanks a lot

[Ksharp]

NO ';' in your code.

DBMS=ACCESS REPLACE;
--->
DBMS=ACCESS REPLACE