DATA Step, Macro, Functions and more

max spend

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

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 ]
Posted in reply to wilsonm3e
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!

View solution in original post


All Replies
Super Contributor
Posts: 490

Re: max spend

Posted in reply to wilsonm3e

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

Super User
Posts: 7,761

Re: max spend

Posted in reply to wilsonm3e

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
Occasional Contributor
Posts: 5

Re: max spend

Posted in reply to KurtBremser

do you have an example code of this ? Smiley Happy

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

Re: max spend

[ Edited ]
Posted in reply to wilsonm3e
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
Super User
Posts: 7,942

Re: max spend

[ Edited ]
Posted in reply to wilsonm3e

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 252 views
  • 2 likes
  • 4 in conversation