SAS Adjacency Matrix Creation

Question

I have created this table:

And from this I want to create an adjacency matrix which shows how many employee_id's the tables share. It would look like this (I think): I'm not sure if I'm going about this the correct way. I think I may be doing it wrong. I know that this is probably easier if I have more SAS products but I only have the basic SAS enterprise guide to work with.

I really appreciate the help. Thank you.

Answer 1

Here's another way using PROC CORR that's still better than the solution above. And you don't need to filter - it doesn't matter regarding the variables, you only specify them in the PROC CORR procedure.

data id;
input id:$4. human alien wizard;
cards;
1005 1 1 0
1018 0 0 1
1022 0 0 1
1024 1 0 0
1034 0 1 0
1069 0 1 0
1078 1 0 0
1247 1 1 1
;;;;
run;

ods output sscp=want;
proc corr  data=id  sscp ;
    var human alien wizard;
run;

proc print data=want;
    format _numeric_ 8.;
run;

Results are:

                   Obs    Variable       human       alien      wizard

                   1      human             4           2           1
                   2      alien             2           4           1
                   3      wizard            1           1           3