returning negative values with no matching positive value

[Solly7]

Hi ,

I need help in extracting only policy numbers with negative amount(i.e -500), but with no preceding positive amount(i.e 500) transaction

Data Have                                                  Data Want

policy_no   amount                              policy_no   amount    

1                500                                     2                -1000

1                -500                                    4                -800

2                -1000                                  6                -350

3                 200

4                 -800

5                   50

5                   -50

6                   -350

[PeterClemmensen]

What if the negative value was first?

[Solly7]

its still fine...i will still need the same result i have specified from "Data Want"

[PeterClemmensen]

Ok. Try this

data have;              
input policy_no amount;
datalines;       
1  500 
1 -500 
2 -1000
3  200 
4 -800 
5  50  
5 -50  
6 -350 
;

data want;
    if _N_=1 then do;
        dcl hash h (dataset : "have");
        h.definekey ("policy_no", "amount");
        h.definedone ();
    end;

    set have(where=(amount < 0));

    if h.check(key : policy_no, key : -amount);
run;

[novinosrin · Accepted solution]

Data Have  ;                                           
input policy_no   amount ;
cards; 
1                500                                 
1                -500                                 
2                -1000                             
3                 200
4                 -800
5                   50
5                   -50
6                   -350
;
data want;
 if _n_=1 then do;
   dcl hash H (multidata:'y') ;
   h.definekey  ("amount") ;
   h.definedata ("amount") ;
   h.definedone () ;
 end;
 do _n_=1 by 1 until(last.policy_no);
  set have;
  by policy_no;
  if amount>0 then h.add();
 end;
 do _n_=1 to _n_;
  set have;
  if amount>=0 then continue;
  if amount<0 and h.check(key:abs(amount))=0 then do;
   h.removedup(key:abs(amount));
   continue;
  end;
  output;
 end;
 h.clear();
run;

[Solly7]

Thank you..it works like magic!

[PaigeMiller]

proc summary data=have nway;
    class policy_no;
    var amount;
    output out=_stats_(drop=_: where=(amount<0 and max<0)) min= max=max;
run;

[ballardw]

A variation that may be of interest.

proc summary data=have nway;
    class policy_no;
    var amount;
    output out=_stats_(drop=_: where=(amount<0 )) sum=;
run;

This would actually show policy where there is a negative value greater than the positive balances and doesn't actually use order.

[PaigeMiller]

---

@ballardw wrote:

A variation that may be of interest.

proc summary data=have nway;
    class policy_no;
    var amount;
    output out=_stats_(drop=_: where=(amount<0 )) sum=;
run;

This would actually show policy where there is a negative value greater than the positive balances and doesn't actually use order.

---

That's an interesting point. However, the original requirement was "but with no preceding positive amount(i.e 500) transaction" and so the way I interpret this, if there is a record at –500 and another record for the same policy_no at +250, this should not be in the output data set because the requirement "no preceding positive amount" is not met here. However, we would need @Solly7 to clarify.

[ed_sas_member]

proc sql;
	select policy_no, amount
	from have
	where amount < 0
		  and amount not in (select -(amount) from have where amount > 0);
quit;