DATA Step, Macro, Functions and more

How to delete data in table

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

How to delete data in table

I have millions of rows of data in a table, how do I delete the data without deleting the tables


Accepted Solutions
Solution
‎07-10-2017 07:29 AM
Super User
Super User
Posts: 7,413

Re: How to delete data in table

There are several methods, I don't off the top of my head know which will be best resourcewise:

proc sql;
  delete from HAVE
  where <condition>;
quit;

data want;
  set have;
  if <condition>;
run;

data want;
  set have;
  where <condition>;
run;

View solution in original post


All Replies
Solution
‎07-10-2017 07:29 AM
Super User
Super User
Posts: 7,413

Re: How to delete data in table

There are several methods, I don't off the top of my head know which will be best resourcewise:

proc sql;
  delete from HAVE
  where <condition>;
quit;

data want;
  set have;
  if <condition>;
run;

data want;
  set have;
  where <condition>;
run;
PROC Star
Posts: 1,564

Re: How to delete data in table

Overwriting it would be the fastest.

data TABLE;

 set TABLE;

 stop;

run;

 

Respected Advisor
Posts: 3,899

Re: How to delete data in table

@dassuz

Assuming we're talking about tables stored as SAS files (SAS tables as opposed to database tables).

 

You can use PROC SQL / DELETE or less commonly use a SAS Datastep Modify / Remove.

 

The issue with SAS tables:

The records don't get physically removed but get just flagged as deleted. The deleted records still require all the storage space.

You can see this when using Proc Contents.

data have;
  do i=1 to 3;output;end;
run;

proc sql;
  delete from have where i>1
  ;
quit;

proc contents data=have;
run;quit;

Capture.JPG

 

If your permanent SAS dataset gets later on Inserts or Updates then the space of the deleted observations gets re-used as far as I understand things.

IF your SAS dataset is also compressed then make sure that it has been created with option REUSE=YES or you'll never get rid of the deleted observations in regards of space usage.

http://support.sas.com/documentation/cdl/en/ledsoptsref/69751/HTML/default/viewer.htm#p1agtd57jq75ou...

 

Given all these challenges it's actually often more efficient to just re-write the whole SAS table. ...But of course if you're only deleting a few records in a big table or you have also indices and/or constraints defined on the table then that's probably not what you want to do.

 

....and for all of the above reasons if it comes to production implementations and bigger data volumes then my preference is to store and maintain data in a database as there the DBMS deals with all this stuff.

 

 

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 130 views
  • 1 like
  • 4 in conversation