/**************************************************************************/
/*** TIP 00406                                                          ***/
/*** Find and delete records that offset (net zero) each other but only ***/
/*** when the absolute values are equal                                 ***/
/**************************************************************************/

/*** create sample test file ***/
data test;                                    
 claimno = 'A100';                            
 x = -100; output;  /*** will offset with next record ***/                          
 x =  100; output;  /*** will offset with prior record ***/                          
 x =  100; output;                            
 x = -50 ; output;                            
 x = -50 ; output;  /*** will offset with next record ***/                          
 x =  50 ; output;  /*** will offset with prior record ***/                          
 x =  90 ; output;                            
 x =  70 ; output;  /*** will not offset with next 2 records ***/ 
 x = -35 ; output; 
 x = -35 ; output;                           
 x =  70 ; output;                            
 run;                                         
 
/*** separate transactions into positive and negative datasets ***/                                             
data plus minus;                                    
 set test;             
 absx = abs(x); 
 if x ge 0.00 then output plus;
              else output minus;                             
run;                                                                          
                                              
proc sort data=plus ; by claimno x   ; run;   
proc sort data=minus; by claimno absx; run;   

/*** create unique id for each duplicate valued record ***/                                             
data plus;                                    
 set plus;                                    
 by claimno absx;                             
 if first.absx then _cnt_ = 1;                
               else _cnt_ + 1;                
run;                                          

/*** create unique id for each duplicate valued record ***/                                           
data minus;                                   
 set minus;                                   
 by claimno absx;                             
 if first.absx then _cnt_ = 1;                
               else _cnt_ + 1;                
run;                                          
                                              
proc sort data=plus ; by claimno x    _cnt_ ; run;
proc sort data=minus; by claimno absx _cnt_ ; run;

/*** now join positive and negative records for each 
     equal ABS and delete if equal ***/                                            
data join;                                    
 merge plus  (in=p)                           
       minus (in=m)                           
 ;                                            
 by claimno absx _cnt_;                       
 if p and m then delete;                      
run;                                          
/*** end of tip 00406 ***/