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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.