topic Re: Do loop for a macro of optimization in Mathematical Optimization, Discrete-Event Simulation, and OR

Do loop for a macro of optimization

ilikesas — Tue, 03 Nov 2015 02:01:42 GMT

Hi,

I have macro for finding the stock weights that will give me the smallest variance for a given return. It works fine, but when I want to do a do loop like this I get the weights for several returns, it breaks down. Here is the optimization part of the macro where I want to do a do loop:

%do n=1 %to 5;
PROC OPTMODEL;

VAR X&n{1..&VARCOUNT} >= 0;
NUM COEFF{1..&VARCOUNT, 1..&VARCOUNT} = [ %DO I = 1 %TO &VARCOUNT;
%DO J = 1 %TO &VARCOUNT;
&&COEFF&I&J %END; %END; ];
NUM R{1..&VARCOUNT}=[ %DO I = 1 %TO &VARCOUNT; &&MEAN&I %END; ];

/* MINIMIZE THE VARIANCE OF THE PORTFOLIO’S TOTAL RETURN */
MINIMIZE F = SUM{I IN 1..&VARCOUNT, J IN 1..&VARCOUNT}COEFF[I,J]*X[I]*X[J];
/* SUBJECT TO THE FOLLOWING CONSTRAINTS */
CON BUDGET: SUM{I IN 1..&VARCOUNT}X[I] = 1;
CON GROWTH: SUM{I IN 1..&VARCOUNT}R[I]*X[I] = 0.05*n;
SOLVE WITH QP;
/*create a data table for the weights of the optimal portfolio*/
create data weights from [i]=(1..&VARCOUNT) xopt=x&n ;
PRINT X&n;
%end ;
%MEND;

So here I want to find the weights for 5 returns: 0.05, 0.1, 0.15, 0.2, 0.25

I tried different ways to do this with the do loop but always get an error message, so could you please help me to put the do loop part correctly because I believe that I misplaced some elements of it.

Thank you very much!!!

Re: Do loop for a macro of optimization

LinusH — Tue, 03 Nov 2015 06:58:13 GMT

So what's the error message?

options symbolgen mprint; helps debug your macro.

Re: Do loop for a macro of optimization

RobPratt — Tue, 03 Nov 2015 12:11:00 GMT

Better to use the looping functionality inside PROC OPTMODEL instead of macro looping that calls PROC OPTMODEL several times. For example, see:
http://support.sas.com/documentation/cdl/en/ormpex/68157/HTML/default/viewer.htm#ormpex_ex22_toc.htm

That way, you declare parameters, read data, and declare model elements only once.

Re: Do loop for a macro of optimization

ilikesas — Wed, 04 Nov 2015 02:02:14 GMT

Hi Rob,

I actually did tried doing the loop for the code that you gave me the other day for the optimization without the macro. Here it is:

PROC OPTMODEL;
set <str> returns;
read data contents into returns=[NAME];
VAR X{returns} >= 0;
NUM COEFF{returns, returns};
read data covtable into [i=_NAME_] {j in returns} <coeff[i,j]=col(j)>;
NUM R{returns};
read data meantable into {j in returns} <r[j]=col(j)>;
/* MINIMIZE THE VARIANCE OF THE PORTFOLIO’S TOTAL RETURN */
do k=1 to 5;
MINIMIZE F = SUM{I IN returns, J IN returns}COEFF[I,J]*X[I]*X[J];
/* SUBJECT TO THE FOLLOWING CONSTRAINTS */
CON BUDGET: SUM{I IN returns}X[I] = 1;
CON GROWTH: SUM{I IN returns}R[I]*X[I] = 0.05*k;
SOLVE;
PRINT X;
print (sqrt(F/10000^2));
end;
quit;

I get an error message that the symbol k is unknown and this error entails other error messages. Could you please help me with the syntax becasue intuitively my code makes sense to me. Please note that I already ran the macro and as such got the variance covariance matrix and all other data which is necessary for the optimizaiton computation, its somewhere in the do loop where the problem lies.

Thank you!

Re: Do loop for a macro of optimization

RobPratt — Wed, 04 Nov 2015 02:13:18 GMT

You must declare k before you can use it in the constraint right-hand side:
num k;

And you cannot have declarations inside the loop. Please see the linked example.

Re: Do loop for a macro of optimization

ilikesas — Thu, 05 Nov 2015 03:21:02 GMT

Hi Rob,

I went to the example that you refered to but found it complicated; the "do loop variable" garage was somehow integrated into the matrix language and I couldn't realize how to do something similar for my case.

At the end I managed to do something not so elegant but I think it gave me the result that I needed:

%MACRO a(FILE);
num k;
%do k = 1 %to 5;
/*EXTRACTION OF CONTENTS*/
PROC CONTENTS DATA = &FILE /*in my example data there is no date!!!*/ OUT = CONTENTS(KEEP = NAME) NOPRINT;
RUN;
/*SETTING THE VARIABLES*/
DATA _NULL_;
SET CONTENTS;
CALL SYMPUT('VAR'||TRIM(LEFT(_N_)),name);
CALL SYMPUT('VARCOUNT',TRIM(LEFT(_N_)));
RUN;
/*CALCULATING THE VARIANCE COVARIANCE TABLE*/
PROC CORR DATA = &FILE OUT=CORRTABLE(WHERE=(UPCASE(_TYPE_) IN ("COV","MEAN"))) COV NOSIMPLE NOPRINT;
VAR %DO I = 1 %TO &VARCOUNT; &&VAR&I %END; ;
WITH %DO I = 1 %TO &VARCOUNT; &&VAR&I %END; ;
RUN;

/*COVARIANCE TABLE AND MEAN TABLE*/
DATA COVTABLE MEANTABLE; SET CORRTABLE;
IF UPCASE(_TYPE_) IN ("MEAN") THEN OUTPUT MEANTABLE;
ELSE OUTPUT COVTABLE;
RUN;

/*ASSIGNING THE COVARIANCE VALUES*/
DATA _NULL_; SET COVTABLE(DROP = _TYPE_ _NAME_);
ARRAY X{&VARCOUNT} %DO I = 1 %TO &VARCOUNT; &&VAR&I %END; ;
DO I = 1 TO &VARCOUNT;
CALL SYMPUT('COEFF'||TRIM(LEFT(_N_))||TRIM(LEFT(I)),X{I}); END;
RUN;
/*ASSIGNING THE MEAN VALUES*/

DATA _NULL_; SET MEANTABLE(DROP = _TYPE_ _NAME_);
ARRAY X{&VARCOUNT} %DO I = 1 %TO &VARCOUNT; &&VAR&I %END; ;
DO I = 1 TO &VARCOUNT;
CALL SYMPUT('MEAN'||TRIM(LEFT(I)),X{I}); END;
RUN;

/*OPTIMIZATION SECTION*/
PROC OPTMODEL;
VAR X&k{1..&VARCOUNT} >= 0;
NUM COEFF{1..&VARCOUNT, 1..&VARCOUNT} = [ %DO I = 1 %TO &VARCOUNT;
%DO J = 1 %TO &VARCOUNT;
&&COEFF&I&J %END; %END; ];
NUM R{1..&VARCOUNT}=[ %DO I = 1 %TO &VARCOUNT; &&MEAN&I %END; ];
/* MINIMIZE THE VARIANCE OF THE PORTFOLIO’S TOTAL RETURN */
MINIMIZE F&k = SUM{I IN 1..&VARCOUNT, J IN 1..&VARCOUNT}COEFF[I,J]*X&k[I]*X&k[J];
/* SUBJECT TO THE FOLLOWING CONSTRAINTS */
CON BUDGET&k: SUM{I IN 1..&VARCOUNT}X&k[I] = 1;
CON GROWTH&k: SUM{I IN 1..&VARCOUNT}R[I]*X&k[I] = 0.05*&k;
SOLVE WITH QP;
/*create a data table for the weights of the optimal portfolio*/
create data weights&k from [i]=(1..&VARCOUNT) xopt&k=x&k ;
%end;
%MEND;

Could you please help me extracting the F&k and the GROWTH&k like this I can put them later into a single table and create the efficient frontier graph.

I tried doing:

create data return&k from growth&k xopt&k=growth&k ;

but received an error message that "the symbol growth may not be used here"

Thank you very much!!!

Re: Do loop for a macro of optimization

RobPratt — Thu, 05 Nov 2015 21:16:27 GMT

Below is a way to do the looping without using the macro language. Note that k is declared before it is used in the constraint, and no declarations appear within the loop. Regarding the error message, I suspect that you intended to use the .BODY constraint suffix.

PROC OPTMODEL;
   set <str> returns;
   read data contents into returns=[NAME];
   VAR X{returns} >= 0;
   NUM COEFF{returns, returns};
   read data covtable into [i=_NAME_] {j in returns} <coeff[i,j]=col(j)>;
   NUM R{returns};
   read data meantable into {j in returns} <r[j]=col(j)>;
   num k;
   /* MINIMIZE THE VARIANCE OF THE PORTFOLIO’S TOTAL RETURN */
   MINIMIZE F = SUM{I IN returns, J IN returns}COEFF[I,J]*X[I]*X[J];
   /* SUBJECT TO THE FOLLOWING CONSTRAINTS */
   CON BUDGET: SUM{I IN returns}X[I] = 1;
   CON GROWTH: SUM{I IN returns}R[I]*X[I] = 0.05*k;
   do k=1 to 5;
      put k=;
      SOLVE;
      PRINT X;
      print (sqrt(F/10000^2));
   end;
quit;

Re: Do loop for a macro of optimization

ilikesas — Fri, 06 Nov 2015 00:22:53 GMT

Hi Rob,

thank you for the code, this way it is more efficient because I don't have to recalculate the variance covariance matrix every time (among other things).

I am almost done with my questions and jus want to ask a last one: how to extract the variance and the return and put them into tables?

To create the data tables for the weights I do the following:

create data weights from [i]=(1..&VARCOUNT) xopt=x ;

But I don't know how to for variance and return. Tried different things but still didn't work.

Thank you!!!

Re: Do loop for a macro of optimization

RobPratt — Tue, 10 Nov 2015 21:53:19 GMT

Here's how you can write the variance (F) values to a data set. You can modify the code to get whatever additional values you want.

PROC OPTMODEL;
   set <str> returns;
   read data contents into returns=[NAME];
   VAR X{returns} >= 0;
   NUM COEFF{returns, returns};
   read data covtable into [i=_NAME_] {j in returns} <coeff[i,j]=col(j)>;
   NUM R{returns};
   read data meantable into {j in returns} <r[j]=col(j)>;
   num k;
   /* MINIMIZE THE VARIANCE OF THE PORTFOLIO’S TOTAL RETURN */
   MINIMIZE F = SUM{I IN returns, J IN returns}COEFF[I,J]*X[I]*X[J];
   /* SUBJECT TO THE FOLLOWING CONSTRAINTS */
   CON BUDGET: SUM{I IN returns}X[I] = 1;
   CON GROWTH: SUM{I IN returns}R[I]*X[I] = 0.05*k;
   set KSET = 1..5;
   num Fsol {KSET};
   do k=KSET;
      put k=;
      SOLVE;
      PRINT X;
      Fsol[k] = F.sol;
   end;
   create data soldata from [k] Fsol;
quit;