How to Delete a Data Set in SAS

Delete a Data Set

Normally, SAS users remove data sets to create free space and/or get rid of old data. In this article, we explain different ways how to delete a data set in SAS. We show the syntax and examples.

Deleting with PROC DATASETS

The first option to delete a data set is with the PROC DATASETS statement. This option is convenient if you want to remove more than one data set of the same library. With the optional parameter library, you define from which library you want to delete your data. You can use the nolist option to prevent SAS to create a result page.

proc datasets library=library-name (nolist);
delete data-set-name-1 (data-set-name-2, …, data-set-name-n);
run;

Deleting with PROC SQL

The second way to delete your data is with PROC SQL. With this option, you can drop tables from different libraries within one drop statement. If you don’t specify the library name, SAS assumes that the table to drop is in the work library. To drop more than 1 table, the table names must be separated by a comma.

proc sql;
drop table libref.data-set-name-1 (, libref.data-set-name-2, …, libref.data-set-name-n);
quit;

Deleting with PROC DELETE

The third option to delete a SAS table is with the PROC DELETE statement. You can use this option to delete one or more tables from different libraries. You need to specify the library’s name if the data set is not in your work library. All tables that you want to delete are separated by a blank space.

proc delete data=libref.data-set-name-1 (libref.data-set-name-2 … libref.data-set-name-n);
run;

Special Ways to Delete Data Sets

Deleting All Data Sets with a Similar Name

In SAS you can delete all tables with a similar name from the same library in a convenient way. For example, suppose you have your sales data stored in a separate table per day. The table names could look like: sales_20190101, sales_20190102, …, sales_20191231. To remove all these tables in one action you can use the colon (:).

proc datasets library=work nolist;
delete sales_:;
run;

Check out this article for more use cases of the colon modifier.

Delete All Data Sets from a Library

If you want to delete all the tables from a library you can use the kill option of the PROC DATASETS statement. However, be careful! This action will delete all the data in a library without a warning.

proc datasets library=library-name kill nolist;
quit;

Show All Tables in a Library

In SAS you can use the PROC DATASETS statement to list all data sets from a library. This can be useful to check if you have successfully deleted your table. With the memtype option, you let SAS know to only show tables.

proc datasets library=library-name memtype=data;
run;

Examples: Deleting Tables in SAS

The code below shows how to delete a data set in SAS with the options discussed above. First, we create subsets of the CARS data set with the IF statement. Then we show one example of each option discussed above. After each example, we show which data sets exist in the work library.

/* CREATE DATA SETS*/
data work.make_audi 
	work.make_bmw 
	work.make_ford 
	work.make_mazda
	work.make_toyota
	work.type_sedan
	work.weight_over_4000;
	
	set sashelp.cars;
	
	if make = 'Audi' then output work.make_audi;
	if make = 'BMW' then output work.make_bmw;
	if make = 'Ford' then output work.make_ford;
	if make = 'Mazda' then output work.make_mazda;
	if make = 'Toyota' then output work.make_toyota;
	if type = 'Sedan' then output work.type_sedan;
	if weight > 4000 then output work.weight_over_4000;
run;
	
/* SHOW ALL DATA SETS IN WORK */
proc datasets library=work memtype=data;
run;



/* DELETE ONE DATA SET (I)*/
proc datasets library=work nolist;
	delete make_audi;
run;

/* SHOW ALL DATA SETS IN WORK */
proc datasets library=work memtype=data;
run;



/* DELETE ONE DATA SET (II)*/
proc sql;
	drop table work.make_bmw;
quit;

/* SHOW ALL DATA SETS IN WORK */
proc datasets library=work memtype=data;
run;



/* DELETE ONE DATA SET (III)*/
proc delete data=work.make_ford;
run;

/* SHOW ALL DATA SETS IN WORK */
proc datasets library=work memtype=data;
run;



/* DELETE ALL DATA SET WITH SIMILAR NAME */
proc datasets library=work nolist;
	delete make_:;
run;

/* SHOW ALL DATA SETS IN WORK */
proc datasets library=work memtype=data;
run;




/* DELETE ALL DATA SETS IN WORK */
proc datasets library=work kill nolist;
quit;

/* SHOW ALL DATA SETS IN WORK */
proc datasets library=work memtype=data;
run;

Note, the last PROC DATASETS statement to show all the existing tables in the work library gives a warning because all data sets are removed and SAS can’t show any table.

All the official SAS documentation to delete a data set with PROC DATASETS, PROC SQL and PROC DELETE can be found on the official website.