Populate a matrix with an existing SAS dataset

[richard_hu2003]

Hi All,

I have the following SAS dataset:

row column element
1 1 0.5
1 3 0.2
2 1 0.4
2 2 0.6
3 2 0.7
3 3 0.1

I want to convert this dataset to a matrix like this:

A=
0.5 0 0.2
0.4 0.6 0
0 0.7 0.1

In other words, variable "row" represents the row number while variable "column" represents the column number for the corresponding "element" value. If row or column information is missing, then that element is set to be zero. How can I do this using PROC IML? Thanks.

[Rick_SAS]

If you know the dimensions of the matrix ahead of time (3 x 3 for your example),
you can just say:

use MyData;
read all var {element};
A = shape(element, 3, 3);

If the number of rows and columns are unknown, then:

read all var {row col element};
A = shape(element, max(row), max(col));

[richard_hu2003]

Hi Rick,

Thanks for your quick reply. Actually, I just revised my question. Some elements are zero if row or column information is missing. Is there any way to handle those zero elements? Thanks.

[Rick_SAS]

Sure. In that case you'll have to know (somehow) the size of the matrix in which you want to store the data. Let's say that you know numRow and numCol.

I haven't tested (or even run) the following statements, so forgive any typos.

If you are using SAS/IML 9.22, you can use the FULL function:
http://support.sas.com/documentation/cdl/en/imlug/63541/HTML/default/viewer.htm#imlug_langref_sect09...

read all var {element row col} into s;
A = full(s);

If you aren't using SAS/IML 9.22, then allocate A and then fill the nonzero elements by using the sub2ind module that I wrote about on my IML blog a few months ago:
http://blogs.sas.com/iml/index.php?/archives/86-Converting-Matrix-Subscripts-to-Indices.html

read all var {element row col};
A = j(numRow, numCol, 0);
idx = sub2ind(numCol, row||col);
A[ idx ] = element;

(You *are* reading my blog, right? 🙂 )