Calculate the top 5 and summarize them by store

Question

Let's say I have stores all around the world and I want to know what was my top losses sales across the world per store. What is the code for that?!

here is my try:

proc sort data= store out=sorted_store;
by store descending amount;
run; 

and

data calc1;
 do _n_=1 by 1 until(last.store);
  set sorted_store;
  by store;
  if _n_ <= 5 then "Sum_5Largest_Losses"n=sum(amount);
 end;
run;

but this just prints out the 5:th amount and not 1.. TO .. 5! and I really don't know how to select the top 5 of EACH store . I think a kind of group by would be a perfect fit. But first things, first. How do I selct i= 1...5 ? And not just = 5?

Answer 1

There is also way of doing it with proc sql:

data have;
    input store$ amount;
    datalines;
A 100
A 200
A 300
A 400
A 500
A 600
A 700
B 1000
B 1100
C 1200
C 1300
C 1400
D 600
D 700
E 1000
E 1100
F 1200
;
run;

proc sql outobs=4; /* limit to first 10 results */
  select store, sum(amount) as TOTAL_AMT
  from have
  group by 1
  order by 2 desc; /* order them to the TOP selection*/ 
quit;

Answer 2

The data step sum(,) function adds up its arguments. If you only give it one argument then there is nothing to actually sum so it just returns the input value.

data calc1;
  do _n_=1 by 1 until(last.store);
    set sorted_store;
    by store;
    if _n_ <= 5 then Sum_5Largest_Losses=sum(Sum_5Largest_Losses,amount);
  end;
run;

Answer 3

I would highly recommend learning the basic methods before getting into DOW loops.

• Add a counter so you can find the first 5 of each store
• As the data step loops the sum accumulates
• Output sum for counter=5

proc sort data= store out=sorted_store;
by store descending amount;
run; 


data calc1;
  set sorted_store;
  by store;
  *if first store then set counter to 1 and total sum to 0;
  if first.store then do ; 
       counter=1;
       total_sum=0; 
   end;
  *otherwise increment the counter;
  else counter+1;
  *accumulate the sum if counter <= 5;
  if counter <=5 then total_sum = sum(total_sum, amount);
    *output only when on last/5th record for each store;
  if counter=5 then output; 
run;