Generate variable: optimisation based on constraint defined by other variables

[Adam Eastwood]

Dear Stata users,

I'm running Stata 15.1, and would like to generate a variable the values of which are the result of constrained optimisation, with the constraint defined by the corresponding values of other variables.

For example, in the example dataset below, Avg_EuropRecip in observation 11 is below Cat_LB (Lower Boundary): 167300 < 200000. I want my new variable, Recip_ADJUSTED, to return the maximum integer which satisfies the constraint Euro_RAW / Recip_ADJUSTED ≥ Cat_LB. For observation 11, the value generated would be 16, since 3346000 / 16 ≥ 200000 whereas the next largest integer, 17, does not satisfy the constraint.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(FY Cat_LB Cat_UB) int Recip_RAW float(Euro_RAW Avg_Euro_per_Recip)
2014      0    500 4770  1330000   278.826
2014    500   1250 4640  3806000  820.2586
2014   1250   2000 2090  3312000  1584.689
2014   2000   5000 2590  7977000  3079.923
2014   5000  10000 1120  7813000  6975.893
2014  10000  20000  820 11691000 14257.317
2014  20000  50000  650 20224000 31113.846
2014  50000 100000  250 17244000     68976
2014 100000 150000   80  9855000  123187.5
2014 150000 200000   40  6950000    173750
2014 200000 250000   20  3346000    167300
2014 250000 300000   10  1400000    140000
2014 300000 500000   10  3518000    351800
2014 500000      .    0   597000         .
end

The only code which I know for sure I need is

if Avg_EuropRecip < Cat_LB

at the end of the line. As for coding the constraint, I am lost, though I am guessing that it might be possible with some combination of egen Recip_ADJ = max() and cond(), but

(1) I don't know how to create code for a self-referential variable (i.e. Recip_ADJ is used for defining Recip_ADJ);
(2) I don't know whether there is a way to have an indefinitely range of numbers in max() i.e. for it to be max(1, 2, ... 1000000).

I would appreciate any help, as it seems it should be possible to achieve without resorting to Mata functions such as optimize(), which I don't at all know how to use.

Thanks.

[Wouter Wakker]

I think you might be making this harder than it is. Dividing Euro_raw by the lower bound gives you the exact number of recipients so that euro_raw/recipients=lower bound. Then you only have to round the recipients down to the nearest integer to satisfy your condition euro_raw/recipients ≥lower bound. "gen int" will do this automatically for you.

. gen int recip_adjusted = Euro_RAW / Cat_LB
(1 missing value generated)

. list

     +--------------------------------------------------------------------+
     |   FY   Cat_LB   Cat_UB   Recip_~W   Euro_RAW   Avg_Eu~p   recip_~d |
     |--------------------------------------------------------------------|
  1. | 2014        0      500       4770    1330000    278.826          . |
  2. | 2014      500     1250       4640    3806000   820.2586       7612 |
  3. | 2014     1250     2000       2090    3312000   1584.689       2649 |
  4. | 2014     2000     5000       2590    7977000   3079.923       3988 |
  5. | 2014     5000    10000       1120    7813000   6975.893       1562 |
     |--------------------------------------------------------------------|
  6. | 2014    10000    20000        820   1.17e+07   14257.32       1169 |
  7. | 2014    20000    50000        650   2.02e+07   31113.85       1011 |
  8. | 2014    50000   100000        250   1.72e+07      68976        344 |
  9. | 2014   100000   150000         80    9855000   123187.5         98 |
 10. | 2014   150000   200000         40    6950000     173750         46 |
     |--------------------------------------------------------------------|
 11. | 2014   200000   250000         20    3346000     167300         16 |
 12. | 2014   250000   300000         10    1400000     140000          5 |
 13. | 2014   300000   500000         10    3518000     351800         11 |
 14. | 2014   500000        .          0     597000          .          1 |
     +--------------------------------------------------------------------+

As you can see, this doesn't work for the first observation because Cat_LB is zero, but in this case the maximum amount of recipients which satisfies your condition is infinite.

[Adam Eastwood]

Thanks very much, that works perfectly.

And in the cases where the numbers are at the other bound, and I'm trying to get the minimum integer which satisfies the opposite constraint (Euro_RAW / Recip_ADJUSTED < Cat_UB), I take it that it is sufficient to bracket the constraint inside the ceil() command?

[Wouter Wakker]

Yes exactly, that will work.

gen int recip_UB = ceil(Euro_RAW / Cat_UB)

[Adam Eastwood]

Thanks!