topic Re: Create multiple cumulative subtotals in SAS Procedures

Create multiple cumulative subtotals

PC — Sat, 19 Oct 2013 22:16:57 GMT

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.

Thank you for your help,

Re: Create multiple cumulative subtotals

Mit — Mon, 21 Oct 2013 02:03:17 GMT

Could you please explain your problem a bit more.

what is cove/hedge etc..

Please put some usuable sample data

Re: Create multiple cumulative subtotals

ballardw — Mon, 21 Oct 2013 15:04:13 GMT

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

Re: Create multiple cumulative subtotals

PC — Mon, 21 Oct 2013 15:46:41 GMT

More details:

Re: Create multiple cumulative subtotals

PC — Mon, 21 Oct 2013 15:58:54 GMT

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

Re: Create multiple cumulative subtotals

Astounding — Mon, 21 Oct 2013 16:48:30 GMT

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.

Re: Create multiple cumulative subtotals

PC — Tue, 22 Oct 2013 11:39:49 GMT

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).