SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Export SAS 9.4 file to Access .mdb

Accepted Solution Solved
Reply
Regular Contributor
Posts: 215
Accepted Solution

Export SAS 9.4 file to Access .mdb

Hi All,

Is there a way to export SAS 9.4 table to Access .mdb file. I know it is an old format, but need that if anyone can help. 

 

 

I have the following code that works with excel, but I need to know how to export it to .mdb file.

 

proc export outfile="want.xlsx"
dbms=xlsx replace
data=have;
sheet='test';
run;

 

 

Thanks,

 


Accepted Solutions
Solution
‎06-28-2017 11:12 AM
Regular Contributor
Posts: 215

Re: Export SAS 9.4 file to Access .mdb

Following code worked!

 

PROC EXPORT DATA=Apple.student_e_c_s_t
OUTTABLE='Employees'
DBMS=ACCESSCS REPLACE;
DATABASE='E:\GW\test.mdb';
run;

View solution in original post


All Replies
Super User
Posts: 17,775

Re: Export SAS 9.4 file to Access .mdb

Change DBMS to ACCESS and the extension. 

 

If you use the GUI, File>Export Data you will be able to save the code and see the specifications. 

Solution
‎06-28-2017 11:12 AM
Regular Contributor
Posts: 215

Re: Export SAS 9.4 file to Access .mdb

Following code worked!

 

PROC EXPORT DATA=Apple.student_e_c_s_t
OUTTABLE='Employees'
DBMS=ACCESSCS REPLACE;
DATABASE='E:\GW\test.mdb';
run;

Contributor
Posts: 20

Re: Export SAS 9.4 file to Access .mdb

Hi,

I think Reeza's answer may not work.  I tried it in University Edition and got an error:

ERROR: DBMS type ACCESS not valid for export.

 

I have the SAS PC File Server installed and can export from Univ. Edition to an mdb file through the PC File Server with this code:

proc export 
    table="class"
    dbms=ACCESSCS replace
    data=sashelp.class;
    database="C:\SASUniversityEdition\myfolders\class.mdb";
    server="&server_name";
run;

Paul

Super User
Posts: 10,476

Re: Export SAS 9.4 file to Access .mdb

Try something like:

PROC EXPORT DATA=yourlib.yourdata
            OUTTABLE='AccessDataTablenameHere'
            DBMS=ACCESS97 REPLACE;
   DATABASE='c:\path\test97.mdb';
RUN;

 

Or Access2003 instead of 97

 

Also, be aware that your SAS datasets may have more variables then Access will accept.

Contributor
Posts: 20

Re: Export SAS 9.4 file to Access .mdb

I still get the error when I try dbms=access97 or access2003 in University Edition.  I don't have access to a SAS installation with SAS/ACCESS for PC Files on Windows or UNIX except for University Edition.  Do the dbms options ACCESS, ACCESS97 and ACCESS2003 work on traditional SAS installations but not Unversity Edition in 9.4?

Super User
Posts: 17,775

Re: Export SAS 9.4 file to Access .mdb

SAS UE doesn't support Access DB by default though it seems you've found a workaround.

The OP didn't mention SAS UE so I'm not sure why you feel it's relevant to the question.
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 1315 views
  • 3 likes
  • 4 in conversation