SAS 9.3: Changing the shape of matrix with missing rows

Question

I have a data with the following formats,

P1 P2 Score  
A  A  1  
A  B  2  
A  C  5  
B  B  4   
B  C  9  
C  A  3  
C  B  6

which I want to make them into a 3*3 matrix, for those missing rows are zeros.

I've googled and found this
http://blogs.sas.com/content/iml/2012/01/16/reading-all-variables-into-a-matrix.html
but I don't know if I have missing rows, how can I do this?

score 1 · Accepted Answer · answered Jan 24 '17 at 13:23

If you want to create a SAS data set that represents a 3x3 matrix you can do that from your data using PROC TRANSPOSE. To fill in the missing combinations of P1 and P2 that can be done many ways. Your data is suited to using the features of PROC SUMMARY COMPLETETYPES to fill the zeros.

data p;
   input (P1 P2)(:$1.) Score;
   cards;
A A 1
A B 2
A C 5
B B 4
B C 9
C A 3
C B 6 
;;;;
   run;
proc summary data=p completetypes nway;
   class p:;
   freq score;
   output out=filled(drop=_type_ rename=(_freq_=Score));
   run;
proc print;
   run;
proc transpose out=M3x3;
   by P1;
   id P2;
   var score;
   run;
proc print;
   run;

Thank you so much, I also make it work to chage p: into p1 and p2 — user131605, Jan 24 '17 at 16:24
@user131605 `p:` should resolve to `p1 p2`; `:` is a wildcard. — Joe, Jan 24 '17 at 17:02

score 0 · Answer 2 · answered Jan 24 '17 at 17:54

I probably would do this in base SAS too, but it's also quite doable in IML. I use the unique-loc method to convert your ABC into numeric values, and then use the FULL function to square the matrix:

data have;
input P1 $ P2 $ Score;
datalines;
A  A  1  
A  B  2  
A  C  5  
B  B  4   
B  C  9  
C  A  3  
C  B  6  
;;;;
run;
proc iml;

use have;
read all var _NUM_ into have_mat;
read all var _CHAR_ into p_mat;

p1_unique = unique(p_mat[,1]);  *get unique values of p1;
p2_unique = unique(p_mat[,2]);  *get unique values of p2;

num_mat = j(nrow(p_mat),3,0);   *generate empty matrix to populate;

do i = 1 to ncol(p1_unique);    *code the p1 values;
  idx = loc(p_mat[,1] = p1_unique[i]);
  num_mat[idx,2] = i;           *sparse matrix format requires 2nd col be row values;
end;

do i = 1 to ncol(p2_unique);    *code the p2 values;
  idx = loc(p_mat[,2] = p2_unique[i]);
  num_mat[idx,3] = i;           *sparse matrix format requires 3rd col be col values;
end;

  num_mat[,1] = have_mat;       *and first col is the values for the matrix itself;

final_mat = full(num_mat);      *FULL() function creates a square matrix and fills with 0s;
print final_mat;
quit;

score 0 · Answer 3 · answered Jan 25 '17 at 16:40

The easiest solution is to use the SPARSE option in PROC FREQ to build the dense matrix. You can then read it the data into SAS/IML and reshape it to make it square. Using 'p' as the name of the data set (as in the other answers):

proc freq data=p noprint;
tables p1*p2 /sparse out=P2;  /* P2 is dense */
weight score;
run;

proc iml;
use p2;  read all var {Count};  close p2;
x = shape(count, sqrt(nrow(Count)));
print x;

SAS 9.3: Changing the shape of matrix with missing rows

3 Answers3