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?

Answer 1

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;