Hello community,
I have a selection plan problem to be solved by SAS programming. I add an example table with pseudo data.
I have four zones for which I have calculated selection estimates by a regulated method. The selection amount for each zone is fixed (regulated), as follows (variable FIXED)
ZONE | FIXED |
1 | 200 |
2 | 120 |
3 | 80 |
4 | 100 |
SUM | 500 |
Because the calculated estimates are slightly lower than the aimed selection, they should be "scaled" by zones to reach the target. The final selection for each class by zone must be an even number so that the sum fulfils the fixed target.
ZONE | CLASS | ESTIMATE | SCALED | SELECTION | SUM BY ZONE |
1 | A | 32,4677 | 30,8443 | 31 | |
1 | B | 36,0680 | 34,2646 | 34 | |
1 | C | 62,6852 | 59,5510 | 60 | |
1 | D | 47,1945 | 44,8348 | 45 | |
1 | E | 31,5844 | 30,0052 | 30 | 200 |
2 | A | 14,5252 | 23,2403 | 23 | |
2 | B | 14,1779 | 22,6846 | 23 | |
2 | C | 29,5432 | 47,2691 | 47 | |
2 | D | 6,7894 | 10,8630 | 11 | |
2 | E | 9,9574 | 15,9318 | 16 | 120 |
3 | A | 51,1834 | 36,3402 | 36 | |
3 | B | 0,6335 | 0,4498 | 0 | |
3 | C | 36,9613 | 26,2425 | 26 | |
3 | D | 5,8361 | 4,1436 | 4 | |
3 | E | 15,3799 | 10,9197 | 11 | |
3 | F | 3,7684 | 2,6755 | 3 | 80 |
4 | A | 39,8149 | 61,7131 | 62 | |
4 | B | 0,4815 | 0,7463 | 1 | |
4 | C | 4,7643 | 7,3847 | 7 | |
4 | D | 2,9040 | 4,5013 | 5 | |
4 | E | 12,9612 | 20,0899 | 20 | |
4 | F | 3,5365 | 5,4816 | 5 | 100 |
SUM | 489,2624 | 500,1771 | 500 | 500 |
Manually I seek for a coefficient to multiply C1 * ESTIMATE = SCALED so that the sum of round(SCALED,1) reaches the FIXED value set for ZONE=1. Respectively, C2 for ZONE=2, C3 for ZONE=3 and C4 for ZONE=4. For this (not real) data the suitable coefficients would be
C1 = 0.95
C2 = 1.60
C3 = 0.71
C4 = 1.55
With real data the coefficients Cn will be quite near 1, something between 0.98 - 1.02.
I'm looking for a way to solve the coefficients so that ESTIMATE is scaled up (or down) so rounded SELECTION fulfils the target set in FIXED by ZONE.
Hopefully someone can help me out with this problem.
I don't think that you can always find a fixed factor that will do the job (but too lazy to prove it). The following allocation procedure always works for me:
data test;
input ZONE CLASS $ ESTIMATE :commax.;
datalines;
1 A 32,4677
1 B 36,0680
1 C 62,6852
1 D 47,1945
1 E 31,5844
2 A 14,5252
2 B 14,1779
2 C 29,5432
2 D 6,7894
2 E 9,9574
3 A 51,1834
3 B 0,6335
3 C 36,9613
3 D 5,8361
3 E 15,3799
3 F 3,7684
4 A 39,8149
4 B 0,4815
4 C 4,7643
4 D 2,9040
4 E 12,9612
4 F 3,5365
;
data zones;
input ZONE FIXED;
datalines;
1 200
2 120
3 80
4 100
;
data want;
sumEst = 0;
do until(last.zone);
merge test zones;
by zone;
sumEst = sumEst + estimate;
end;
remain = fixed;
do until(last.zone);
set test; by zone;
selection = round((estimate / sumEst) * remain);
sumEst = sumEst - estimate;
remain = remain - selection;
output;
end;
keep zone class estimate selection;
run;
proc sql;
select *, sum(selection) as sumFixed
from want
group by zone
order by zone, class;
quit;
ZONE CLASS ESTIMATE selection sumFixed ------------------------------------------------- 1 A 32.4677 31 200 1 B 36.068 34 200 1 C 62.6852 60 200 1 D 47.1945 45 200 1 E 31.5844 30 200 2 A 14.5252 23 120 2 B 14.1779 23 120 2 C 29.5432 47 120 2 D 6.7894 11 120 2 E 9.9574 16 120 3 A 51.1834 36 80 3 B 0.6335 0 80 3 C 36.9613 26 80 3 D 5.8361 4 80 3 E 15.3799 11 80 3 F 3.7684 3 80 4 A 39.8149 62 100 4 B 0.4815 1 100 4 C 4.7643 7 100 4 D 2.904 4 100 4 E 12.9612 20 100 4 F 3.5365 6 100
I don't think that you can always find a fixed factor that will do the job (but too lazy to prove it). The following allocation procedure always works for me:
data test;
input ZONE CLASS $ ESTIMATE :commax.;
datalines;
1 A 32,4677
1 B 36,0680
1 C 62,6852
1 D 47,1945
1 E 31,5844
2 A 14,5252
2 B 14,1779
2 C 29,5432
2 D 6,7894
2 E 9,9574
3 A 51,1834
3 B 0,6335
3 C 36,9613
3 D 5,8361
3 E 15,3799
3 F 3,7684
4 A 39,8149
4 B 0,4815
4 C 4,7643
4 D 2,9040
4 E 12,9612
4 F 3,5365
;
data zones;
input ZONE FIXED;
datalines;
1 200
2 120
3 80
4 100
;
data want;
sumEst = 0;
do until(last.zone);
merge test zones;
by zone;
sumEst = sumEst + estimate;
end;
remain = fixed;
do until(last.zone);
set test; by zone;
selection = round((estimate / sumEst) * remain);
sumEst = sumEst - estimate;
remain = remain - selection;
output;
end;
keep zone class estimate selection;
run;
proc sql;
select *, sum(selection) as sumFixed
from want
group by zone
order by zone, class;
quit;
ZONE CLASS ESTIMATE selection sumFixed ------------------------------------------------- 1 A 32.4677 31 200 1 B 36.068 34 200 1 C 62.6852 60 200 1 D 47.1945 45 200 1 E 31.5844 30 200 2 A 14.5252 23 120 2 B 14.1779 23 120 2 C 29.5432 47 120 2 D 6.7894 11 120 2 E 9.9574 16 120 3 A 51.1834 36 80 3 B 0.6335 0 80 3 C 36.9613 26 80 3 D 5.8361 4 80 3 E 15.3799 11 80 3 F 3.7684 3 80 4 A 39.8149 62 100 4 B 0.4815 1 100 4 C 4.7643 7 100 4 D 2.904 4 100 4 E 12.9612 20 100 4 F 3.5365 6 100
Thanks PGStats! Your suggestion worked out well for my needs. I think the same that there is no a fixed factor available due to rounding process - rather it is a small range of factors.
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.