topic PROC EXPORT doesnt replace Access in New SAS User

PROC EXPORT doesnt replace Access

Gateux — Thu, 08 Nov 2018 18:02:02 GMT

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

Re: PROC EXPORT doesnt replace Access

Reeza — Thu, 08 Nov 2018 18:06:12 GMT

Have you tried dropping the table first?

Re: PROC EXPORT doesnt replace Access

Gateux — Fri, 09 Nov 2018 09:08:18 GMT

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

Re: PROC EXPORT doesnt replace Access

Reeza — Fri, 09 Nov 2018 16:00:21 GMT

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.

Re: PROC EXPORT doesnt replace Access

Gateux — Mon, 12 Nov 2018 16:20:12 GMT

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.