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?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.