BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ss59
Obsidian | Level 7

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

10 REPLIES 10
Reeza
Super User

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

ss59
Obsidian | Level 7

@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
Obsidian | Level 7

@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
Super User

Your modified code runs fine, the original did not.

ss59
Obsidian | Level 7

@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_
Rhodochrosite | Level 12

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
Obsidian | Level 7

@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
Tom
Super User Tom
Super User

@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
Obsidian | Level 7

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

Ksharp
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.

From The DO Loop
Want more? Visit our blog for more articles like these.
Discussion stats
  • 10 replies
  • 2279 views
  • 0 likes
  • 5 in conversation