Dynamic Matrix Multiplication

Solved
Occasional Contributor
Posts: 14

Dynamic Matrix Multiplication

[ Edited ]

Hello All,

I have a dataset in the following form below where colums C1..... C9 are dynamically generated in the previous steps.

1. at first step, I need to multiply for every P_ID, matrix of Y1 to Y2 = Y12.

2. Multiply Y12 ( only 9 X8) with Y2 (8X1 i.e only last column.)

Challenge is that i have multiple P_ID and have to also do matrix multiplication until 20 years.

I have created a macro which can always multiply 2 matrices but the major problem is also to select data dynamically and saving it.

Really appreciate if somebody can help me solve the problem , the sample data looks like below.

Thanks for your help.

 P_ID Col Year c1 c2 c3 c4 c5 c6 c7 c8 c9 A 1 Y1 0.932606 0 0 0 0.059015 0.002698 0.003264 0.00071 0.001707 A 2 Y1 0 0.868185 0 0 0.1086 0.008444 0.008169 0.001671 0.004932 A 3 Y1 0 0 0.823402 0 0.14322 0.012453 0.010085 0.002494 0.008346 A 4 Y1 0 0 0 0.758853 0.178104 0.024061 0.016185 0.00652 0.016278 A 5 Y1 0 0 0 0 0.798871 0.031061 0.081029 0.021842 0.067197 A 6 Y1 0 0 0 0 0.298265 0.213144 0.150618 0.049483 0.288489 A 7 Y1 0 0 0 0 0.179519 0.021707 0.239906 0.084662 0.474207 A 8 Y1 0 0 0 0 0.039876 0.005683 0.022931 0.053959 0.877551 A 9 Y1 0 0 0 0 0.010017 0.001072 0.00444 0.003509 0.980962 A 1 Y2 0.932606 0 0 0 0.059015 0.002698 0.003264 0.00071 0.001707 A 2 Y2 0 0.868185 0 0 0.1086 0.008444 0.008169 0.001671 0.004932 A 3 Y2 0 0 0.823402 0 0.14322 0.012453 0.010085 0.002494 0.008346 A 4 Y2 0 0 0 0.758853 0.178104 0.024061 0.016185 0.00652 0.016278 A 5 Y2 0 0 0 0 0.798871 0.031061 0.081029 0.021842 0.067197 A 6 Y2 0 0 0 0 0.298265 0.213144 0.150618 0.049483 0.288489 A 7 Y2 0 0 0 0 0.179519 0.021707 0.239906 0.084662 0.474207 A 8 Y2 0 0 0 0 0.039876 0.005683 0.022931 0.053959 0.877551 A 9 Y2 0 0 0 0 0.010017 0.001072 0.00444 0.003509 0.980962 B 1 Y1 0.932606 0 0 0 0.059015 0.002698 0.003264 0.00071 0.001707 B 2 Y1 0 0.868185 0 0 0.1086 0.008444 0.008169 0.001671 0.004932 B 3 Y1 0 0 0.823402 0 0.14322 0.012453 0.010085 0.002494 0.008346 B 4 Y1 0 0 0 0.758853 0.178104 0.024061 0.016185 0.00652 0.016278 B 5 Y1 0 0 0 0 0.798871 0.031061 0.081029 0.021842 0.067197 B 6 Y1 0 0 0 0 0.298265 0.213144 0.150618 0.049483 0.288489 B 7 Y1 0 0 0 0 0.179519 0.021707 0.239906 0.084662 0.474207 B 8 Y1 0 0 0 0 0.039876 0.005683 0.022931 0.053959 0.877551 B 9 Y1 0 0 0 0 0.010017 0.001072 0.00444 0.003509 0.980962

Accepted Solutions
Solution
‎02-27-2017 07:30 AM
Super User
Posts: 10,850

Re: Dynamic Matrix Multiplication

[ Edited ]
Posted in reply to akshaybatra1

OK. Here fit all your requirement. Assuming data has already been sorted just as you showed.

``````data have;
infile cards dlm='09'x truncover;
input P_ID \$	Col	Year \$	c1	c2	c3	c4	c5	c6	c7	c8	c9;
cards;
A	1	Y1	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
A	2	Y1	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
A	3	Y1	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
A	4	Y1	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
A	5	Y1	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
A	6	Y1	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
A	7	Y1	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
A	8	Y1	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
A	9	Y1	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
A	1	Y2	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
A	2	Y2	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
A	3	Y2	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
A	4	Y2	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
A	5	Y2	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
A	6	Y2	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
A	7	Y2	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
A	8	Y2	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
A	9	Y2	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
A	1	Y3	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
A	2	Y3	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
A	3	Y3	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
A	4	Y3	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
A	5	Y3	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
A	6	Y3	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
A	7	Y3	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
A	8	Y3	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
A	9	Y3	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
B	1	Y1	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
B	2	Y1	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
B	3	Y1	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
B	4	Y1	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
B	5	Y1	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
B	6	Y1	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
B	7	Y1	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
B	8	Y1	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
B	9	Y1	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
B	1	Y2	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
B	2	Y2	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
B	3	Y2	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
B	4	Y2	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
B	5	Y2	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
B	6	Y2	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
B	7	Y2	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
B	8	Y2	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
B	9	Y2	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
B	1	Y3	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
B	2	Y3	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
B	3	Y3	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
B	4	Y3	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
B	5	Y3	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
B	6	Y3	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
B	7	Y3	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
B	8	Y3	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
B	9	Y3	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
;
run;

proc iml;
vnames=contents(have);
var_c=vnames[loc(prxmatch('/^c\d+\s*\$/i',vnames))];

use have;

id=unique(p_id);

do i=1 to ncol(id);
read all var {year} where (p_id=(id[i]));
read all var var_c where (p_id=(id[i])) into c;

y=t(year[uniqueby(year)]);

do j=1 to ncol(y)-1;
idx_y1=loc(year=(y[j]));
idx_y2=loc(year=(y[j+1]));

y1=c[idx_y1,];
y2=c[idx_y2,];

yxy=y1*y2;
want=yxy[,1:(ncol(yxy)-1)]*y2[1:(ncol(yxy)-1),ncol(yxy)];

label="Result for: "+id[i]+" ("+y[j]+"-"+y[j+1]+")";
label=repeat(label,nrow(want));

labels=labels//label;
wants=wants//want;

end;
end;
close;

create want from wants[r=labels];
append from wants[r=labels];
close;
quit;

``````

All Replies
Super User
Posts: 24,010

Re: Dynamic Matrix Multiplication

Posted in reply to akshaybatra1

It would help if you simplify the problem and include an example,of input and outputs required.

Occasional Contributor
Posts: 14

Re: Dynamic Matrix Multiplication

For exmaple, loop should first select Y1 Matrix for P_ID "A" and then Y2 Matrix for P_ID A and Multiply them.

 c1 c2 c3 c4 c5 c6 c7 c8 c9 Matrix 1 0.932606 0 0 0 0.059015 0.002698 0.003264 0.00071 0.001707 0 0.868185 0 0 0.1086 0.008444 0.008169 0.001671 0.004932 0 0 0.823402 0 0.14322 0.012453 0.010085 0.002494 0.008346 0 0 0 0.758853 0.178104 0.024061 0.016185 0.00652 0.016278 0 0 0 0 0.798871 0.031061 0.081029 0.021842 0.067197 0 0 0 0 0.298265 0.213144 0.150618 0.049483 0.288489 0 0 0 0 0.179519 0.021707 0.239906 0.084662 0.474207 0 0 0 0 0.039876 0.005683 0.022931 0.053959 0.877551 0 0 0 0 0.010017 0.001072 0.00444 0.003509 0.980962

 Matrix 2 0.932606 0 0 0 0.059015 0.002698 0.003264 0.00071 0.001707 0 0.868185 0 0 0.1086 0.008444 0.008169 0.001671 0.004932 0 0 0.823402 0 0.14322 0.012453 0.010085 0.002494 0.008346 0 0 0 0.758853 0.178104 0.024061 0.016185 0.00652 0.016278 0 0 0 0 0.798871 0.031061 0.081029 0.021842 0.067197 0 0 0 0 0.298265 0.213144 0.150618 0.049483 0.288489 0 0 0 0 0.179519 0.021707 0.239906 0.084662 0.474207 0 0 0 0 0.039876 0.005683 0.022931 0.053959 0.877551 0 0 0 0 0.010017 0.001072 0.00444 0.003509 0.980962

 Matrix 1 X Matrix 2 0.869754 0 0 0 0.103619 0.005001 0.009039 0.002405 0.010181 0 0.753745 0 0 0.185142 0.012696 0.019183 0.00504 0.024194 0 0 0.677991 0 0.238049 0.017599 0.024298 0.006816 0.035248 0 0 0 0.575857 0.287942 0.029325 0.034442 0.011808 0.060627 0 0 0 0 0.663549 0.033389 0.089649 0.02726 0.186153 0 0 0 0 0.333751 0.058555 0.094821 0.033496 0.479377 0 0 0 0 0.20108 0.0164 0.079418 0.031538 0.671564 0 0 0 0 0.048609 0.004195 0.014722 0.009085 0.923389 0 0 0 0 0.019085 0.001708 0.006475 0.00428 0.968453

and then from the result select all columns except last which means (9 X8 matrix ) multiplied by only last column from Y2 except the last value (8 X1) and this gives result a vector (9X1)

 0.016287 0.033341 0.044236 0.063877 0.120655 0.113679 0.08358 0.01943 0.008601
Occasional Contributor
Posts: 14

Re: Dynamic Matrix Multiplication

Posted in reply to akshaybatra1

I made a demo macro  as below but i am not able to make it work for different P_ID and collecting the result in 1 table

%macro test(P_ID);
data market1;
set wide1;
where (P_ID="&P_ID") and(year= "Y1") ;
drop P_ID year;
run;
data market2;
set wide1;
by portfolio_id;
where (P_ID="&P_ID") and(year= "PD_Y1_12M") ;
drop P_ID year i;
run;
%prod_mat_merge(in_A =market1,in_B=market2,ou_AB=AB);

%mend;

%test(P_ID= A);

Valued Guide
Posts: 505

Re: Dynamic Matrix Multiplication

Posted in reply to akshaybatra1
``````Dynamic Matrix Multiplication

If you have IML you can cut and paste the code into IML/R interface
or just use IML directly

I simplified the problem so I could check the algorithm.
Should be fairly easy to adapt this algorithm to IML
a datastep ,or even an FCMP function.

The hard part is breaking the problem down. I hope I did it correctly?

inspired by
https://goo.gl/XMGUKM
https://communities.sas.com/t5/SAS-Enterprise-Guide/Dynamic-Matrix-Multiplication/m-p/335968

Problem

Multiply Y12 ( only 9 X8) with Y2 (8X1 i.e only last column.)
and then from the result select all columns except last which means (9 X8 matrix )
multiplied by only last column from Y2 except the last value (8 X1) and this gives result a vector (9X1)

HAVE two SAS sdatasets Y1 and Y2

Up to 40 obs from sd1.y1 total obs=3

Obs    C1    C2    C3

1      1     0     0
2      1     1     0
3      1     1     1

Up to 40 obs from sd1.y2 total obs=3

Obs    C1    C2    C3

1      2     2     2
2      0     2     2
3      0     0     2

WANT

Up to 40 obs from wantwps total obs=3

Obs    V1

1      8
2     12
3     12

DETAILS

1. Matrix mutiply Y1 * Y2
=Sum(elementwise mutiplication of row of y1 times column of y2)
Sometimes called an inner product

Y1         Y2          Y12
1  0  0     2  2  2     2  2  2
1  1  0  X  0  2  2  =  2  4  4
1  1  1     0  0  2     2  4  6*

Example
2
*6= [1 1 1] *2  = 1*2 + 1*2 + 1*2 =6

2. Reduce the size of Y12 by removing the last column and
reduce the size of Y2 by selecting the rows 1 and 2 of the last column

Reduced  Reduced
Y12      Y2
=
2  2      2     =  1*2 + 2*2 =  8
2  4      2     =  2*2 + 4*2 = 12
2  4               2*2 + 4*2 = 12

WORKING CODE
============

R
1.  Compute y12
y12<-y1 %*% y2

Multiply reduced matrices
2.  y12[,c(1,2)] %*% y2[-1,3]

FULL SOLUTION
=============

Multiply Y12 ( only 9 X8) with Y2 (8X1 i.e only last column.)
and then from the result select all columns except last which means (9 X8 matrix )
multiplied by only last column from Y2 except the last value (8 X1) and this gives result a vector (9X1)

* create some data
options validvarname=upcase;
libname sd1 "d:/sd1";
data sd1.y1 (keep=c:) sd1.y2 (keep=c:);
input Year \$ c1 c2 c3;
if year='Y1' then output sd1.y1;
else output sd1.y2;
cards4;
Y1 1 0 0
Y1 1 1 0
Y1 1 1 1
Y2 2 2 2
Y2 0 2 2
Y2 0 0 2
;;;;
run;quit;

%utl_submit_wps64('
libname sd1 "d:/sd1";
options set=R_HOME "C:/Program Files/R/R-3.3.1";
libname wrk "%sysfunc(pathname(work))";
proc r;
submit;
library(haven);
library(dplyr);
y1;
y2;
y12<-y1 %nrstr(%%)*%y2;
y12;
y3x2<-y12[,c(1,2)];
y3x2;
y2[-1,3];
want<-y3x2%nrstr(%%)*%y2[-1,3];
want;
endsubmit;
import r=want data=wrk.wantwps;
run;quit;
');

proc print data=wantwps;
run;quit;

The WPS System

C1 C2 C3
[1,]  1  0  0
[2,]  1  1  0   Y1
[3,]  1  1  1

C1 C2 C3
[1,]  2  2  2
[2,]  0  2  2   Y2
[3,]  0  0  2

C1 C2 C3
[1,]  2  2  2   Y12
[2,]  2  4  4
[3,]  2  4  6

C1 C2
[1,]  2  2     Reduced Y12
[2,]  2  4
[3,]  2  4

[1] 2 2       Reduced Y2

[,1]
[1,]    8
[2,]   12
[3,]   12

``````
Occasional Contributor
Posts: 14

Re: Dynamic Matrix Multiplication

Posted in reply to rogerjdeangelis

Super User
Posts: 10,850

Re: Dynamic Matrix Multiplication

Posted in reply to akshaybatra1

It is really easy for IML code, you should take on IML .

``````data have;
infile cards dlm='09'x truncover;
input P_ID \$	Col	Year \$	c1	c2	c3	c4	c5	c6	c7	c8	c9;
cards;
A	1	Y1	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
A	2	Y1	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
A	3	Y1	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
A	4	Y1	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
A	5	Y1	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
A	6	Y1	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
A	7	Y1	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
A	8	Y1	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
A	9	Y1	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
A	1	Y2	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
A	2	Y2	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
A	3	Y2	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
A	4	Y2	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
A	5	Y2	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
A	6	Y2	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
A	7	Y2	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
A	8	Y2	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
A	9	Y2	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
B	1	Y1	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
B	2	Y1	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
B	3	Y1	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
B	4	Y1	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
B	5	Y1	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
B	6	Y1	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
B	7	Y1	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
B	8	Y1	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
B	9	Y1	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
;
run;

proc iml;
use have;
read all var {c1 c2	c3	c4	c5	c6	c7	c8	c9}
where (p_id='A' & year='Y1') into y1[c=vnames];
read all var {c1 c2	c3	c4	c5	c6	c7	c8	c9}
where (p_id='A' & year='Y2') into y2;
close;

y=y1*y2;
want=y[,1:8]*y2[1:8,9];

print y,want;
quit;``````
Super User
Posts: 10,850

Re: Dynamic Matrix Multiplication

Posted in reply to akshaybatra1

Here is doing the things for each and every P_ID and year Y1-Y3 .

``````data have;
infile cards dlm='09'x truncover;
input P_ID \$	Col	Year \$	c1	c2	c3	c4	c5	c6	c7	c8	c9;
cards;
A	1	Y1	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
A	2	Y1	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
A	3	Y1	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
A	4	Y1	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
A	5	Y1	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
A	6	Y1	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
A	7	Y1	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
A	8	Y1	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
A	9	Y1	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
A	1	Y2	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
A	2	Y2	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
A	3	Y2	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
A	4	Y2	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
A	5	Y2	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
A	6	Y2	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
A	7	Y2	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
A	8	Y2	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
A	9	Y2	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
A	1	Y3	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
A	2	Y3	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
A	3	Y3	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
A	4	Y3	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
A	5	Y3	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
A	6	Y3	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
A	7	Y3	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
A	8	Y3	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
A	9	Y3	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
B	1	Y1	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
B	2	Y1	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
B	3	Y1	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
B	4	Y1	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
B	5	Y1	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
B	6	Y1	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
B	7	Y1	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
B	8	Y1	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
B	9	Y1	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
B	1	Y2	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
B	2	Y2	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
B	3	Y2	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
B	4	Y2	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
B	5	Y2	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
B	6	Y2	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
B	7	Y2	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
B	8	Y2	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
B	9	Y2	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
B	1	Y3	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
B	2	Y3	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
B	3	Y3	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
B	4	Y3	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
B	5	Y3	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
B	6	Y3	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
B	7	Y3	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
B	8	Y3	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
B	9	Y3	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
;
run;

proc iml;
use have;

y="Y1":"Y3";
id=unique(p_id);

do i=1 to ncol(id);
read all var {year} where (p_id=(id[i]));
read all var {c1 c2	c3	c4	c5	c6	c7	c8	c9}
where (p_id=(id[i]) ) into c;

do j=1 to ncol(y)-1;
idx_y1=loc(year=(y[j]));
idx_y2=loc(year=(y[j+1]));

y1=c[idx_y1,];
y2=c[idx_y2,];

yxy=y1*y2;
want=yxy[,1:(ncol(yxy)-1)]*y2[1:(ncol(yxy)-1),ncol(yxy)];
print "Result for" (id[i]) (y[j])"-"(y[j+1]),yxy,want;
end;
end;
close;
quit;
``````
Occasional Contributor
Posts: 14

Re: Dynamic Matrix Multiplication

[ Edited ]

Thanks for the solution, It's really close to what i want. I have 2 question where i need your help.

1. Columns c1 to c9 are dynamically generated and are not fixed but i know the maximum length will be until c30. How can we make it dynamic ? It may be possible that column length for different p_id is diffrerent. Can we still manage it in calculation ?

2. I would like to store " want" results along with p_id and year as shown in print into table, what would be the easiest way ?

Thanks again for the help

Occasional Contributor
Posts: 14

Re: Dynamic Matrix Multiplication

Posted in reply to akshaybatra1

I made some changes to the code as per my requirements. I have 2 question where i need your help.

1. Columns c1 to c9 are dynamically generated and are not fixed but i know the maximum length will be until c30. How can we make it dynamic ? It may be possible that column length for different p_id is diffrerent. Can we still manage it in calculation ?

2. I would like to store " want" results along with p_id and year as shown in print into table, what would be the easiest way . As shown in the result table below (example)?

data have;
set wide1;
run;

proc iml;
use have;
y="Y0":"Y20";
id=unique(p_id);

do i=1 to ncol(id);
read all var {year} where (p_id=(id[i]));
read all var {c1 c2 c3 c4 c5 c6 c7 c8 c9 }
where (p_id=(id[i]) ) into c;

do j=1 to 20;
if (j<5) then;
do;
idx_y1=loc(year=(y[j]));
idx_y2=loc(year=(y[j+1]));
y1=c[idx_y1,];
y2=c[idx_y2,];
end;
else;
do;
idx_y1=loc(year=(y[4]));
idx_y2=loc(year=(y[4]));
y1=c[idx_y1,];
y2=c[idx_y2,];
end;

if j= 1 then
yxy=y1;
else yxy= yxy *y1;

want=yxy[,1ncol(yxy)-1)]*y2[1ncol(yxy)-1),ncol(yxy)];

print "Result for" (id[i]) (y[j])"-"(y[j+1]),yxy,want;
end;
end;

close;
quit;

Only final results " want" are stored here. based on p_id unitl Y20 as modified in the code.

 Y1 Y2 Y3 Y4 Y5 Y6 Y7 Y8 Y9 Y10 A 0.008507 0.016288 A 0.019356 0.033341 A 0.02706 0.044236 A 0.044659 0.063877 A 0.120235 0.120655 A 0.19638 0.113679 A 0.206385 0.08358 A 0.062545 0.01943 A 0.006168 0.008601 B 0.000778 0.001107 B 0.002922 0.004151 B 0.007357 0.010309 B 0.009653 0.011941 B 0.0279 0.03376 B 0.107928 0.065267 B 0.143504 0.070502 B 0.102335 0.049216 B 0.033293 0.039901
Solution
‎02-27-2017 07:30 AM
Super User
Posts: 10,850

Re: Dynamic Matrix Multiplication

[ Edited ]
Posted in reply to akshaybatra1

OK. Here fit all your requirement. Assuming data has already been sorted just as you showed.

``````data have;
infile cards dlm='09'x truncover;
input P_ID \$	Col	Year \$	c1	c2	c3	c4	c5	c6	c7	c8	c9;
cards;
A	1	Y1	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
A	2	Y1	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
A	3	Y1	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
A	4	Y1	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
A	5	Y1	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
A	6	Y1	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
A	7	Y1	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
A	8	Y1	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
A	9	Y1	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
A	1	Y2	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
A	2	Y2	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
A	3	Y2	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
A	4	Y2	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
A	5	Y2	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
A	6	Y2	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
A	7	Y2	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
A	8	Y2	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
A	9	Y2	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
A	1	Y3	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
A	2	Y3	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
A	3	Y3	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
A	4	Y3	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
A	5	Y3	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
A	6	Y3	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
A	7	Y3	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
A	8	Y3	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
A	9	Y3	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
B	1	Y1	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
B	2	Y1	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
B	3	Y1	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
B	4	Y1	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
B	5	Y1	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
B	6	Y1	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
B	7	Y1	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
B	8	Y1	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
B	9	Y1	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
B	1	Y2	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
B	2	Y2	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
B	3	Y2	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
B	4	Y2	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
B	5	Y2	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
B	6	Y2	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
B	7	Y2	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
B	8	Y2	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
B	9	Y2	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
B	1	Y3	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
B	2	Y3	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
B	3	Y3	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
B	4	Y3	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
B	5	Y3	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
B	6	Y3	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
B	7	Y3	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
B	8	Y3	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
B	9	Y3	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
;
run;

proc iml;
vnames=contents(have);
var_c=vnames[loc(prxmatch('/^c\d+\s*\$/i',vnames))];

use have;

id=unique(p_id);

do i=1 to ncol(id);
read all var {year} where (p_id=(id[i]));
read all var var_c where (p_id=(id[i])) into c;

y=t(year[uniqueby(year)]);

do j=1 to ncol(y)-1;
idx_y1=loc(year=(y[j]));
idx_y2=loc(year=(y[j+1]));

y1=c[idx_y1,];
y2=c[idx_y2,];

yxy=y1*y2;
want=yxy[,1:(ncol(yxy)-1)]*y2[1:(ncol(yxy)-1),ncol(yxy)];

label="Result for: "+id[i]+" ("+y[j]+"-"+y[j+1]+")";
label=repeat(label,nrow(want));

labels=labels//label;
wants=wants//want;

end;
end;
close;

create want from wants[r=labels];
append from wants[r=labels];
close;
quit;

``````
Occasional Contributor
Posts: 14

Re: Dynamic Matrix Multiplication

[ Edited ]

Thanks a lot ksharp for the solution. It was extremely helpful.

I have modified the code a bit as per my requirement as below. I am facing 1 problem i.e.

If i have certain number of blanks in P_Id  B which means P_ID A has 9 classes whereas P_ID B has only 5, so the last 4 columns has ". "values. How to exclude these column from the do loop as it gives an error. I tried supressing them to 0 but calculations didnt work because number of rows are 5 and columns become 9.

I think there is some filter required in the do loop while assigning the values to variable c so that it can ignore  all dots"." related column and matrix multiplication should work then.

Thanks for your help again.

data have;

set wide1;

run;

proc iml;

vnames=contents(have);

var_c=vnames[loc(prxmatch('/^C\d+\s*\$/',vnames))];

use have;

y="Y0":"Y20";

id=unique(p_id);

do i=1 to ncol(id);

read all var {year} where (p_id=(id[i]));

read all var var_c where (p_id=(id[i])) into c;

do j=1 to 20;

if(j=1) then

do;

idx_y1=loc(year=(y[j]));

y1=c[idx_y1,];

end;

else if (j<5) then;

do;

idx_y1=loc(year=(y[j-1]));

idx_y2=loc(year=(y[j+1-1]));

y1=c[idx_y1,];

y2=c[idx_y2,];

end;

else;

do;

idx_y1=loc(year=(y[4]));

idx_y2=loc(year=(y[4]));

y1=c[idx_y1,];

y2=c[idx_y2,];

end;

if j= 1 then;

do;

yxy=y1;

want=yxy[1ncol(yxy)),ncol(yxy)];

end;

else if j=2 then ;

do;

want=y1[,1ncol(yxy)-1)]*y2[1ncol(yxy)-1),ncol(yxy)];

yxy= y1 *y2;

end;

else if j>2 then;

do;

want=yxy[,1ncol(yxy)-1)]*y1[1ncol(yxy)-1),ncol(yxy)];

yxy= yxy *y1;

end;

/*         print "Result for" (id[i]) (y[j])"-"(y[j+1]),yxy,want;*/

/*  label=id[i];*/

/*  label1=y[j+1];*/

label=repeat(id[i],nrow(want));

x=repeat(y[j+1-1],nrow(want));

labels=labels//label;

label1=label1//x;

wants=wants//want;

end;

end;

close;

create Year var {label1};

append;

close;

create P_id var {labels};

append;

close;

create value from wants;

append from wants;

close;

quit;

data want;

merge P_id Year value;

run;

Super User
Posts: 10,850

Re: Dynamic Matrix Multiplication

Posted in reply to akshaybatra1

Post your data to see how B has 5,A has 9 .

Occasional Contributor
Posts: 14

Re: Dynamic Matrix Multiplication

[ Edited ]

In the below example. P_ID A is 8X8 matrix whereas P_ID A is 6X6 matrix and thats why the last 2 columns are "." with non existing values. sample data below. in my data, i will always have square matrices and dimensions can be different from one P_ID to the other.

 P_ID Col Year C1 C2 C3 C4 C5 C6 C7 C8 A 1 Y0 0.935193 0.061191 0.00326 0.000198 0 9.92E-05 0 0.001119 A 2 Y0 0.09008 0.866152 0.041217 0.002112 0.000155 0.00024 0 0.001211 A 3 Y0 0.009539 0.151812 0.772266 0.058096 0.005424 0.001286 0.000781 0.003409 A 4 Y0 8.24E-03 0.017303 0.245627 0.658377 0.050229 0.014127 0.002094 0.018758 A 5 Y0 2.00E-07 0.007187 0.029421 0.241866 0.628595 0.060768 0.014678 0.027137 A 6 Y0 0 1.80E-03 0.009076 0.040047 0.116802 0.670406 0.064916 0.096957 A 7 Y0 0 2.40E-06 0.065577 0.118361 0.088068 0.226496 0.490294 0.088054 A 8 Y0 0 0.001281 0.003049 0.004147 0.007201 0.0236 0.023381 0.937342 A 1 Y1 0.935193 0.061191 0.00326 0.000198 0 9.92E-05 0 0.001119 A 2 Y1 0.09008 0.866152 0.041217 0.002112 0.000155 0.00024 0 0.001211 A 3 Y1 0.009539 0.151812 0.772266 0.058096 0.005424 0.001286 0.000781 0.003409 A 4 Y1 8.24E-03 0.017303 0.245627 0.658377 0.050229 0.014127 0.002094 0.018758 A 5 Y1 2.00E-07 0.007187 0.029421 0.241866 0.628595 0.060768 0.014678 0.027137 A 6 Y1 0 1.80E-03 0.009076 0.040047 0.116802 0.670406 0.064916 0.096957 A 7 Y1 0 2.40E-06 0.065577 0.118361 0.088068 0.226496 0.490294 0.088054 A 8 Y1 0 0.001281 0.003049 0.004147 0.007201 0.0236 0.023381 0.937342 A 1 Y2 0.935193 0.061191 0.00326 0.000198 0 9.92E-05 0 0.001119 A 2 Y2 0.09008 0.866152 0.041217 0.002112 0.000155 0.00024 0 0.001211 A 3 Y2 0.009539 0.151812 0.772266 0.058096 0.005424 0.001286 0.000781 0.003409 A 4 Y2 8.24E-03 0.017303 0.245627 0.658377 0.050229 0.014127 0.002094 0.018758 A 5 Y2 2.00E-07 0.007187 0.029421 0.241866 0.628595 0.060768 0.014678 0.027137 A 6 Y2 0 1.80E-03 0.009076 0.040047 0.116802 0.670406 0.064916 0.096957 A 7 Y2 0 2.40E-06 0.065577 0.118361 0.088068 0.226496 0.490294 0.088054 A 8 Y2 0 0.001281 0.003049 0.004147 0.007201 0.0236 0.023381 0.937342 B 1 Y0 0.982259 0.010519 0.003558 0.002125 0.000042 0.002968 . . B 2 Y0 0 0.85109 0.051942 0.049293 0.001082 0.046594 . . B 3 Y0 0 0.389006 0.210199 0.132965 0.00446 0.26337 . . B 4 Y0 0 0.172864 0.043859 0.192941 0.005411 0.584925 . . B 5 Y0 0 0.000421 0.000213 0.000674 0.000988 0.997704 . . B 6 Y0 0 0.02149 0.004954 0.010492 0.000634 0.062927 . . B 1 Y1 0.982259 0.010519 0.003558 0.002125 0.000042 0.002968 . . B 2 Y1 0 0.85109 0.051942 0.049293 0.001082 0.046594 . . B 3 Y1 0 0.389006 0.210199 0.132965 0.00446 0.26337 . . B 4 Y1 0 0.172864 0.043859 0.192941 0.005411 0.584925 . . B 5 Y1 0 0.000421 0.000213 0.000674 0.000988 0.997704 . . B 6 Y1 0 0.02149 0.004954 0.010492 0.000634 0.062927 . . B 1 Y2 0.982259 0.010519 0.003558 0.002125 0.000042 0.002968 . . B 2 Y2 0 0.85109 0.051942 0.049293 0.001082 0.046594 . . B 3 Y2 0 0.389006 0.210199 0.132965 0.00446 0.26337 . . B 4 Y2 0 0.172864 0.043859 0.192941 0.005411 0.584925 . . B 5 Y2 0 0.000421 0.000213 0.000674 0.000988 0.997704 . . B 6 Y2 0 0.02149 0.004954 0.010492 0.000634 0.062927 . . B 1 Y3 0.982259 0.010519 0.003558 0.002125 0.000042 0.002968 . . B 2 Y3 0 0.85109 0.051942 0.049293 0.001082 0.046594 . . B 3 Y3 0 0.389006 0.210199 0.132965 0.00446 0.26337 . . B 4 Y3 0 0.172864 0.043859 0.192941 0.005411 0.584925 . . B 5 Y3 0 0.000421 0.000213 0.000674 0.000988 0.997704 . . B 6 Y3 0 0.02149 0.004954 0.010492 0.000634 0.062927 . . B 1 Y4 0.982259 0.010519 0.003558 0.002125 0.000042 0.002968 . . B 2 Y4 0 0.85109 0.051942 0.049293 0.001082 0.046594 . . B 3 Y4 0 0.389006 0.210199 0.132965 0.00446 0.26337 . . B 4 Y4 0 0.172864 0.043859 0.192941 0.005411 0.584925 . . B 5 Y4 0 0.000421 0.000213 0.000674 0.000988 0.997704 . . B 6 Y4 0 0.02149 0.004954 0.010492 0.000634 0.062927 . .
Super User
Posts: 10,850

Re: Dynamic Matrix Multiplication

Posted in reply to akshaybatra1

Then my last code should handle this.

☑ This topic is solved.

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

Discussion stats
• 16 replies
• 649 views
• 1 like
• 4 in conversation