topic Re: Import ACCESS to SAS 9.4 issue in SAS Programming

Import ACCESS to SAS 9.4 issue

jzhu3 — Thu, 06 Apr 2017 19:30:20 GMT

After upgraded SAS from SAS 9.3 to SAS 9.4. I couldn’t import ACCESS tables into SAS, both in code and in Import wizard. The error massager is:

Error in the LIBNAME statement.

Connect: Class not registered

Here is my code:

Libname test "I:\USERS\test"; run;

%let db="I:\USERS\raw data\2013\crash-database-2013.accdb";

PROC IMPORT OUT=dop

DATATABLE= "damageproperty"

DBMS=ACCESS REPLACE;

DATABASE=&db;

GETNAMES=YES;

SCANTEXT=YES;

USEDATE=YES;

SCANTIME=YES;

RUN;

It runs well in SAS 9.3 but not work on SAS 9.4. Did I miss something?

Thanks!

Jing

Re: Import ACCESS to SAS 9.4 issue

Yavuz — Thu, 06 Apr 2017 19:37:49 GMT

I think sas 9.4 64 bit not compitable with office 32 bit. Please try upgrade office 64 bit.

Re: Import ACCESS to SAS 9.4 issue

art297 — Thu, 06 Apr 2017 19:40:42 GMT

Have you tried the wizard using Microsoft Access Database on PC Files Server? The following note is for xls files, but probably applied to access as well. It's regarding using 32-bit Access on 64-bit SAS

Art, CEO, AnalystFinder.com

Re: Import ACCESS to SAS 9.4 issue

jzhu3 — Thu, 06 Apr 2017 19:56:17 GMT

Yes, I did. But still didn't work. I imported Excel data into SAS 9.4 by coding. it works only changing DBMS=xlsx;

Thanks,

Jing

Re: Import ACCESS to SAS 9.4 issue

jzhu3 — Thu, 06 Apr 2017 20:00:42 GMT

Hi Yavuz,

My office is using Access 2016. Just upgrade to 64 bit.

Thanks,

Jing

Re: Import ACCESS to SAS 9.4 issue

art297 — Thu, 06 Apr 2017 20:04:26 GMT

Have you tried opening the files in Access and then saving them as 64-bit files?

Art, CEO, AnalystFinder.com

Re: Import ACCESS to SAS 9.4 issue

Yavuz — Thu, 06 Apr 2017 20:05:25 GMT

Dear jzhu3, if its possible, could you please check office 2016 youre usins is 32 bit or 64 bit. Because i got many problem with this issue and all of them is related with office version. Thanks.

Re: Import ACCESS to SAS 9.4 issue

rogerjdeangelis — Thu, 06 Apr 2017 20:07:18 GMT

Import ACCESS to SAS 9.4 issue

this message
https://goo.gl/KgY15M
https://communities.sas.com/t5/Base-SAS-Programming/Import-ACCESS-to-SAS-9-4-issue/m-p/347889

HAVE
====

 sas has a Access tabe at

 C:\Program Files\sashome\SASFoundation\9.4\access\sasmisc\demo.mdb

Libref         MDB
Engine         ACCESS
Physical Name  C:\Program Files\sashome\SASFoundation\9.4\access\sasmisc\demo.mdb
User           Admin
                                                 DBMS
              Member                Member
#  Name       Type     Vars  Label  Type

1  Customers  DATA      10          TABLE
2  Employees  DATA      11          TABLE
3  Invoice    DATA       8          TABLE
4  Orders     DATA      10          TABLE

WANT
====

Up to 40 obs WORK.INVOICE total obs=17

                               AMOUNT_BILLED_                    AMOUNT_
       INVOICE_    BILLED_        IN_LOCAL_                     BILLED_IN_
Obs       ID          TO          CURRENCY       COUNTRY        US_DOLLARS

  1      11270     39045213     8738600640.00    Brazil       3675848666.21
  2      11271     18543489       11063836.00    USA            11063836.00
  3      11273     19783482         252148.50    USA              252148.50
  4      11276     14324742        1934460.00    USA             1934460.00
  5      11278     14898029        1400825.00    USA             1400825.00
  6      11280     39045213     8738600640.00    Brazil       3675848666.21
  7      11282     19783482         252148.50    USA              252148.50
  8      11285     38763919        2234301.30    Argentina        772847.05

*          _       _   _
 ___  ___ | |_   _| |_(_) ___  _ __
/ __|/ _ \| | | | | __| |/ _ \| '_ \
\__ \ (_) | | |_| | |_| | (_) | | | |
|___/\___/|_|\__,_|\__|_|\___/|_| |_|

;

libname mdb "C:\Program Files\sashome\SASFoundation\9.4\access\sasmisc\demo.mdb";

data invoice;
  set mdb.invoice;
run;quit;

Re: Import ACCESS to SAS 9.4 issue

rogerjdeangelis — Thu, 06 Apr 2017 20:13:08 GMT

If you have IML/R interface you can use RODBC. I think the 32bit driver here handles both 32 an64 bit access.

I use WPS but you can use IML/R

%utl_submit_wps64('
options set=R_HOME "C:/Program Files/R/R-3.3.1";
libname wrk "%sysfunc(pathname(work))";
proc r;
submit;
library(RODBC);
myDB<-odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=d:/mdb/HelpfulContacts.mdb");
mtcarsmdb<-sqlQuery(myDB, paste("select * from mtcars"));
mtcarsmdb;
close(myDB);
endsubmit;
import r=mtcarsmdb data=wrk.mtcarsmdb;
run;quit;
');

Re: Import ACCESS to SAS 9.4 issue

jzhu3 — Thu, 06 Apr 2017 20:45:07 GMT

Hello Yavuz,

Checked the Office 2016 and it's 32 bits version. It maybe the problem. How do you solve those bits issue?

Thanks a lot!

Jing

Re: Import ACCESS to SAS 9.4 issue

art297 — Thu, 06 Apr 2017 20:49:01 GMT

Do you have the right bit pc files server version? Take a look at: http://support.sas.com/documentation/cdl/en/pcfsicg/69732/PDF/default/pcfsicg.pdf

I think that will be your solution.

Art, CEO, AnalystFinder.com

Re: Import ACCESS to SAS 9.4 issue

Yavuz — Thu, 06 Apr 2017 20:49:27 GMT

Hi Jing, i guessed it. You must install 64 bit office. But please know that when you install 64 bit office then sas 4.0 will work fine but same problem may occure with sas 9.3 (couse of the 32 bit)

Re: Import ACCESS to SAS 9.4 issue

jzhu3 — Fri, 07 Apr 2017 14:15:19 GMT

I tried it but still caused same error msg. Thank you.