BookmarkSubscribeRSS Feed
richard_hu2003
Calcite | Level 5
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.
4 REPLIES 4
darrylovia
Quartz | Level 8
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
Calcite | Level 5
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
Quartz | Level 8 SPR
Quartz | Level 8
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
Super User
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 3212 views
  • 0 likes
  • 4 in conversation