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;
... View more
