Dropping a table in SAS

Question

What is the most efficient way to drop a table in SAS?

I have a program that loops and drops a large number of tables, and would like to know if there is a performance difference between PROC SQL; and PROC DATASETS; for dropping a single table at a time..

Or if there is another way perhaps???

Answer 1

If it is reasonable to outsource to the OS, that might be fastest. Otherwise, my unscientific observations seem to suggest that drop table in proc sql is fastest. This surprised me as I expected proc datasets to be fastest.

In the code below, I create 4000 dummy data sets then try deleting them all with different methods. The first is with sql and on my system took about 11 seconds to delete the files.

The next two both use proc datasets. The first creates a delete statement for each data set and then deletes. The second just issues a blanket kill command to delete everything in the work directory. (I had expected this technique to be the fastest). Both proc datasets routines reported about 20 seconds to delete all 4000 files.

%macro create;
proc printto log='null';run;
%do i=1 %to 4000;
data temp&i;
x=1;
y="dummy";
output;run;
%end;
proc printto;run;
%mend;

%macro delsql;
proc sql;
%do i=1 %to 4000;
drop table temp&i;
%end;
quit;
%mend;

%macro deldata1;
proc datasets library=work nolist;
   %do i=1 %to 4000;
   delete temp&i.;
   %end;
run;quit;
%mend;

%macro deldata2;
proc datasets library=work kill;
run;quit;
%mend;

option fullstimer;
%create;
%delsql;

%create;
%deldata1;

%create;
%deldata2;

Answer 2

I tried to fiddle with the OS-delete approach.

Deleting with the X-command can not be recommended. It took forever!

I then tried with the system command in a datastep:

%macro delos;
data _null_;
do i=1 to 9;
delcmd="rm -f "!!trim(left(pathname("WORK","L")))!!"/temp"!!trim(left(put(i,4.)))!!"*.sas7*";
rc=system(delcmd);
end;
run;
%mend;

As you can see, I had to split my deletes into 9 separate delete commands. The reason is, I'm using wildcards, "*", and the underlying operating system (AIX) expands these to a list, which then becomes too large for it to handle...

The program basically constructs a delete command for each of the nine filegroups "temp[1-9]*.sas7*" and issues the command.

Using the create macro function from cmjohns answer to create 4000 data tables, I can delete those in only 5 seconds using this approach.

So a direct operating system delete is the fastest way to mass-delete, as I expected.