Learning SAS? Welcome to the exclusive online community for all SAS learners.

SAS University Edition Access Table Errors

Reply
Occasional Contributor
Posts: 12

SAS University Edition Access Table Errors

Hello

    Since SAS UnivED does not appear to allow ODBC connection to databases, I added the Northwind Access db to my 'infolder'. I tried to read the customer table from it using Proc Import and received the message that ACCESS is not valid for import. Snapshot of log and Studio page is included.

Is it possible for the UnivED to read from RDBMS's, and if so is there a specfic method for doing so?

SAS UnivEd Access Tst.jpg

Super User
Posts: 19,832

Re: SAS University Edition Access Table Errors

Yes, you should be able to access a MS Access DB.

Try DBMS=AccessCS

Occasional Contributor
Posts: 12

Re: SAS University Edition Access Table Errors

Hello Reeza

  Sorry for my delay in thanking you for your help!  I did resolve this issue by using your suggestion combined with the following..

   1) Downloaded and installed pcfiles server on host machine.  After this, it still did not work until I..

   2) Changed from SAS University Edition for Oracle VM to the VMPlayer version.

Worked first attempt after that.

Thanks

Occasional Contributor
Posts: 12

Re: SAS University Edition Access Table Errors

Hello Reeza

   I forgot to mention that an error still occurs when trying to Proc Import an  Access .accdb, but no issue with .mdb. 

Super User
Posts: 19,832

Re: SAS University Edition Access Table Errors

What DBMS are you using for the two different files, I'm pretty sure they require different ones.

Occasional Contributor
Posts: 12

Re: SAS University Edition Access Table Errors

Tried ACCESS and ACCESSCS for both, only ACCESSCS works. See below.


/*    Using .accdb  results in error     */
/*****************************************/
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
42 ;
43 PROC IMPORT OUT=work.Customers3
44 TABLE='CUSTOMERS'
45 DBMS=ACCESSCS REPLACE;
46 DATABASE='C:\Users\GeraldLap\Documents\Northwind1.accdb';
47 SERVER='GeraldLap-PC';
48 RUN;
ERROR: CLI error trying to establish connection: [Microsoft][ODBC Microsoft Access Driver] Cannot open database '(unknown)'. It
may not be a database that your application recognizes, or the file may be corrupt.
ERROR: Error in the LIBNAME statement.
Connection Failed. See log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 15.02 seconds
cpu time 0.03 seconds

/*  Using .mdb succeeds!                     */
/********************************************/

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
42 ;
43 PROC IMPORT OUT=work.Customers3
44 TABLE='CUSTOMERS'
45 DBMS=ACCESSCS REPLACE;
46 DATABASE='C:\Users\GeraldLap\Documents\Nwind.mdb';
47 SERVER='GeraldLap-PC';
48 RUN;
NOTE: WORK.CUSTOMERS3 data set was successfully created.
NOTE: The data set WORK.CUSTOMERS3 has 91 observations and 11 variables.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 2.97 seconds
cpu time 0.21 seconds

Ask a Question
Discussion stats
  • 5 replies
  • 656 views
  • 6 likes
  • 2 in conversation