BookmarkSubscribeRSS Feed

How to delete SAS data sets

Started ‎02-20-2021 by
Modified ‎11-03-2022 by
Views 119,551

SAS data sets are a frequent by-product of SAS programs. While we often keep these data sets as assets for use in reports and other purposes, eventually you will have data sets that no longer "spark joy" and you'll decide that it's time to delete them. Deleting data sets that you no longer need can save space and memory during processing, and it's best practice to clean up. In this article I'll discuss several ways to delete SAS data sets. (I'm not going to presume that these are all the methods you can use, but these are the most common in my experience.)

 

PROC DELETE - quick and clean

Like most tasks in SAS, "there's a PROC for that." PROC DELETE has one job and it does it efficiently.

 

To delete a single data set named WORK.DATA1:

proc delete data=work.data1; run;

 

To delete multiple named data sets, simply add them in the DATA= option: 

proc delete data=work.a1 work.a2 ; run;

 

To delete a range of data sets named in WORK that are named in sequence, for example DATA1 through DATA5 (and here I'm using the LIBRARY= option so I can omit the libname on the DATA=):

proc delete library=work data=DATA1-DATA5; run;

 

Alas, it does not support "wildcard" colon-list matching for data set names, but read on for how you can accomplish this in PROC DATASETS.  PROC DELETE has several options for different member types and generation data sets -- see the documentation.

 

Note: for many years PROC DELETE existed only as a mythical undocumented procedure. Beginning with SAS 9.4, it was brought into real life by the sheer will of SAS programmers and an enterprising SAS R&D developer. It's official now.

 

PROC DATASETS using the DELETE statement

PROC DATASETS is a versatile utility procedure for "all things data sets" -- you can use it to copy, move, modify attributes, and of course...DELETE.

 

To delete three data sets named A1, A2, and A3 from the WORK library:

proc datasets nolist lib=work;
 delete a1 a2 a3 ;
quit;

 

Or you can use the colon list (or range list) syntax:

proc datasets nolist lib=work;
 delete a: ;    /* all data sets that begin with "A" */
 delete a1-a3;  /* or data sets in this range */
quit;

 

Note that I used the NOLIST option. That prevents the DATASETS procedure from generating a verbose output showing what's now in your SAS library.  By default, PROC DATASETS is quite chatty -- but you can limit the output using NOLIST and NODETAILS.

 

Delete an entire library with PROC DATASETS KILL

If you are absolutely certain that you can discard all data sets within a given SAS library, you can use the KILL option.

/* delete all data files from WORK */
proc datasets nolist lib=work kill;
quit;

 

Because the WORK library often contains data related to work-in-progress, sometimes I will create a library in a temp location that's specifically for transient data, which I can then "kill" when that part of the program completes.

/* create a subfolder in WORK for my data */
options dlcreatedir;
libname scratch "%sysfunc(getoption(WORK))/scratch";

/* just adding some data for example purpose */
data scratch.a1 scratch.a2 scratch.a3;
 set sashelp.class;
run;

/* more operations with scratch data */

/* now clear it when ready */
proc datasets lib=scratch kill nolist;
quit;

 

Delete all but a few with PROC DATASETS SAVE statement

What if you want to delete most of the data from a library, but you want to spare just a few lucky files? You can use the SAVE statement.

 

Here's my previous KILL example, modified to use SAVE instead and save just one data set:

/* just adding some data for example purpose */
data scratch.a1 scratch.a2 scratch.a3;
 set sashelp.class;
run;

/* now delete all but A1 */
proc datasets lib=scratch nolist;
 save a1;
quit;

 

I know...the syntax might seem odd because it doesn't reflect your intent to delete everything else...but that's how it works.  Here's the SAS log from those statements:

NOTE: Saving SCRATCH.A1 (memtype=DATA).
NOTE: Deleting SCRATCH.A2 (memtype=DATA).
NOTE: Deleting SCRATCH.A3 (memtype=DATA).

 

There is more to PROC DATASETS for managing the lifecycle of SAS data sets.  Check out the excellent paper by @MMMIIIIKKKKEEEE: PROC DATASETS: The Swiss Army Knife of SAS Procedures.

 

Delete within PROC SQL using DROP TABLE

Finally, here's something for the SQL coders.  If you're in the throes of a PROC SQL sequence and you want to clean up some data, you can use the DROP TABLE statement:

proc sql;
 drop table work.a1;
 drop table work.a2;
 drop table work.a3;
quit;

 

This might make even more sense if your data is in a database that you're accessing through a SAS/ACCESS engine -- PROC SQL should send the appropriate command to the database to remove the data.  There's also a DROP VIEW and DROP INDEX statement you can use for those related data types.

Use the %DELTABLE autocall macro

SAS includes a clever autocall macro that you can use to automate the PROC SQL DROP TABLE approach. %DELTABLE allows you to list multiple tables, and it's smart enough to detect whether the data file exists and whether it's a table or view...and then generate the correct statements for you.

 

%deltable (tables=scratch.a1 scratch.a2 scratch.a3)

 

See also

How to delete temporary tables in CAS (SAS Viya)

Comments

Chris,

Nice blog on the several ways SAS programmers can send their unneeded SAS data sets to that Big Bit Bucket in the Sky!

Thank you for mentioning my PROC DATASETS paper.  It has been my most popular paper by far.

Best wishes!

--Michael

Pretty much have only ever used Proc Datasets; with Deletes ... 

but all these other ways, wow! 
mold SAS code dogs can learn new tricks. Thanks. 
P.S. MMMMIIIIKKKKEEEE, was that really your most popular paper???

And if you want to get rid of a dataset that another user has open (and have the luxury of working on UNIX), attend my presentation at SASGF2021!

Thank you Chris, with some delay. Excellent recap. This is mandatory reading for SAS coders, accumulation of unneeded data has become so prevalent as a kind of misguided practice ("keep it always", "never delete", "just in case", "storage is costless" etc....) ; now with your article, ignorance won't be an excuse anymore ;-). 

As a side note, there is also some performance issues sometimes between Proc Delete / {Proc Datasets + Delete} to consider, this paper provides a good understanding for it  :  

 

https://www.mwsug.org/proceedings/2013/BB/MWSUG-2013-BB09.pdf 

Thanks @ronan - yes, PROC DELETE "shoots first and asks questions later" -- thus doesn't carry the same overhead as PROC DATASETS + DELETE when processing lots of data set files.

@ChrisHemedinger  Hi, where can I find more information about the %DELTABLE autocall macro and others?

@cgp23645 I'm not sure where to find an exhaustive list. The autocall macros available may vary based on products installed. Some are listed here. And more details here.

Tom

If you want to see the definition just look at the source code.

data _null_;
  infile sasautos('deltable.sas');
  input;
  put _infile_;
run;

@ChrisHemedinger and @Tom : Thank you both for your responses. 

If you want to know where the autocall location is for that macro use:

options mautolocdisplay;
%deltable(tables=test)

and it will show you the location in the log

Version history
Last update:
‎11-03-2022 12:48 PM
Updated by:

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Tags