BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mlogan
Lapis Lazuli | Level 10

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,

 

1 ACCEPTED SOLUTION

Accepted Solutions
mlogan
Lapis Lazuli | Level 10

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

6 REPLIES 6
Reeza
Super User

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
Lapis Lazuli | Level 10

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
Obsidian | Level 7

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

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
Obsidian | Level 7

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

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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