Delete rows quickly from table containing 200 million observations

[sriharivn]

Thanks Kurt. One clarification on the last step.

I want to keep all the old records for the customer (flag='N') and delete only the Ys in the history for the customers in X. I want to keep the Ys for the other records which are not present in X.

data customer_history_new;
set customer_history;
if current ne 'Y' or put(customer_no,$checkfmt.) = 'no';
run;

I guess this will remove all the Ys in the history and give me only Ns and also remove the X customers.

Is my understanding right?

[Kurt_Bremser]

Yes. The condition is a DeMorgan transformation of your delete condition.

[ChrisNZ]

1- Formats can be slow for large numbers of values in my experience.

2- Keep in mind that a sorted table will be fast not only for maintenance for you as an admin, but also for all users.

3- Rewriting sorted tables is just a matter of copying the table, so if you have the space it should not take long considering the volume you have.

4- If you are I/O bound, using SPDE with binary compression instead of the V9 engine is a must as it will typically decreases table size by 90%. This engine also processes indexes much faster.

5- When doing such updates, I like to keep tabs of what happens in the log and usually use something like:

data NEWHISTORY (drop=_:); 
  if LASTOBS then put _REPLACED= _ADDED= _KEPT=; 
  merge HISTORY (in=OLD)
        NEW     (in=NEW)
        end=LASTOBS;
  by CUSTOMERNO CURRENT_FLAG;
  if OLD and NEW then _REPLACED+1;
  else if OLD then    _KEPT    +1;
  else                _ADDED   +1;
run;

proc sort data=NEWHISTORY 
          out =HISTORY(index=( ... )) 
          presorted;
  by CUSTOMERNO CURRENT_FLAG ;
run;

Add the deletion logic as you see fit for your data (like:  if DEL then do; _DELETE+1; delete; end; )

The sort step is there just because SAS have been sitting on their hands and there is still no way to set the SORT VALIDATED flag in the data step. PROC SORT just copies the table here.

Index creation is fast on sorted tables.