GAMS outputs of loop runs to different excel files

Question

I'm using one of the implementations of the epsilon-constrained method to find some of the solutions in the Pareto front. However, I would like to store the results and some post-processed parameters into separate excel files. Most of these solutions are obtained in loops and, therefore, I would like to use the values of the sets being looped to name each excel file.

$Set Instance CraggyTerrain
loop(kp,
  kk(kp)=yes;
  repeat
    solve mod_payoff using mip maximizing obj;
    payoff(kp,kk) = z.l(kk);
    z.fx(kk) = z.l(kk);
*// freeze the value of the last objective optimized
    kk(f++1) = kk(f);
*******************************Mipstart_ Give an intial solution
    x.l(i,j,k)=x.l(i,j,k);
    b.l(i,k)=b.l(i,k);
    y.l(i,j)=y.l(i,j);

execute_unload "Results_payoff%Instance%_%FirstOF(kp)%_%SecondOF(kk)%.gdx" z.l x.L y.L b.L;
execute 'GDXXRW.EXE Results_payoff%Instance%_%FirstOF(kp)%_%SecondOF(kk)%.gdx   var=x.l                rng=x!b2'
execute 'GDXXRW.EXE Results_payoff%Instance%_%FirstOF(kp)%_%SecondOF(kk)%   var=y.L                rng=y!b2'
execute 'GDXXRW.EXE Results_payoff%Instance%_%FirstOF(kp)%_%SecondOF(kk)%   var=b.L                rng=b!b2'

until kk(kp); kk(kp) = no;
* release the fixed values of the objective functions for the new iteration
  z.up(f) = inf; z.lo(f) =-inf;
);

For the %Instance% part I can simply declare a local variable like $Set Instance CraggyTerrain but I can't seem to find a way to use these variables (local, global, environment) to build the rest of the file name, generating a different excel file for each solution.

Is there a way to update local variables with the parameters, set or variables from the model within a loop/repeat statement?

Thank you in advance, Raquel Aguiar.

Have a look at this older post. The answer should have all you need: https://stackoverflow.com/questions/42859215/loop-in-gams-for-scenario-generation-in-excel — Lutz, Mar 24 '20 at 13:50
Thank you @Lutz, I've heard of that GAMS put_utility before, but I've never really looked it up. I've come up with a sloppy solution, where I use a scalar to count the number of runs and then I use an if statement and exhaustively write all the combinations of %FirstOF(kp)%_%SecondOF(kk)%. Now I'm rather pressured to get the results, but once I've finished I'll try make experiments with that GAMS tool and answer this question or make an improved question. Thank you very much. P.S The links in your former answer don't work. — Raquel Aguiar, Mar 24 '20 at 15:06
Thanks for the hint. Here are the current links: https://www.gams.com/latest/docs/UG_Put.html#UG_Put_PutUtil ; https://www.gams.com/latest/docs/T_GDXXRW.html — Lutz, Mar 24 '20 at 15:12

score 1 · Accepted Answer · answered Jul 28 '22 at 18:41

That can be done this way:

Let's say your main gams file is called mymodel.gms, so

First, create an auxiliary file myaux.gms with the following

set i instances /1*2/;
file frun / run.gms /;
put frun '* Run file to run ' card(i):0 'instances of mymodel';
loop(i,
  put / '$call gams mymodel.gms --inst=' i.tl:0
  ' lo=2 o=mymodel' i.tl:0 '.lst lf=mymodel' i.tl:0 '.log'
  / "$if errorlevel 1 $abort 'problems with instance i" i.tl:0 "'";
);

In aux.gms, the set i, serves as the counter for the excel file name. It also creates a third gms file called run.gms. After running aux.gms, you'll see inside the newly craeted runs.gms repetitions of the same call to mymodel.gms for each element in i.

The other stuff: lo, o and lf are used to keep a copy of the .lst and .log files of mymodel.gms.

You then just need to run run.gms.

Inside mymodel.gms, you'll have your model... something like this:

Positive Variables
x,y;
Variable
obj;
Equation
eq1;

Set t /1*2/;

Parameter
g(t)
/
1 100,
2 3
/;

eq1.. obj =e= x+y;
x.up = 2;
y.up = g('%inst%');
model test /all/;

solve test using LP max obj;
mysum = x.l + y.l + %inst%;
execute_unload "exfile_%inst%.gdx"

execute 'gdxxrw.exe exfile_%inst%.gdx o=exfile_%inst%.xlsx par=mysum rng=firstsheet!b2';
execute 'gdxxrw.exe exfile_%inst%.gdx o=exfile_%inst%.xlsx var=x.l rng=secondsheet!c2';

Thank you very much for your answer. It is very complete and almost pedagogical, which is appreciated. That chuck of code is embedded into a bigger part of code and I would probably have to think on how to structure it into the files you suggested. Nevertheless, I didn't know you could do this: g('%inst%'). It actually solved the main problem I had scheduled for this week and will allow me to go on holidays, relaxed, starting tomorrow. What a perfectly timed answer! Thank you once again. — Raquel Aguiar, Jul 29 '22 at 13:26

GAMS outputs of loop runs to different excel files

1 Answers1