Page 1 of 1

Appending to a parameter

Posted: Wed Jan 02, 2019 1:18 am
by davidwogan
Hi everyone,

I am constructing a linear program of an energy system with multiple sectors. For each sector, some calculations are performed and saved in an Excel workbook. Each workbook contains a portion of the data that makes up a parameter. I would like to define a parameter in a .gms file and call each of the Excel workbooks (using GDXXRW) to construct the single parameter. I have not found a way to do this that doesn't overwrite the parameter with each GDXXRW call.

For example, workbook 1 would have:

EconomyA.Sector1.1990
EconomyA.Sector1.1991
EconomyA.Sector1.1992

and workbook 2 would have:
EconomyA.Sector2.1990
EconomyA.Sector2.1991
EconomyA.Sector2.1992

The parameter would be the six elements. Has anyone tried to do this? I searched on the forum but didn't find a related post (I might have missed one, though).

Thank you,

David Wogan

Re: Appending to a parameter

Posted: Wed Jan 02, 2019 2:15 pm
by dirkse
David,

To solve your problem it's helpful to get a little bit below the surface and note that gdxxrw does not communicate directly with GAMS - instead, it moves data between Excel and a GDX file. There's plenty to say about GDX, but I can't do it better than in the docs:

https://www.gams.com/latest/docs/UG_GDX.html

So your gdxxrw calls will read data from spreadsheets into GDX files. You mention 6 sectors. I will assume you read data into 6 different GDX files. Once this is done, you can merge the data using the $onMulti directive, like this example for two gdx files s1 and s2:

Code: Select all

parameter a(*,*);

$gdxin s1.gdx
$load a
$gdxin

$onMulti
$gdxin s2.gdx
$load a
$gdxin
$offMulti
You could also go for a scheme where you use the same file name and repeatedly write the GDX and read it into GAMS. That would also work.

-Steve

Re: Appending to a parameter

Posted: Wed Jan 02, 2019 9:36 pm
by davidwogan
Hi Steve,

Thanks for the tips. Looks like $onMulti and $offMulti are exactly what I was looking for:
By default, GAMS does not allow data statements to be redefined. If this option is activated the second or subsequent data statements are merged with entries of the previous ones. Note that all multiple data statements are performed before any other statement is executed.
https://www.gams.com/latest/docs/UG_Dol ... onoffmulti

Thanks!

David