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

[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.

[darrylovia]

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;

[richard_hu2003]

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.

[SPR]

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

[Ksharp]

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