PROC EXPORT doesnt replace Access

[Gateux]

I am using exactly this type of macro:

%MACRO EXPORT_TABLE(table, database, name);
	PROC EXPORT
	DATA = WORK.&table.
	OUTTABLE = "&name."
	DBMS = ACCESS REPLACE;
	DATABASE = &database.;
	RUN;
%MEND;

%EXPORT_TABLE(Somename, "path.mdb", Anothername);

However, if the file already exists, SAS doesn't replace it, but makes the file bigger in size (number of rows/cols are preserved, but size of the file is higher). You can imagine how many problems this causes.

Is there any way, how to deal with this? I have tried NEWFILE = yes;, but it doesn't work with access databases.

Thank you

[Reeza]

Have you tried dropping the table first?

[Gateux]

Hi, thank you for reply. Could you be more specific about dropping the table? Access is first time referenced in the macro. Thanks

[Reeza · Accepted solution]

Create a libname reference to the Access DB. Then use PROC SQL or PROC DATASETS to delete the table in the ACCESS DB that you're not interested in and THEN do your creation/insert step.

My assumption - you have an Access DB and are replacing a table in the DB. If otherwise, provide more details on your question.

[Gateux]

Unfortunately, this doesn't work. I implemented it into the macro in this way:

%MACRO EXPORT_TABLE(table, database, name);
LIBNAME ACC &database.;

PROC DATASETS LIB = ACC NOPRINT;
        DELETE &name.;
QUIT;


PROC EXPORT
	DATA = WORK.&table.
	OUTTABLE = "&name."
	DBMS = ACCESS REPLACE;
	DATABASE = &database.;
RUN;
%MEND;

%EXPORT_TABLE(Somename, "path.mdb", Anothername);

Log says that the libname was successfully assigned, also 'Deleting ACC.tablename"... but still, the file size is increasing.

Edit:

When using with DIRECT_EXE and PROC SQL:

LIBNAME ACC &database. DIRECT_EXE = DELETE;

PROC SQL; DELETE FROM ACC.&name.; QUIT;

LIBNAME ACC CLEAR;

It seems like working. The table itself is not deleted but just cleared (ie it is empty). However, the size doesn't apparently change.

Thank you for the solution.