How about :
/********create parametrisation table*********/
data param_table;
infile datalines dsd;
length FieldName $20 FieldSourceTable $20;
input Matrix_Id Row_Order Column_Order IsZero IsRowResidual IsColumnResidual FieldName FieldSourceTable;
datalines;
1, 1, 1, 0, 1, 0, ., .
1, 1, 2, 0, 0, 0, xyz, table1
1, 1, 3, 0, 0, 0, abc, table1
1, 2, 1, 1, 0, 0, ., .
1, 2, 2, 0, 0, 0, pqr, table1
1, 2, 3, 0, 0, 0, mno, table1
1, 3, 1, 0, 0, 0, ab, table1
1, 3, 2, 0, 0, 0, pq, table1
1, 3, 3, 0, 1, 0, ., .
2, 1, 1, 1, 0, 0, ., .
2, 1, 2, 0, 0, 0, result1, table2
2, 1, 3, 0, 0, 0, result2, table2
2, 2, 1, 1, 0, 0, ., .
2, 2, 2, 0, 0, 0, sum1, table2
2, 2, 3, 0, 0, 0, sum2, table2
2, 3, 1, 0, 0, 0, prod1, table2
2, 3, 2, 0, 0, 0, prod2, table2
2, 3, 3, 1, 0, 0, ., .
;
proc sort data= param_table out= param_table;
by Matrix_Id Row_Order Column_Order;
quit;
proc sql;
create table param_tr as
select * from work.param_table
where Matrix_Id = 1;
quit;
proc sql;
create table param_flow as
select * from work.param_table
where Matrix_Id = 2;
quit;
/********create data table*********/
data table1;
input Year (country method Segment) ( : $12.)
ABC XYZ PQR MNO AB PQ;
datalines;
2017 France ABC Retail 0.2 0.5 0.4 0.3 0.6 0.1
2017 France XYZ Corporate 0.1 0.5 0.4 0.2 0.6 0.2
;
run;
/*********build matrices and write out*********/
proc iml;
use param_tr;
read all var {Column_Order Row_Order FieldName};
close;
idx = loc(FieldName ^= " ");
refNames = FieldName[idx];
use param_flow;
read all var {Column_Order Row_Order FieldName};
close;
idx_2 = loc(FieldName ^= " ");
refNames_2 = FieldName[idx_2];
use table1;
read all var refNames into Y;
close;
/******Create 3x3 EAD transition matrix for each row of table 1*****/
do i = 1 to nrow(Y); /*I want to include the loop*/
TR_MAT = j(3,3,0);
TR_MAT[idx_2] = Y[i,]; /*this should be Y[i,]*/
temp = J(3,1)- TR_MAT[,+];
do j=1 to 3;
TR_MAT[j,j] = temp[j,1];
end;
EAD_INIT = {10, 20, 33};
EAD_flows=(EAD_INIT#TR_MAT);
/**********write elements of EAD flow out*********/
EAD_flow_MAT = EAD_flows[idx_2];
EAD_flow_MAT = t(EAD_flow_MAT);
new_EAD_flow_MAT=new_EAD_flow_MAT//EAD_flow_MAT;
end;
mattrib new_EAD_flow_MAT colname=refNames_2;
create table2 from new_EAD_flow_MAT[colname = refNames_2];
append from new_EAD_flow_MAT;
close table2;
quit;
... View more