Create organized table from OPTMODEL

[sebgon]

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

15

10

18

obj obj1

40

260

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

prod | _tail_ | _head_1 | head 2 | head 3 | obj | obj1

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

Thank you very much for your help

[Ksharp]

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

[RobPratt · Accepted solution]

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.

[sebgon]

Thank you Rob,

I also saw there is an easy way like this 

create data datasetname from  [_tail_ _head_] variables;