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,
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.
Could you please explain your problem a bit more.
what is cove/hedge etc..
Please put some usuable sample data
More details:
What in the data tells that an id group is starting or ending?
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
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.
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).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.