* 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".
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] ...]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.
(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.
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 zWhen the output file ex2.csv is loaded into a spreadsheet, it has the following layout:
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 );No output is written to the file in this case.
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 );Economics Department, University of Colorado, Boulder CO 80309-0256