How to Drop Tables (SAS / SQL)

There are times when you may be running a macro which generates a series of data sets, with each prior data set being utilized to create the newest iteration. If this is the case, with the creation of each new set, the prior set’s continued existence is un-necessary. While this can be handled though manual deletion after the completion of the macro, if hard drive space is a concern, it may be optimal to include a drop statement within your macro code. A drop statement is a native SQL statement, however, it can be utilized in SAS through the usage of PROC SQL.

For example, if you wanted to delete the data set, TABLEA, you would only need to run this simple line of code:

Proc SQL;
Drop table TABLEA;
Quit;

PROC SQL invokes the SAS library pertaining to SQL, DROP TABLE specifies the table to be deleted, and QUIT ends the SQL emulation. It is important not to neglect the QUIT statement. If neglected, the SAS compiler will continue to run in the background of the SAS platform while you are working.

I often utilize the drop statement, even when not running macros, simply because it is easier for me to keep track of data sets that I have deleted. Additionally, if space is a concern, and you need to re-compile your code from top to bottom, the drop statement will make sure that your drive does not run out of memory during the compilation phase.

A best practice that I recommend, when using the DROP statement within your code, is to notate exactly why you are dropping the tables that your statement specifies.

Ex.

Proc SQL; /* Tells SAS that we will be utilizing SQL functionality */

Drop table TABLEA, TABLEB, TABLEC; /* Drop table(s) TableA, TableB, TableC. These tables are combined in TableD  (in the case of this example). */

Quit; /* End compiling/processing */

Hopefully you have found this article helpful. Remember, even if you are dropping multiple tables, the SQL statement will always read: DROP TABLE. The plural, "tables", even though grammatically correct, never applies.