Hi all! I have a generational table represented by a matrix where each row represents the initial ages of two individuals (X and J) and each column represents a annuity factor that is calendar-year specific. Both ages as well the projection year can vary between 0 and 125 which results in a matrix M of dimension 15876 x 126. Now lets say that the initial ages for X and J are zero (on row 1 of M), in the following year they would be one year older (x=1 and j=1 located at row x of M) and so on and so forth until both X and J reach the max age (125) - which would then result in a square matrix N of 126x126. My main goal is for retrieve each diagonal of N and performs some calculations and export the results for later use. I was able to do this using the following steps: 1) imported my gen table into a matrix called 'inputDjxx' using proc iml; 2) Subset the above matrix by using a few loops in conjuction with the loc function; 3) Another loop to get each diagonal and do some additional calculations. I am trying to optimize the following code but i am stuck, is there a better way to do this without so many loops? Please, see attached file for reference and the code below: PROC IML;
use work.gen_table;
read all var _all_ into inputDjxx;
close work.gen_table;
do age_x=0 to &MaxAge; *Initial age for X;
do age_j=0 to &MaxAge; *Initial age for J;
do t=0 to &MaxAge;
new_x=min(&MaxAge, age_x+t);
new_j=min(&MaxAge, age_j+t);
*Identify lines for corresponding to ages x and j;
idx = loc(inputDjxx[,3]=new_x & inputDjxx[,4]=new_j);
if t=0 then
idxj=idx;
else
idxj=insert(idxj,idx,nrow(idxj)+1);
end;
*Submatrix of dimension 126x126;
Djxx=inputDjxx[idxj, 3:128];
lastrow=nrow(Djxx);
lastcol=ncol(Djxx);
*Matrix to hold final results;
Njxx=j(lastrow, lastcol,0);
*Get each diagonal of the square matrix and performs some calculations;
do rowx= 1 to lastrow;
Djxx_subset = Djxx[rowx:lastrow,1:lastcol];
Djxx_diag = vecdiag(Djxx_subset);
idx=0:(nrow(Djxx_diag)-1);
DiagOffset=lag(Djxx_diag, -idx);
DiagOffsetSum=DiagOffset[+,];
free Djxx_diag DiagOffset;
if rowx=1 then do;
Njxx = DiagOffsetSum;
end;
else do;
IsMiss = j(1,1,.);
DiagOffsetSum = DiagOffsetSum || repeat(IsMiss,1,rowx-1);
Njxx=insert(Njxx, DiagOffsetSum,nrow(Njxx)+1);
end;
end;
*print or create table from resulting matrix;
end;
end;
end;
QUIT; Thanks in advance/
... View more