Calcite | Level 5

## Appending to a Dataset in Optmodel

Hi.  I am working on a shortest-distance problem where I am finding the shortest distance between every room on a certain floor in a certain building. My goal is to output the dataset in the following manner:

room1 room2 dist

T4-1    T4-2    5

T4-1    T4-3    6

etc

Here's my code:

``````proc optmodel;
set<string, string> arcs;
num edge {arcs};
set <string> rooms;
set nodes = union {<i, j> in arcs} {i, j}; /* used to refer to each node singly, and not as an <i, j> tuple */
string source init &source;
string sink init &sink;

var flow {arcs} >=0;

min totalCost = sum {<i, j> in arcs} edge[i, j] * flow[i, j];

con bal {i in nodes}:
sum {<(i), j> in arcs} flow[i, j] - sum{<j, (i)> in arcs} flow[j, i] =  (if i = source then 1
else if i = sink then -1
else 0);

set t4_1f_rooms = {s in rooms: substr(s, 1, 5) = 'T4_R1'};
/*set t4_1f_rooms = {'T4_R103', 'T4_R129', 'T4_R151', 'T4_R176'}; */

read data t4_1f_arcs into arcs=[node_1 node_2] edge;

/*	create data t4_1f_room_dist from i j dist;*/

impvar r2rdist = sum{<i, j> in arcs: flow[i,j].sol > 1e-6} edge[i, j];

cofor{so in t4_1f_rooms, si in t4_1f_rooms} do;
if so = si then continue;
source = so;
sink = si;

solve;

/*		create data (source || '_' || sink) from [i j]={<i,j> in arcs: flow[i, j].sol > 1e-6} flow edge;*/

create data ('sum_' || source || '_' || sink) from source sink r2rdist;
/*		create data soldata from source sink r2rdist;*/
end;

run;``````

I use an impvar in order to sum the edges of the traversed nodes, but for each solution this results in a dataset with one observation and I want to append the solution for each room to room combo to an existing dataset instead of creating a new one so I don't have hundreds of output data sets.  I can't perform the append in a data step as the sheer number of output data sets causes EG to crash.  The solver runs fine when data set output is suppressed.

I thought of declaring a new problem each iteration and referencing each problem's solution in another loop, but apparently problem declarations cannot be nested statements.

Is there a way to append the output to an existing data set within optmodel?

Thanks!

SAS Super FREQ

## Re: Appending to a Dataset in Optmodel

You can call PROC APPEND from a SUBMIT block:

``````      submit;
proc append base=ds1 data=ds2;
run;
endsubmit;
``````

Another approach is to save the results in a numeric parameter, indexed by sources and sinks, and then call CREATE DATA only once, outside the COFOR loop.  This approach is illustrated in this documentation example.

Note that you can also use the network solver (instead of LP) to solve shortest path problems in PROC OPTMODEL.  In fact, the default is all-pairs shortest paths, so you would need to call the network solver only once.

Discussion stats