Help using Base SAS procedures

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

Reply
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

Posted in reply to richard_hu2003
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=xSmiley Happy;
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

Posted in reply to darrylovia
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
Super Contributor
Posts: 365

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

Posted in reply to richard_hu2003
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,028

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

Posted in reply to richard_hu2003
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
Ask a Question
Discussion stats
  • 4 replies
  • 316 views
  • 0 likes
  • 4 in conversation