Export multiple SAS tables to Access

[Rebecca_LK]

Hi,

I want to export 10 tables from SAS to an Access database.  Names are similar, though not all (Color1, Color2, Color3, Colorall).

Other than writing 10 PROC EXPORT statements, what is an efficient way to export them?

Note, they do not represent all the tables in my SAS library, so I can't just export the entire library.  My backup plan would be to move them into a separate library and export that en masse, but I'm hoping people can give me a macro/other solution so I don't have to do that.

Thanks.

[ballardw]

If you have Access to PC Files licensed easiest would to assign a library to the Access data base such as:

LIBNAME MYDb ACCESS PATH='C:\PCFData\Demo.accdb';

Then the access db is accessible as any library and you could copy or move as to any library.

Even the basic data step.

Data MYdb.Thisset;

    set somelib.thisset;

run;

[Rebecca_LK]

That's a great feature. 

I just downloaded: https://support.sas.com/downloads/package.htm?pid=753#

But when I try to make the libname, using your code, it doesn't work--class name not registered (which I've solved before making it accesscs) and error in libname statement--even though I used the same path (without the Access database name) in another libname.

libname db 'G:\[long path]\database\';  *this one works fine;
libname mydb "G:\[same long path]\database\new review.accdb" ; *this one doesn't;

29   libname db 'G:\....
29 ! ...\database\';
NOTE: Libref DB was successfully assigned as follows:
      Engine:        V9
      Physical Name: G:\...
      ...\database
30   libname mydb "G:\...
30 ! ...\database\new review.accdb" ;
ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.

Can you help?

[Rebecca_LK]

Sorry--I altered the libname to test it the normal way.

When I used your code I got the same error message:

libname test access path= "G:\[long path]\new review.accdb" ;

ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.

[ballardw]

Run this code to see if you have Access to PC files actually installed:

proc product_status;run;

If you do not SAS/ACCESS Interface to PC Files or similar then it has not been installed.

Also there may be an issue with "bitness". Which version of Office / Access do you have installed and is it 32 or 64 bit?

[Rebecca_LK]

I just checked--it's installed.

For SAS/ACCESS Interface to PC Files ...
   Custom version information: 9.4

Access--is 32 bit!  SAS is 64 bit -- which is why I downloaded the 64 bit version.  I'll go install the 32 bit version now.

[Tom]

Sounds like you have 32 bit Access and 64 bit SAS.  Either use a SAS PCfiles server or install compatible versions of Microsoft Office and SAS.

[Rebecca_LK]

There was an error installing the regular PC Files Server, as well as the 64-bit (which I forgot about).

It said the installation may not have been done correctly.  I didn't change any settings on the installation wizard, so I'm not sure what to do now.

[Reeza]

Try it...

libname test pcfiles path='path to access db';

[Rebecca_LK]

I tried it with the 32 bit PC File Server, and it didn't work.  Got this error message:

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Microsoft Access
       Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for
       process 0x282c Thread 0x2f68 DBC 0x2dd7f0c
                        Jet'.
ERROR: Error in the LIBNAME statement.

Also, I tried uninstalling both the 32 and 64-bit downloads yesterday, and while the 32-bit successfully uninstalled, the 64 bit ran all night and still didn't finish downloading.

So I finally just wrote 10 proc export statements

I appreciate all your help and ideas.  I wish PC File Server worked, but it doesn't install correctly so I'm kinda stuck (I can't change the software on my work computer). 

I can't even export to Access--

Connection Failed.  See log for details.
NOTE: The SAS System stopped processing this step because of errors.

So I ended up importing in Access from SAS.  (I can export to Excel and then import that into Access, but that's twice the steps.)

Thanks, everyone.

[ballardw]

You should contactyour SAS Admin to get SAS tech support involved with install/ uninstall issues.