BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
wilsonm3e
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
mohamed_zaki
Barite | Level 11
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

5 REPLIES 5
mohamed_zaki
Barite | Level 11

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

wilsonm3e
Fluorite | Level 6

do you have an example code of this ? 🙂

mohamed_zaki
Barite | Level 11
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
Diamond | Level 26 RW9
Diamond | Level 26

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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