Hello all
I am sure this question is out there, I just cannot find it!
I have member ids that have multiple purchase dates. So I took the most recent date. Well, some member ids have multiple purchases in one day.
Is there a code to take the max spend for that mem id so I can get rid of the second date with the smaller amount?
Mary
Data have;
input ID Date Amount;
cards;
111 1 30
111 1 34
111 5 70
112 1 30
112 2 100
112 2 79
112 5 55
112 5 5
;
run;
proc sort data=have;
by ID date Amount;
run;
Data want ;
set have;
by ID ;
if last.ID then output;
run;
By this you will get the max spend in the most recent date for each mem id....
Hope this what you want!
Can not you give example data? And the desired output?
Sort by id, purchase date and amount.
Then use last.id in a data step to get the most recent date, and from that the highest amount.
do you have an example code of this ? 🙂
Data have;
input ID Date Amount;
cards;
111 1 30
111 1 34
111 5 70
112 1 30
112 2 100
112 2 79
112 5 55
112 5 5
;
run;
proc sort data=have;
by ID date Amount;
run;
Data want ;
set have;
by ID ;
if last.ID then output;
run;
By this you will get the max spend in the most recent date for each mem id....
Hope this what you want!
What happens if you have multiple rows with the same max? As an alternative this returns all records meeting the max() result:
proc sql; create table WANT as select distinct * /* Note take distinct out if you want all rows */ from HAVE group by ID having AMOUNT=max(AMOUNT); quit;
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.