Transforming single column into matrix

[Jhoony]

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.

[ghosh]

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;

[HB]

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

[Reeza · Accepted solution]

A data step solution:

1. Add a counter variable to indicate which values go in which columns, change the value in the MOD() function to generate matrices of different sizes.
2. Use PROC TRANSPOSE to flip the data, specifying the PREFIX to have the columns named P1-P3

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;

[Tom]

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
;