max spend

[wilsonm3e]

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

[mohamed_zaki]

Can not you give example data? And the desired output?

[Kurt_Bremser]

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.

[wilsonm3e]

do you have an example code of this ? 🙂

[mohamed_zaki · Accepted solution]

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!

[RW9]

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;