topic Re: Real time Problem how to aggregate on two variables based on the third variable value and get ou in SAS Programming

Real time Problem how to aggregate on two variables based on the third variable value and get output

Priya_26 — Mon, 05 Feb 2018 06:16:14 GMT

Hello Everyone, I facing a real Time problem as below. I have modified the data accordingly. Below is the data i have. Its transactional data.

Here Trn_code 'C' represent credit and 'D' represent Debit.

Trn_key trn_CODE Sender Receiver Amount sender_in_receiver_list Receiver_in_Sender_list

1234        C                   John       Louis      10000                   Y                           N
2233        D                    David      John       20000                   N                           Y
1111        D                    Louis      David      10000                   N                          Y
2345        C                   Thomas     John       30000                   Y                           N
3456        D                    Daniel       Louis     25000                   Y                           Y
4567        C                    John       Thomas     50000                   Y                           Y

Now I need to get only observations where

    1) (sender_in_receiver_list = 'Y' or     Receiver_in_Sender_list = 'Y' )    AND
    2) The aggregate amount received by receiver is greater than or equal to Minimum Aggregate Daily Amount (Threshold A= 5000) AND
    3) The aggregate amount sent from sender must be greater than or equal to a Minimum Percentage [Threshold B = 10%] of the amount in Condition 2.    AND
    4) The aggregate amount sent from sender must be less than or equal to a Maximum Percentage [Threshold C = 40% ] of the amount in Condition 2.

/**/

if we take example of John
He sent money to Louis and Thomas ( 10000+50000 = 60000) and recived from David and thomas (20000+30000 = 50000)
condition 1 satisfy as Jhon occurs in both send and receiver list.
condition 2 satisfy as jhon has received a total of 50000 which is >= 5000.
condition 3 satisfy as jhon has sent 60000 which is >= (10% of 50000).
condition 4 is satisfied as jhon has sent 60000 which is <= (120% of 50000)

I should get Johns in my output.

I will appreciate help on this. Thanks in advance !! 🙂

Re: Real time Problem how to aggregate on two variables based on the third variable value and get ou

error_prone — Mon, 05 Feb 2018 07:55:50 GMT

Interesting problem. Hash objects could be part of a solution or a really horrible proc sql. Can't work on it right now,sorry.

Re: Real time Problem how to aggregate on two variables based on the third variable value and get ou

Ksharp — Mon, 05 Feb 2018 13:28:31 GMT

data have;
input Trn_key  trn_CODE $  Sender  $ Receiver   $  Amount      sender_in_receiver_list $   Receiver_in_Sender_list $;
cards;
1234        C                   John       Louis        10000                   Y                             N
2233        D                    David      John        20000                   N                            Y
1111        D                     Louis      David      10000                   N                            Y
2345        C                   Thomas     John       30000                   Y                           N
3456        D                    Daniel       Louis       25000                   Y                           Y
4567        C                    John       Thomas     50000                   Y                           Y
;
run;
data k;
 set have;
 k=Sender;if sender_in_receiver_list='Y' then output;
 k=Receiver;if Receiver_in_Sender_list='Y' then output;
 keep k;
run;
proc sort data=k nodupkey;by k;run;
data sender;
 set have;
 k=Sender;
 keep k amount;
run;
data receiver;
 set have;
 k=Receiver;v=Receiver_in_Sender_list;
 keep k amount;
run;
data want;
 if _n_=1 then do;
  if 0 then set sender;
  declare hash s(dataset:'sender',multidata:'y');
  s.definekey('k');
  s.definedata('amount');
  s.definedone();
  
  if 0 then set receiver;
  declare hash r(dataset:'receiver',multidata:'y');
  r.definekey('k');
  r.definedata('amount');
  r.definedone();
 end;
set k;
rc=r.find();
amount_receive=0;
do while(rc=0);
 amount_receive+amount;
 rc=r.find_next();
end;
condition2=ifn(amount_receive>=5000,1,0);

rc=s.find();
amount_send=0;
do while(rc=0);
 amount_send+amount;
 rc=s.find_next();
end;
condition3=ifn(amount_send>=amount_receive*0.1,1,0);
condition4=ifn(amount_send<=amount_receive*1.2,1,0);

if condition2 and condition3 and condition4 then output;
keep k;
run;