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".

• Installation

• Syntax

• General Comments

• Example 1: generic invocation.

• Example 2: Alternative output formats.

• Example 3: Domain specification and zeros.

• Example 4: Invocation within an if block.

• Example 5: Using GAMS2CSV with XLWRITE.

• Example 6: Using prefixes.

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.

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

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

(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.

(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:

csv1,csv2,csv3,csv4,csv5,csvu1,csvu2,csvu3,csvu4,csvu5,csvuc

(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.

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
,"i1",1.7174713200000E-01
,"i2",8.4326670800000E-01
,"i3",5.5037535600000E-01

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

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:

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

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:

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:

loop(sc,
$setglobal prefix sc.tl
$libinclude gams2csv x

);