Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 03-22-2019 11:06 PM
(905 views)

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

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

3 REPLIES 3

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

**Don't miss out on SAS Innovate - Register now for the FREE Livestream!**

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.