Issue in picking max amount form same date

[don21]

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..

[gamotte]

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;

[don21]

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.

[gamotte]

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;

[Kurt_Bremser]

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;