BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ilikesas
Barite | Level 11

Hi,

 

here is a part of a macro that I did to find the optimal portfolio with a smallest variance for a given rate of return:

 

/*OPTIMIZATION SECTION*/

PROC OPTMODEL;

VAR X{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] = 10000;

CON GROWTH: SUM{I IN 1..&VARCOUNT}R[I]*X[I] = 500;

 SOLVE WITH QP;

 PRINT X;

 

This macro works fine and it gives me the optimal solution, but how do I extract the actual variance of the portfolio which it calculated and which is supposed to be the minimal variance for a given return?

 

Thnak you!

1 ACCEPTED SOLUTION

Accepted Solutions
RobPratt
SAS Super FREQ

By the way, you can avoid all the macro language by instead reading the data sets directly in PROC OPTMODEL:

 

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

View solution in original post

10 REPLIES 10
RobPratt
SAS Super FREQ
The objective function is F. You can display its value in the listing by using the PRINT statement, like you did for X:

print F;

You can also save its value to a macro variable or a data set.
ilikesas
Barite | Level 11

Hi RobPratt,

 

I did the Print F and the result that I got is SAS Output

F

694025835

 

This is weired becasue I double chequed with Excel and the weights that I obtain in SAS are the same as those that I obtain from Excel so they are correct, but the portfolio stdev in Excel is 2.63 ?

 

Also, I tried saving my weights into a data set by doing something like 

create data weights from xopt=x ;

 but this didn't give me any new data set....

 

 

Thank you!

RobPratt
SAS Super FREQ

Please attach your full code and data.

 

Correct syntax for CREATE DATA is:

 

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

ilikesas
Barite | Level 11

Hi RobPratt,

 

here is my data and the macro code:

 

data returns;

input a b c;

datalines;

10 2 5

3 -5 7

-1 -7 6

1 3 -2

;

run;

 

%MACRO a(FILE);

 

/*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{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] = 10000;

CON GROWTH: SUM{I IN 1..&VARCOUNT}R[I]*X[I] = 500;

 SOLVE WITH QP;

 PRINT X;

%MEND;

 

%a (returns)

RobPratt
SAS Super FREQ

 

It appears that the portfolio standard deviation is calculated by post-processing the X values to sum to 1:

 

for {i in 1..&varcount} X[i] = X[i]/10000;
print (sqrt(F));

 

Alternatively, you can leave the X values alone and divide by the square of the budget:

print (sqrt(F/10000^2));

 

SAS Output

2.6344
RobPratt
SAS Super FREQ

By the way, you can avoid all the macro language by instead reading the data sets directly in PROC OPTMODEL:

 

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

Hi RobPratt,

 

Thanks a lot now SAS outputs the same stdev that I obtained from Excel!!!

Also thank you for the code of putting the data set directly into proc optmodel without doing a macro!!!

 

You are helping me a lot so please allow me to ask you some more related questions 🙂

 

What I want to do now is for a given stdev to find the max return. 

 

The actual code:

 

data returns;
input a b c;
datalines;
10 2 5
3 -5 7
-1 -7 6
1 3 -2
;
run;

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)>;
/* Maximize the return */
maximize GROWTH= SUM{I IN returns}R[I]*X[I] ;
/* SUBJECT TO THE FOLLOWING CONSTRAINTS */
CON BUDGET: SUM{I IN returns}X[I] = 10000;
con var: SUM{I IN returns, J IN returns}COEFF[I,J]*X[I]*X[J] =4;
SOLVE;
PRINT X;
print growth;
quit;

 

So here I have a budget of 10000 and want to find the max return for a variance = 4

The result that I get is:

 

SAS Output

1] X
a -822.39
b 1145.79
c 1212.73

GROWTH
173.01

 

This is different from what I got in Excel because in Excel I assumed that there is no short selling (i.e, all weights are greater or equal to 0) and in this code I also mentioned 'VAR X{returns} >= 0;' 

So what I can't figure out is why a = -822.39 since the smallest weight that I can get is 0?

 

Thank you very much!!! 

 

RobPratt
SAS Super FREQ

You should see these NOTEs in the log:

 

NOTE: Maximum number of iterations reached.
NOTE: Objective = 175.59076489.
NOTE: The least infeasible solution found is returned.

 

Also notice the positive value for Infeasibility in the Solution Summary:

 

SAS Output

Infeasibility 8467.8268186

 

For your var constraint, try a less aggressive right-hand side like 4e8, which is on the order of the value from your earlier optimization.  Alternatively, change the right-hand side of your budget constraint to 1.

ilikesas
Barite | Level 11

Hi Rob,

 

I set the budget constraint =1 and left the variance = 4 as before and I managed to get the same result that Ecxel solver gave me.

Here is the code:

 

data returns;
input a b c;
datalines;
10 2 5
3 -5 7
-1 -7 6
1 3 -2
;
run;

%MACRO a(FILE);
/*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{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; ];
/*maximize the growth*/
maximize GROWTH= SUM{I IN 1..&VARCOUNT}R[I]*X[I];
/* SUBJECT TO THE FOLLOWING CONSTRAINTS */
CON BUDGET: SUM{I IN 1..&VARCOUNT}X[I] = 1;
con f: SUM{I IN 1..&VARCOUNT, J IN 1..&VARCOUNT}COEFF[I,J]*X[I]*X[J] = 4 ;
SOLVE ;
PRINT X;
print growth;
%MEND;

%a (returns);

 

 

But now some other small issue appeared: the code that you wrote for doing the whole process without the macro works (at least when I tried it) only after I run the macro. When I opened SAS and wanted to find the optimal weights for my data with the macro-less code I got error messages such that data contents doesn't exist etc.

 

Thank you!!!

RobPratt
SAS Super FREQ

I meant that you don't need any macro language within the PROC OPTMODEL call, and you don't need to generate the MEAN&i and COEFF&i&j macro variables.  You do need to generate the meantable and covtable data sets before you can read them with the READ DATA statement in PROC OPTMODEL.

SAS Innovate 2025: Register Now

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!

Multiple Linear Regression in SAS

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.

Discussion stats
  • 10 replies
  • 3501 views
  • 7 likes
  • 2 in conversation