Colorado GAMS

GAMS2TBL: A GAMS LIBINCLUDE Program

for Producing Formatted Tables

Thomas F. Rutherford*

Department of Economics
University of Colorado

August, 1997

Revised, January, 1998

* This research supported by the GAMS Applied General Equilibrium Research Fund. The software described here operates only with GAMS 2.25.087 or later on the PC, shipped in December, 1995. The author remains responsible for any bugs which exist in this software. This software is not officially supported by GAMS Corporation.


This is unsupported software developed for my own use which I am distributing freely to GAMS users. This program is intended to automate report writing from GAMS programs. The GAMS PUT facility provides all the tools required for writing detailed output reports, but model-specific report-writing with the PUT facility can be tedious. The include program described here provides a simplified wrapper around the PUT statements, so that it is possible to generate nice clean tabular reports without much effort.

GAMS2TBL is a $LIBINCLUDE program which produces formatted tables in a any of four formats: flat text (TXT), Latex tables (TEX), numeric spreadsheet import format (PRN) and Netscape markup language (HTML).


Changes in 3/98 bug fix: tables output in PRN format can now be imported into Excel. (Thanks to that spreadsheet wizard Michael Ferris for this fix!)

Changes in the 9/97 update: GAMS2TBL now can be used to produce tables with text rather than numeric contents (see example7.gms), it is possible to embed a loop label inside a title (see example6.gms), and the Latex output now centers column headings while right-justifying table entries.

Changes in the 1/98 update: libinclude files are now included in the common archive, inclib.pck.

Changes in 4/99 update: included warning about establishing the PUT unit immmediately before $libinclude gams2tbl.


Contents:


Invocation:

The standard invocation sequence for generating a text table from within a GAMS program using the GAMS2TBL package is:


file ktab /mytable.txt/; put ktab;
$libinclude gams2tbl [item]

where [item] is an two-dimensional parameter, level value or marginal. In some cases (e.g., when GAMS2TBL is to be called from within a loop), it is necessary to make an initialization call to the package with no output item. For this reason, [item] is optional.


Using GAMS2TBL with MPSGE (tips provided by Randy Wigle)

When you are using MPSGE it is essential to establish the output file immediately before invoking the GAMS2TBL library routine. The following code fragment illustrates how to initialize and then invoke GAMS2TBL when solving an MPSGE model within a loop. We include a PUT to the proper unit just before calling GAMS2TBL because otherwise output would be written to the MPSGE scratch file.


file ktab /mytable.txt/; 

$libinclude gams2tbl

loop(sc,

...

$include mymodel.gen
solve mymodel using mcp;

...

put ktab;
$libinclude gams2tbl report

);

Files distributed with the package include:

FileDescription
example1.gmsNo-frills invocation illustrating various table formats.
example2.gms Illustrates how it is possible to order columns and rows.
example3.gms Illustraes relabeling of rows and columns.
example4.gms Illustrates numeric formatting.
example5.gms Illustrates alternative treatments of zero entries and zero rows.
example6.gms Illustrates generation of labelled tables within a loop.
example7.gms Illustrates generation of text tables.
test.bat Batch file to process the examples and display results
gams2tbl.gms Libinclude file (GAMS code to place in \gams\inclib)

Hardware and software requirements:

1. A computer (no PC-specific code!)

2. GAMS version 2.25.089 or later (released in October, 1996), available from: GAMS Development Corp., 1217 Potomac Street NW, Washington DC 20007 Phone: (202) 342 0180, Fax: (202) 342 0181 email:sales@gams.com

Installation:

1. Download inclib.pck into your GAMS system directory, and run GAMSINST.

The file inclib.pck contains all of the libinclude programs which have been developed at the University of Colorado during the past couple of years. These include files for data file management (GAMS2TXT), spreadsheet interface (SSLINK), plotting (GNUPLOT), and interactive modeling (GAMSCGI). Henceforth I will be keeping libinclude source files for all of these applications in a single archive in order to simplify maintenance. You will need to download additional batch and executable files in order to install these other packages.

2. Download gams2tbl.zip into a working directory, and execute test.bat.

Reserved names:

The libinclude file uses some parameters and sets which are declared the first time the program is included. The names which are reserved for use in GAMS2TBL all begin with the letters "tbl". Programs which are using the libinclude program must avoid all of the following names:

tblc            tblr            tbllw           tbltw           tbl_rv  
tblcol          tblrow          tbllwidth       tblu_1  
tblcols         tblrows         tblnd           tblu_2  
tblcount        tbltj           tblnw           tbl_cv  

GAMS environment variables for formatting

:

Global (GAMS) environment variables control the output. The example programs illustrate how these switches can be employed. The following lists all of the control parameters which can be used to control GAMS2TBL output:


        col_label(i)    Subset defining column i lables to substitute
                        for set identifiers. (See Example3.gms).

        col_order(i)    Parameter array specifying column order.
                        Columns are displayed in increasing order
                        (See Example2.gms.)

        col_set         Set identifying column elements to include in
                        the  table.  This set specification can be
                        used to suppress certain columns from the
                        output table, or it may be used to force display
                        of a column of zeros.

        c_decimals      Parameter array specifying the number of
                        decimal places for entries in a given column.
                        (See Example4.gms)

        format          Name of the output format (txt, html, tex or
                        prn -- See Example1.gms)

        row_label       Row label set  (See Example3.gms).

        row_order       Parameter defining row order (See Example2.gms)

        row_set         Set identifying rows to include in the displayed 
                        table.  By default all non-zero rows are 
                        displayed. (See Example5.gms)

        r_decimals      Parameter specifying the number of decimal
                        places in each row.  (See Example4.gms)

        title           Table title (See Example3.gms) 

        title_just      Title justification -- left, center, right
                        (See Example3.gms.)
        
        zeros           Controls whether zeros are displayed as numeric
                        values or spaces.  Default value is "yes" -- 
                        matrix zeros are formatted as numbers.
                        (See Example5.gms)



Example1.gms:

$title GAMS2TBL Example 1: Simple no-frills invocation set i /1*5/; set j /a,b,c,d/; parameter A A random table with default formatting; A(i,j) = uniform(0,100); file ktxt /example1.txt/; put ktxt; $libinclude gams2tbl A * Then generate files with the table in other formats: file ktex /example1.tex/; put ktex; $setglobal format tex $libinclude gams2tbl A file kprn /example1.prn/; put kprn; $setglobal format prn $libinclude gams2tbl A file khtm /example1.htm/; put khtm; $setglobal format html $libinclude gams2tbl A


Example1.txt:

 
A random table with default formatting
-------------------------------------------------
            A           B           C           D
-------------------------------------------------
1       17.17       84.33       55.04       30.11
2       29.22       22.41       34.98       85.63
3        6.71       50.02       99.81       57.87
4       99.11       76.23       13.07       63.97
5       15.95       25.01       66.89       43.54
-------------------------------------------------


Example1.tex:


\begin{table}[ht]
\caption{A random table with default formatting }
\begin{center}
\begin{tabular}{lrrrr}  \hline
 &\multicolumn{1}{c}{A}
 &\multicolumn{1}{c}{B}
 &\multicolumn{1}{c}{C}
 &\multicolumn{1}{c}{D}
 \\ \hline
1 &        17.17 &        84.33 &        55.04 &        30.11  \\
2 &        29.22 &        22.41 &        34.98 &        85.63  \\
3 &         6.71 &        50.02 &        99.81 &        57.87  \\
4 &        99.11 &        76.23 &        13.07 &        63.97  \\
5 &        15.95 &        25.01 &        66.89 &        43.54  \\
\hline
\end{tabular}
\end{center}
\end{table}

The table produced by GAMS2TBL can be included in a separate LaTeX file, e.g.:


%== file ex1.tex
\documentstyle[12pt]{article}
\pagestyle{empty}
\begin{document}
\input example1.tex
\end{document}

The following image of the table has been produced on a PC using the HTeX package (command "textogif ex1 350".):


Example1.htm:

A random table with default formatting
A B C D

1 17.17 84.33 55.04 30.11
2 29.22 22.41 34.98 85.63
3 6.71 50.02 99.81 57.87
4 99.11 76.23 13.07 63.97
5 15.95 25.01 66.89 43.54


Example1.prn:

 
"A random table with default formatting"

" ""A""B""C""D"
"1"       17.17       84.33       55.04       30.11
"2"       29.22       22.41       34.98       85.63
"3"        6.71       50.02       99.81       57.87
"4"       99.11       76.23       13.07       63.97
"5"       15.95       25.01       66.89       43.54


Example2.gms:

$title  GAMS2TBL Example 2: Ordering table columns and rows

set     i       /1*5/;
set     j       /a,b,c,d/;

parameter  A   User-Specified Row and Column Order;
A(i,j) = uniform(0,100);


*       Any user-specified numeric values can be used to
*       sort rows and columns:

parameter roworder(i), colorder(j);

*       Reverse the row order:

roworder(i) = card(i) - ord(i) + 1;

*       Randomly order the columns:

colorder(j) = uniform(0,1);

*       ... but let columns A and B have the same rank (this will 
*       cause the displayed order for A and B to follow the 
*       standard GAMS symbol table):

colorder("a") = colorder("b");

$setglobal row_order    roworder
$setglobal col_order    colorder

file ktxt /example2.txt/; put ktxt;
$libinclude gams2tbl A


Example2.txt:

 
User-Specified Row and Column Order
-------------------------------------------------
            C           D           A           B
-------------------------------------------------
5       66.89       43.54       15.95       25.01
4       13.07       63.97       99.11       76.23
3       99.81       57.87        6.71       50.02
2       34.98       85.63       29.22       22.41
1       55.04       30.11       17.17       84.33
-------------------------------------------------


Example3.gms:


$title  GAMS2TBL Example 3: Relabeling rows and columns in the output table

set     i       /1*5/;
set     j       /a,b,c,d/;

set     il(i)   /1      This is row 1,
                 2      Entry for row 2,
                 3      Row 3 for you to see
                 4      The fourth row
                 5      The fifth and final /;

set     jl(j)   /a      Column A
                 b      Column B 
                 c      Column C
                 d      Column D /;

parameter  A    A data array (technical description);
A(i,j) = uniform(0,100);

$setglobal title "Specified Labels and Title"
$setglobal title_just center
$setglobal row_label il
$setglobal col_label jl

file ktxt /example3.txt/; put ktxt;
$libinclude gams2tbl A


Example3.txt:


 
                     Specified Labels and Title
--------------------------------------------------------------------
                        Column A    Column B    Column C    Column D
--------------------------------------------------------------------
This is row 1              17.17       84.33       55.04       30.11
Entry for row 2            29.22       22.41       34.98       85.63
Row 3 for you to see        6.71       50.02       99.81       57.87
The fourth row             99.11       76.23       13.07       63.97
The fifth and final        15.95       25.01       66.89       43.54
--------------------------------------------------------------------


Example4.gms:


$title  GAMS2TBL Example 4: Controling the numeric format

set     i       /1*5/;
set     j       /a,b,c,d/;

parameter decimals(j) Number of decimals by column /a 3, b 2, c 2, d 1/;

parameter  A  Non-Default Numeric Formats;

A(i,j) = uniform(-0.3,0.3);

$eolcom !

*       Use numeric control parameters provided by the PUT
*       facility to control the output format:

ktxt.nw = 15;    ! Table columns with 15 characters
ktxt.nz = 0.005; ! Numeric zero tolerance (abs less than 0.005 set to zero)

*       Specify number of decimals by column.  (Could alternatively
*       define r_decimals to control numeric format by row.  When
*       both r_decimals and c_decimals are specified, r_decimals is
*       ignored.)

$setglobal c_decimals decimals

file ktxt /example4.txt/; put ktxt;
$libinclude gams2tbl A


Example4.txt:

 
Non-Default Numeric Formats
-------------------------------------------------------------
               A              B              C              D
-------------------------------------------------------------
1         -0.197           0.21           0.03           -0.1
2         -0.125          -0.17          -0.09            0.2
3         -0.260           0.00           0.30            0.0
4          0.295           0.16          -0.22            0.1
5         -0.204          -0.15           0.10            0.0
-------------------------------------------------------------


Example5.gms:


$title  GAMS2TBL Example 5: Handling Zeros

set     i       /1*10/;
set     j       /a,b,c,d/;

parameter  A    A sparse table with zeros suppressed;
A(i,j)$(uniform(0,1) gt 0.5) = uniform(0,100);

file ktxt /example5.txt/; put ktxt;

$setglobal zeros no
put //'Table generated with default row_set (zero rows are skipped):'//;
$libinclude gams2tbl A

put //'Table generated with specified row_set (zero rows are listed):'//;
$setglobal row_set i
$libinclude gams2tbl A


Example5.txt:


Table generated with default row_set (zero rows are skipped):

 
A sparse table with zeros suppressed
--------------------------------------------------
             A           B           C           D
--------------------------------------------------
1                    55.04
2                                 6.71       99.81
3        99.11       13.07       15.95
4        43.54
5                    83.09                   77.59
6                                16.02       26.51
7                    72.27       46.38
9                    56.07       29.78       75.58
10       28.39                               54.53
--------------------------------------------------



Table generated with specified row_set (zero rows are listed):

 
A sparse table with zeros suppressed
--------------------------------------------------
             A           B           C           D
--------------------------------------------------
1                    55.04
2                                 6.71       99.81
3        99.11       13.07       15.95
4        43.54
5                    83.09                   77.59
6                                16.02       26.51
7                    72.27       46.38
8
9                    56.07       29.78       75.58
10       28.39                               54.53
--------------------------------------------------


Example6.gms:


$title  GAMS2TBL Example 6: Generating Labelled Tables within a Loop

set     sc      Scenario identifiers /sc1*sc4/;
set     i       /1*5/;
set     j       /a,b,c,d/;

parameter  A    A table of random results;

*       Include a blank invocation to initialize before calling
*       the system from within a loop:

$libinclude gams2tbl 

file ktxt /example6.txt/; put ktxt;

loop(sc,

*       Define the title with double quotes around an otherwise valid
*       PUT output list which begins and ends with quoted strings, except

*	OMIT SINGLE QUOTES AT THE FRONT OF THE FIRST QUOTED STRING
*	AND AT THE END OF THE LAST QUOTED STRING (these are attached
*	by gams2tbl).

*	The example shown below represents the PUT output list:

*		'Results for Scenario (',sc.tl,')'

*	To embed this as the title, remove the leading and trailing
*	single quotes, producing:

*		Results for Scenario (',sc.tl,')

*	then place everything inside double quotes in the setglobal
*	statement, as shown below.

*       Note: if you embed a label within the title this way, you
*       you should not specify "$setglobal title_just".

$setglobal title "Results for Scenario (',sc.tl,')"

        A(i,j) = uniform(0,100);

$libinclude gams2tbl A

);


Example6.txt:


Results for Scenario (SC1)
-------------------------------------------------
            A           B           C           D
-------------------------------------------------
1       17.17       84.33       55.04       30.11
2       29.22       22.41       34.98       85.63
3        6.71       50.02       99.81       57.87
4       99.11       76.23       13.07       63.97
5       15.95       25.01       66.89       43.54
-------------------------------------------------

 
Results for Scenario (SC2)
-------------------------------------------------
            A           B           C           D
-------------------------------------------------
1       35.97       35.14       13.15       15.01
2       58.91       83.09       23.08       66.57
3       77.59       30.37       11.05       50.24
4       16.02       87.25       26.51       28.58
5       59.40       72.27       62.82       46.38
-------------------------------------------------

 
Results for Scenario (SC3)
-------------------------------------------------
            A           B           C           D
-------------------------------------------------
1       41.33       11.77       31.42        4.66
2       33.86       18.21       64.57       56.07
3       77.00       29.78       66.11       75.58
4       62.74       28.39        8.64       10.25
5       64.13       54.53        3.15       79.24
-------------------------------------------------

 
Results for Scenario (SC4)
-------------------------------------------------
            A           B           C           D
-------------------------------------------------
1        7.28       17.57       52.56       75.02
2       17.81        3.41       58.51       62.12
3       38.94       35.87       24.30       24.64
4       13.05       93.34       37.99       78.34
5       30.00       12.55       74.89        6.92
-------------------------------------------------


Example7.gms:

$title  GAMS2TBL Example 7: Text Tables 

set     i       /1*5/;
set     j       /a,b,c,d/;

set     k(i,j)  A table with strings rather than numbers
                /1.a    This
                 1.c    is
                 2.b    a
                 3.a    TEXT
                 3.d    Table.
                 4.a    Do
                 4.c    You
                 4.d    See
                 5.a    How
                 5.c    It works! /;


file ktxt /example7.txt/; put ktxt;
$libinclude  gams2tbl k


Example7.txt:

 
A table with strings rather than numbers
-------------------------------------------------
            A           B           C           D
-------------------------------------------------
1        This                      is
2                       a
3        TEXT                              Table.
4          Do                     You         See
5         How               It works!
-------------------------------------------------


Economics Department, University of Colorado, Boulder CO 80309-0256
Phone: (303) 492-5169, Fax: (303) 492-8969
email:rutherford@colorado.edu
Last modified January, 1998 by TR