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 !! 🙂
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.