DATA Step, Macro, Functions and more

Import ACCESS to SAS 9.4 issue

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Import ACCESS to SAS 9.4 issue

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


Accepted Solutions
Solution
‎04-07-2017 09:58 AM
PROC Star
Posts: 7,468

Re: Import ACCESS to SAS 9.4 issue

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

 

View solution in original post


All Replies
Contributor
Posts: 54

Re: Import ACCESS to SAS 9.4 issue

I think sas 9.4 64 bit not compitable with office 32 bit. Please try upgrade office 64 bit.
Occasional Contributor
Posts: 5

Re: Import ACCESS to SAS 9.4 issue

Hi Yavuz,

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

 

Thanks,

Jing

PROC Star
Posts: 7,468

Re: Import ACCESS to SAS 9.4 issue

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

 

Occasional Contributor
Posts: 5

Re: Import ACCESS to SAS 9.4 issue

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

PROC Star
Posts: 7,468

Re: Import ACCESS to SAS 9.4 issue

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

 

Art, CEO, AnalystFinder.com

 

Valued Guide
Posts: 505

Re: Import ACCESS to SAS 9.4 issue

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;




Occasional Contributor
Posts: 5

Re: Import ACCESS to SAS 9.4 issue

Posted in reply to rogerjdeangelis
I tried it but still caused same error msg. Thank you.
Contributor
Posts: 54

Re: Import ACCESS to SAS 9.4 issue

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.
Valued Guide
Posts: 505

Re: Import ACCESS to SAS 9.4 issue

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;
');

Occasional Contributor
Posts: 5

Re: Import ACCESS to SAS 9.4 issue

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

Solution
‎04-07-2017 09:58 AM
PROC Star
Posts: 7,468

Re: Import ACCESS to SAS 9.4 issue

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

 

Contributor
Posts: 54

Re: Import ACCESS to SAS 9.4 issue

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)
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 380 views
  • 1 like
  • 4 in conversation