Statistical programming, matrix languages, and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

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

[ Edited ]

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!

 


Accepted Solutions
Highlighted
Solution
‎01-03-2018 03:13 PM
Super User
Posts: 10,618

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

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 solution in original post


All Replies
Super User
Posts: 22,857

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

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

Contributor
Posts: 42

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

@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.

 

 

Contributor
Posts: 42

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

@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.

Super User
Posts: 22,857

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

Your modified code runs fine, the original did not.

Contributor
Posts: 42

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

@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;
PROC Star
Posts: 500

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

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

Contributor
Posts: 42

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

[ Edited ]

@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
352
066
19.83.3

9.9

 

 

EAD_flows
451
084
19.86.66.6
Super User
Super User
Posts: 7,860

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


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?

Contributor
Posts: 42

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

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

 

FieldNameFieldSourceTableMatrix_IdRow_OrderColumn_OrderIsZeroIsRowResidualIsColumnResidual
  211100
result1table2212000
result2table2213000
  221100
sum1table2222000
sum2table2223000
prod1table2231000
prod2table2232000
  233100

 

My desired table2 would be:

 

result1result2sum1sum2prod1prod2
526619.83.3
518419.86.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

Highlighted
Solution
‎01-03-2018 03:13 PM
Super User
Posts: 10,618

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

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 280 views
  • 0 likes
  • 5 in conversation