Fluorite | Level 6

## How to compute matrix product by group

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

1 ACCEPTED SOLUTION

Accepted Solutions
SAS Super FREQ

## Re: How to compute matrix product by group

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"

3 REPLIES 3
Tourmaline | Level 20

## Re: How to compute matrix product by group

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;``````
Super User

## Re: How to compute matrix product by group

``````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;
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;``````
SAS Super FREQ

## Re: How to compute matrix product by group

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"

From The DO Loop