sum of the first two transactions

[Solly7]

Hi , i need to sum the amount of the first two transactions per cust_no on the below data 

                                                                     DATA HAVE  (TRNS_TABLE)

 cust_no   prod_code     yearmonth      TRNS_AMT   TXN_TIME                      EFF_DATE  
1                     WX                202212           500.00         9:09:40.000000          17DEC2022 
1                      WX               202212           200.00        12:36:19.000000           19DEC2022 
1                      WX               202301           500.00        11:14:00.000000           30JAN2023 
1                      WX               202301           500.00        15:42:32.000000          30JAN2023 
1                      WX               202301            450.00       12:18:57.000000            31JAN2023 

2                      AB                202302           1000.00      12:22:45.00000             01FEB2023

The data is already orderd by eff_date and txn_time. The eff_date is date9. format, txn_time is time15.6 format. I need to sum first two transactions per cust_no and summarize my data per cust_no and yearmonth.

                                                                DATA WANT

cust_no     yearmonth    sum_first_two

1                   202212                700

1                   202301               1000

2                  202302              1000

I tried below code didnt work

data temp;
set trns_table;
by cust_no eff_date;
retain rn;
if first.cust_no then rn = 1;
else rn + 1;
run;

proc sql;
create table summary as
select
  cust_no,
  yearmonth,
  sum(
    case when rn <= 2 then TRNS_AMT else 0 end
  ) as first_two_trns
from temp
group by cust_no, yearmonth;
quit;

[Solly7]

@Kurt_Bremser @PaigeMiller 

[Solly7]

For example when i run my code, on cust_no=1 my rn column gives me 1,2,3,4,5 for all those five rows in the order they appear on my data want.

[PaigeMiller]

Please provide the data as working SAS data step code, which you can type in yourself or follow these instructions.

[Solly7]

data TRNS_TABLE;

informat EFF_DATE date9.;
informat TXN_TIME time15.6;

format EFF_DATE date9.;
format TXN_TIME time15.6;

input cust_no prod_code $ yearmonth TRNS_AMT TXN_TIME EFF_DATE;

datalines;
1 WX 202212 500.00 09:09:40.000000 17DEC2022
1 WX 202212 200.00 12:36:19.000000 19DEC2022
1 WX 202301 500.00 11:14:00.000000 30JAN2023
1 WX 202301 500.00 15:42:32.000000 30JAN2023
1 WX 202301 450.00 12:18:57.000000 31JAN2023
2 AB 202302 1000.00 12:22:45.000000 01FEB2023
;

run;

Hi see above working dataset

[PaigeMiller · Accepted solution]

I need to sum first two transactions per cust_no and summarize my data per cust_no and yearmonth.

Then your code says:

by cust_no eff_date;
retain rn;
if first.cust_no then rn = 1;

You haven't programmed what you said. You programmed first two transactions per cust_no and eff_date. What change should you make so that you get first two transactions by cust_no and yearmonth?

In addition, when you use first.cust_no, you are re-starting the counter RN every time you have a new cust_no. But again, that's not what you said you want, you want the counter to re-start every time there is a new yearmonth. What change would you need?

Lastly, doing this sum in SQL is a lot of typing. Much less typing:

proc summary data=temp(where=(rn<=2)) nway;
    class cust_no yearmonth;
    var trns_amt;
    output out=want sum=sum_trns_amt;
run;

[Solly7]

Hi @PaigeMiller , your proposed solution gives me desired results. Thanks a lot, always learning from you!

[PaigeMiller]

I tried below code didnt work

Never (that's NEVER) tell us something didn't work, without giving more explanation. We don't know what problems you are seeing. Please explain why you say it "didn't work", what is the evidence? Be specific and detailed.