Fetching only one first transaction

[Solly7]

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

[Solly7]

see above corrected desired results

Data want

act_status      acct_no      yearmonth  EFF_DATE     trns_type
reactivated    101                202301        05JAN2023     TypeC
reactivated   103                202302       03FEB2023     TypeA
reactivated   103                202303      11MAR2023     TypeD

[Kurt_Bremser · Accepted solution]

proc sql;
create table want as
  select
    a.*,
    b.eff_date,
    b.trns_date
  from acct_details a left join payment_trns b
  on a.acct_no = b.acct_no and a.yearmonth = b.yearmonth and a.acct_status = "reactivated"
  group by a.acct_no, a.yearmonth
  having b.eff_date = min(b.eff_date)
;
quit;

Untested, posted from my tablet.

[Solly7]

Thanks a lot @Kurt_Bremser , it works i just changed 

and a.acct_status = "reactivated"

to 

where a.acct_status = "reactivated"