How to compute matrix product by group

[FcoAVC]

Hello

I have a problem which I need to multiply X1 by 100 weights W1 ... W100 and then sum them (weighted sum). My dataset has 100,000 cases

So I decided to compute matrix products in order to avoid loops.

Here is my code:

data datos;
input group $ x1 x2 w1 w2 w3;
datalines;
A	55.3	41.1	2	3	1
A	50.5	40	3	2	2
A	52.4	39.8	2	1	3
A	58.1	39	1	2	1
A	54	39.5	2	3	2
A	55.6	40.5	3	2	3
B	49.5	50.5	5	5	5
B	48.8	50	4	6	6
B	50.1	49.5	6	4	5
B	49.9	48	5	5	5
C	60.1	60	8	9	6
C	59.9	61	7	8	7
C	58	59	8	7	8
C	58.5	60.5	7	8	8
C	61.2	59.5	8	7	9
C	60	60	9	8	9
;
run;
%LET x = x1 x2;
%LET w = w1 w2 w3;

proc iml;
use datos;
read all var { &x} into x[rowname=group colname = nr];
read all var { &w} into w[rowname=group colname = nc];
close datos;
mattrib a rowname = nr colname = nc label = "Overall sum";
a = t(x)*w;
print a;
quit;

I currently obtain this:

Overall sum
	w1	w2	w3
x1	4495.8	4501.4	4489.4
x2	4329.3	4332.6	4338.5

but now I need to calculate matrix product by group in order to get this result:

sums by group
		w1	w2	w3
A	x1	699.8	708.7	646.4
A	x2	521.3	520.6	480.0
B	x1	992.8	990.2	1040.3
B	x2	989.5	990.5	1040.0
C	x1	2803.2	2802.5	2802.7
C	x2	2818.5	2821.5	2818.5

Thanks

[PeterClemmensen]

Here is a way to implement the logic

data datos;
input group $ x1 x2 w1 w2 w3;
datalines;
A	55.3	41.1	2	3	1
A	50.5	40	3	2	2
A	52.4	39.8	2	1	3
A	58.1	39	1	2	1
A	54	39.5	2	3	2
A	55.6	40.5	3	2	3
B	49.5	50.5	5	5	5
B	48.8	50	4	6	6
B	50.1	49.5	6	4	5
B	49.9	48	5	5	5
C	60.1	60	8	9	6
C	59.9	61	7	8	7
C	58	59	8	7	8
C	58.5	60.5	7	8	8
C	61.2	59.5	8	7	9
C	60	60	9	8	9
;
run;

%LET x = x1 x2;
%LET w = w1 w2 w3;

proc iml;

use datos;
   read all var {&x} into x;
   read all var {&w} into w;
   read all var {group} into group;
close datos;

u=unique(group);
x=t(x);
s = j(nrow(x), ncol(u));

do k=1 to 3;
   idx=loc(group=u[k]);
   do i=1 to nrow(x);
      do j=1 to ncol(u);
         s[i,j]=x[i, idx] * w[idx, j];
      end;
   end;
a=a//s;
end;

print a;

quit;

[Ksharp]

data datos;
input group $ x1 x2 w1 w2 w3;
datalines;
A	55.3	41.1	2	3	1
A	50.5	40	3	2	2
A	52.4	39.8	2	1	3
A	58.1	39	1	2	1
A	54	39.5	2	3	2
A	55.6	40.5	3	2	3
B	49.5	50.5	5	5	5
B	48.8	50	4	6	6
B	50.1	49.5	6	4	5
B	49.9	48	5	5	5
C	60.1	60	8	9	6
C	59.9	61	7	8	7
C	58	59	8	7	8
C	58.5	60.5	7	8	8
C	61.2	59.5	8	7	9
C	60	60	9	8	9
;
run;
%LET x = x1 x2;
%LET w = w1 w2 w3;

proc iml;
use datos;
read all var {group};
read all var { &x} into x[rowname=group colname = nr];
read all var { &w} into w[rowname=group colname = nc];
close datos;
levels=unique(group);
do i=1 to ncol(levels);
 idx=loc(group=levels[i]);
 x1=x[idx,];
 w1=w[idx,];
 a=a//t(x1)*w1;
 r=r//t(nr);
 g=g//repeat(levels[i],ncol(nr),1);
end;

create g var {g};
append;
close;
create w from a[r=r c=nc];
append from a[r=r];
close;
quit;
data want;
 merge g w;
run;

[Rick_SAS · Accepted solution]

If the groups are unsorted, you can use the UNIQUE-LOC technique to obtain the rows for each group.

What you do next depends what you want to do with the matrix products. Here is a loop that just prints out the products. You could also write them to a data set.

u = unique(group);
do i = 1 to ncol(u);
   gIdx = loc(group = u[i]);  /* row numbers for i_th group */
   Xg = X[gIdx,];              /* subset data by group */
   Wg = W[gIdx,];
   A = Xg` * Wg;
   labl = "Group = " + u[i];
   print A[r=nr c=nc label = labl];
end;

To write to a data set. Use the CREATE FROM statement to open the data set BEFORE the loop. Use the APPEND FROM statement inside the loop, and close the data set outside the loop. Here is an example of writing data in a loop.

For a complete example of a "BY-group " analysis in SAS/IML, see the article "Matrix operations and BY groups"