Proc Import MS Access and Excel

Reply
New Contributor
Posts: 4

Proc Import MS Access and Excel

We are converting from a 32-bit server to a 64-bit server as we go from SAS 9.1.3 to SAS 9.3. I'm having trouble using proc import for MS Access and Excel. I've tried many permutations and combinations with this code . . .

 

 

PROC IMPORT OUT=statmast

DBMS=ACCESSCS

DATATABLE='stations' REPLACE;

DATABASE='E:\Shellfish\Datasets\MDE Shellfish Master Station List and Rainfall.mdb' ;

SERVER='servername.mde.state.md.us';

PORT=9621;

RUN;

I get this error when I've put in the real server address both alphabetically and numerically.

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name

not found and no default driver specified

ERROR: Error in the LIBNAME statement.

Connection Failed. See log for details.

I have just been submitting this code solely. No Libname statement.

Also the MS Access file is MS Office 2003.

PROC Star
Posts: 7,364

Re: Proc Import MS Access and Excel

Depending upon which version of Office you have, the following might be of help: http://support.sas.com/kb/43/802.html

New Contributor
Posts: 4

Re: Proc Import MS Access and Excel

Arthur,

We had installed the PC files server and it still didn't work for us.

Elinor

PROC Star
Posts: 7,364

Re: Proc Import MS Access and Excel

Just out of curiosity, have you tried surrounding the database name with double rather than single quotes?

Super User
Posts: 17,899

Re: Proc Import MS Access and Excel

One thing to try is the libname method instead:

libname test access ''E:\Shellfish\Datasets\MDE Shellfish Master Station List and Rainfall.mdb';

And

libname testcs accesscs 'E:\Shellfish\Datasets\MDE Shellfish Master Station List and Rainfall.mdb';

Check the log and library and see if neither show your tables.

Because of the many variants that can affect access your best bet may be to contact tech support.

PROC Star
Posts: 1,236

Re: Proc Import MS Access and Excel

Hi,

PC Files Server can be tough to get working, but it should do the trick.

For Excel, PROC IMPORT should work if you specify dbms=xls, regardless of bitness issues.

HTH,

--Q.

Frequent Contributor
Posts: 78

Re: Proc Import MS Access and Excel

This worked for me, although I used dbms=xlsx for excel 2010.

Frequent Contributor
Posts: 75

Re: Proc Import MS Access and Excel

Even though this was a pretty old question, out of curiousity, was the E:\ drive mapped?

If it was, try the UNC path (I think this is what it's called).

Ask a Question
Discussion stats
  • 7 replies
  • 1095 views
  • 1 like
  • 6 in conversation