Export SAS 9.4 file to Access .mdb

[mlogan]

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,

[Reeza]

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. 

[mlogan · Accepted solution]

Following code worked!

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

[Paul_OldenKamp_org]

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

[ballardw]

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.

[Paul_OldenKamp_org]

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?

[Reeza]

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.