DATA Step, Macro, Functions and more

Latest transaction with multiple entries

Reply
Occasional Contributor
Posts: 15

Latest transaction with multiple entries

Hi guys! How do I get the latest payment of an account with multiple payments on the same date? I need the sum of payments if seperate payments are done on the same date.

Acct_num Payment Pymnt_date
1 21 1oct2015
1 10 1oct2015
1 12 29sep2015
2 15 5oct201
2 11 3oct2015

Latest payment of acct_num 1 should be 31 and 15 for acct_num 2.
Super Contributor
Posts: 275

Re: Latest transaction with multiple entries


proc sql;
select acct_num,sum(payment) as sum
from (select acct_num,payment,pymnt_date
from have group by acct_num
having pymnt_date=max(pymnt_date))
group by acct_num;
quit;

Contributor
Posts: 52

Re: Latest transaction with multiple entries

*** a non SQL solution ****;

 

data t_have;
input Acct_num Payment Pymnt_date date9.;
cards;
1 21 1oct2015
1 10 1oct2015
1 12 29sep2015
2 15 5oct2015
2 11 3oct2015
;
run;

proc print data=t_a;
format Pymnt_date date9.;
run;

proc sort data=t_have; by acct_num descending Pymnt_date; run;

data t_want(keep=acct_num last_date sum_tot);
  set t_have;
  retain sum_tot last_date;
  by acct_num;
  if first.acct_num then do; sum_tot=0; last_date=Pymnt_date; end;
    if (Pymnt_date= last_date) then sum_tot+payment;
  if last.acct_num then output;
run;

proc print data=t_want;
 format last_date date9.;
run;

Super User
Posts: 5,499

Re: Latest transaction with multiple entries

Why do identical amounts on the same day (but with different transaction numbers) represent the same transaction?  Couldn't a customer change his mind and go back and buy another of the same item (or perhaps another item with the same price)?  Couldn't a refund be for part of a purchase instead of the entire purchase?

Occasional Contributor
Posts: 15

Re: Latest transaction with multiple entries

What I meant was multiple payments to the same account on the same day. Like when you pay your elec bill twice on the same day.
Super User
Posts: 19,772

Re: Latest transaction with multiple entries

You could also do a proc means by acct_num and pymt_date. Then calculate 2 metrics, total payment and number of payments per date.

Ask a Question
Discussion stats
  • 5 replies
  • 266 views
  • 0 likes
  • 5 in conversation