I have following data : data _tmp_; length Customer $6. Type $4.; input Customer $ Type $ Leakage_amount year; datalines; ABC123 Mid 1500 2019 ABC123 Mid 7000 2020 ABC123 SB&A 2000 2020 ;run; And I want my output as, summing Leakage_Amount and picking Type with highest Leakage_Amount - ABC123 Mid 1500 2019 ABC123 Mid 9000 2020 I can get this by proc sql; select a.*,b.Type from (select Customer, year, sum(Leakage_amount) as Leakage_amount from _tmp_ group by 1,2) as a left join (select Customer, year, Type from _tmp_ group by 1,2 having Leakage_amount = max(Leakage_amount)) as b on A.Customer=B.Customer and A.Year=B.Year ;quit; But this seems like overkill, there must be an easier and neater way to do this. Please help.
... View more
