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:
15 | 10 | 18 |
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
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.
Better post it at OR forum . @RobPratt is there .
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.
Thank you Rob,
I also saw there is an easy way like this
create data datasetname from [_tail_ _head_] variables;
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.
Find more tutorials on the SAS Users YouTube channel.