topic Re: Fetching only one first transaction in SAS Programming

Fetching only one first transaction

Solly7 — Thu, 17 Aug 2023 13:21:16 GMT

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

Re: Fetching only one first transaction

Solly7 — Thu, 17 Aug 2023 13:24:20 GMT

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

Re: Fetching only one first transaction

Kurt_Bremser — Thu, 17 Aug 2023 14:02:29 GMT

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.

Re: Fetching only one first transaction

Solly7 — Thu, 17 Aug 2023 14:13:03 GMT

Thanks a lot @Kurt_Bremser , it works i just changed

and a.acct_status = "reactivated"

where a.acct_status = "reactivated"