Evaluate a value against an aggregate value in the same table

[DiegoDiaz]

Hi All,

I have been trying code the following logic but I have not had any lucky with this.

I need to aggregate an amount by account and review the date of each fee(ATM_OD) and calculates amount of fees for 60-days prior with the following conditions:

2. If the balance on the day of the fee(ATM_OD) is less than the aggregate value then that fee is owed to the account and is part of the aggregate amount.

3. If a fee is owed, it becomes part of the 60-day aggregate to determine if the next fee is due to the account

please see the following table with the flow of this logic.

any help is appreciated

Account	GROUP	POST_DT	prior60	Balance	Fee_Amount	Fee_Amount_Updated	Comments	Aggregate_Amount	prior60	Condition (Balance < Aggregate Value)
1	ATM_WITH	26Sep2016	28Jul2016	.	$              0.50			$                            0.50
1	ATM_WITH	26Sep2016	28Jul2016	$             -	$              0.50			$                            1.00
1	ATM_WITH	26Sep2016	28Jul2016	$             -	$              0.50			$                            1.50
1	ATM_OD	30Sep2016	01Aug2016	$ (150.14)	$            36.00	0.00	The fee amount should be updated for 0 since the condition is False	$                            1.50	$      1.50	FALSE
1	ATM_WITH	06Oct2016	07Aug2016	.	$              0.50			$                            2.00
1	ATM_WITH	12Oct2016	13Aug2016	$             -	$              0.50			$                            2.50
1	ATM_WITH	26Oct2016	27Aug2016	$             -	$              0.50			$                            3.00
1	ATM_INQ	26Oct2016	27Aug2016	$             -	$              0.50			$                            3.50
1	ATM_OD	31Oct2016	01Sep2016	$   (52.86)	$            38.00	0.00	The fee amount should be updated for 0 since the condition is False	$                            3.50	$      3.50	FALSE
1	ATM_OD	14Nov2016	15Sep2016	$ (187.87)	$            38.00	0.00	The fee amount should be updated for 0 since the condition is False	$                            3.50	$      3.50	FALSE
1	ATM_OD	16Nov2016	17Sep2016	$      (2.00)	$            38.00	38.00	The fee amount Keeps the value	$                          41.50	$      3.00	TRUE
2	ATM_WITH	05Jan2017	06Nov2016	.	$              0.50			$                            0.50
2	ATM_INQ	13Jan2017	14Nov2016	$             -	$              0.50			$                            1.00
2	ATM_INQ	20Jan2017	21Nov2016	$             -	$              0.50			$                            1.50
2	ATM_OD	23Jan2017	24Nov2016	$ (231.29)	$            38.00	0.00	The fee amount should be updated for 0 since the condition is False	$                            1.50	$      1.50	FALSE
2	ATM_OD	24Jan2017	25Nov2016	$ (493.54)	$            38.00	0.00	The fee amount should be updated for 0 since the condition is False	$                            1.50	$      1.50	FALSE
2	ATM_OD	27Jan2017	28Nov2016	$      (1.00)	$            36.00	36.00	The fee amount Keeps the value	$                          37.50	$      1.50	TRUE
3	ATM_INQ	13Feb2017	15Dec2016		$              0.50			$                            0.50
3	ATM_INQ	16Feb2017	18Dec2016		$              0.50			$                            1.00
3	ATM_INQ	17Feb2017	19Dec2016		$              0.50			$                            1.50
3	ATM_OD	21Feb2017	23Dec2016	$      (0.20)	$            38.00	38.00	The fee amount Keeps the value	$                          39.50	$      1.50	TRUE

Diego Diaz

[tomrvincent]

Calc your aggregates in one pass then join the 2 tables together.

[Reeza]

1. Can you please provide your data in a data step, please make it clear what you're starting with versus what needs to be calculated.

Here are instructions on how to provide sample data as a data step:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

2. Can you show what you've tried so far?

[DiegoDiaz]

Thanks! Attached is an excel file with the logic.

data transactions;

infile datalines dsd dlm='|' truncover;

format POST_DT prior60 date9.;

input Account:$8. GROUP:$10. POST_DT:date9. prior60:date9. Balance:32. Fee_Amount:32.;

datalines;

1|ATM_WITH|26Sep2016|28Jul2016|.|0.50

1|ATM_WITH|26Sep2016|28Jul2016|.|0.50

1|ATM_WITH|26Sep2016|28Jul2016|.|0.50

1|ATM_OD|30Sep2016|01Aug2016|-150.14|36.00

1|ATM_WITH|06Oct2016|07Aug2016|.|0.50

1|ATM_WITH|12Oct2016|13Aug2016|.|0.50

1|ATM_WITH|26Oct2016|27Aug2016|.|0.50

1|ATM_INQ|26Oct2016|27Aug2016|.|0.50

1|ATM_OD|31Oct2016|01Sep2016|-52.86|38.00

1|ATM_OD|14Nov2016|15Sep2016|-187.87|38.00

1|ATM_OD|16Nov2016|17Sep2016|-2.00|38.00

2|ATM_WITH|05Jan2017|06Nov2016|.|0.50

2|ATM_INQ|13Jan2017|14Nov2016|.|0.50

2|ATM_INQ|20Jan2017|21Nov2016|.|0.50

2|ATM_OD|23Jan2017|24Nov2016|-231.29|38.00

2|ATM_OD|24Jan2017|25Nov2016|-493.54|38.00

2|ATM_OD|27Jan2017|28Nov2016|-1.00|36.00

;;;;;;

run;

This is what I've tried so far:

Data atm_60p;

if _n_=1 then do;

if 0 then set ATM_FEES_4;

dcl hash H (dataset:'ATM_FEES_4',multidata:'y') ;

h.definekey ("FULL_ACCT_NBR") ;

h.definedata ("PST50_POST_DT");

h.definedone ();

end;

set ATM_FEES_4(rename=(PST50_POST_DT=_PST50_POST_DT)rename=(REF_DUE2=_REF_DUE2));

count=0;

f_cumulative_fee=0;

rc = h.find();

do while(rc = 0);

if intnx('days',_PST50_POST_DT,-60)<=PST50_POST_DT<=_PST50_POST_DT then do;

count+1;

f_cumulative_fee=sum(f_cumulative_fee,_REF_DUE2); /*Here is when I wanted to evaluate the current balance amount against the aggregate amount*/

end;

rc = h.find_next();

end;

drop PST50_POST_DT rc REF_DUE2;

format prior60 date9.;

prior60=intnx('days',_PST50_POST_DT,-60);

run;

[Reeza]

Paging @novinosrin since I'm allergic to hash tables. 

[novinosrin]

Hello @DiegoDiaz  More than happy to help. All I have done so far is created the HAVE and looked through your excel/code. Can you explain in simple terms what's the logic in simple words/terms.

Aggregate as in only where GROUP=ATM_OD (sum logic/look up logic)

Please explain the how you derive the output  if you can while I'll see if I can understand your excel.

In observation 4, isn't -150.14 <$1.50  satisfying balance<aggregate value??? Your excel comment says it's false???

Thank you @Reeza  for the plug

[DiegoDiaz]

Thank you very much.

My table contains information that is break out by three groups(ATM_WITH, ATM_INQ and ATM_OD), I need to create an aggregate amount for the field "Fee_Amount" by account; however, when the current record has "ATM_OD" as group I need to do the following validation:

1. If the balance amount (field Balance) on the day of the fee (ATM_OD) is less than an aggregate value calculated based on the prior 60 days then that fee is owed to the account, it would be part of the aggregate amount coming from the Fee_Amount and it also becomes part of the 60-day aggregate to determine if the next fee is due to the account. otherwise the aggregate amount would keep the previous value and the fee_amount does not be considered for any 60-day aggregate amount later.

In my excel file. I created three columns:

1. the first column is the aggregate amount based on the fee amount field.

2. the second column is the 60-days aggregate amount when the group is "ATM_OD", also there are two column that explain when the fee needs to be considered or not.

3. and the third column has the condition of Balance < 60-days aggregate value.

so you can see when the condition is highlighted  it is because the condition met the criteria and the amount needs to be part of the main aggregate amount.

hope it helps.

Thank you very much for your help

[novinosrin]

@DiegoDiaz   You got to bear with me as I try to understand you. Ok here I am trying to build the logic but the sentences needs the logical transformation. So a combination or pseudocode+syntax and you explain the piece I need

Let's break see your explanation to my understanding

data want;

set transactions;

/*I need to create an aggregate amount for the field "Fee_Amount" by account*/

by account;

Aggregate_Amount+Fee_Amount;

/*break*/

/* however, when the current record has "ATM_OD"

as group I need to do the following validation:*/

if group='ATM_OD' then do; /*if true continue*/

 if balance<Aggregate_Amount then do;  /*if true continue*/

/*which in the first case is true -150 is < 1.50  OBSNo 4*/

/*it would be part of the aggregate amount coming from the Fee_Amount

 and it also becomes part of the 60-day aggregate to determine

 if the next fee is due to the account. otherwise the aggregate amount

 would keep the previous value and the fee_amount does not be considered

 for any 60-day aggregate amount later.*/---> What's the equation or formula logic here for the long sentence???

[DiegoDiaz]

Thanks @novinosrin 

Here is an explanation in red:

if group='ATM_OD' then do; /*if true continue*/

if balance<Aggregate_Amount then do;  /*if true continue*//*in this condition, the aggregate amount must be based on sum of Fee_Amount of the prior 60 days how I show it in the column J in my excel file and the balance amount must be absolute value*/ basically, here is where I have my issue I have not been able to calculate this aggregate amount. please see below the logic:

if abs(balance) < prior60_aggregate_amt then do;

    Aggregate_Amount+Fee_Amount;

end;

else do;

    Aggregate_Amount+0;

    Fee_Amount=0; /*The fee amount must be updated for 0 in order to not be considered in the prior60 aggregate amount      for any next fee */

end;

Please let me know if you have any question

Thanks for the help

[novinosrin]

Hello @DiegoDiaz  Okay , the example excel seems to give me the direction I need. I've just got home and it is 7:40pm EDT. Let me do some chores and thoroughly review the excel and start coding. I think I'm getting the hang of things now. I'll try to to see if I can fnd time tonight, if not tomorrow. 

Well, the community is open 24/7, so you may have got the solution before I could. I'll get on it though. 

[ballardw]

---

@novinosrin wrote:

Hello @DiegoDiaz  More than happy to help. All I have done so far is created the HAVE and looked through your excel/code. Can you explain in simple terms what's the logic in simple words/terms.

Aggregate as in only where GROUP=ATM_OD (sum logic/look up logic)

Please explain the how you derive the output  if you can while I'll see if I can understand your excel.

In observation 4, isn't -150.14 <$1.50  satisfying balance<aggregate value??? Your excel comment says it's false???

Thank you @Reeza  for the plug

---

@DiegoDiaz 

The actual condition appears to be Absolute value < comparison value. So ABS(-150.14) = 150.14 which is not < 150. The column heading is somewhat misleading, a not uncommon problem with Excel.

It doesn't help that there are two columns labeled Prior60 either. Especially considering one of them is a date and the other a currency amount.

[novinosrin]

Hello @DiegoDiaz  By any chance, are you asking for something as simple as this. This is basically following your Transaction (input) and excel 

data transactions;
infile datalines dsd dlm='|'  truncover;
format POST_DT prior60 date9.;
input Account:$8. GROUP:$10. POST_DT:date9. prior60:date9. Balance:32. Fee_Amount:32.;
datalines;
1|ATM_WITH|26Sep2016|28Jul2016|.|0.50
1|ATM_WITH|26Sep2016|28Jul2016|.|0.50
1|ATM_WITH|26Sep2016|28Jul2016|.|0.50
1|ATM_OD|30Sep2016|01Aug2016|-150.14|36.00
1|ATM_WITH|06Oct2016|07Aug2016|.|0.50
1|ATM_WITH|12Oct2016|13Aug2016|.|0.50
1|ATM_WITH|26Oct2016|27Aug2016|.|0.50
1|ATM_INQ|26Oct2016|27Aug2016|.|0.50
1|ATM_OD|31Oct2016|01Sep2016|-52.86|38.00
1|ATM_OD|14Nov2016|15Sep2016|-187.87|38.00
1|ATM_OD|16Nov2016|17Sep2016|-2.00|38.00
2|ATM_WITH|05Jan2017|06Nov2016|.|0.50
2|ATM_INQ|13Jan2017|14Nov2016|.|0.50
2|ATM_INQ|20Jan2017|21Nov2016|.|0.50
2|ATM_OD|23Jan2017|24Nov2016|-231.29|38.00
2|ATM_OD|24Jan2017|25Nov2016|-493.54|38.00
2|ATM_OD|27Jan2017|28Nov2016|-1.00|36.00
3|ATM_INQ|13Feb2017|15Dec2016| . | 0.50 
3|ATM_INQ|16Feb2017|18Dec2016| . | 0.50 
3|ATM_INQ|17Feb2017|19Dec2016| . | 0.50 
3 |ATM_OD|21Feb2017|23Dec2016| -0.20| 38.00 
;;;;;;
run;

data want;
 set transactions;
 by account;
 if first.account then do; Aggregate_Amount=.;prior60_amt=.;end;
 if group='ATM_OD' then do;
  if abs(balance)<Aggregate_Amount then Fee_Amount_updated=Fee_Amount;
  else  Fee_Amount_updated=0;
  Aggregate_Amount+Fee_Amount_updated;
 end;
 else do;
  Aggregate_Amount+Fee_Amount;
  prior60_amt+Fee_Amount;
end;
run;

Sir @ballardw  and @Reeza , if you have time, can you verify too? Thanks in advance!

[DiegoDiaz]

Hi @novinosrin Thanks for your promptly response.

You're right the code below is following the excel results; however, there is something that needs to be changed. In my example, I did not include transactions with more than 60 days by account. so the aggregate amount in this condition " if abs(balance)<Aggregate_Amount " must be calculate based on the prior 60 days from the current transaction date.

data want;
set transactions;
by account;
if first.account then do; Aggregate_Amount=.;prior60_amt=.;end;
if group='ATM_OD' then do;
  if abs(balance)<Aggregate_Amount /*This amount must be calculate based on the prior 60 days from the current transaction date*/ then Fee_Amount_updated=Fee_Amount;
  else Fee_Amount_updated=0;
  Aggregate_Amount+Fee_Amount_updated;
end;
else do;
  Aggregate_Amount+Fee_Amount;
  prior60_amt+Fee_Amount;
end;
run;

Please let me know if you have any question

Many thanks again!

[novinosrin · Accepted solution]

Hi @DiegoDiaz   For some reason, we are proving my comprehension skills/language skills need dire improvement/learning. Anyways, I had to reach out to my mother to get help with business understanding, here you go. 

data transactions;
infile datalines dsd dlm='|'  truncover;
format POST_DT prior60 date9.;
input Account:$8. GROUP:$10. POST_DT:date9. prior60:date9. Balance:32. Fee_Amount:32.;
datalines;
1|ATM_WITH|26Sep2016|28Jul2016|.|0.50
1|ATM_WITH|26Sep2016|28Jul2016|.|0.50
1|ATM_WITH|26Sep2016|28Jul2016|.|0.50
1|ATM_OD|30Sep2016|01Aug2016|-150.14|36.00
1|ATM_WITH|06Oct2016|07Aug2016|.|0.50
1|ATM_WITH|12Oct2016|13Aug2016|.|0.50
1|ATM_WITH|26Oct2016|27Aug2016|.|0.50
1|ATM_INQ|26Oct2016|27Aug2016|.|0.50
1|ATM_OD|31Oct2016|01Sep2016|-52.86|38.00
1|ATM_OD|14Nov2016|15Sep2016|-187.87|38.00
1|ATM_OD|16Nov2016|17Sep2016|-2.00|38.00
2|ATM_WITH|05Jan2017|06Nov2016|.|0.50
2|ATM_INQ|13Jan2017|14Nov2016|.|0.50
2|ATM_INQ|20Jan2017|21Nov2016|.|0.50
2|ATM_OD|23Jan2017|24Nov2016|-231.29|38.00
2|ATM_OD|24Jan2017|25Nov2016|-493.54|38.00
2|ATM_OD|27Jan2017|28Nov2016|-1.00|36.00
3|ATM_INQ|13Feb2017|15Dec2016| . | 0.50 
3|ATM_INQ|16Feb2017|18Dec2016| . | 0.50 
3|ATM_INQ|17Feb2017|19Dec2016| . | 0.50 
3 |ATM_OD|21Feb2017|23Dec2016| -0.20| 38.00 
;;;;;;
run;

data want;
  set transactions;
  by account;
  if _n_=1 then do;
  if 0 then set transactions(rename=(Fee_Amount=_fmt post_dt=_prior60_dt group=_grp));
  dcl hash H (dataset:'transactions(rename=(Fee_Amount=_fmt post_dt=_prior60_dt group=_grp))',multidata: "Y");
  h.definekey  ("account");
  h.definedata ("_fmt","_prior60_dt","_grp");
  h.definedone ();
  end;
  if first.account then Aggregate_Amount=.;
  if group='ATM_OD' then do;
	 if abs(balance)<Aggregate_Amount then do;
	 call missing(Aggregate_Amount);
     do _rc=h.find() by 0 while(_rc=0);
      if intnx('days',post_dt,-60)<=_prior60_dt<=post_dt and _grp ne 'ATM_OD' then 
	  Aggregate_Amount=sum(Aggregate_Amount,_fmt);
	  _rc=h.find_next();
     end;
	 Aggregate_Amount=sum(Aggregate_Amount,fee_amount);
     Fee_Amount_updated=Fee_Amount;
	 end;
	 else  Fee_Amount_updated=0;
  end;
  else Aggregate_Amount+Fee_Amount;
  drop _:;
run;

PS Coding took 10 mins, comprehending the question/sentences took ages and involved my family lol 🙂