topic Re: Summary of top 3 values by group in SAS Programming

Summary of top 3 values by group

DiG — Mon, 25 Feb 2019 18:58:54 GMT

Hi,

A seemingly easy task of summarizing a variable by group has become challenging when I was asked to find the sum of only top three values in each group. I attached a sample table here. Need to find the totals of top 3 amounts for each industry group (amounts are already sorted in descending order). Some industry groups have only one or two records. Can anybody help me with this?

Thanks.

Re: Summary of top 3 values by group

novinosrin — Mon, 25 Feb 2019 19:07:39 GMT

Are you after this?


data have;
input memberid : $	industry	amount;
cards;
A2348	110	10045643
A5469	110	9543376
A21563	110	9034166
D6765	110	4876
B2345	110	46
A5463	111	89
A9876	111	30
B8766	112	583497
A5689	112	525488
D9655	112	75158
A4566	112	43866
D67896	112	1865
D4566	112	568
A2876	112	366
;

data want;
if 0 then set have;
sum=0;
do _n_=1 by 1 until(last.industry);
set have;
by industry;
sum+(_n_<=3)*amount;
end;
drop amount;
run;

Re: Summary of top 3 values by group

novinosrin — Mon, 25 Feb 2019 19:11:57 GMT

@DiG if the previous is not clear. the traditional method as follows

data want;
set have;
by industry;
if first.industry then do; n=1;sum=0;end;
else n+1;
sum+(n<=3)*amount;
if last.industry;
drop n amount;
run;

Re: Summary of top 3 values by group

DiG — Mon, 25 Feb 2019 19:29:38 GMT

It works!

Thank you novinosrin !