Efficiently delete rows based on conditions in an indexed table

[ChrisNZ]

@mkeintz

Your code for HAVE3 only deletes one record.

To delete all targeted records, the hash table iterator is 10 times slower than the data step's implicit iterator.

data HAVE1 HAVE2 HAVE3
     deletes (keep=I);
  length x $200; retain x ' ';
  do I=1 to 5e6;
    output have1 have2 have3;
    if mod(i,100)=0 then output deletes;
  end;
run; 

data have1;
  if _n_=1 then do;
    declare hash h (dataset:'deletes');
      h.definekey('i');
      h.definedone();
  end;
  modify have1 ;
  if h.find()=0 then remove;
run;

proc sql; 
  create index I on have2;
data have2; 
  set deletes; 
  modify have2 key=I;  
  if _IORC_= %sysrc(_sok) then remove; else _ERROR_=0; 
run;

proc sql; 
  create index I on have3;
data have3;
  declare hash h (dataset:'deletes');
    h.definekey('i');
    h.definedone();
  declare hiter hi ('h');
  rc=hi.first() ;    
  do while (rc=0);    
    modify have3 key=I;  
    if _IORC_= %sysrc(_sok) then remove; else _ERROR_=0; 
    rc=hi.next();                   
  end;
  stop;
run;

1- 4.7 s

2- 1.6 + 0.8 s

3- 1.6 + 7.3 s