topic Populate a matrix with an existing SAS dataset in SAS/IML Software and Matrix Computations

Populate a matrix with an existing SAS dataset

richard_hu2003 — Wed, 11 May 2011 15:26:15 GMT

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.

Re: Populate a matrix with an existing SAS dataset

Rick_SAS — Wed, 11 May 2011 15:32:58 GMT

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

Re: Populate a matrix with an existing SAS dataset

richard_hu2003 — Wed, 11 May 2011 15:48:23 GMT

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.

Re: Populate a matrix with an existing SAS dataset

Rick_SAS — Wed, 11 May 2011 16:40:20 GMT

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_sect098.htm

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? 🙂 )