BookmarkSubscribeRSS Feed
Rebecca_LK
Calcite | Level 5

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.

10 REPLIES 10
ballardw
Super User

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
Calcite | Level 5

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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
Super User Tom
Super User

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
Calcite | Level 5

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
Super User

Try it...

 

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

Rebecca_LK
Calcite | Level 5

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
Super User

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

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1426 views
  • 0 likes
  • 4 in conversation