topic Re: sum of the first two transactions in SAS Programming

sum of the first two transactions

Solly7 — Thu, 25 May 2023 08:16:35 GMT

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;

Re: sum of the first two transactions

Solly7 — Thu, 25 May 2023 09:15:07 GMT

@Kurt_Bremser @PaigeMiller

Re: sum of the first two transactions

PaigeMiller — Thu, 25 May 2023 09:25:51 GMT

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.

Re: sum of the first two transactions

Solly7 — Thu, 25 May 2023 09:35:27 GMT

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.

Re: sum of the first two transactions

PaigeMiller — Thu, 25 May 2023 09:39:11 GMT

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

Re: sum of the first two transactions

Solly7 — Thu, 25 May 2023 09:57:42 GMT

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

Re: sum of the first two transactions

PaigeMiller — Thu, 25 May 2023 10:10:12 GMT

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;

Re: sum of the first two transactions

Solly7 — Thu, 25 May 2023 11:05:41 GMT

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