topic Dynamic Matrix Multiplication in SAS Enterprise Guide

Dynamic Matrix Multiplication

akshaybatra1 — Sun, 26 Feb 2017 14:54:36 GMT

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

Re: Dynamic Matrix Multiplication

Reeza — Sun, 26 Feb 2017 15:16:16 GMT

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

Re: Dynamic Matrix Multiplication

akshaybatra1 — Sun, 26 Feb 2017 15:45:37 GMT

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

Re: Dynamic Matrix Multiplication

akshaybatra1 — Sun, 26 Feb 2017 16:24:58 GMT

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

Re: Dynamic Matrix Multiplication

rogerjdeangelis — Sun, 26 Feb 2017 21:56:35 GMT

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<-as.matrix(read_sas("d:/sd1/y1.sas7bdat"));
y2<-as.matrix(read_sas("d:/sd1/y2.sas7bdat"));
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

Re: Dynamic Matrix Multiplication

Ksharp — Mon, 27 Feb 2017 02:36:32 GMT

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;

Re: Dynamic Matrix Multiplication

Ksharp — Mon, 27 Feb 2017 03:11:23 GMT

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;
read all var{p_id};

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;

Re: Dynamic Matrix Multiplication

akshaybatra1 — Mon, 27 Feb 2017 08:27:32 GMT

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

Re: Dynamic Matrix Multiplication

akshaybatra1 — Mon, 27 Feb 2017 09:47:48 GMT

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

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;
read all var{p_id};
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[,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;

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

Re: Dynamic Matrix Multiplication

Ksharp — Mon, 27 Feb 2017 10:31:03 GMT

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;
read all var{p_id};

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;

Re: Dynamic Matrix Multiplication

akshaybatra1 — Mon, 27 Feb 2017 13:10:18 GMT

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;

read all var{p_id};

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[1:(ncol(yxy)),ncol(yxy)];

end;

else if j=2 then ;

do;

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

yxy= y1 *y2;

end;

else if j>2 then;

do;

want=yxy[,1:(ncol(yxy)-1)]*y1[1:(ncol(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;

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;

Re: Dynamic Matrix Multiplication

akshaybatra1 — Mon, 27 Feb 2017 12:36:09 GMT

Thanks for your reply.

Re: Dynamic Matrix Multiplication

Ksharp — Tue, 28 Feb 2017 02:11:52 GMT

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

Re: Dynamic Matrix Multiplication

Ksharp — Tue, 28 Feb 2017 03:11:01 GMT

OK. You mean this.

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

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

use have;
read all var{p_id};

id=t(p_id[uniqueby(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,1:ncol(idx_y1)];
  y2=c[idx_y2,1:ncol(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;

Re: Dynamic Matrix Multiplication

akshaybatra1 — Tue, 28 Feb 2017 08:18:15 GMT

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

Re: Dynamic Matrix Multiplication

Ksharp — Wed, 01 Mar 2017 03:10:27 GMT

Then my last code should handle this.

Re: Dynamic Matrix Multiplication

akshaybatra1 — Thu, 02 Mar 2017 07:44:03 GMT

yes, it worked, thanks a lot 🙂 🙂