topic Re: returning negative values with no matching positive value in SAS Programming

returning negative values with no matching positive value

Solly7 — Thu, 23 Jan 2020 13:05:26 GMT

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

Re: returning negative values with no matching positive value

PeterClemmensen — Thu, 23 Jan 2020 13:08:12 GMT

What if the negative value was first?

Re: returning negative values with no matching positive value

Solly7 — Thu, 23 Jan 2020 13:37:14 GMT

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

Re: returning negative values with no matching positive value

PeterClemmensen — Thu, 23 Jan 2020 14:31:51 GMT

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;

Re: returning negative values with no matching positive value

novinosrin — Thu, 23 Jan 2020 14:56:22 GMT


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;

Re: returning negative values with no matching positive value

PaigeMiller — Thu, 23 Jan 2020 14:14:14 GMT

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

Re: returning negative values with no matching positive value

ed_sas_member — Thu, 23 Jan 2020 14:32:51 GMT

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

Re: returning negative values with no matching positive value

ballardw — Thu, 23 Jan 2020 15:29:49 GMT

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.

Re: returning negative values with no matching positive value

PaigeMiller — Thu, 23 Jan 2020 15:36:00 GMT

@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.

Re: returning negative values with no matching positive value

Solly7 — Fri, 24 Jan 2020 09:58:13 GMT

Thank you..it works like magic!