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 |
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;
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;
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;
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 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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.