## Do loop for a macro of optimization

Solved
Super Contributor
Posts: 455

# Do loop for a macro of optimization

[ Edited ]

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!!!

Accepted Solutions
Solution
‎11-06-2015 11:35 PM
SAS Employee
Posts: 538

## Re: Do loop for a macro of optimization

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;
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;``````

All Replies
Super User
Posts: 5,826

## Re: Do loop for a macro of optimization

So what's the error message?

options symbolgen mprint; helps debug your macro.

Data never sleeps
SAS Employee
Posts: 538

## Re: Do loop for a macro of optimization

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.
Super Contributor
Posts: 455

## Re: Do loop for a macro of optimization

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;
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!

SAS Employee
Posts: 538

## Re: Do loop for a macro of optimization

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.
Super Contributor
Posts: 455

## Re: Do loop for a macro of optimization

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!!!

Solution
‎11-06-2015 11:35 PM
SAS Employee
Posts: 538

## Re: Do loop for a macro of optimization

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;
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;``````
Super Contributor
Posts: 455

## Re: Do loop for a macro of optimization

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!!!

SAS Employee
Posts: 538

## Re: Do loop for a macro of optimization

[ Edited ]

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;
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;``````

🔒 This topic is solved and locked.