topic Re: Export SAS 9.4 file to Access .mdb in SAS Data Management

Export SAS 9.4 file to Access .mdb

mlogan — Mon, 07 Mar 2016 18:57:59 GMT

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,

Re: Export SAS 9.4 file to Access .mdb

Reeza — Mon, 07 Mar 2016 19:01:53 GMT

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.

Re: Export SAS 9.4 file to Access .mdb

Paul_OldenKamp_org — Mon, 07 Mar 2016 23:00:46 GMT

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

Re: Export SAS 9.4 file to Access .mdb

ballardw — Mon, 07 Mar 2016 23:22:42 GMT

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.

Re: Export SAS 9.4 file to Access .mdb

Paul_OldenKamp_org — Mon, 07 Mar 2016 23:52:29 GMT

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?

Re: Export SAS 9.4 file to Access .mdb

Reeza — Tue, 08 Mar 2016 02:37:00 GMT

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.

Re: Export SAS 9.4 file to Access .mdb

mlogan — Tue, 08 Mar 2016 18:07:14 GMT

Following code worked!

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