Identify similar (negative) values

[kashun]

I am trying to identify credited amounts in a data set and changing their payment by negating their similar values previously displayed in a column.

Did tried hash objects but was unable to make it work. Can you please help.

Have:

ID	Amount	Payment
243	600	40
243	800	60
243	400	20
243	200	70
243	-600	0
243	-300	0
145	250	50
145	300	20
145	680	70
145	-500	0
145	500	50
134	780	20
134	400	30
134	450	50
134	200	10
134	-450	0

Want:

ID	Amount	Flag	Payment
243	600	1	40
243	800	0	60
243	400	0	20
243	200	0	70
243	-600	1	-40
243	-300	0	0
145	250	0	50
145	300	0	20
145	680	0	70
145	-500	1	-50
145	500	1	50
134	780	0	20
134	400	0	30
134	450	1	50
134	200	0	10
134	-450	1	-50

[AMSAS]

Please provide more details on the logic you want to apply
I don't understand what the logic is for setting flag=1 or the negative payments

[kashun]

The flag means a corresponded value has been identified with its credited value (i.e. negative value ).

[hhinohar · Accepted solution]

I assume logic is Group by id and flip positive payment value to minus when amount is minus and absolute values are equal.
Below program is done accordingly but performance may be slow.

data have;
infile datalines dlm="09"x;
input ID Amount Payment;
datalines;
243	600	40
243	800	60
243	400	20
243	200	70
243	-600	0
243	-300	0
145	250	50
145	300	20
145	680	70
145	-500	0
145	500	50
134	780	20
134	400	30
134	450	50
134	200	10
134	-450	0
;
run;

data want;
  if _N_=1 then do;
    if 0 then set have;
    dcl hash h(multidata:"y") ;
    h.definekey("id","amount");
    h.definedata("amount","payment");
    h.definedone();
  end;
  do until(last);
    set have end=last;
    by id notsorted;
    if sign(amount)=-1 then _iorc_=h.add(key:id,key:abs(amount),data:abs(amount),data:abs(payment));
  end;
  if last;
  do until(last1);
    set have end=last1;
    by id notsorted;
    _payment=payment;
    if sign(amount)=1 and (h.check(key:id,key:abs(amount)) = 0) then do;
        _iorc_=h.replace(key:id,key:abs(amount),data:abs(amount),data:max(abs(payment),abs(_payment)));
      end;
  end;
  if last1;
  do until(last2);
    set have end=last2;
    by id notsorted;
    _payment=payment;
    _amount=amount;
    flag=(h.find(key:id,key:abs(_amount)) =0 and payment ne 0);
    if sign(_amount)=-1 then payment=-payment;
    output;
  end;	
  drop amount _payment;
  rename _amount=amount;
run;

[hhinohar]

After all, this is much cleaner.

data want;
  if _N_=1 then do;
    if 0 then set have;
    dcl hash h(dataset:"have");
    h.definekey("id","amount");
    h.definedata("payment");
    h.definedone();
  end;
    set have;
    by id notsorted;
    *keep original payment value;
    _payment=payment;
    *find negative amount key;
    flag=(h.find(key:id,key:-amount)=0);
    *fill payment value;
    payment=ifn(sign(amount)=-1,-payment,_payment);
  drop _payment;
run;

[andreas_lds]

Is it necessary to maintain the original order of observations?

If not try:

proc sql;
   select Id, Amount, count(*)-1 as Flag, Payment
      from have
      group by id, abs(Amount)
   ;
quit;

Assuming that for each ID and abs(Amount) only one or two observations exist.

NOTE: Wait, that step is to simple, of course, because it doesn't look for positive amount and a negative amount, two positive/negative will also be flagged. Sorry.

[kashun]

Hi @andreas_lds. Thanks very much for the input. Yes the order of the observations should be maintained

[andreas_lds]

---

@kashun wrote:

I am trying to identify credited amounts in a data set and changing their payment by negating their similar values previously displayed in a column.

Did tried hash objects but was unable to make it work. Can you please help.

Helping you would be a lot easier, if you would post the code you have.

The following step seems to do what you expect, i am not sure if all special-cases are properly handled, because you have none in the example you have posted. In the future, please post data in usable form.

data want;
   set have;

   if _n_ = 1 then do;
      declare hash h(dataset: 'work.have(drop=Payment');
      h.defineKey('Id', 'Amount');
      h.defineDone();
   end;

   Amount = Amount * -1; 
   Flag = h.check() = 0; /* check returns 0 if they key-pair is found */
   Amount = Amount * -1; /* reverting to the original value */
run;

[kashun]

@andreas_lds. It seems this provides the flags and the proc sql deals with the payment calculation. I will add the data next time to make it easier. Thanks vert much.