topic Re: Evaluate a value against an aggregate value in the same table in SAS Programming

Evaluate a value against an aggregate value in the same table

DiegoDiaz — Wed, 21 Aug 2019 16:14:55 GMT

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

Re: Evaluate a value against an aggregate value in the same table

tomrvincent — Wed, 21 Aug 2019 17:04:35 GMT

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

Re: Evaluate a value against an aggregate value in the same table

Reeza — Wed, 21 Aug 2019 17:05:32 GMT

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-data-AKA-generate/ta-p/258712

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

Re: Evaluate a value against an aggregate value in the same table

DiegoDiaz — Wed, 21 Aug 2019 18:26:11 GMT

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_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;

Re: Evaluate a value against an aggregate value in the same table

Reeza — Wed, 21 Aug 2019 18:56:17 GMT

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

Re: Evaluate a value against an aggregate value in the same table

novinosrin — Wed, 21 Aug 2019 19:44:54 GMT

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

Re: Evaluate a value against an aggregate value in the same table

DiegoDiaz — Wed, 21 Aug 2019 19:45:00 GMT

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

Re: Evaluate a value against an aggregate value in the same table

novinosrin — Wed, 21 Aug 2019 20:20:10 GMT

@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???

Re: Evaluate a value against an aggregate value in the same table

ballardw — Wed, 21 Aug 2019 21:14:48 GMT

@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.

Re: Evaluate a value against an aggregate value in the same table

DiegoDiaz — Wed, 21 Aug 2019 22:15:18 GMT

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

Re: Evaluate a value against an aggregate value in the same table

DiegoDiaz — Wed, 21 Aug 2019 22:17:19 GMT

Re: Evaluate a value against an aggregate value in the same table

novinosrin — Wed, 21 Aug 2019 23:41:25 GMT

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.

Re: Evaluate a value against an aggregate value in the same table

novinosrin — Thu, 22 Aug 2019 17:57:03 GMT

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!

Re: Evaluate a value against an aggregate value in the same table

DiegoDiaz — Thu, 22 Aug 2019 18:18:27 GMT

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!

Re: Evaluate a value against an aggregate value in the same table

novinosrin — Thu, 22 Aug 2019 21:28:58 GMT

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 🙂

Re: Evaluate a value against an aggregate value in the same table

hashman — Fri, 23 Aug 2019 03:56:36 GMT

@DiegoDiaz :

For a more dynamic, hash-based solution (requiring but a single pass over the input data set), look at the post by @novinosrin.

Below, essentially the same logic is executed using arrays. The first SQL data pass is used to size their high bound. Note that the Prior60 variable is extraneous (since all we need here is POST_DT), and so it's not used. I've also taken the liberty of abbreviating some variables and code True and False simply as T and F. Also note that -138061 is not some mystery number but merely the earliest possible SAS date value; it's used in the array D[0] item as a bulletproof sentinel.

data have ;                                                                 
  input acct group:$8. post_dt :date. bal fee ;                             
  format POST_DT yymmdd10. ;                                                
  cards ;                                                                   
1  ATM_WITH  26Sep2016      .      0.50                                     
1  ATM_WITH  26Sep2016      .      0.50                                     
1  ATM_WITH  26Sep2016      .      0.50                                     
1  ATM_OD    30Sep2016  -150.14   36.00                                     
1  ATM_WITH  06Oct2016      .      0.50                                     
1  ATM_WITH  12Oct2016      .      0.50                                     
1  ATM_WITH  26Oct2016      .      0.50                                     
1  ATM_INQ   26Oct2016      .      0.50                                     
1  ATM_OD    31Oct2016   -52.86   38.00                                     
1  ATM_OD    14Nov2016  -187.87   38.00                                     
1  ATM_OD    16Nov2016    -2.00   38.00                                     
2  ATM_WITH  05Jan2017      .      0.50                                     
2  ATM_INQ   13Jan2017      .      0.50                                     
2  ATM_INQ   20Jan2017      .      0.50                                     
2  ATM_OD    23Jan2017  -231.29   38.00                                     
2  ATM_OD    24Jan2017  -493.54   38.00                                     
2  ATM_OD    27Jan2017    -1.00   36.00                                     
run ;                                                                       
                                                                            
proc sql noprint ;                                                          
  select max (q) into :hb from (select count(*) as q from have group acct) ;
quit ;                                                                      
                                                                            
data want (drop = _:) ;                                                     
  array d [0 : &hb] _temporary_ (-138061, &hb * 0) ;                        
  array f [0 : &hb] _temporary_ (      0, &hb * 0) ;                        
  do _i = 1 by 1 until (last.acct) ;                                        
    set have ;                                                              
    by acct ;                                                               
    if group = "ATM_OD" then do ;                                           
      do _j = _i - 1 to 1 by -1 while (post_dt - d[_j] <= 60) ;             
        _fees = sum (_fees, f[_j]) ;                                        
      end ;                                                                 
      _tf = abs (bal) < _fees ;                                             
      flag = char ("FT", 1 + _tf) ;                                         
      fee_updt = ifn (_tf, fee, 0) ;                                        
      f[_i] = fee_updt ;                                                    
    end ;                                                                   
    else f[_i] = fee ;                                                      
    d[_i] = post_dt ;                                                       
    output ;                                                                
    call missing (fee_updt, flag) ;                                         
  end ;                                                                     
run ;

Kind regards

Paul D.

Re: Evaluate a value against an aggregate value in the same table

novinosrin — Fri, 23 Aug 2019 13:34:22 GMT

@hashman aka Emperor of Hash and king of Paint-brush. I bow to you!

Re: Evaluate a value against an aggregate value in the same table

DiegoDiaz — Mon, 26 Aug 2019 16:13:03 GMT

Thank you very much @novinosrin I made some changes but it is working out.