Statistical programming, matrix languages, and more

proc IML by group

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

proc IML by group

[ Edited ]

Hello,

 

I performed 6 different rolling windows regressions (each one having 222 60-month windows) and obtained, for each of the 6 regressions, several tables where results (predicted values, residuals...) are grouped by the number of the window (column "grp" with values from 0 to 221).

 

Then I managed to perform the following computations to get the results for the first window:

 

 

data Reg1.Residuals;
    merge Reg1.Res1 (obs= 60) Reg1.Res2 (obs= 60) Reg1.Res3 (obs= 60) Reg1.Res4 (obs= 60) Reg1.Res5 (obs= 60) Reg1.Res6 (obs= 60);
    keep residuals1 residuals2 residuals3 residuals4 residuals5 residuals6;
run;

Proc corr noprint
    data = Reg1.Residuals
    outp = Reg1.MVC
    COV
    VARDEF=N;
run;

proc IML;
    use Reg1.Predicted;
    read point{1} var{Predicted1 Predicted2 Predicted3 Predicted4 Predicted5 Predicted6} into E;
    Exp = T(E);

    use Reg1.MVC where(_TYPE_="COV");
    read all var _NUM_ into MVC;

    inv_MVC = INV(MVC);
    One = {1, 1, 1, 1, 1, 1};
    A = T(One)*inv_MVC*One;
    B = T(One)*inv_MVC*Exp;
    C = E*inv_MVC*Exp;
    U = inv_MVC*One/A;
    V = inv_MVC*Exp/B;

    use Reg1.Std_res;
    read point{1} var{Std_Rm} into sigma;

    alpha = -B*sqrt(A*sigma**2-1)/sqrt(A*C-B**2)+1;
    W = alpha*U+(1-alpha)*V;

    Expected_portfolio = T(W)*Exp;
    create Reg1.Expected_port from Expected_portfolio;
    append from Expected_portfolio;

    use Reg1.Sample;
    read point{61} var{Y1 X2 X3 X4 X5 X6} into R;

    Realized_portfolio = R*W;
    create Reg1.Realized_port from Realized_portfolio;
    append from Realized_portfolio;
quit;

 

 

But now I would like to do the same computations for the 221 other groups and that the results append in the two tables Reg1. Realized_port and Reg1.Expected_port. So I started with this:

 

 

data Reg1.Residuals;
    merge Reg1.Res1 Reg1.Res2 Reg1.Res3 Reg1.Res4 Reg1.Res5 Reg1.Res6;
    keep grp residuals1 residuals2 residuals3 residuals4 residuals5 residuals6;
run;

Proc corr noprint
    data = Reg1.Residuals
    outp = Reg1.MVC
    COV
    VARDEF=N;
    by grp;
run;

 

 

but I do not manage to perform the proc IML computations by group.

 

Thank you very much in advance for any advice,

 

 


Accepted Solutions
Solution
‎02-10-2017 02:01 PM
SAS Super FREQ
Posts: 3,420

Re: proc IML by group

[ Edited ]

You don't really need to use BY-group processing here because every matrix is pxp, where p is the number of variables. Thus you merely need to loop over the k groups and extract the next p rows into a matrix (call it A), then call INV on A.

 

In fact, the output data set will look very similar to the input data set, so you don't really need to manage the GRP and _NAME_ variables in IML. You can just merge them from the MVC data set after exiting IML.

 

/* CREATE sample data */
data have;
set sashelp.iris;
grp = species;
run;

Proc corr noprint data=have outp=MVC COV VARDEF=N;
by grp;
run;

proc IML;
use MVC where (_TYPE_="COV");
read all var {grp};
read all var{SepalLength SepalWidth PetalLength PetalWidth} into MVC[r=_NAME_ c=VarNames];
close;

p = ncol(VarNames);   /* dimension of covariance */
k = nrow(grp) / p;    /* number of groups */

/* show the basic idea for the first BY group */
A = MVC[1:p,];
inv_Mvc = inv(A);
create iMvc from inv_MVC[c=VarNames];
append from inv_MVC;
 
do i = 2 to k;                /* iterate over th remaining groups */
   startRow = p*(i-1) + 1;
endRow = startRow + p - 1; A = MVC[startRow:endRow,]; inv_Mvc = inv(A); append from inv_MVC; end; close iMvc; QUIT; /* merge the GRP and _NAME_ variables from MVC */ data inv_Mvc; merge Mvc(keep=grp _NAME_ _TYPE_ where=(_TYPE_="COV")) iMvc; _TYPE_ = "INV"; run; proc print; run;

 

View solution in original post


All Replies
Valued Guide
Posts: 524

Re: proc IML by group

I would recommend that you post your question in the IML forum here Smiley Happy

 

https://communities.sas.com/t5/SAS-IML-Software-and-Matrix/bd-p/sas_iml

 

Contributor
Posts: 21

Re: proc IML by group

Thank you, it was moved in the right section by an admin.

Contributor
Posts: 21

Re: proc IML by group

[ Edited ]

I would still need some help please.

 

I finally managed to do it for the first transposed:

 

proc IML;
	use Reg1.Predicted;
	read all var{grp};
	byGroup = unique(grp);           /* compute unique levels */

	do i = 1 to nrow(byGroup);
		read all var{Predicted1 Predicted2 Predicted3 Predicted4 Predicted5 Predicted6} into E;
		Esp = T(E);
   		append from Esp;
	end;

	create Reg1.Esp from Esp;
	append from Esp;
quit;

but I still not manage to do it for the variance-covariance matrix. Without the loop I have now:

proc IML;
	use Reg1.MVC where (grp=0);
	read all var{Residual1 Residual2 Residual3 Residual4 Residual5 Residual6} where (_TYPE_="COV") into MVC[r=_NAME_ c=VarNames];
	inv_MVC = INV(MVC);
	append from inv_MVC;
	
	create Reg1.inv_Mvc from inv_MVC;
	append from inv_MVC;

	print Mvc; print inv_MVC;
quit;

the variable "grp" ranging from 0 to 221.

 

Any help would be most welcome,

 

SAS Super FREQ
Posts: 3,420

Re: proc IML by group

You have too many undefined quantities for me to follow what you are trying to do. For example, what is Reg1.Std_res? What is Reg1.Sample?

 

 I suggest you use a Sashelp data set to illustrate your problem. then we can all use the same data and you can say what computations you want to perform. Try this to get started:

 

%let varnames = SepalLength SepalWidth PetalLength PetalWidth;

data have;
set sashelp.iris;
grp = species;
run;

Proc corr noprint data=have outp=MVC COV VARDEF=N;
by grp;
run;

Now write the SAS/IML code for the case WHERE GRP="Setosa" (the first BY group).  If you do that, we can show you how to extend the computation to multiple BY groups.

Contributor
Posts: 21

Re: proc IML by group

Thank you so much for your help Rick and for your blog as well, I read many posts. I did not know that there even was templates in Sashelp...

 

So I would like to compute the inverse of the variance-covariance matrix for each grp. Here is my code for the first grp "Setosa":

 

proc IML;
	use MVC where (grp="Setosa");
	read all var{SepalLength SepalWidth PetalLength PetalWidth} where (_TYPE_="COV") into MVC[r=_NAME_ c=VarNames];
	inv_MVC = INV(MVC);
	append from inv_MVC;

	create inv_Mvc from inv_MVC;
	append from inv_MVC;

	print Mvc; print inv_MVC;
quit;

I obtain the inverse of the matrix but I do not have a first colum "grp", indicating "Setosa", in the resulting table. Furthermore, I do not manage to create a loop, and append the inverse of the variance-covariance matrix for the other groups. It seems harder with this template, as groups have names (Setosa, Versicolor and Virginica) while in my original file, groups are numbered from 0 to 221.

 

Thank you so much for your help,

Solution
‎02-10-2017 02:01 PM
SAS Super FREQ
Posts: 3,420

Re: proc IML by group

[ Edited ]

You don't really need to use BY-group processing here because every matrix is pxp, where p is the number of variables. Thus you merely need to loop over the k groups and extract the next p rows into a matrix (call it A), then call INV on A.

 

In fact, the output data set will look very similar to the input data set, so you don't really need to manage the GRP and _NAME_ variables in IML. You can just merge them from the MVC data set after exiting IML.

 

/* CREATE sample data */
data have;
set sashelp.iris;
grp = species;
run;

Proc corr noprint data=have outp=MVC COV VARDEF=N;
by grp;
run;

proc IML;
use MVC where (_TYPE_="COV");
read all var {grp};
read all var{SepalLength SepalWidth PetalLength PetalWidth} into MVC[r=_NAME_ c=VarNames];
close;

p = ncol(VarNames);   /* dimension of covariance */
k = nrow(grp) / p;    /* number of groups */

/* show the basic idea for the first BY group */
A = MVC[1:p,];
inv_Mvc = inv(A);
create iMvc from inv_MVC[c=VarNames];
append from inv_MVC;
 
do i = 2 to k;                /* iterate over th remaining groups */
   startRow = p*(i-1) + 1;
endRow = startRow + p - 1; A = MVC[startRow:endRow,]; inv_Mvc = inv(A); append from inv_MVC; end; close iMvc; QUIT; /* merge the GRP and _NAME_ variables from MVC */ data inv_Mvc; merge Mvc(keep=grp _NAME_ _TYPE_ where=(_TYPE_="COV")) iMvc; _TYPE_ = "INV"; run; proc print; run;

 

Contributor
Posts: 21

Re: proc IML by group

Thank you very much Rick! However this code is working for the first grp (the inverse of the variance-covariance matrix for the first group is correct), but then the values are incorrect for the 2 other groups.

 

I'm going to work on this code to understand it and see where is the mistake.

SAS Super FREQ
Posts: 3,420

Re: proc IML by group

Sorry, I had an "off-by-one" bug in the calculation of the row numbers. The correct indices are

startRow = p*(i-1) + 1;
endRow = startRow + p - 1;

 

Contributor
Posts: 21

Re: proc IML by group

Thank you so much for your responsiveness Rick!!

 

All good now Smiley Happy

 

That really was the most difficult part for me. Now I should manage to end my computations. I'll indicate that the thread is solved once I'm sure about it.

 

Thank you again Rick,

Contributor
Posts: 21

Re: proc IML by group

[ Edited ]

Now I need to use the inverse of the variance-covariance matrix of each group, to make some computations for each group, such as:

proc IML;
One = {1, 1, 1, 1, 1, 1};
A = T(One)*inv_MVC*One;
   U = inv_MVC*One/A;
quit;

 

So maybe it would be more convenient to use BY group processing such as:

proc IML;
    use Reg1.Predicted;
    read all var{grp};
    byGroup = unique(grp);           /* compute unique levels */
    do i = 1 to nrow(byGroup);
        read all var{Predicted1 Predicted2 Predicted3 Predicted4 Predicted5 Predicted6} into E;
        Esp = T(E);
           append from Esp;
    end;
quit;

as I will need to compute as well:

proc IML;
        B = T(One)*inv_MVC*Esp;
	C = E*inv_MVC*Esp;
	U = inv_MVC*One/A;
	V = inv_MVC*Esp/B
quit;

for each group.

 

What do you think? Can the inverse of the variance-covariance matrix be done for each group like this, or should I inspire from your code to continue my computations?

SAS Super FREQ
Posts: 3,420

Re: proc IML by group

Proceeding from my code would be more efficient. Also, my code works whereas yours still has some issues.

 

True BY-group processing assumes that the number of observations in each group is (potentially) different. In your case, each covariance matrix is the same size, so it is easy to run through the data and directly reference the information for each group.

Contributor
Posts: 21

Re: proc IML by group

[ Edited ]

I'm really sorry to ask for help once again, but I tried to finish my computations with your code but I do not manage to do it as I do not fully understand it, such as "E = MVC[1: p,]".

 

In order to help me to understand your code, could you please adapt it, with the following example, to get the transpose of the following vector for each group:

 

 

%let varnames = SepalLength SepalWidth PetalLength PetalWidth;

data have;
	set sashelp.iris;
	grp = species;
run;

Proc corr noprint data=have outp=MVC COV VARDEF=N;
	by grp;
run;

data Have2;
	set Have;
	by grp;
	if last.grp then output Have2;
	keep grp SepalLength SepalWidth PetalLength PetalWidth;
run;

i.e. to compute the transpose of the row [SepalLength SepalWidth PetalLength PetalWidth] for each grp

in order to get it in column [SepalLength, SepalWidth, PetalLength, PetalWidth] for each grp

 

That should look like:

 

proc IML;
/* Transpose of vector */
	use Have2;
	read all var{grp};
	byGroup = unique(grp);           /* compute unique levels */

	do i = 1 to nrow(byGroup);
		read all var{SepalLength SepalWidth PetalLength PetalWidth} into E;
		Esp = T(E);
   		append from Esp;
	end;

	Create Esp from Esp;
	append from Esp;
quit;

data Esp;
	set Esp (rename=(COL1=Setosa COL2=Versicolor COL3=Virginica));
run;

except that all results should be on the same column by grp with your code if I am not mistaken, which is totally fine by me and should even make easier the following computations.

 

 

Thank you so much in advance,

 

SAS Super FREQ
Posts: 3,420

Re: proc IML by group

I don't have time right now to adapt your code, but perhaps someone else will help you. 

 

A few obvious problems:

1. Use 

do i = 1 to NCOL(byGroup);

2. The first APPEND statement (in the loop) is invalid because you have not opened a data set for writing. Maybe move the CREATE statement before the loop?

3. In the loop you are doing the same operation for each group. I don't see why that makes sense.  Did you mean to use a WHERE clause to read only certain observations?

 

I'm sure that if you work at it you will make progress.  Use lots of PRINT statements and remember that PROC IML is an interactive procedure, so delete that QUIT statement while you are debugging.

 

Good luck.

Contributor
Posts: 21

Re: proc IML by group

I finally managed to make all my computations with your code Smiley Happy

 

I used many post from you to help me, and this one helped me to understand your code:

http://blogs.sas.com/content/iml/2015/11/25/extract-elements-matrix.html

This is a very basic thing, but for a beginner like me it prevented from understanding.

 

So thank you so much Rick, I would have never done it without your help,

 

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 17 replies
  • 681 views
  • 6 likes
  • 3 in conversation