Reconciliation btw transactions and aggregates

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Reconciliation btw transactions and aggregates

[ Edited ]

Hello,

I have the following datasets:

data ONE;
input ID $1. START_DT  : ddmmyy10.  AMOUNT : 10.;
format  START_DT ddmmyy10.;
cards;
a   01/01/2016   305.19
a   02/01/2016   275.66
a   03/01/2016   290.32
b   02/03/2016   397.01
b   03/03/2016   397.01
b   04/03/2016   352.55
b   02/05/2016   364.31
c   04/05/2016   30
c   04/05/2016   10
run;


data TWO;
input ID $1.  AMOUNT : 10.;
cards;
a   758.79
b   1000
c   35
run;

The situation is ,dataset one contains 9 records with transactions, and dataset two contains totals for those IDs, the goal is that total amount for each ID in dataset one to equal total amount for each ID in dataset two.

I want to adjust amount/delete observations from dataset one to accomplish that goal.

My desired output is :

a   01/01/2016   305.19
a   02/01/2016   275.66
a   03/01/2016   177.94
b   02/03/2016   397.01
b   03/03/2016   397.01
b   04/03/2016   205.98
c   04/05/2016   30
c   04/05/2016   5

 

Can someone point me in the right direction, please?


Accepted Solutions
Solution
‎10-19-2016 07:28 AM
Esteemed Advisor
Posts: 6,725

Re: Reconciliation btw transactions and aggregates

data want (keep=id start_dt amount);
merge
  one (in=a)
  two (in=b rename=(amount=top_amount))
;
by id;
if a and b;
retain sum_amount;
if first.id then sum_amount = 0;
if sum_amount < top_amount; * do not go on once the treshold has been reached;
if amount + sum_amount > top_amount
then amount = top_amount - sum_amount; * adjust current amount to fit maximum total;
sum_amount + amount; * cumulate;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Frequent Contributor
Posts: 144

Re: Question

[ Edited ]

 Hi,

 

I guess that the first think that you should do is merge data (take care of change the name of AMOUNT in the second data set or you will have a unappropiate merge).

 

After that, you should have a dataset with ONE variables + total.

 

In this result dataset, you could retain the amount for each subject. If Retain variable is greatter than total, then substract that difference to amount, if substract is 0 then delete record.

 

Frequent Contributor
Posts: 83

Re: Question

data ONE;
input ID $1. START_DT : ddmmyy10. AMOUNT : 10.;
format START_DT ddmmyy10.;
cards;
a 01/01/2016 305.19
a 02/01/2016 275.66
a 03/01/2016 290.32
b 02/03/2016 397.01
b 03/03/2016 397.01
b 04/03/2016 352.55
b 02/05/2016 364.31
c 04/05/2016 30
c 04/05/2016 10
run;


data TWO;
input ID $1. AMt : 10.;
cards;
a 758.79
b 1000
c 35
run;

 

data three; merge one two; by id;

   tamt+amount;

if last.id then do;
   diff=tamt-amt;
   amount=amount-diff;
   tamt=0;
end;

proc print; run;

 

 

/** I got a different answer in B id  */;

Solution
‎10-19-2016 07:28 AM
Esteemed Advisor
Posts: 6,725

Re: Reconciliation btw transactions and aggregates

data want (keep=id start_dt amount);
merge
  one (in=a)
  two (in=b rename=(amount=top_amount))
;
by id;
if a and b;
retain sum_amount;
if first.id then sum_amount = 0;
if sum_amount < top_amount; * do not go on once the treshold has been reached;
if amount + sum_amount > top_amount
then amount = top_amount - sum_amount; * adjust current amount to fit maximum total;
sum_amount + amount; * cumulate;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: Reconciliation btw transactions and aggregates

Thank you very much Kurt!

Your solution is brilliant.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 342 views
  • 0 likes
  • 4 in conversation