Transferring data from SAS to Access

[n6]

Both my SAS and my Microsoft Office are 64 bit so that's not the issue.  And I have PC Files so that's not the issue.

I'm making a little library of files to transfer a dataset from SAS to X and then from X back into SAS so that when I have to do it in the future, I just come to library of files and pick the right one and change the pathname and run it.  All has gone well so far with XLSX, CSV and TXT files.  For example, the following works fine.  The folder Transfer Test contains a SAS dataset named "class."

libname tt 'c:\Transfer Test';
PROC EXPORT
DATA = tt.class
OUTFILE = "C:\Transfer Test\class.xlsx"
DBMS = XLSX REPLACE ;
SHEET = "Class" ;
RUN;

Okay fine.  But when I try to do it with ACCESS I get an error.  Below is my code.  I've tried variations on it too.  I first made an empty ACCESS database named class.mdb so it would have something to point to.  Also, I've also done it for the ACCESS database with the suffix accdb, both in creating an empty one and in changing the code below so that it has accdb in the last line instead of mdb.  But I keep getting the dreaded "Connect: Class not registered. Error in the LIBNAME statement" error message.  After I figure out how to output from SAS to ACCESS I'd like to then input ACCESS into SAS, but I'm guessing that solving my current problem will also solve that.  Any help is appreciated.

libname tt 'c:\Transfer Test';

PROC EXPORT
DATA = tt.class
OUTTABLE = "Class"
DBMS = ACCESS REPLACE;
DATABASE = "C:\Transfer Test\class.mdb";
RUN;

[ChrisNZ]

Try

proc export data     = TT.CLASS
            dbms     = accesscs
            outtable = CLASS
            replace;
            database = "c:\dir\NewDB.accdb";
run;

See https://communities.sas.com/t5/General-SAS-Programming/LIBNAME-error/td-p/282394

[n6]

It doesn't work but the error message I get is different now.  Now it is as follows:

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.

[SASKiwi]

The ACCESSCS DBMS option uses the SAS PC Files Server Have you got that installed on your PC and working?

Explanation here: https://documentation.sas.com/?docsetId=acpcref&docsetTarget=p0psac3j16cioen1nq2hkwrnk55y.htm&docset...

[n6]

I'm pretty sure I have that installed but I'm going to investigate to be certain.

[n6]

I have it installed and the date is 2016, which sounds about right  But for the data accessed it has October 2019.  I can't remember specifically but that might have been when I was installing Windows 10.  I wonder if that could have done something.

The strange thing is that I have no problem with XLXS, CSV, TXT or DTA (Stata) files.

[SASKiwi]

Check Windows Task Manager to be sure it is running.

[n6]

I opened Task Manager and ran it again and got the same error.  I'd have been surprised if running it while Task Manager was open changed anything but I guess you never know.

[SASKiwi]

@n6  - You misunderstood what I said. I just wanted you to check if the SAS PC File Server was running or not by looking in Windows Task Manager. Of course having Task Manager open is not going to change anything when you run your program.

So is SAS PC File Server running?

[n6]

I don't see SAS PC File Server in the Task Manager.  I didn't know SAS PC File Server was something that had to be "running."  I thought you just downloaded it and it becomes part of SAS and SAS uses it whenever necessary.

If I Left Click on SAS PC File Server in the list of programs, nothing happens.

If I Right Click on SAS PC File Server in the list of programs, I get a lot of options but the only one that looks like running it is "Run as Administrator," which I can't do since I'm not an administrator, but which I could have an authorized person do for me when I'm at work during business hours.

Any suggestions?

[SASKiwi]

@n6  - I've never had much to do with it myself. I think we have it running on one of our SAS servers so I can check it out at work tomorrow.

Edit - From what I understand it needs to run as a Windows service on your PC (see the Services tab in Windows Task Manager). So that means it can only be started with Administrator rights. Please arrange this with an authorised person.

[ChrisNZ]

> ERROR: Error in the LIBNAME statement.

There is no libname in the code provided.

What are you doing?

Provide the full code and log please.

[n6]

Here is the full code, same as I posted above.  (But I did change the pathname and libname to make it more generic.)

libname tt 'c:\Transfer Test';

PROC EXPORT
DATA = tt.class
OUTTABLE = "Class"
DBMS = ACCESS REPLACE;
DATABASE = "C:\Transfer Test\class.mdb";
RUN;

And here is the full result from the Log window.  Note that the libname above doesn't matter.  If I make a temporary dataset named "class" and I put 'work.class" in the DATA line instead of "tt.class" I get the same result in the Log window.

ERROR: Connect: Class not registered
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 EXPORT used (Total process time):
real time 0.83 seconds
cpu time 0.06 seconds

[n6]

BTW, I see the solution you linked to is 3.5 years old.  I'm not 100% certain but I'm pretty sure that about a year ago this time I was doing this successfully on my computer.  And also, last fall I put Windows 10 on because the people at work were mandating it.  So I wonder if my current lack of success has to do with me switching to Windows 10.