## max spend

Solved
Occasional Contributor
Posts: 5

# max spend

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

Accepted Solutions
Solution
‎01-25-2016 09:36 AM
Super Contributor
Posts: 490

## Re: max spend

[ Edited ]
``````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!

All Replies
Super Contributor
Posts: 490

## Re: max spend

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

Super User
Posts: 10,209

## Re: max spend

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 5

## Re: max spend

do you have an example code of this ?

Solution
‎01-25-2016 09:36 AM
Super Contributor
Posts: 490

## Re: max spend

[ Edited ]
``````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!

Super User
Posts: 9,599

## Re: max spend

[ Edited ]

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;```
🔒 This topic is solved and locked.