Aggregating a column and keeping rows with max in same step

[thepushkarsingh]

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.

[PaigeMiller]

You can use PROC MEANS or PROC SUMMARY with the SUM output statistic and the MAXID output statistic.

Example: https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=proc&docsetTarget=n18axsz...

[thepushkarsingh]

Many thanks Paige, yours is a quite neat way. I really appreciate this very informative link. I wasn't even aware of MAXID.

[novinosrin · Accepted solution]

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;

proc sql;
 create table want as
 select *, sum(Leakage_amount) as sum_Leakage_amount 
 from _tmp_
 group by customer,year
 having Leakage_amount=max(Leakage_amount);
quit;

 A good use case to take advantage of auto-remerge by the SQL processor.

[thepushkarsingh]

Many thanks, I seem to missing the part where I had to rename the summed variable. I was using the same name(replacing Leakage_Amount) and it was throwing error, so had to go a long route.

[yabwon]

Hi,

Maybe double DoW-loop:

data have;
length Customer $6. Type $4.;
input
Customer $ Type $ Leakage_amount year;
datalines4;
ABC123 Mid 1500 2019
ABC123 Mid 7000 2020
ABC123 SB&A 2000 2020
;;;;
run;

data want;
  _max=.;
  _out=0;
  _tot=0;
  do _N_ = 1 by 1 until(last.year);
    set have;
    by year;

    if Leakage_amount > _max then
      do;
        _max = Leakage_amount;
        _out = _N_;
      end;
    _tot + Leakage_amount;
  end;

  do _N_ = 1 to _N_;
    set have;

    if _out = _N_ then
      do;
        Leakage_amount = _tot;
        output;
      end;
  end;
  drop _:;
run;
proc print;
run;

Bart

[thepushkarsingh]

This is more of overkill!