BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
FcoAVC
Fluorite | Level 6

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
 w1w2w3
x14495.84501.44489.4
x24329.34332.64338.5

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

sums by group
  w1w2w3
Ax1699.8708.7646.4
Ax2521.3520.6480.0
Bx1992.8990.21040.3
Bx2989.5990.51040.0
Cx12803.22802.52802.7
Cx22818.52821.52818.5

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Rick_SAS
SAS Super FREQ

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"

View solution in original post

3 REPLIES 3
PeterClemmensen
Tourmaline | Level 20

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
Super User
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
SAS Super FREQ

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"

sas-innovate-2024.png

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.

 

Register now!

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.

From The DO Loop
Want more? Visit our blog for more articles like these.
Discussion stats
  • 3 replies
  • 906 views
  • 0 likes
  • 4 in conversation