Hi I have two tables in sas,(acct_details table) with columns acct_no ,act_status and yearmonth
Table B( payment_trns table) has columns: yearmonth , trns_type, acct_no , and eff_date
I need to fetch the first transaction from payment_trns(Only the trns_type, eff_date columns) done in the same period the account was re-activated. I created the logic on the provided code below code but i get incorrect results on the screenshot below the code
data acct_details;
format act_status $12.;
input acct_no act_status $ yearmonth;
datalines;
101 reactivated 202301
102 active 202301
103 reactivated 202302
103 reactivated 202303
;
run;
data payment_trns;
informat EFF_DATE date9.;
format EFF_DATE date9.;
input yearmonth trns_type $ acct_no eff_date;
datalines;
202301 TypeA 101 05JAN2023
202301 TypeB 101 10JAN2023
202302 TypeC 101 15FEB2023
202301 TypeA 102 07JAN2023
202301 TypeB 102 12JAN2023
202302 TypeA 103 03FEB2023
202302 TypeB 103 10FEB2023
202303 TypeD 103 11MAR2023
;
run;
proc sql;
create table final_table as
select A.*,
B.eff_date,
B.trns_type
from acct_details as A
left join (
select yearmonth,
acct_no,
min(eff_date) as first_eff_date
from payment_trns
group by yearmonth, acct_no
) as B_temp
on A.yearmonth = B_temp.yearmonth and A.acct_no = B_temp.acct_no
left join payment_trns as B
on B_temp.yearmonth = B.yearmonth and B_temp.acct_no = B.acct_no
where A.act_status = 'reactivated';
quit;
Data want
act_status acct_no yearmonth EFF_DATE trns_type reactivated 101 202301 15JAN2023 TypeC reactivated 103 202302 03FEB2023 TypeA reactivated 103 202303 11MAR2023 TypeD
... View more
