Colorado GAMS

A LIBINCLUDE Tool for Writing
Comma-Delimited Data Files

Thomas F. Rutherford*

Department of Economics
University of Colorado

January, 2002 (revised)

* This research supported by the GAMS Applied General Equilibrium Research Fund. The author remains responsible for any bugs which exist in this software. This software is not officially supported by GAMS Corporation.

It is often the case that GAMS programmers need to move data to an external spreadsheet or database program. This document describes a libinclude routine which can simplify the transfer of data to a comma-delimited save file, typically identified by the suffix ".CSV".


A number of libinclude routines must be installed in the GAMS include library subdirectory (LDIR) if you want to have these tools as a standard component of your GAMS installation.

N.B. If you want to use these routines in a program which may be run by someone who may not have installed the library, include the libinclude programs in your model directory and replace "$libinclude" by "$batinclude".

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

Syntax for gams2csv

FILE unit /name/; 
PUT unit; 
[$SETGLOBAL prefix "string"]
$LIBINCLUDE gams2csv  [row domain [column domain] ] item[.suffix] [item[.suffix] ...]

Some additional information:

(i) gams2csv works for parameters, variables, and equations.

(ii) The first invocation must be outside of a loop or if block. Use a blank invocation (without arguments) to initialize.

(iii) All items written in a single call must be of the same dimension. If row and column domains are specified, they apply to all of the output items. To write items of different dimension or domain, use multiple calls to the routine.

(iv) Values of EPS are written as zero on the output file.

(vii) Global environment variable "zeros" controls whether zeros are written to the output file. (Default is "no".)

(vi) Global environment variable "prefix" can add labels to output.

General Comments:

(1) Data is written to the currently active file. The user program must declare a file and make it current with a PUT statement prior to invoking either routine.

(2) The output may be from a parameter, level or marginal (variable or an equation). A variable or equation may only be output with a suffix, such as id.L (indicating level value), id.M (mararginal), id.LO (lower bound), or id.UP (upper bound).

(3) These routines may be used within a loop or if block only if they are first initialized with blank invocations ("$LIBINCLUDE gams2prm" or "$LIBINCLUDE gams2txt") in a context where set and parameter declarations are permitted.

(4) The following names are used within these routines and may not be used in the calling program:


(5) To improve portability of GAMS code between PC and Unix platforms, use a lower case reference to the batch file, gams2csv.

(6) The global environment variable 'prefix' can be used to add characters to the output label.

Example 1: generic invocation

set i /i1*i3/; 
parameter	x(i)	Parameter vector X;
x(i) = uniform(0,1);

file kout /ex1.csv/;  
put kout;

$libinclude gams2csv x 

Example1 writes the following lines to x.csv:

"x",Parameter vector X

Example 2: Alternative output formats.

set i /i1*i2/, j/j1*j2/, k/k1*k2/;

parameter	x(i)		A one-dimensional vector.
		y(i,j,k)	A three dimensional array written with column headers
		z(i,j,k)	A three dimensional array written in list form;

x(i) = uniform(0,1);
y(i,j,k) = uniform(0,1);
z(i,j,k) = y(i,j,k);

file kout /ex2.csv/;  
put kout;

$libinclude gams2csv i x
$libinclude gams2csv i,j k y
$libinclude gams2csv i,j,k z

When the output file ex2.csv is loaded into a spreadsheet, it has the following layout:
The CSV output is designed to simplify navigation between tables along column A.

Example 3: Domain specification and zeros.

The default mode of operation for gams2csv is to write only nonzeros. This works fine in many applications, but in some cases it is not acceptable. For example, if you want to provide data for a pivot table in an Excel spreadsheet and you want to maintain a fixed-sized table. The use of an explicit output domain solves this problem:
set i /i1*i3/; 
set j /j1*j3/;

parameter x(i,j);
x(i,j) = uniform(0,1);

*	Suppose that one row of X were entirely zero:

x("i1",j) = 0;

file kout /ex3.csv/;  
put kout;

*	When X is output without a domain, the only rows and columns
*	which appear are those which are not zero:

$libinclude gams2csv x 

*	When row and column domains are specified, all rows appear:

$libinclude gams2csv i j x 

*	The same applies for list format:

$libinclude gams2csv i,j x 

*	A specific set of elements can be output:

set l(i,j)	Output domain which is not a full cross product /i1.j1,i2.j2,i2.j3,i3.j1/;

$libinclude gams2csv l x

Example 3 generates the following spreadsheet:

Example 4: Invocation within an if block.

Note that this example uses gams2csv within an if block, so an initialization call is required.

set i /i1*i3/;
parameter x(i); 
x(i) = uniform(0,1);

file kout /x.csv/;  
put kout;

$libinclude gams2csv

if (x("i1") gt 10,
$libinclude gams2csv x 

No output is written to the file in this case.

Example 5: Using GAMS2CSV with XLWRITE.

Several CSV files can be generated and combined into an Excel workbook using the XLWRITE utility. In order to do this, write one CSV file for each worksheet you wish to add to the workbook. The CSV file name prefix is used to name the worksheet in the workbook. The following example illustrates how to write the CSV files as temporary files in the GAMS scratch directory. If the files are written in the working directory, they are retained after the program is completed.
set	i/1*3/;

alias (i,j,k,l);

parameter a(i,j,k,l)	My test parameter - price data,
	  b(i,j)	My second test parameter - price data
	  c(i)		My third test parameter - quantity data
	  d(i,j)	My fourth test parameter - quantity data;

a(i,j,k,l) = uniform(0,1);
b(i,j) = uniform(0,1);
c(i) = uniform(0,1);
d(i,j) = uniform(0,1);

*	Create a separate CSV file for each worksheet that is to be
*	written into the XL workbook.  Here we illustrate how the 
*	GAMS scratch directory can be used so that the CSV files are 
*	erased at the end of the GAMS run.  

*		*gams.scrdir%	represents the scratch directory (e.g. 225a)
*		.scr		is a non-standard suffix for a CSV file which
*				is used so that GAMS will erase the file at
*				the end of the run.

file	kprc /%gams.scrdir%prices.scr/; 

*	If the CSV file were to be retained at the end of the run, it could be
*	declared simply:

*			file	kprc /prices.csv/;

put kprc;
$libinclude gams2csv i,j,k l a    
$libinclude gams2csv i j b 

*	Create a second worksheet for inclusion in the workbook:

file	kqty /%gams.scrdir%quants.scr/; put kqty;

$libinclude gams2csv i c 
$libinclude gams2csv d 
$libinclude gams2csv i,j d

*	Need to close both CSV files before writing the workbook:

putclose kprc;
putclose kqty;

*	Now call XLWRITE:

execute 'xlwrite %gams.scrdir%prices.scr %gams.scrdir%quants.scr ex5.xls';

Example 5 a workbook with two named worksheets:

Example 6: Using prefixes.

set	i	Goods output /i1*i3/;
set	sc	Scenario /sc1*sc4/;

parameter x(i); 

file kout /ex6.csv/;   put kout;

x(i) = uniform(0,1);

*	Add one extra row label:

$setglobal prefix "'x'"
$libinclude gams2csv x

*	Add two extra row labels:

$setglobal prefix "'x','y'"
$libinclude gams2csv x

*	Next label inside a loop:

$setglobal prefix
$libinclude gams2csv x

Economics Department, University of Colorado, Boulder CO 80309-0256
Phone: (303) 492-5169, Fax: (303) 492-8969
Revised January 26 by TFR