Contributor
Posts: 27

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

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.
Frequent Contributor
Posts: 139

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

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;
Contributor
Posts: 27

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

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.
Super Contributor
Posts: 366

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

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
Super User
Posts: 10,787

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

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
Discussion stats
• 4 replies
• 805 views
• 0 likes
• 4 in conversation