Solved
New Contributor
Posts: 4

# Create multiple cumulative subtotals

Hello All,

I have this dataset:

 Case Amount Need1 Need2 Need3 1 30 20 15 10 2 12 20 15 10 3 10 20 15 10 4 8 20 15 10 5 5 20 15 10 6 2 20 15 10

And need your help to obtain Cumulative_subtotal and Cumulative_subtotal_id fields:

 Case Amount Need1 Need2 Need3 Cumulative_subtotal Cumulative_subtotal_id 1 30 20 15 10 30 1 2 12 20 15 10 12 2 3 10 20 15 10 22 2 4 8 20 15 10 8 3 5 5 20 15 10 13 3 6 2 20 15 10 2 0

Create multiple cumulative subtotals of amount that cover/hedge Needi.

Cumulative_subtotal=30>=Need1=20 end of id=1;

Cumulative_subtotal=22>=Need2=15 end of id=2;

Cumulative_subtotal=13>=Need3=10 end of id=3;

Cumulative_subtotal_id=0 means not needed.

Accepted Solutions
Solution
‎10-21-2013 12:48 PM
Super User
Posts: 6,781

## Re: Create multiple cumulative subtotals

Your explanation helps.  Here is one possibility.

data want;

cumulative_subtotal=0;

retain cumulative_subtotal_id 1;

array need {3} ;

if cumulative_subtotal_id = 0 then total_need = 9999999999999999;

else total_need = need{cumulative_subtotal_id};

do until (cumulative_subtotal >= total_need);

set have;

cumulative_subtotal + amount;

output;

end;

cumulative_subtotal_id + 1;

if cumulative_subtotal_id > 3 then cumulative_subtotal_id=0;

run;

It's untested code, so you may need to tweak it.  But it should have enough working pieces.  Usually when a program is this clunky there is an easier way.  I'm just not seeing it right now.

Good luck.

All Replies
Frequent Contributor
Posts: 83

## Re: Create multiple cumulative subtotals

what is cove/hedge etc..

Please put some usuable sample data

New Contributor
Posts: 4

More details:

Super User
Posts: 13,563

## Re: Create multiple cumulative subtotals

What in the data tells that an id group is starting or ending?

New Contributor
Posts: 4

## Re: Create multiple cumulative subtotals

An id identifies a subtotal: amounts that fill needi must have id=i. The id=i  ends when amount is no longer required for fill needi.

Thanks

Solution
‎10-21-2013 12:48 PM
Super User
Posts: 6,781

## Re: Create multiple cumulative subtotals

Your explanation helps.  Here is one possibility.

data want;

cumulative_subtotal=0;

retain cumulative_subtotal_id 1;

array need {3} ;

if cumulative_subtotal_id = 0 then total_need = 9999999999999999;

else total_need = need{cumulative_subtotal_id};

do until (cumulative_subtotal >= total_need);

set have;

cumulative_subtotal + amount;

output;

end;

cumulative_subtotal_id + 1;

if cumulative_subtotal_id > 3 then cumulative_subtotal_id=0;

run;

It's untested code, so you may need to tweak it.  But it should have enough working pieces.  Usually when a program is this clunky there is an easier way.  I'm just not seeing it right now.

Good luck.

New Contributor
Posts: 4

## Re: Create multiple cumulative subtotals

Astounding,

Thank you for your help. Works perfectly in my example . I just did a very small adjustment for cases when I have Needi=0 (ex. Need2=0, no amount is necessary to fill).

🔒 This topic is solved and locked.