Removing negative values

[Solly7]

Hi I need help with below, i need to remove rows for policies which have negative amount(i.e $500) but with no corresponding positive amount(i.e $500). I also need to remove  1 negative transaction from policies that have 2 negative transactions(same amount, i.e $500) and positive transaction($500). keep those transactions with one negative transaction($700) and one positive tranasaction(i.e $700

                              data.transactions

     Data Have                                           Data Want

policy no    amount                               policy no    amount

1                -500                                     1                -500

1                -500                                     1                 500

1                 500                                     3                -700                           

2                -1000                                   3                 700

3                -700

3                 700                 

[novinosrin · Accepted solution]

HI @Solly7  Will this help?

   Data Have        ;                                 
input policy_no    amount    ;
cards; 
1                -500                                     
1                -500                                
1                 500                                                             
2                -1000                                  
3                -700
3                 700  
;

data want;
 if _n_=1 then do;
   dcl hash H (multidata:'y') ;
   h.definekey  ("amount") ;
   h.definedone () ;
 end;
 do _n_=1 by 1 until(last.policy_no);
  set have;
  by policy_no;
  if sign(amount)=1 then _iorc_=h.add();
 end;
 do _n_=1 to _n_;
  set have;
  if sign(amount)=-1 and h.check(key:abs(amount)) then continue;
  if sign(amount)=-1 and h.check(key:abs(amount))=0 then h.removedup(key:abs(amount));
  output;
 end;
 h.clear();
run;

 

[Solly7]

BRO...you are superstar!

[ed_sas_member]

Hi @Solly7 

Here is an alternative method using proc sql, probably not as efficient as the one provided by @novinosrin 

It depends whether you're familiar with hash object.

Best,

proc sql;
	create table pos as
	select distinct policy_no, amount as pos_amount
	from have
	where amount > 0
	order by policy_no, amount;

	create table neg as
	select distinct policy_no, amount as neg_amount
	from have
	where amount < 0
	order by policy_no, amount;
quit;

proc sql;
	create table have2 as
	select a.policy_no, a.pos_amount, b.neg_amount
	from pos as a inner join neg as b
	on a.policy_no = b.policy_no and a.pos_amount = abs(b.neg_amount);
quit;

proc sql;
	create table want as
	select policy_no, pos_amount as amount
	from have2
	union
	select policy_no, neg_amount as amount
	from have2
	order by policy_no, abs(amount);
quit;

[mkeintz]

I have re-framed your task to: for each positive, keep it and a single matched negative:

data Have ;                                 
  input policy_no    amount    ;
cards; 
1                -500
1                -500
1                 500
2                -1000
3                -700
3                 700
run;

data want (drop=_p);
  set have (where=(amount>0) in=inpos)
      have (in=infull);
  by policy_no;
  array pos_vals {20} _temporary_;

  if first.policy_no then call missing(of pos_vals{*});
  if inpos then pos_vals{1+n(of pos_vals{*})}=amount;
  if infull;

  if amount<0 then _p=whichn(-1*amount,of pos_vals{*});
  if _p>0 or amount>0 then output;
  if _p>0 then pos_vals{_p}=.;
run;

For each policy_no, this reads all the positive, followed by all the positives and negatives.  In the first pass of positives, add to a  list of positive values.  In the second pass, when a negative value is encountered, see if it is matched in the list of positives.  If so keep it and delete the corresponding positive entry in the list.  Any negative not so matched is not output.  Also keep all positives.

[hashman]

@Solly7:

Methinks the simplest thing logically is:

1. Store the records with the positive amounts in a search table by policy and amount
2. Read the file one record at a time.
3. If amount is positive, output the record.
4. Otherwise see if the absolute value of amount for the current value of policy is in the table. If it is, output the record and remove the respective item from the table to prevent another output for the same policy and negative amount downstream.

Extra advantages of the approach (besides the logical):

• it works regardless of the input file's record order (sorted or disordered)
• it requires less than 2 passes through the input data

. In SAS words:  

data have ;                                                                                                                             
  input policy_no amount ;                                                                                                              
  cards ;                                                                                                                               
3   -700                                                                                                                                
1    500                                                                                                                                
2  -1000                                                                                                                                
1   -500                                                                                                                                
3    700                                                                                                                                
1   -500                                                                                                                                
run ;                                                                                                                                   
                                                                                                                                        
data want ;                                                                                                                             
  if _n_ = 1 then do ;                                                                                                                  
    dcl hash h (dataset:"have (where = (amount > 0))") ;                                                                                
    h.definekey ("policy_no", "amount") ;                                                                                               
    h.definedone () ;                                                                                                                   
  end ;                                                                                                                                 
  set have ;                                                                                                                            
  if amount > 0 then output ;                                                                                                           
  else if h.check (key:policy_no, key:-amount) = 0 then do ;                                                                            
    output ;                                                                                                                            
    h.remove (key:policy_no, key:-amount) ;                                                                                             
  end ;                                                                                                                                 
run ;                   

Kind regards

Paul D.

[Ksharp]

 Data Have        ;                                 
input policy_no    amount    ;
cards; 
1                -500                                     
1                -500                                
1                 500                                                             
2                -1000                                  
3                -700
3                 700  
;

data positive negative;
 set have;
 if sign(amount)=-1 then output negative;
  else output positive;
 run;
data negative;
 set negative;
 amount=-amount;
run;
data temp;
 ina=0;inb=0;
 merge positive(in=ina) negative(in=inb);
 by policy_no amount;
 if ina and inb;
run;
data want;
 set temp;
 output;
 amount=-amount;output;
run;

[Oligolas]

PROC SQL;
   CREATE TABLE want AS
      SELECT DISTINCT a.*
      FROM have a
      INNER JOIN have b 
      ON a.policy_no eq b.policy_no
      AND ((a.amount<0 AND abs(a.amount) eq b.amount)
           OR (a.amount>0 AND a.amount eq abs(b.amount)))
   ;
QUIT;

[srujana_hm]

HI @Solly7 .

Felt my solution will be more simplified and this worked. you can try this.

 Data Have        ;                                 
input policy_no    amount    ;
cards; 
1                -500                                     
1                -500                                
1                 500                                                             
2                -1000                                  
3                -700
3                 700 
3                 100 
;
run;

proc sort data=have nodup;
by policy_no    amount;
run;
data want;
set have;
by policy_no    amount;
if first.policy_no ne last.policy_no;
output;
run;