BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dassuz
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
ChrisNZ
Tourmaline | Level 20

Overwriting it would be the fastest.

data TABLE;

 set TABLE;

 stop;

run;

 

Patrick
Opal | Level 21

@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.

 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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