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

Hello All,

 

I have a dataset in the following form below where colums C1..... C9 are dynamically generated in the previous steps.

 

1. at first step, I need to multiply for every P_ID, matrix of Y1 to Y2 = Y12.

2. Multiply Y12 ( only 9 X8) with Y2 (8X1 i.e only last column.)

 

Challenge is that i have multiple P_ID and have to also do matrix multiplication until 20 years.

 

I have created a macro which can always multiply 2 matrices but the major problem is also to select data dynamically and saving it.

 

Really appreciate if somebody can help me solve the problem , the sample data looks like below.

 

Thanks for your help.

 

P_IDColYearc1c2c3c4c5c6c7c8c9
A1Y10.9326060000.0590150.0026980.0032640.000710.001707
A2Y100.868185000.10860.0084440.0081690.0016710.004932
A3Y1000.82340200.143220.0124530.0100850.0024940.008346
A4Y10000.7588530.1781040.0240610.0161850.006520.016278
A5Y100000.7988710.0310610.0810290.0218420.067197
A6Y100000.2982650.2131440.1506180.0494830.288489
A7Y100000.1795190.0217070.2399060.0846620.474207
A8Y100000.0398760.0056830.0229310.0539590.877551
A9Y100000.0100170.0010720.004440.0035090.980962
A1Y20.9326060000.0590150.0026980.0032640.000710.001707
A2Y200.868185000.10860.0084440.0081690.0016710.004932
A3Y2000.82340200.143220.0124530.0100850.0024940.008346
A4Y20000.7588530.1781040.0240610.0161850.006520.016278
A5Y200000.7988710.0310610.0810290.0218420.067197
A6Y200000.2982650.2131440.1506180.0494830.288489
A7Y200000.1795190.0217070.2399060.0846620.474207
A8Y200000.0398760.0056830.0229310.0539590.877551
A9Y200000.0100170.0010720.004440.0035090.980962
B1Y10.9326060000.0590150.0026980.0032640.000710.001707
B2Y100.868185000.10860.0084440.0081690.0016710.004932
B3Y1000.82340200.143220.0124530.0100850.0024940.008346
B4Y10000.7588530.1781040.0240610.0161850.006520.016278
B5Y100000.7988710.0310610.0810290.0218420.067197
B6Y100000.2982650.2131440.1506180.0494830.288489
B7Y100000.1795190.0217070.2399060.0846620.474207
B8Y100000.0398760.0056830.0229310.0539590.877551
B9Y100000.0100170.0010720.004440.0035090.980962
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

OK. Here fit all your requirement. Assuming data has already been sorted just as you showed.

 

 

data have;
infile cards dlm='09'x truncover;
input P_ID $	Col	Year $	c1	c2	c3	c4	c5	c6	c7	c8	c9;
cards;
A	1	Y1	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
A	2	Y1	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
A	3	Y1	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
A	4	Y1	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
A	5	Y1	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
A	6	Y1	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
A	7	Y1	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
A	8	Y1	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
A	9	Y1	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
A	1	Y2	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
A	2	Y2	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
A	3	Y2	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
A	4	Y2	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
A	5	Y2	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
A	6	Y2	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
A	7	Y2	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
A	8	Y2	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
A	9	Y2	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
A	1	Y3	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
A	2	Y3	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
A	3	Y3	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
A	4	Y3	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
A	5	Y3	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
A	6	Y3	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
A	7	Y3	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
A	8	Y3	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
A	9	Y3	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
B	1	Y1	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
B	2	Y1	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
B	3	Y1	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
B	4	Y1	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
B	5	Y1	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
B	6	Y1	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
B	7	Y1	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
B	8	Y1	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
B	9	Y1	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
B	1	Y2	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
B	2	Y2	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
B	3	Y2	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
B	4	Y2	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
B	5	Y2	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
B	6	Y2	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
B	7	Y2	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
B	8	Y2	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
B	9	Y2	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
B	1	Y3	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
B	2	Y3	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
B	3	Y3	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
B	4	Y3	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
B	5	Y3	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
B	6	Y3	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
B	7	Y3	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
B	8	Y3	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
B	9	Y3	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
;
run;

proc iml;
vnames=contents(have);
var_c=vnames[loc(prxmatch('/^c\d+\s*$/i',vnames))];

use have;
read all var{p_id};

id=unique(p_id);

do i=1 to ncol(id);
 read all var {year} where (p_id=(id[i]));
 read all var var_c where (p_id=(id[i])) into c;

 y=t(year[uniqueby(year)]);

 do j=1 to ncol(y)-1;
  idx_y1=loc(year=(y[j]));
  idx_y2=loc(year=(y[j+1]));

  y1=c[idx_y1,];
  y2=c[idx_y2,];
  
  yxy=y1*y2;
  want=yxy[,1:(ncol(yxy)-1)]*y2[1:(ncol(yxy)-1),ncol(yxy)];
  
  label="Result for: "+id[i]+" ("+y[j]+"-"+y[j+1]+")";
  label=repeat(label,nrow(want));
  
  labels=labels//label;
  wants=wants//want;

 end;
end;
close;

create want from wants[r=labels];
append from wants[r=labels];
close;
quit;


View solution in original post

16 REPLIES 16
Reeza
Super User

It would help if you simplify the problem and include an example,of input and outputs required. 

 

 

akshaybatra1
Obsidian | Level 7

For exmaple, loop should first select Y1 Matrix for P_ID "A" and then Y2 Matrix for P_ID A and Multiply them.

 c1c2c3c4c5c6c7c8c9
Matrix 10.9326060000.0590150.0026980.0032640.000710.001707
 00.868185000.10860.0084440.0081690.0016710.004932
 000.82340200.143220.0124530.0100850.0024940.008346
 0000.7588530.1781040.0240610.0161850.006520.016278
 00000.7988710.0310610.0810290.0218420.067197
 00000.2982650.2131440.1506180.0494830.288489
 00000.1795190.0217070.2399060.0846620.474207
 00000.0398760.0056830.0229310.0539590.877551
 00000.0100170.0010720.004440.0035090.980962

 

Matrix 20.9326060000.0590150.0026980.0032640.000710.001707
 00.868185000.10860.0084440.0081690.0016710.004932
 000.82340200.143220.0124530.0100850.0024940.008346
 0000.7588530.1781040.0240610.0161850.006520.016278
 00000.7988710.0310610.0810290.0218420.067197
 00000.2982650.2131440.1506180.0494830.288489
 00000.1795190.0217070.2399060.0846620.474207
 00000.0398760.0056830.0229310.0539590.877551
 00000.0100170.0010720.004440.003509

0.980962

 

Matrix 1 X Matrix 20.8697540000.1036190.0050010.0090390.0024050.010181
 00.753745000.1851420.0126960.0191830.005040.024194
 000.67799100.2380490.0175990.0242980.0068160.035248
 0000.5758570.2879420.0293250.0344420.0118080.060627
 00000.6635490.0333890.0896490.027260.186153
 00000.3337510.0585550.0948210.0334960.479377
 00000.201080.01640.0794180.0315380.671564
 00000.0486090.0041950.0147220.0090850.923389
 00000.0190850.0017080.0064750.004280.968453

 

and then from the result select all columns except last which means (9 X8 matrix ) multiplied by only last column from Y2 except the last value (8 X1) and this gives result a vector (9X1)

 

0.016287
0.033341
0.044236
0.063877
0.120655
0.113679
0.08358
0.01943
0.008601
akshaybatra1
Obsidian | Level 7

I made a demo macro  as below but i am not able to make it work for different P_ID and collecting the result in 1 table

 

%macro test(P_ID);
data market1;
set wide1;
where (P_ID="&P_ID") and(year= "Y1") ;
drop P_ID year;
run;
data market2;
set wide1;
by portfolio_id;
where (P_ID="&P_ID") and(year= "PD_Y1_12M") ;
drop P_ID year i;
run;
%prod_mat_merge(in_A =market1,in_B=market2,ou_AB=AB);

%mend;

%test(P_ID= A);

rogerjdeangelis
Barite | Level 11
Dynamic Matrix Multiplication

If you have IML you can cut and paste the code into IML/R interface
or just use IML directly

I simplified the problem so I could check the algorithm.
Should be fairly easy to adapt this algorithm to IML
 a datastep ,or even an FCMP function.

The hard part is breaking the problem down. I hope I did it correctly?

inspired by
https://goo.gl/XMGUKM
https://communities.sas.com/t5/SAS-Enterprise-Guide/Dynamic-Matrix-Multiplication/m-p/335968


Problem

Multiply Y12 ( only 9 X8) with Y2 (8X1 i.e only last column.)
and then from the result select all columns except last which means (9 X8 matrix )
multiplied by only last column from Y2 except the last value (8 X1) and this gives result a vector (9X1)


HAVE two SAS sdatasets Y1 and Y2

Up to 40 obs from sd1.y1 total obs=3

Obs    C1    C2    C3

 1      1     0     0
 2      1     1     0
 3      1     1     1

Up to 40 obs from sd1.y2 total obs=3

Obs    C1    C2    C3

 1      2     2     2
 2      0     2     2
 3      0     0     2


WANT

Up to 40 obs from wantwps total obs=3

Obs    V1

 1      8
 2     12
 3     12

DETAILS

 1. Matrix mutiply Y1 * Y2
        =Sum(elementwise mutiplication of row of y1 times column of y2)
    Sometimes called an inner product

      Y1         Y2          Y12
   1  0  0     2  2  2     2  2  2
   1  1  0  X  0  2  2  =  2  4  4
   1  1  1     0  0  2     2  4  6*

   Example
               2
  *6= [1 1 1] *2  = 1*2 + 1*2 + 1*2 =6

  2. Reduce the size of Y12 by removing the last column and
     reduce the size of Y2 by selecting the rows 1 and 2 of the last column

      Reduced  Reduced
        Y12      Y2
  =
        2  2      2     =  1*2 + 2*2 =  8
        2  4      2     =  2*2 + 4*2 = 12
        2  4               2*2 + 4*2 = 12


WORKING CODE
============

     R
      1.  Compute y12
              y12<-y1 %*% y2

          Multiply reduced matrices
      2.  y12[,c(1,2)] %*% y2[-1,3]

FULL SOLUTION
=============

Multiply Y12 ( only 9 X8) with Y2 (8X1 i.e only last column.)
and then from the result select all columns except last which means (9 X8 matrix )
multiplied by only last column from Y2 except the last value (8 X1) and this gives result a vector (9X1)

* create some data
options validvarname=upcase;
libname sd1 "d:/sd1";
data sd1.y1 (keep=c:) sd1.y2 (keep=c:);
input Year $ c1 c2 c3;
if year='Y1' then output sd1.y1;
else output sd1.y2;
cards4;
Y1 1 0 0
Y1 1 1 0
Y1 1 1 1
Y2 2 2 2
Y2 0 2 2
Y2 0 0 2
;;;;
run;quit;


%utl_submit_wps64('
libname sd1 "d:/sd1";
options set=R_HOME "C:/Program Files/R/R-3.3.1";
libname wrk "%sysfunc(pathname(work))";
proc r;
submit;
library(haven);
library(dplyr);
y1<-as.matrix(read_sas("d:/sd1/y1.sas7bdat"));
y2<-as.matrix(read_sas("d:/sd1/y2.sas7bdat"));
y1;
y2;
y12<-y1 %nrstr(%%)*%y2;
y12;
y3x2<-y12[,c(1,2)];
y3x2;
y2[-1,3];
want<-y3x2%nrstr(%%)*%y2[-1,3];
want;
endsubmit;
import r=want data=wrk.wantwps;
run;quit;
');

proc print data=wantwps;
run;quit;

The WPS System

     C1 C2 C3
[1,]  1  0  0
[2,]  1  1  0   Y1
[3,]  1  1  1

     C1 C2 C3
[1,]  2  2  2
[2,]  0  2  2   Y2
[3,]  0  0  2

     C1 C2 C3
[1,]  2  2  2   Y12
[2,]  2  4  4
[3,]  2  4  6

     C1 C2
[1,]  2  2     Reduced Y12
[2,]  2  4
[3,]  2  4

[1] 2 2       Reduced Y2

     [,1]
[1,]    8
[2,]   12
[3,]   12

akshaybatra1
Obsidian | Level 7

Thanks for your reply.

Ksharp
Super User

It is really easy for IML code, you should take on IML .

 

data have;
infile cards dlm='09'x truncover;
input P_ID $	Col	Year $	c1	c2	c3	c4	c5	c6	c7	c8	c9;
cards;
A	1	Y1	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
A	2	Y1	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
A	3	Y1	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
A	4	Y1	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
A	5	Y1	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
A	6	Y1	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
A	7	Y1	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
A	8	Y1	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
A	9	Y1	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
A	1	Y2	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
A	2	Y2	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
A	3	Y2	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
A	4	Y2	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
A	5	Y2	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
A	6	Y2	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
A	7	Y2	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
A	8	Y2	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
A	9	Y2	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
B	1	Y1	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
B	2	Y1	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
B	3	Y1	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
B	4	Y1	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
B	5	Y1	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
B	6	Y1	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
B	7	Y1	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
B	8	Y1	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
B	9	Y1	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
;
run;

proc iml;
use have;
read all var {c1 c2	c3	c4	c5	c6	c7	c8	c9} 
where (p_id='A' & year='Y1') into y1[c=vnames];
read all var {c1 c2	c3	c4	c5	c6	c7	c8	c9} 
where (p_id='A' & year='Y2') into y2;
close;

y=y1*y2;
want=y[,1:8]*y2[1:8,9];

print y,want;
quit;
Ksharp
Super User

Here is doing the things for each and every P_ID and year Y1-Y3 .

 

data have;
infile cards dlm='09'x truncover;
input P_ID $	Col	Year $	c1	c2	c3	c4	c5	c6	c7	c8	c9;
cards;
A	1	Y1	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
A	2	Y1	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
A	3	Y1	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
A	4	Y1	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
A	5	Y1	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
A	6	Y1	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
A	7	Y1	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
A	8	Y1	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
A	9	Y1	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
A	1	Y2	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
A	2	Y2	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
A	3	Y2	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
A	4	Y2	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
A	5	Y2	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
A	6	Y2	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
A	7	Y2	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
A	8	Y2	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
A	9	Y2	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
A	1	Y3	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
A	2	Y3	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
A	3	Y3	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
A	4	Y3	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
A	5	Y3	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
A	6	Y3	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
A	7	Y3	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
A	8	Y3	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
A	9	Y3	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
B	1	Y1	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
B	2	Y1	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
B	3	Y1	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
B	4	Y1	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
B	5	Y1	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
B	6	Y1	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
B	7	Y1	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
B	8	Y1	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
B	9	Y1	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
B	1	Y2	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
B	2	Y2	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
B	3	Y2	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
B	4	Y2	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
B	5	Y2	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
B	6	Y2	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
B	7	Y2	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
B	8	Y2	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
B	9	Y2	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
B	1	Y3	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
B	2	Y3	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
B	3	Y3	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
B	4	Y3	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
B	5	Y3	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
B	6	Y3	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
B	7	Y3	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
B	8	Y3	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
B	9	Y3	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
;
run;

proc iml;
use have;
read all var{p_id};

y="Y1":"Y3";
id=unique(p_id);

do i=1 to ncol(id);
 read all var {year} where (p_id=(id[i]));
 read all var {c1 c2	c3	c4	c5	c6	c7	c8	c9} 
 where (p_id=(id[i]) ) into c;

 do j=1 to ncol(y)-1;
  idx_y1=loc(year=(y[j]));
  idx_y2=loc(year=(y[j+1]));

  y1=c[idx_y1,];
  y2=c[idx_y2,];
  
  yxy=y1*y2;
  want=yxy[,1:(ncol(yxy)-1)]*y2[1:(ncol(yxy)-1),ncol(yxy)];
  print "Result for" (id[i]) (y[j])"-"(y[j+1]),yxy,want;
 end;
end;
close;
quit;
akshaybatra1
Obsidian | Level 7

Thanks for the solution, It's really close to what i want. I have 2 question where i need your help.

 

1. Columns c1 to c9 are dynamically generated and are not fixed but i know the maximum length will be until c30. How can we make it dynamic ? It may be possible that column length for different p_id is diffrerent. Can we still manage it in calculation ?

2. I would like to store " want" results along with p_id and year as shown in print into table, what would be the easiest way ?

 

Thanks again for the help 🙂 🙂

akshaybatra1
Obsidian | Level 7

I made some changes to the code as per my requirements. I have 2 question where i need your help.

 

1. Columns c1 to c9 are dynamically generated and are not fixed but i know the maximum length will be until c30. How can we make it dynamic ? It may be possible that column length for different p_id is diffrerent. Can we still manage it in calculation ?

 

2. I would like to store " want" results along with p_id and year as shown in print into table, what would be the easiest way . As shown in the result table below (example)?


data have;
set wide1;
run;

 

 

 

proc iml;
use have;
read all var{p_id};
y="Y0":"Y20";
id=unique(p_id);

do i=1 to ncol(id);
read all var {year} where (p_id=(id[i]));
read all var {c1 c2 c3 c4 c5 c6 c7 c8 c9 }
where (p_id=(id[i]) ) into c;

do j=1 to 20;
if (j<5) then;
do;
idx_y1=loc(year=(y[j]));
idx_y2=loc(year=(y[j+1]));
y1=c[idx_y1,];
y2=c[idx_y2,];
end;
else;
do;
idx_y1=loc(year=(y[4]));
idx_y2=loc(year=(y[4]));
y1=c[idx_y1,];
y2=c[idx_y2,];
end;

if j= 1 then
yxy=y1;
else yxy= yxy *y1;

want=yxy[,1:(ncol(yxy)-1)]*y2[1:(ncol(yxy)-1),ncol(yxy)];

print "Result for" (id[i]) (y[j])"-"(y[j+1]),yxy,want;
end;
end;

close;
quit;

 

Only final results " want" are stored here. based on p_id unitl Y20 as modified in the code.

 

 

  Y1Y2 Y3 Y4 Y5 Y6 Y7 Y8 Y9 Y10
A0.0085070.016288        
A0.0193560.033341        
A0.027060.044236        
A0.0446590.063877        
A0.1202350.120655        
A0.196380.113679        
A0.2063850.08358        
A0.0625450.01943        
A0.0061680.008601        
B0.0007780.001107        
B0.0029220.004151        
B0.0073570.010309        
B0.0096530.011941        
B0.02790.03376        
B0.1079280.065267        
B0.1435040.070502        
B0.1023350.049216        
B0.0332930.039901        
Ksharp
Super User

OK. Here fit all your requirement. Assuming data has already been sorted just as you showed.

 

 

data have;
infile cards dlm='09'x truncover;
input P_ID $	Col	Year $	c1	c2	c3	c4	c5	c6	c7	c8	c9;
cards;
A	1	Y1	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
A	2	Y1	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
A	3	Y1	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
A	4	Y1	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
A	5	Y1	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
A	6	Y1	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
A	7	Y1	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
A	8	Y1	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
A	9	Y1	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
A	1	Y2	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
A	2	Y2	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
A	3	Y2	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
A	4	Y2	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
A	5	Y2	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
A	6	Y2	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
A	7	Y2	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
A	8	Y2	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
A	9	Y2	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
A	1	Y3	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
A	2	Y3	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
A	3	Y3	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
A	4	Y3	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
A	5	Y3	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
A	6	Y3	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
A	7	Y3	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
A	8	Y3	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
A	9	Y3	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
B	1	Y1	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
B	2	Y1	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
B	3	Y1	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
B	4	Y1	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
B	5	Y1	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
B	6	Y1	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
B	7	Y1	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
B	8	Y1	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
B	9	Y1	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
B	1	Y2	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
B	2	Y2	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
B	3	Y2	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
B	4	Y2	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
B	5	Y2	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
B	6	Y2	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
B	7	Y2	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
B	8	Y2	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
B	9	Y2	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
B	1	Y3	0.932606	0	0	0	0.059015	0.002698	0.003264	0.00071	0.001707
B	2	Y3	0	0.868185	0	0	0.1086	0.008444	0.008169	0.001671	0.004932
B	3	Y3	0	0	0.823402	0	0.14322	0.012453	0.010085	0.002494	0.008346
B	4	Y3	0	0	0	0.758853	0.178104	0.024061	0.016185	0.00652	0.016278
B	5	Y3	0	0	0	0	0.798871	0.031061	0.081029	0.021842	0.067197
B	6	Y3	0	0	0	0	0.298265	0.213144	0.150618	0.049483	0.288489
B	7	Y3	0	0	0	0	0.179519	0.021707	0.239906	0.084662	0.474207
B	8	Y3	0	0	0	0	0.039876	0.005683	0.022931	0.053959	0.877551
B	9	Y3	0	0	0	0	0.010017	0.001072	0.00444	0.003509	0.980962
;
run;

proc iml;
vnames=contents(have);
var_c=vnames[loc(prxmatch('/^c\d+\s*$/i',vnames))];

use have;
read all var{p_id};

id=unique(p_id);

do i=1 to ncol(id);
 read all var {year} where (p_id=(id[i]));
 read all var var_c where (p_id=(id[i])) into c;

 y=t(year[uniqueby(year)]);

 do j=1 to ncol(y)-1;
  idx_y1=loc(year=(y[j]));
  idx_y2=loc(year=(y[j+1]));

  y1=c[idx_y1,];
  y2=c[idx_y2,];
  
  yxy=y1*y2;
  want=yxy[,1:(ncol(yxy)-1)]*y2[1:(ncol(yxy)-1),ncol(yxy)];
  
  label="Result for: "+id[i]+" ("+y[j]+"-"+y[j+1]+")";
  label=repeat(label,nrow(want));
  
  labels=labels//label;
  wants=wants//want;

 end;
end;
close;

create want from wants[r=labels];
append from wants[r=labels];
close;
quit;


akshaybatra1
Obsidian | Level 7

Thanks a lot ksharp for the solution. It was extremely helpful.

 

I have modified the code a bit as per my requirement as below. I am facing 1 problem i.e.

 

If i have certain number of blanks in P_Id  B which means P_ID A has 9 classes whereas P_ID B has only 5, so the last 4 columns has ". "values. How to exclude these column from the do loop as it gives an error. I tried supressing them to 0 but calculations didnt work because number of rows are 5 and columns become 9.

 

I think there is some filter required in the do loop while assigning the values to variable c so that it can ignore  all dots"." related column and matrix multiplication should work then.

 

Thanks for your help again.

 

 

 data have;

set wide1;

run;

proc iml;

     vnames=contents(have);

     var_c=vnames[loc(prxmatch('/^C\d+\s*$/',vnames))];

     use have;

     read all var{p_id};

     y="Y0":"Y20";

     id=unique(p_id);

 

     do i=1 to ncol(id);

           read all var {year} where (p_id=(id[i]));

           read all var var_c where (p_id=(id[i])) into c;

 

           do j=1 to 20;

                if(j=1) then

                     do;

                           idx_y1=loc(year=(y[j]));

                           y1=c[idx_y1,];

                     end;

                else if (j<5) then;

                do;

                     idx_y1=loc(year=(y[j-1]));

                     idx_y2=loc(year=(y[j+1-1]));

                     y1=c[idx_y1,];

                     y2=c[idx_y2,];

                end;

           else;

           do;

                idx_y1=loc(year=(y[4]));

                idx_y2=loc(year=(y[4]));

                y1=c[idx_y1,];

                y2=c[idx_y2,];

           end;

 

           if j= 1 then;

           do;

                yxy=y1;

                want=yxy[1:(ncol(yxy)),ncol(yxy)];

                end;

           else if j=2 then ;

                do;

                want=y1[,1:(ncol(yxy)-1)]*y2[1:(ncol(yxy)-1),ncol(yxy)];

                yxy= y1 *y2;

                end;

           else if j>2 then;

                do;

                want=yxy[,1:(ncol(yxy)-1)]*y1[1:(ncol(yxy)-1),ncol(yxy)];

                yxy= yxy *y1;

                end;

 

           /*         print "Result for" (id[i]) (y[j])"-"(y[j+1]),yxy,want;*/

           /*  label=id[i];*/

           /*  label1=y[j+1];*/

           label=repeat(id[i],nrow(want));

           x=repeat(y[j+1-1],nrow(want));

           labels=labels//label;

           label1=label1//x;

           wants=wants//want;

           end;

     end;

 

     close;

     create Year var {label1};

     append;

     close;

     create P_id var {labels};

     append;

     close;

     create value from wants;

     append from wants;

     close;

quit;

 

data want;

     merge P_id Year value;

run;

Ksharp
Super User

Post your data to see how B has 5,A has 9 .

akshaybatra1
Obsidian | Level 7

In the below example. P_ID A is 8X8 matrix whereas P_ID A is 6X6 matrix and thats why the last 2 columns are "." with non existing values. sample data below. in my data, i will always have square matrices and dimensions can be different from one P_ID to the other.

 

           
           
P_IDColYearC1C2C3C4C5C6C7C8
A1Y00.9351930.0611910.003260.00019809.92E-0500.001119
A2Y00.090080.8661520.0412170.0021120.0001550.0002400.001211
A3Y00.0095390.1518120.7722660.0580960.0054240.0012860.0007810.003409
A4Y08.24E-030.0173030.2456270.6583770.0502290.0141270.0020940.018758
A5Y02.00E-070.0071870.0294210.2418660.6285950.0607680.0146780.027137
A6Y001.80E-030.0090760.0400470.1168020.6704060.0649160.096957
A7Y002.40E-060.0655770.1183610.0880680.2264960.4902940.088054
A8Y000.0012810.0030490.0041470.0072010.02360.0233810.937342
A1Y10.9351930.0611910.003260.00019809.92E-0500.001119
A2Y10.090080.8661520.0412170.0021120.0001550.0002400.001211
A3Y10.0095390.1518120.7722660.0580960.0054240.0012860.0007810.003409
A4Y18.24E-030.0173030.2456270.6583770.0502290.0141270.0020940.018758
A5Y12.00E-070.0071870.0294210.2418660.6285950.0607680.0146780.027137
A6Y101.80E-030.0090760.0400470.1168020.6704060.0649160.096957
A7Y102.40E-060.0655770.1183610.0880680.2264960.4902940.088054
A8Y100.0012810.0030490.0041470.0072010.02360.0233810.937342
A1Y20.9351930.0611910.003260.00019809.92E-0500.001119
A2Y20.090080.8661520.0412170.0021120.0001550.0002400.001211
A3Y20.0095390.1518120.7722660.0580960.0054240.0012860.0007810.003409
A4Y28.24E-030.0173030.2456270.6583770.0502290.0141270.0020940.018758
A5Y22.00E-070.0071870.0294210.2418660.6285950.0607680.0146780.027137
A6Y201.80E-030.0090760.0400470.1168020.6704060.0649160.096957
A7Y202.40E-060.0655770.1183610.0880680.2264960.4902940.088054
A8Y200.0012810.0030490.0041470.0072010.02360.0233810.937342
B1Y00.9822590.0105190.0035580.0021250.0000420.002968..
B2Y000.851090.0519420.0492930.0010820.046594..
B3Y000.3890060.2101990.1329650.004460.26337..
B4Y000.1728640.0438590.1929410.0054110.584925..
B5Y000.0004210.0002130.0006740.0009880.997704..
B6Y000.021490.0049540.0104920.0006340.062927..
B1Y10.9822590.0105190.0035580.0021250.0000420.002968..
B2Y100.851090.0519420.0492930.0010820.046594..
B3Y100.3890060.2101990.1329650.004460.26337..
B4Y100.1728640.0438590.1929410.0054110.584925..
B5Y100.0004210.0002130.0006740.0009880.997704..
B6Y100.021490.0049540.0104920.0006340.062927..
B1Y20.9822590.0105190.0035580.0021250.0000420.002968..
B2Y200.851090.0519420.0492930.0010820.046594..
B3Y200.3890060.2101990.1329650.004460.26337..
B4Y200.1728640.0438590.1929410.0054110.584925..
B5Y200.0004210.0002130.0006740.0009880.997704..
B6Y200.021490.0049540.0104920.0006340.062927..
B1Y30.9822590.0105190.0035580.0021250.0000420.002968..
B2Y300.851090.0519420.0492930.0010820.046594..
B3Y300.3890060.2101990.1329650.004460.26337..
B4Y300.1728640.0438590.1929410.0054110.584925..
B5Y300.0004210.0002130.0006740.0009880.997704..
B6Y300.021490.0049540.0104920.0006340.062927..
B1Y40.9822590.0105190.0035580.0021250.0000420.002968..
B2Y400.851090.0519420.0492930.0010820.046594..
B3Y400.3890060.2101990.1329650.004460.26337..
B4Y400.1728640.0438590.1929410.0054110.584925..
B5Y400.0004210.0002130.0006740.0009880.997704..
B6Y400.021490.0049540.0104920.0006340.062927..
Ksharp
Super User

Then my last code should handle this.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 16 replies
  • 2090 views
  • 1 like
  • 4 in conversation