topic Re: Selecting the cumulative till I reach 75% in SAS Enterprise Guide

Selecting the cumulative till I reach 75%

AK100 — Mon, 30 Mar 2020 13:44:50 GMT

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.

Re: Selecting the cumulative till I reach 75%

Kurt_Bremser — Mon, 30 Mar 2020 13:56:10 GMT

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;

Re: Selecting the cumulative till I reach 75%

art297 — Mon, 30 Mar 2020 14:54:23 GMT

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

Re: Selecting the cumulative till I reach 75%

Kurt_Bremser — Mon, 30 Mar 2020 15:16:54 GMT

Happens when you have no data to test against.

Re: Selecting the cumulative till I reach 75%

AK100 — Tue, 31 Mar 2020 05:38:03 GMT

That was a good one @art297.

Re: Selecting the cumulative till I reach 75%

AK100 — Tue, 31 Mar 2020 05:38:23 GMT

thx.