Aggregating tables in Gams in a flexible way using mappings and compile-time variables

Gams makes aggregating tables quite easy. Imagine you a table with data for 100 sectors but you want to run your model in the testing phase for an aggregation of these sectors (e.g. an aggregate the 100 sectors to the three sector groups “agriculture”, “industry”, and “services”). This is a typical situation in CGE (computable general equilibrium modeling): you have a social accounting matrix for your country and you want to start with a simple model having only a few sectors, one household, no taxes, and no government).
In Gams you simply introduce a mapping that maps the 100 sectors to the three groups. Once you have this mapping, producing the aggregated table of the data is just a one-liner.

What follows is the code to import a table from Excel, aggregate the table and export it.The Excel table is called sam.xlsx and contains the following table in sheet1:

The following file “data.gms” produces an aggregated table:

And this is the result:

You can make this procedure flexible by defining a compile-time variable, so you can have multiple aggregations (you probably want to have your data for the full table and an aggregated table). For this, we enter the following code at the beginning of the file:

This defines the local compile-time variable “mapping” which has the value “agg1”. When you compile/execute your file, Gams will replace %mapping% in your code with the actual value of the variable “mapping”.

We move the mapping (lines 6-11) in a separate file called “” and in the main file we put here instead the following line:

Gams will now replace %mapping% by “agg1” and include the file “”. We also change the line, where we export the results as follows, so Gams will export the results to the file “agg1.gdx”:

Additionally, we add a mapping file with the full disaggregation (, which maps each element to itself:

We now can produce a gdx with the full table by replacing “agg1” by “full” (or run the file in the command line as “gams data.gms –mapping=full”):

We now will have a gdx file “full.gdx” with the full table.

Now, we can run our model code in the full disaggregated or aggregated version. For this, you will have to put the following lines at the start of your model file:

Notice, that I revert back to the original names for the set and parameter by loading and renaming at the same time (“$load alle=agg” loads the set agg as “alle”).

In a next post, I will introduce some code to check if your mapping contains all set elements or repeats elements on both sides.