Hello,
I have a single column dataset, which I would like to transform into 3x3 matrix.
In my dataset p1 is the column which includes the variables and p2, p3 are just empty colums
p1 p2 p3
71 . .
122 . .
167 . .
122 . .
194 . .
266 . .
167 . .
266 . .
365 . .
So, I would like to transform this dataset into 3x3 matrix like this
p1 p2 p3
77 122 167
122 294 266
167 266 365
In other words, I would like to select three set of rows 1:3, 3:6 and 6:9 and form a matrix.
A data step solution:
data have2;
set have;
retain counter 0;
if mod(_n_, 3) = 1 then counter+1;
run;
proc transpose data=have2 out=want prefix=P;
by counter;
var p1;
run;
data have;
input p1 p2 p3;
datalines;
71 . .
122 . .
167 . .
122 . .
194 . .
266 . .
167 . .
266 . .
365 . .
;
proc sql noprint;
select name
into :list separated by ' '
from dictionary.columns
where libname eq "WORK"
and memname eq "HAVE"
;
quit;
%put &=list;
proc iml;
use have (keep=p1);
read all into p;
w=shape(p,3,3);
print p,w;
create want from w[colname={&list}];
append from w;
close;
quit;
proc print data=want;
run;
I would bet you really don't want to transform your data like that.
Perhaps you could tell us a little more about the problem you are trying to solve.
Edit:
If you don't understand @ghosh 's solution or @Reeza 's solution you could try:
data have;
input p1;
datalines;
71
122
167
122
194
166
167
266
365
;
run;
data one;
set have (firstobs=1 obs=3);
rownum=_n_;
run;
data two;
set have (firstobs=4 obs=6);
rownum=_n_;
run;
data three;
set have (firstobs=7 obs=9);
rownum=_n_;
run;
proc sql;
create table want as
select one.p1 as p1, two.p1 as p2, three.p1 as p3
from one inner join two on one.rownum = two.rownum
inner join three on one.rownum = three.rownum;
quit;
p1 p2 p3
71 122 167
122 194 266
167 166 365
A data step solution:
data have2;
set have;
retain counter 0;
if mod(_n_, 3) = 1 then counter+1;
run;
proc transpose data=have2 out=want prefix=P;
by counter;
var p1;
run;
Simple enough with an array. But the input variable name should not be one of the target variable names, that just complicates things.
data have;
input X;
datalines;
71
122
167
122
194
166
167
266
365
;
So read 3 observations at a time and insert them into the proper variable using an index into an array.
data want;
array p p1-p3;
do column=1 to 3 until(eof);
set have end=eof;
p[column]=x;
end;
drop column x;
run;
If you are actually reading the data from a text file then just read it originally directly into the array.
data want;
input p1-p3;
datalines;
71
122
167
122
194
166
167
266
365
;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.