yes, it worked, thanks a lot 🙂 🙂 ... View more

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_ID Col Year C1 C2 C3 C4 C5 C6 C7 C8 A 1 Y0 0.935193 0.061191 0.00326 0.000198 0 9.92E-05 0 0.001119 A 2 Y0 0.09008 0.866152 0.041217 0.002112 0.000155 0.00024 0 0.001211 A 3 Y0 0.009539 0.151812 0.772266 0.058096 0.005424 0.001286 0.000781 0.003409 A 4 Y0 8.24E-03 0.017303 0.245627 0.658377 0.050229 0.014127 0.002094 0.018758 A 5 Y0 2.00E-07 0.007187 0.029421 0.241866 0.628595 0.060768 0.014678 0.027137 A 6 Y0 0 1.80E-03 0.009076 0.040047 0.116802 0.670406 0.064916 0.096957 A 7 Y0 0 2.40E-06 0.065577 0.118361 0.088068 0.226496 0.490294 0.088054 A 8 Y0 0 0.001281 0.003049 0.004147 0.007201 0.0236 0.023381 0.937342 A 1 Y1 0.935193 0.061191 0.00326 0.000198 0 9.92E-05 0 0.001119 A 2 Y1 0.09008 0.866152 0.041217 0.002112 0.000155 0.00024 0 0.001211 A 3 Y1 0.009539 0.151812 0.772266 0.058096 0.005424 0.001286 0.000781 0.003409 A 4 Y1 8.24E-03 0.017303 0.245627 0.658377 0.050229 0.014127 0.002094 0.018758 A 5 Y1 2.00E-07 0.007187 0.029421 0.241866 0.628595 0.060768 0.014678 0.027137 A 6 Y1 0 1.80E-03 0.009076 0.040047 0.116802 0.670406 0.064916 0.096957 A 7 Y1 0 2.40E-06 0.065577 0.118361 0.088068 0.226496 0.490294 0.088054 A 8 Y1 0 0.001281 0.003049 0.004147 0.007201 0.0236 0.023381 0.937342 A 1 Y2 0.935193 0.061191 0.00326 0.000198 0 9.92E-05 0 0.001119 A 2 Y2 0.09008 0.866152 0.041217 0.002112 0.000155 0.00024 0 0.001211 A 3 Y2 0.009539 0.151812 0.772266 0.058096 0.005424 0.001286 0.000781 0.003409 A 4 Y2 8.24E-03 0.017303 0.245627 0.658377 0.050229 0.014127 0.002094 0.018758 A 5 Y2 2.00E-07 0.007187 0.029421 0.241866 0.628595 0.060768 0.014678 0.027137 A 6 Y2 0 1.80E-03 0.009076 0.040047 0.116802 0.670406 0.064916 0.096957 A 7 Y2 0 2.40E-06 0.065577 0.118361 0.088068 0.226496 0.490294 0.088054 A 8 Y2 0 0.001281 0.003049 0.004147 0.007201 0.0236 0.023381 0.937342 B 1 Y0 0.982259 0.010519 0.003558 0.002125 0.000042 0.002968 . . B 2 Y0 0 0.85109 0.051942 0.049293 0.001082 0.046594 . . B 3 Y0 0 0.389006 0.210199 0.132965 0.00446 0.26337 . . B 4 Y0 0 0.172864 0.043859 0.192941 0.005411 0.584925 . . B 5 Y0 0 0.000421 0.000213 0.000674 0.000988 0.997704 . . B 6 Y0 0 0.02149 0.004954 0.010492 0.000634 0.062927 . . B 1 Y1 0.982259 0.010519 0.003558 0.002125 0.000042 0.002968 . . B 2 Y1 0 0.85109 0.051942 0.049293 0.001082 0.046594 . . B 3 Y1 0 0.389006 0.210199 0.132965 0.00446 0.26337 . . B 4 Y1 0 0.172864 0.043859 0.192941 0.005411 0.584925 . . B 5 Y1 0 0.000421 0.000213 0.000674 0.000988 0.997704 . . B 6 Y1 0 0.02149 0.004954 0.010492 0.000634 0.062927 . . B 1 Y2 0.982259 0.010519 0.003558 0.002125 0.000042 0.002968 . . B 2 Y2 0 0.85109 0.051942 0.049293 0.001082 0.046594 . . B 3 Y2 0 0.389006 0.210199 0.132965 0.00446 0.26337 . . B 4 Y2 0 0.172864 0.043859 0.192941 0.005411 0.584925 . . B 5 Y2 0 0.000421 0.000213 0.000674 0.000988 0.997704 . . B 6 Y2 0 0.02149 0.004954 0.010492 0.000634 0.062927 . . B 1 Y3 0.982259 0.010519 0.003558 0.002125 0.000042 0.002968 . . B 2 Y3 0 0.85109 0.051942 0.049293 0.001082 0.046594 . . B 3 Y3 0 0.389006 0.210199 0.132965 0.00446 0.26337 . . B 4 Y3 0 0.172864 0.043859 0.192941 0.005411 0.584925 . . B 5 Y3 0 0.000421 0.000213 0.000674 0.000988 0.997704 . . B 6 Y3 0 0.02149 0.004954 0.010492 0.000634 0.062927 . . B 1 Y4 0.982259 0.010519 0.003558 0.002125 0.000042 0.002968 . . B 2 Y4 0 0.85109 0.051942 0.049293 0.001082 0.046594 . . B 3 Y4 0 0.389006 0.210199 0.132965 0.00446 0.26337 . . B 4 Y4 0 0.172864 0.043859 0.192941 0.005411 0.584925 . . B 5 Y4 0 0.000421 0.000213 0.000674 0.000988 0.997704 . . B 6 Y4 0 0.02149 0.004954 0.010492 0.000634 0.062927 . . ... View more

Thanks for your reply. ... View more

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; ... View more

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.        Y1 Y2  Y3  Y4  Y5  Y6  Y7  Y8  Y9  Y10 A 0.008507 0.016288                 A 0.019356 0.033341                 A 0.02706 0.044236                 A 0.044659 0.063877                 A 0.120235 0.120655                 A 0.19638 0.113679                 A 0.206385 0.08358                 A 0.062545 0.01943                 A 0.006168 0.008601                 B 0.000778 0.001107                 B 0.002922 0.004151                 B 0.007357 0.010309                 B 0.009653 0.011941                 B 0.0279 0.03376                 B 0.107928 0.065267                 B 0.143504 0.070502                 B 0.102335 0.049216                 B 0.033293 0.039901                 ... View more

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 🙂 🙂 ... View more

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); ... View more

For exmaple, loop should first select Y1 Matrix for P_ID "A" and then Y2 Matrix for P_ID A and Multiply them.   c1 c2 c3 c4 c5 c6 c7 c8 c9 Matrix 1 0.932606 0 0 0 0.059015 0.002698 0.003264 0.00071 0.001707   0 0.868185 0 0 0.1086 0.008444 0.008169 0.001671 0.004932   0 0 0.823402 0 0.14322 0.012453 0.010085 0.002494 0.008346   0 0 0 0.758853 0.178104 0.024061 0.016185 0.00652 0.016278   0 0 0 0 0.798871 0.031061 0.081029 0.021842 0.067197   0 0 0 0 0.298265 0.213144 0.150618 0.049483 0.288489   0 0 0 0 0.179519 0.021707 0.239906 0.084662 0.474207   0 0 0 0 0.039876 0.005683 0.022931 0.053959 0.877551   0 0 0 0 0.010017 0.001072 0.00444 0.003509 0.980962   Matrix 2 0.932606 0 0 0 0.059015 0.002698 0.003264 0.00071 0.001707   0 0.868185 0 0 0.1086 0.008444 0.008169 0.001671 0.004932   0 0 0.823402 0 0.14322 0.012453 0.010085 0.002494 0.008346   0 0 0 0.758853 0.178104 0.024061 0.016185 0.00652 0.016278   0 0 0 0 0.798871 0.031061 0.081029 0.021842 0.067197   0 0 0 0 0.298265 0.213144 0.150618 0.049483 0.288489   0 0 0 0 0.179519 0.021707 0.239906 0.084662 0.474207   0 0 0 0 0.039876 0.005683 0.022931 0.053959 0.877551   0 0 0 0 0.010017 0.001072 0.00444 0.003509 0.980962   Matrix 1 X Matrix 2 0.869754 0 0 0 0.103619 0.005001 0.009039 0.002405 0.010181   0 0.753745 0 0 0.185142 0.012696 0.019183 0.00504 0.024194   0 0 0.677991 0 0.238049 0.017599 0.024298 0.006816 0.035248   0 0 0 0.575857 0.287942 0.029325 0.034442 0.011808 0.060627   0 0 0 0 0.663549 0.033389 0.089649 0.02726 0.186153   0 0 0 0 0.333751 0.058555 0.094821 0.033496 0.479377   0 0 0 0 0.20108 0.0164 0.079418 0.031538 0.671564   0 0 0 0 0.048609 0.004195 0.014722 0.009085 0.923389   0 0 0 0 0.019085 0.001708 0.006475 0.00428 0.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 ... View more

Super !! It works, i just changed it in a way i need it.   Thanks a lot.   %macro myprep(period,start_date); %put &period; %put &start_date; run; %mend myprep;   data _null_; set cohort_table; Str= catt('%myprep(',12, ',',start_date, ');'); call execute(Str); run;   ... View more

I have a macro %myprep(period, start_date) where we need to pass parameters for every cohort, so ideally users have to write in the program  multiple times, as below. %myprep(12,'201012') %myprep(12,'201101') %myprep(12,'201102') %myprep(12,'201103') %myprep(12,'201104')    My program creates a SQL table  period     Start_date 12            201012 12            201101 12            201102 I would like to use this table and pass it into function %myprep(period, start_date) so that we dont need to invoke macro function multiple times. ... View more

if you run the code in SAS , except the macro, it will give you a table COHORT_TABLE. this  has    Period  Start_date 12        200803 12        200804   I would like to pass these parameters into macro function dynamically. ... View more

Thanks for your reply,   I tried using    call execute('%myprep(period'||period||',start_date'||start_date||');');  call execute('%nrstr(%myprep('||period||','||start_date||'))');   it doesnt work aswell. ... View more