topic Re: Removing negative values in SAS Programming

Removing negative values

Solly7 — Tue, 14 Jan 2020 12:55:37 GMT

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

Re: Removing negative values

novinosrin — Tue, 14 Jan 2020 13:19:09 GMT

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;

Re: Removing negative values

ed_sas_member — Tue, 14 Jan 2020 20:29:00 GMT

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;

Re: Removing negative values

mkeintz — Wed, 15 Jan 2020 01:15:52 GMT

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.

Re: Removing negative values

hashman — Wed, 15 Jan 2020 04:25:47 GMT

@Solly7:

Methinks the simplest thing logically is:

Store the records with the positive amounts in a search table by policy and amount
Read the file one record at a time.
If amount is positive, output the record.
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.

Re: Removing negative values

Ksharp — Wed, 15 Jan 2020 06:12:04 GMT

 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;

Re: Removing negative values

Solly7 — Wed, 15 Jan 2020 12:05:10 GMT

BRO...you are superstar!

Re: Removing negative values

Oligolas — Wed, 15 Jan 2020 12:49:43 GMT

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;

Re: Removing negative values

srujana_hm — Tue, 28 Jan 2020 09:44:22 GMT

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;