matrixgroup-bysassas-studio

SAS: turn rows into a matrix


Hi i'm working on a project for summer school and the person teaching likes to work by row. I think it would be beneficial to turn it into a matrix and work with arrays.

The current dataset looks like this:

data longdata;
input var1 var2 Value;
datalines;

rowone colone 1
rowone coltwo 1
rowone colthree 2
rowone colfour 3
rowtwo colone 1
rowtwo coltwo 1
rowtwo colthree 1
rowtwo colfour 2
rowthree colone 3
rowthree coltwo 3
rowthree colthree 3
rowthree colfour 4
rowfour colone 1
rowfour coltwo 3 
rowfour colthree 3
rowfour colfour 3
;
run;

I'd like to turn the above into this, preferably through a by statement:

data matrixdata;
input var1-var4;
datalines;

       colone coltwo colthree colfour
rowone       1      1      2        2
rowtwo       1      1      1        2
rowthree     3      3      3        4
rowfour      1      3      3        3
;
run;

Solution

  • Proc transpose is excellent for transforming categorical data into other layout forms.

    proc transpose data=have out=want;
      by var1 notsorted;
      id var2;
      var value;
    run;
    

    Despite looking like an array the data is not implicitly an array. To be a 4x4 array during a single implicit data step iteration the incoming data set would really want 16 variables per row, say v1-v16, with values 1 1 2 3 1 1 1 2 3 3 3 4 1 3 3 3. Each row would correspond one instance of the array.

    data want2;
      retain v1-v16;
      array v (16) v1-v16;
      set have end=end;
      v(_n_) = value;
      if end;
      keep v:;
    run;
    
    data _null_;
      set want2;
      array m(4,4) v1-v16;
      do row = 1 to dim(m,1);
      do col = 1 to dim(m,2);
        put row= col= m(row,col)=;
      end;
      end;
    run;