- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Please provide the data as working SAS data step code, which you can type in yourself or follow these instructions.
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi @PaigeMiller , your proposed solution gives me desired results. Thanks a lot, always learning from you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
Paige Miller