CODEWITHSUNDEEP
excelloopsoutputgams-math
Raquel Aguiar
Raquel Aguiar

Reputation: 73

GAMS outputs of loop runs to different excel files

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.

Upvotes: 2

Views: 297

Answers (1)

Arraval
Arraval

Reputation: 1130

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';

Upvotes: 1

Related Questions