topic Re: Create a matrix using a SAS dataset with row and column information in SAS Procedures

Create a matrix using a SAS dataset with row and column information

richard_hu2003 — Wed, 11 May 2011 15:27:22 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: Create a matrix using a SAS dataset with row and column information

darrylovia — Wed, 11 May 2011 15:50:10 GMT

I have a general approach that uses BASE SAS and not PROC IML but this should get what you are looking for.

** Data in a listing structure;
data one;
input row column element;
datalines;
1 1 0.5
1 3 0.2
2 1 0.4
2 2 0.6
3 2 0.7
3 3 0.1
;
run;

** get maximum dimension;
proc sql;
select max(column)
into :c
from one;
quit;
%let c=&c;

%put c=&c;

proc sort data=one;
by row;
run;
** Convert data from listing to matrix structure;
data matrix(keep=x:);
set one;

array x(&c);

do until (last.row);
set one end=eof;
by row;
x(column)=element;
end;

do i=1 to dim(x);
if missing(x(i)) then x(i)=0;
end;
run;

Re: Create a matrix using a SAS dataset with row and column information

richard_hu2003 — Wed, 11 May 2011 16:03:01 GMT

Thanks, darrylovia.

Is there any way to use IML to get the matrix? My dataset is very large so I am concerned with run time if I have to use BASE approach. Thanks.

Re: Create a matrix using a SAS dataset with row and column information

SPR — Wed, 11 May 2011 16:43:54 GMT

Hello Richard,

This is another solution:
[pre]
data one;
input row column element;
datalines;
1 1 0.5
1 3 0.2
2 1 0.4
2 2 0.6
3 2 0.7
3 3 0.1
run;
proc SQL;
select MAX(row) as mr, MAX(Column) as mc into :mr, :mc
from one
;quit;
data v/view=v;
do row=1 to &mr;
do column=1 to &mc;
element=0;
output;
end;
end;
run;
data r;
merge v one;
by row column;
run;
[/pre]
Sincerely,
SPR

Re: Create a matrix using a SAS dataset with row and column information

Ksharp — Thu, 12 May 2011 05:33:48 GMT

I think you need to post it in SAS/IML forum ,if you want some iml solution.

[pre]
data one;
input row column element;
datalines;
1 1 0.5
1 3 0.2
2 1 0.4
2 2 0.6
3 2 0.7
3 3 0.1
;
run;
options missing=0;
data _null_;
set one end=last;
array a{4,4} _temporary_ ;
a{row,column}=element;

if last then do;
do i=1 to 4;
do j=1 to 4;
put a{i,j}= ;
end;
end;
end;
run;

[/pre]

Ksharp