DATA Step, Macro, Functions and more

Issue in picking max amount form same date

Reply
Frequent Contributor
Posts: 79

Issue in picking max amount form same date

Dear all,

 

I have been facing an issue with one logic, I have a list of dates and amounts, I need to pick the max amount from a same date based on a name.. attached is the example. kindly help in finding the mistake here.. Thank you..

 

 

 

Super Contributor
Posts: 340

Re: Issue in picking max amount form same date

Hello,

 

data have;
    infile cards dlm=',' dsd;
    attrib Date format=date9. informat=ddmmyy8.;
    length name $10.;
    input ID Date name $ Amount Flag_first_name Flag_last_name;
cards;
12345,15082014,Bob,100,0,0
12345,20122016,Bob,200,0,0
12345,20122016,Bob,150,0,1
67890,31012012,John,100,0,0
67890,31012006,John,200,1,0
;
run;

proc sql;
    CREATE TABLE want AS
    SELECT *
    FROM have
    WHERE strip(name)="Bob"
    GROUP BY name, date
    HAVING Amount=max(Amount);
quit;
Frequent Contributor
Posts: 79

Re: Issue in picking max amount form same date

Hi Gamotte,

 

Your below program will not work when there is a case like 

 

Bob  21032016  200

Bob  21032016  300

Bob  31122014   500

 

in this case, your logic will return 500, but the answer I am looking for is 200,

 

requirement is , when name=bob, date is latest (when latest data is same and has multiple records, then it should return max amount from those).. so the answer should be Bob  21032016  200

 

kindly advise.

Super Contributor
Posts: 340

Re: Issue in picking max amount form same date

I am not sure i understand the logic you want to implement.

From your description, i would guess the answer should be 300 and not 200.

 

 

data have;
    infile cards dlm=',' dsd;
    attrib Date format=date9. informat=ddmmyy8.;
    length name $10.;
    input ID Date name $ Amount Flag_first_name Flag_last_name;
cards;
12345,21032016,Bob,200,0,0
12345,21032016,Bob,300,0,0
12345,31122014,Bob,500,0,0
67890,31012012,John,100,0,0
67890,31012006,John,200,1,0
;
run;

proc sql;
    CREATE TABLE want AS
	SELECT * FROM (
        SELECT *
        FROM have
        WHERE strip(name)="Bob"
        GROUP BY name
        HAVING Date=max(Date)
	)
    GROUP BY Name
    HAVING Amount=max(Amount);
quit;
Super User
Posts: 10,270

Re: Issue in picking max amount form same date

Stealing @gamotte's example data:

data have;
    infile cards dlm=',' dsd;
    attrib Date format=date9. informat=ddmmyy8.;
    length name $10.;
    input ID Date name $ Amount Flag_first_name Flag_last_name;
cards;
12345,21032016,Bob,200,0,0
12345,21032016,Bob,300,0,0
12345,31122014,Bob,500,0,0
67890,31012012,John,100,0,0
67890,31012006,John,200,1,0
;
run;

proc sort data=have;
by id date amount;
run;

data want;
set have;
by id;
if last.id;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 4 replies
  • 63 views
  • 0 likes
  • 3 in conversation