turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-26-2017 05:53 AM - edited 12-26-2017 11:36 PM

Hi all,

I have a parametrization table that mentions whether the (i,j) th element of "matrix 1" is zero, residual of the row sum or has to be read from the data table. I also have a data table with all the values for different segments. How do I construct the matrix?

For example, let's say "param_table" is the parametrization table:

```
data param_table;
infile datalines dsd;
length FieldName $20 FieldSourceTable $20;
input Matrix_Id Column_Order Row_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, ., .
;
```

Matrix_Id | Column_Order | Row_Order | IsZero | IsRowResidual | IsColumnResidual | FieldName | FieldSourceTable |

1 | 1 | 1 | FALSE | TRUE | FALSE | ||

1 | 1 | 2 | FALSE | FALSE | FALSE | XYZ | table1 |

1 | 1 | 3 | FALSE | FALSE | FALSE | ABC | table1 |

1 | 2 | 1 | TRUE | FALSE | FALSE | ||

1 | 2 | 2 | FALSE | FALSE | FALSE | PQR | table1 |

1 | 2 | 3 | FALSE | FALSE | FALSE | MNO | table1 |

1 | 3 | 1 | FALSE | FALSE | FALSE | AB | table1 |

1 | 3 | 2 | FALSE | FALSE | FALSE | PQ | table1 |

1 | 3 | 3 | FALSE | TRUE | FALSE |

"table 1" is the actual data containing the values and references from earlier 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;
```

Country | Year | method | Segment | ABC | XYZ | PQR | MNO | AB | PQ |

France | 2017 | ABC | Retail | 0.2 | 0.5 | 0.4 | 0.3 | 0.6 | 0.1 |

France | 2018 | XYZ | Corporate | 0.1 | 0.5 | 0.4 | 0.2 | 0.6 | 0.2 |

How do I create matrices with these rules for each row (each key set) in table 1? For example, matrix for row 1 of "table1" would be:

(1-ab) | 0 | ab |

xyz | pqr | pq |

abc | mno | (1-abc-mno) |

therefore:

0.4 | 0 | 0.6 |

0.5 | 0.4 | 0.1 |

0.1 | 0.3 | 0.6 |

I have added the excel file "param_table" which contains the references (the column names) and if it is zero or row residual. Also added the "table1" file which contains the actual values and for each row of "table1" we should have a matrix based on the rules mentioned in param_table.

Thanks!

Accepted Solutions

Solution

01-02-2018
09:40 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ss59

12-26-2017 11:29 AM

I'm not going to argue with you, but there is a difference between including excel files and putting data in a DATA step. Many people cannot download excel files onto their work computers; their company forbids it because of fear of viruses. What you should do is to create the SAS data set on your computer, then convert your SAS data set into a DATA step and post the DATA step.

The following SAS/IML program should answer your questions.

```
proc iml;
use have; /* parameterization table */
read all var {Column_Order Row_Order FieldName};
close;
idx = loc(FieldName ^= " ");
refNames = FieldName[idx];
rows = Row_Order[idx];
cols = Column_Order[idx];
print rows cols refNames;
use Table1;
read all var refNames into Y;
close;
do i = 1 to nrow(Y);
X = j(3,3,0);
X[idx] = Y[i,];
print X;
end;
```

To generalize to more than one MATRIX_ID value, you can use the UNIQUE-LOC technique to iterate over all values of the MATRIX_ID variable.

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ss59

12-26-2017 07:11 AM

Please post your data in the form of a DATA step. We are happy to answer your questions, but you need to make it easier to read your sample data in SAS. If you don't know how to read the data, please ask that question in the Base SAS DATA step community.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ss59

12-26-2017 07:20 AM

Your expected answer does not seem to match your question:

1. The [1,1[ and [3,3] are not specified in the parameterization table, yet you say you expect 0.2 and 0.3 for those values.

2. The [2,2] cell is specified as 0.4 in the parameterization table, yet you say you expect 0.35 for that value.

With the information you've provided, I get

X | ||
---|---|---|

0 | 0.5 | 0.2 |

0 | 0.4 | 0.3 |

0.6 | 0.1 | 0 |

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Rick_SAS

12-26-2017 07:25 AM - edited 12-26-2017 10:09 AM

Yes @Rick_SAS, you are correct, I created the matrix wrongly. I have modified the post to correct my error. Basically for row order 2 and column order 1, the value is in "XYZ", which is 0.5 in row 1 of "table1". so (2,1)th element of the matrix would be 0.5 and so on.

Also I have attached the sample excel files so that one doesn't have to create the data files themselves, it can be imported using this:

```
PROC IMPORT OUT= WORK.param_table
DATAFILE= "your path"
DBMS=XLSX;
sheet="sheet1";
RUN;
PROC IMPORT OUT= WORK.table1
DATAFILE= "your path"
DBMS=XLSX;
sheet="sheet2";
RUN;
```

Also please find the excels attached again.

Solution

01-02-2018
09:40 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ss59

12-26-2017 11:29 AM

I'm not going to argue with you, but there is a difference between including excel files and putting data in a DATA step. Many people cannot download excel files onto their work computers; their company forbids it because of fear of viruses. What you should do is to create the SAS data set on your computer, then convert your SAS data set into a DATA step and post the DATA step.

The following SAS/IML program should answer your questions.

```
proc iml;
use have; /* parameterization table */
read all var {Column_Order Row_Order FieldName};
close;
idx = loc(FieldName ^= " ");
refNames = FieldName[idx];
rows = Row_Order[idx];
cols = Column_Order[idx];
print rows cols refNames;
use Table1;
read all var refNames into Y;
close;
do i = 1 to nrow(Y);
X = j(3,3,0);
X[idx] = Y[i,];
print X;
end;
```

To generalize to more than one MATRIX_ID value, you can use the UNIQUE-LOC technique to iterate over all values of the MATRIX_ID variable.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Rick_SAS

12-26-2017 02:46 PM

Thanks @Rick_SAS. That works for reading the names from the file. However, I'm not being able to do the residual bit (assign 1 - sum of row to the elements as mentioned in the parametrisation table).

Please find my code with data steps:

```
data param_table;
infile datalines dsd;
length FieldName $20 FieldSourceTable $20;
input Matrix_Id Column_Order Row_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, ., .
;
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;
proc iml;
/*varNames = {"Matrix_ID" "Column_Order" "Row_Order" "FieldName" "IsRowResidual" "IsColumnResidual" "FieldSourceTable"};*/
use param_table; /* parameterization table */
read all var _all_;
close;
idx = loc(FieldName ^= " ");
rowsumx = loc(IsRowResidual=1);
refNames = FieldName[idx];
rows = Row_Order[idx];
cols = Column_Order[idx];
row_res = Row_Order[rowsumx];
col_res = Column_Order[rowsumx];
create residual var {row_res col_res};
append;
use residual;
read all var _all_ into res_mat;
close;
a = res_mat[,1];
b = res_mat[,2];
print idx,rows cols refNames;
use Table1;
read all var refNames into Y;
close;
PRINT Y,
do i = 1 to nrow(Y);
X = j(3,3,0);
X[idx] = Y[1,];
rowsum = X[,+];
temp = J(3,1)- X[,+];
** X[a,b] = temp[a,1]; /**----------- trying to assign the (a,b) th element to be (1 - sum of all the elements in ath row) **/
end;
print X;
end;
```

Appreciate your help!

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ss59

12-28-2017 07:15 AM

So if a diagonal element is zero, you want to replace it by (1 - sum(row))? If so, use

```
do i = 1 to nrow(Y);
X = j(3,3,0);
X[idx] = Y[i,];
do j = 1 to 3;
if X[j,j]=0 then X[j,j] = 1 - sum(X[j,]);
end;
print X;
end;
```