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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.