topic Re: Transforming single column into matrix in SAS Programming

Transforming single column into matrix

Jhoony — Wed, 21 Sep 2022 10:39:17 GMT

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.

Re: Transforming single column into matrix

ghosh — Wed, 21 Sep 2022 21:35:28 GMT

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;

Re: Transforming single column into matrix

HB — Wed, 21 Sep 2022 21:56:27 GMT

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

Re: Transforming single column into matrix

Reeza — Wed, 21 Sep 2022 21:49:18 GMT

A data step solution:

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.
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;

Re: Transforming single column into matrix

Tom — Thu, 22 Sep 2022 04:53:50 GMT

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
;