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.
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;
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;
It works!
Thank you novinosrin !
@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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.