Selecting the cumulative till I reach 75%

[AK100]

Hello all,

I'am quite new to SAS EG and I want to get the following: I want to select all the measurements who have the highest percentage (from high to low per month AND section) till I reach a cumulative total percentage of minimal 75%. .

So (see screenshot) from the month July and section 2632CT I want to select just the percentage 53% + 28% in a cumulative column. For the month July and section 2646BT I want to select just 49% and 26% in cumulative etc. So I just want to select the highest percentages in a cumulative form until I reach a minimal of 75%. The count has to start from 0 again at the beginning of every new section (so actually 2x per month)

I hope its all clear, I appreciate your help.

[Kurt_Bremser]

Sort by month, section and descending percentage;

Then do

data want;
set have;
by maand1 sectie;
if sum_perc le .75 then output;
if first.section
then sum_perc = percentage;
else sum_perc + percentage; /* causes automatic retain */
drop sum_perc;
run;

[art297 · Accepted solution]

Slight problem with the code suggested by @Kurt_Bremser . I'd use:

proc sort data=have out=need;
  by maand1 sectie descending percentage;
run;

data want;
  set need;
  by maand1 sectie;
  if first.sectie then sum_perc = 0;
  if sum_perc lt .75 then do;
    sum_perc+percentage;
    output;
  end;
  drop sum_perc;
run;

Art, CEO, AnalystFinder.com

[Kurt_Bremser]

Happens when you have no data to test against.

[AK100]

That was a good one @art297.

[AK100]

thx.