- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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"
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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"