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
Super User
Posts: 10,272

Re: Reconciliation btw transactions and aggregates

Posted in reply to aurel_hogea
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
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Frequent Contributor
Posts: 146

Re: Question

[ Edited ]
Posted in reply to aurel_hogea

 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: 102

Re: Question

Posted in reply to arodriguez

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
Super User
Posts: 10,272

Re: Reconciliation btw transactions and aggregates

Posted in reply to aurel_hogea
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
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 5

Re: Reconciliation btw transactions and aggregates

Posted in reply to KurtBremser

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
  • 418 views
  • 0 likes
  • 4 in conversation