topic Re: Export multiple SAS tables to Access in SAS Procedures

Export multiple SAS tables to Access

Rebecca_LK — Tue, 08 Aug 2017 16:05:40 GMT

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.

Re: Export multiple SAS tables to Access

ballardw — Tue, 08 Aug 2017 16:10:50 GMT

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;

Re: Export multiple SAS tables to Access

Rebecca_LK — Tue, 08 Aug 2017 16:50:00 GMT

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?

Re: Export multiple SAS tables to Access

Rebecca_LK — Tue, 08 Aug 2017 16:51:58 GMT

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.

Re: Export multiple SAS tables to Access

ballardw — Tue, 08 Aug 2017 16:56:26 GMT

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?

Re: Export multiple SAS tables to Access

Rebecca_LK — Tue, 08 Aug 2017 17:02:50 GMT

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.

Re: Export multiple SAS tables to Access

Tom — Tue, 08 Aug 2017 17:06:43 GMT

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.

Re: Export multiple SAS tables to Access

Rebecca_LK — Tue, 08 Aug 2017 17:09:34 GMT

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.

Re: Export multiple SAS tables to Access

Reeza — Tue, 08 Aug 2017 23:29:46 GMT

Try it...

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

Re: Export multiple SAS tables to Access

Rebecca_LK — Wed, 09 Aug 2017 14:03:36 GMT

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.

Re: Export multiple SAS tables to Access

ballardw — Wed, 09 Aug 2017 16:00:33 GMT

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