topic Goal seek to solve the rounded numbers with target sum in SAS Programming

Goal seek to solve the rounded numbers with target sum

1Alina — Wed, 24 Apr 2019 19:06:50 GMT

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.

Re: Goal seek to solve the rounded numbers with target sum

PGStats — Wed, 24 Apr 2019 19:47:29 GMT

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

Re: Goal seek to solve the rounded numbers with target sum

1Alina — Thu, 25 Apr 2019 14:50:11 GMT

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.