topic Re: max spend in SAS Programming

max spend

wilsonm3e — Mon, 25 Jan 2016 14:08:54 GMT

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

Re: max spend

mohamed_zaki — Mon, 25 Jan 2016 14:13:48 GMT

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

Re: max spend

Kurt_Bremser — Mon, 25 Jan 2016 14:18:56 GMT

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.

Re: max spend

wilsonm3e — Mon, 25 Jan 2016 14:24:49 GMT

do you have an example code of this ? 🙂

Re: max spend

mohamed_zaki — Mon, 25 Jan 2016 14:32:23 GMT

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!

Re: max spend

RW9 — Mon, 25 Jan 2016 14:41:56 GMT

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;