topic Re: Selecting the right volume based on weight and scores - Urgent in SAS Programming

Selecting the right volume based on weight and scores - Urgent

Question — Wed, 03 Feb 2021 16:04:30 GMT

Hi,

I have the table below and I would like to reach a sum of weight for example sum(weight)=12,933 (it could be any other number I am targeting) and ideally I would like to pick the ones with the highest scores. I am wondering if there is a way of summing the column Weight till I reach 12,933 as an example.

Your help would be much appreciated. I believe nothing is impossible in the programming world :-).

Thank you

Weight	Region	score	decile
1520	North West	0.80164312	1
2164	North West	0.71899042	1
1849	North West	0.57949577	1
1284	North West	0.36499641	2
1284	North West	0.35679843	2
1739	North West	0.27635649	2
1894	North West	0.26110187	2
1653	North West	0.21283665	2
1739	North West	0.10142876	3
1555	North West	0.08766419	3
1280	North West	0.08371918	3

Re: Selecting the right volume based on weight and scores - Urgent

ballardw — Wed, 03 Feb 2021 16:13:10 GMT

You need to show what the expected result actually looks like.

Also what are the rules when the target cannot be reached exactly? Does Region actually have any role in the calculation/selection? How about Decile? If so, you need to describe how they impact the process.

Re: Selecting the right volume based on weight and scores - Urgent

Question — Wed, 03 Feb 2021 16:49:55 GMT

Hi,

Thank you for coming back...

Basically I have a target volume per region like below....and each region will have weight, scores and deciles. Don't worry about decile etc , as long as I can get to the volume I need (sum of weight) that would be good....if I do a cumulative sum of weight, it doesn't work...but summing them up randomly I could reach almost my target. It doesn't have to be 100% match, as long as it is very close. Thank you

Region	Target Volume (Sum of Weight)
East Midlands	11,939
East of England	14,070
London	12,791
North East	7,829
North West	12,933
Northern Ireland	0
Scotland	14,945
South East	17,597
South West	14,511
Wales	5,704
West Midlands	14,890
Yorks and the Humber	10,526
Total	137,735

Re: Selecting the right volume based on weight and scores - Urgent

ballardw — Wed, 03 Feb 2021 17:13:57 GMT

How close is "close enough"?

So how is score supposed to fit in?

You need to show what you expect the output data set to look like.

Re: Selecting the right volume based on weight and scores - Urgent

Reeza — Wed, 03 Feb 2021 17:18:04 GMT

Would you prefer to match under or over? Wouldn't sorting descending and using a running total until you hit the desired amount work here?
Assuming your Target is in the same data set, if it's not merge it in first and ensure your data is sorted by region and descending weight.

proc sort data=have; by region descending weight; run;

data want;
set have;
by region descending weight;

*holds value across rows;
retain running_total;

*if first region re-set values to prevent prior data from affecting calcuations;
if first.region then do;
running_total = weight;
inclusion = 1;
end;
else if inclusion = 1 then do;
running_total = running_total + weight;
if running_total > target_weight then inclusion = 0;
end;
run;

That's the brute force basic logic way. A slight improvement would be picking the value before the max and then finding the least amount after that would allow the sum to be closest to the target.

Or try PROC OPTMODEL in SAS/OR if you have the correct license, but beyond my knowledge.
https://communities.sas.com/t5/SAS-Programming/Summing-to-a-target-value/td-p/707928

Re: Selecting the right volume based on weight and scores - Urgent

Question — Wed, 03 Feb 2021 18:19:42 GMT

Hi Reeza,

Thank you for your suggestion, I will give it ago 🙂

And for the PROC OPTMODEL , wasn't aware of it...

Best wishes

Re: Selecting the right volume based on weight and scores - Urgent

Question — Thu, 04 Feb 2021 08:26:56 GMT

Thank you Reeza, the link you sent me solved my problem 🙂

I've used a code that was in one of the conversations!

Best Wishes