topic Re: Import/Transfer Ms Access tables directly into SAS in SAS Programming

Import/Transfer Ms Access tables directly into SAS

Anita_n — Tue, 08 Dec 2020 14:17:42 GMT

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

Re: Import/Transfer Ms Access tables directly into SAS

Kurt_Bremser — Tue, 08 Dec 2020 14:26:33 GMT

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.

Re: Import/Transfer Ms Access tables directly into SAS

Anita_n — Tue, 08 Dec 2020 18:13:30 GMT

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.

Re: Import/Transfer Ms Access tables directly into SAS

Kurt_Bremser — Tue, 08 Dec 2020 20:17:40 GMT

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

Re: Import/Transfer Ms Access tables directly into SAS

Anita_n — Wed, 09 Dec 2020 07:31:36 GMT

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

Re: Import/Transfer Ms Access tables directly into SAS

andreas_lds — Wed, 09 Dec 2020 07:45:04 GMT

@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).

Re: Import/Transfer Ms Access tables directly into SAS

Kurt_Bremser — Wed, 09 Dec 2020 07:57:15 GMT

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.

Re: Import/Transfer Ms Access tables directly into SAS

Anita_n — Wed, 09 Dec 2020 09:05:57 GMT

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

Re: Import/Transfer Ms Access tables directly into SAS

Kurt_Bremser — Wed, 09 Dec 2020 10:07:04 GMT

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.

Re: Import/Transfer Ms Access tables directly into SAS

Anita_n — Wed, 09 Dec 2020 10:18:33 GMT

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

Thanks a lot

Re: Import/Transfer Ms Access tables directly into SAS

Ksharp — Wed, 09 Dec 2020 14:06:40 GMT

NO ';' in your code.

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