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;
*** 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;
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: