topic Re: Create organized table from OPTMODEL in Mathematical Optimization, Discrete-Event Simulation, and OR

Create organized table from OPTMODEL

sebgon — Wed, 04 Dec 2019 00:15:07 GMT

Hi, I have the following optmodel

data arcdata;
input prod $ _tail_ $ _head_ $ _lo_ _capac_ _cost_ _km_ _d_;
datalines;
x 100 200 0 15 2 100 23
x 100 300 0 10 1 60 10
x 100 400 0 40 0 50 10
y 600 700 0 15 2 100 23
y 600 800 0 10 1 60 10
y 600 900 0 40 0 50 10
;

%macro opttabla(TABLA);

PROC SQL;
CREATE TABLE WORK.LISTA AS
SELECT distinct PROD AS PROD
FROM WORK.&TABLA.;
QUIT;

proc sql NOPRINT;
SELECT COUNT(prod) INTO: NUM_PROD FROM WORK.LISTA;
quit;

%do i=1 %to &NUM_PROD;

DATA _NULL_;
SET WORK.LISTA(FIRSTOBS=&i OBS=&i);
CALL SYMPUTX ("PROD", PROD, 'G');
RUN;

proc sql;
create table arcdata_&i. as
select *
FROM WORK.arcdata
WHERE PROD="&PROD";
quit;

proc optmodel printlevel=2;
ODS Output PrinTable=expt;
set <str,str> arcdatatemp&i;
num L {arcdatatemp&i};
num U {arcdatatemp&i};
num C {arcdatatemp&i};
num K {arcdatatemp&i};
num D {arcdatatemp&i};

read data arcdata_&i. into arcdatatemp&i=[_tail_ _head_]
L=_lo_ U=_capac_ C=_cost_ K=_km_ D=_d_;

var x {<i,j> in arcdatatemp&i} >= L[i,j] <= U[i,j];
min obj = sum {<i,j> in arcdatatemp&i} C [i,j]* x[i,j];
min obj1 = sum {<i,j> in arcdatatemp&i} C[i,j]* K [i,j];
con sum {<i,j> in arcdatatemp&i} x[i,j] = sum {<i,j> in arcdatatemp&i} D[i,j];
con sum {<i,j> in arcdatatemp&i} x[i,j] <= sum {<i,j> in arcdatatemp&i} U[i,j];

solve with lp / algorithm=ns scale=none logfreq=1;
print x obj obj1;
%end;
%mend;

%opttabla(arcdata);

For the prod=y the print generated by default is this:

x 700 800 900600

obj obj1

260

I would like to arrange it in an organized table like this

y | 600 | 700 | 800 | 900 | 40 | 260

I saw some ODS output procedures from the documentation but none give something similar to the outcome that I need, is there a way that I could do that?

Thank you very much for your help

Re: Create organized table from OPTMODEL

Ksharp — Wed, 04 Dec 2019 11:43:59 GMT

Better post it at OR forum . @RobPratt is there .

Re: Create organized table from OPTMODEL

RobPratt — Wed, 04 Dec 2019 17:11:12 GMT

Assuming each prod can have more than one _tail_, here's one way to get what you want:

set TAILS = setof {<t,h> in arcdatatemp&i} t;
num numHeads {TAILS} init 0;
str head {t in TAILS, 1..numHeads[t]} init '';
for {<t,h> in arcdatatemp&i} do;
   numHeads[t] = numHeads[t] + 1;
   head[t,numHeads[t]] = h;
end;
num maxNumHeads = max {t in TAILS} numHeads[t];
create data mytable from [_tail_]=TAILS prod="&prod" 
   {j in 1..maxNumHeads} <col('_head_'||j)=head[_tail_,j]>
   obj obj1;

Note also that the runOptmodel action in SAS Viya 3.5 (just released on November 19) now supports BY-group processing, which enables you to avoid the explicit loop that calls PROC OPTMODEL.

Re: Create organized table from OPTMODEL

sebgon — Thu, 26 Dec 2019 18:23:04 GMT

Thank you Rob,

I also saw there is an easy way like this

create data datasetname from [_tail_ _head_] variables;