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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.