Writing to a data table from a matrix within a do loop - iml

[ss59]

Hi,

I have a parametrisation table and a data table. For each row of data table, I am creating a transition matrix, multiplying that matrix to a constant matrix element-wise and trying to write specific elements of output matrix to a new table.

The following code works perfectly without the do loop, i.e. it only works for one row of table1 at a time. The writing ("create" and "append" statement within a do loop) does not work when I have the do loop. Ideally I want for one run of the loop a new row will be appended to the table2 that I am creating.

/********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[2,];  /*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);
mattrib EAD_flow_MAT colname=refNames_2;

create table2 from EAD_flow_MAT[colname = refNames_2];
append from EAD_flow_MAT; /** create data set **/
close table2;

/*end;*/ *******I want to have the do loop

How can I write the result of each row table1 to different rows of "table2" within a loop? Thanks!

[Reeza]

Your code has errors....is that what you're trying to resolve?

[ss59]

@Reeza this code runs in my machine in the current form. Could you please clarify what error you are getting?

I'm trying to resolve this section:

create table2 from EAD_flow_MAT[colname = refNames_2];
append from EAD_flow_MAT; /** create data set **/
close table2;

I want have the do loop, create the dataset table2 in the first go of the loop, and then append to it in each subsequent steps of he do loop. 

Please let me know if that was not clear.

[ss59]

@Reeza when I specify i =1 or 2, I get the desired output in dataset table2. I am trying to have the dataset open and append to it in each run of the loop and get as many rows as table1 as an end result.

[Reeza]

Your modified code runs fine, the original did not.

[ss59]

@Reeza yes, sorry about that. But even the modified code does not serve my purpose. I want something like this (and the code below does not work) :

/********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);

TR_MAT = j(3,3,0);
TR_MAT[idx_2] = 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);
mattrib EAD_flow_MAT colname=refNames_2;

create table2 from EAD_flow_MAT[colname = refNames_2];
append from EAD_flow_MAT; /** create data set and append in each subsequent steps**/

end;

close table2;

[kiranv_]

if you want to append data then 

create table2 from EAD_flow_MAT[colname = refNames_2];

has to be out of loop. otherwise you replacing that dataset then appending to the dataset

[ss59]

@kiranv_  - that results in only the last matrix to be written to the data. Only one row. The other matrices in the loop are not captured.

If you do print EAD_flows; before the end; you'll see these two matrices. I want one row for each matrix in the table2 data through the loop.

EAD_flows
3	5	2
0	6	6
19.8	3.3	9.9

EAD_flows
4	5	1
0	8	4
19.8	6.6	6.6

[Tom]

---

@ss59 wrote:

@kiranv_  - that results in only the last matrix to be written to the data. Only one row. The other matrices in the loop are not captured.

If you do print EAD_flows; before the end; you'll see these two matrices. I want one row for each matrix in the table2 data through the loop.

EAD_flows
3	5	2
0	6	6
19.8	3.3	9.9

EAD_flows
4	5	1
0	8	4
19.8	6.6	6.6

---

What does that mean?   An observation in a dataset cannot be a "matrix", unless it was a matrix with only one row.

Do you want to write multiple observations for each pass of the do loop?  Or do you want to just write one of the rows of the matrix?  if so which row?

Do you want to reshape your matrix into a one row matrix and append that?

[ss59]

Hi @kiranv_, if you look at the param_table for Matrix_ID 2, it looks like following:

FieldName	FieldSourceTable	Matrix_Id	Row_Order	Column_Order	IsZero	IsRowResidual	IsColumnResidual
		2	1	1	1	0	0
result1	table2	2	1	2	0	0	0
result2	table2	2	1	3	0	0	0
		2	2	1	1	0	0
sum1	table2	2	2	2	0	0	0
sum2	table2	2	2	3	0	0	0
prod1	table2	2	3	1	0	0	0
prod2	table2	2	3	2	0	0	0
		2	3	3	1	0	0

My desired table2 would be:

result1	result2	sum1	sum2	prod1	prod2
5	2	6	6	19.8	3.3
5	1	8	4	19.8	6.6

The first row comes from the first EAD_flows matrix, second row comes form the second matrix (with desired elements as mentioned in param_table). I can do it for one row, but seeking help to do it in the loop so that I get the dataset table2 with multiple rows corresponding to each Ead_flow matrix

[Ksharp · Accepted solution]

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;