DELETE vs DROP In SAS
As a general concept DELETE keyword delete rows from the SAS dataset. It means it only removes observations from the dataset and structure or metadata of that dataset remains intact.
Whereas DROP keyword delete entire dataset by physically deleting data files, metadata files and index files if it exists.
There are slightly different ways how DELETE and DROP keywords are used with other SAS procedures such as proc sql and proc datasets.
DELETE statement with PROC SQL procedure delete rows from the dataset whereas DELETE statement with PROC DATASETS and DROP statement with PROC SQL procedure delete entire dataset.
- Delete All the Rows: proc sql with DELETE
- Delete Entire Dataset: proc sql with DROP, or proc datasets with DELETE
The following sample datasets will be created to demonstrate difference between DELETE and DROP in SAS.
/* create sample datasets */
data mydata1;
input var1 var2 $;
datalines;
1 A
3 B
5 C
7 D
9 E
;
run;
data mydata2;
input var1 var2 $;
datalines;
2 X
4 Y
6 X
8 Z
10 N
;
run;
/* view dataset*/
proc print data=mydata1;
title 'work.mydata1';
run;
proc print data=mydata2;
title 'work.mydata2';
run;
DELETE Keyword in SAS
There are different ways you can use DELETE keyword to delete sas datasets or rather I’d say, delete rows from sas dataset.
1. DELETE With PROC SQL
You can use delete keyword with proc sql procedure to delete one or more datasets from different sas libraries. This method only deletes observations but the table structure remains intact.
/* delete all the observations from sas data sets*/
proc sql;
delete from work.mydata1;
delete from work.mydata2;
quit;
2. DELETE With PROC DATASETS
When you use DELETE keyword with proc datasets procedure it actually deletes all the physical files including data, meta, and index. It actually drops the entire dataset.
/* delete (DROP) multiple SAS datasets */
proc datasets library=work nolist;
delete mydata1 mydata2;
quit;
3. DELETE Keyword With DATA STEP
You can use DELETE keyword in DATA STEP to delete observations based on conditions. You can either create a new dataset or update the original dataset.
In this example we will update original dataset work.mydata1. The observations will be deleted based on condition var1=1
/* delete rows from sas dataset */
data mydata1;
set mydata1;
if var1=1 then delete;
run;
/* view dataset */
proc print data=mydata1;
title 'work.mydata1';
run;
DROP Keyword In SAS
There are different ways DROP keyword is being used in SAS. DROP keyword deletes the entire dataset or variables (columns) from sas dataset.
It can be used with the data step or proc sql procedure.
1. DROP Keyword With PROC SQL
DROP keyword can be used in the PROC SQL procedure to drop the dataset. It deletes entire dataset including all the files such as data, metadata, index files.
The following example demonstrates how to use drop keyword with proc sql procedure.
/* drop multiple datasets using proc sql*/
proc sql;
drop table work.mydata1;
drop table work.mydata2;
quit;
2. DROP Keyword With DATA STEP
Drop can be used with data step to delete entire variables (columns) from sas dataset. The following example shows how to drop variables using drop keyword in data step.
/* drop variables (columns) from SAS dataset */
data mydata1;
set mydata1;
drop var1; /* drop variable list */
run;
/* view dataset */
proc print data=mydata1;
title 'work.mydata1';
run;
I hope everything is clear about difference between delete and drop in SAS. Check out this article to learn more about: How To Delete Datasets in SAS (5+ Examples)
FAQ
You can use DROP keyword with PROC SQL to delete the entire dataset. Alternatively, you could use proc delete procedure.
The observations can be deleted using proc sql with DELETE keyword. It removes all the observations from a dataset. The dataset structure remains intact.