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.

How is the last column/row determined? – Reeza Dec 21 '16 at 21:31 — Reeza, Dec 21 '16 at 21:31

data _null_ · Answer 1 · 2016-12-22T15:46:41.003

1

I think this is what you want but it does not give the thing you show as answer.

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;

proc corr noprint nocorr sscp out=sscp;
   var human alien wizard;
   run;
proc print;
   run;

edited Dec 22 '16 at 15:46

answered Dec 21 '16 at 21:09

data _null_

8,534
12
14

This doesn't include the last column/row. The ODS table is closer to the desired output, in this case. – Reeza Dec 21 '16 at 21:35
Thanks a lot, I really appreciate your help. I can see there are many ways to do this now. – hope288 Dec 23 '16 at 15:42

score 1 · Accepted Answer · answered Dec 22 '16 at 23:46

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

Thanks! I wouldn't have known to do it this way. – hope288 Dec 23 '16 at 15:41 — hope288, Dec 23 '16 at 15:41

score -1 · Answer 3 · answered Dec 22 '16 at 14:27

-1

I was able to get the answer using this, although it does not include the last cell I wanted (human_alien_wizard):

proc transpose data=FULL_JOIN_ALL3 out=FULL_JOIN_ALL3_v2;
  by employee_id;
  var human_table alien_table wizard_table;
run; 

proc sql;
  create table FULL_JOIN_ALL3_v3 as
  select distinct a._name_ as anm,b._name_ as bnm,
  count(distinct case when a.col1=1 and b.col1=1 then a.employee_id else . end) as smalln
  from FULL_JOIN_ALL3_v2 a, FULL_JOIN_ALL3_v2 b
  where a.employee_id=b.employee_id
  group by anm,bnm
; 

proc tabulate data=FULL_JOIN_ALL3_v3;
 class anm bnm;
 var smalln;
 table anm='',bnm=''*smalln=''*sum=''*f=best3. / rts=5;
run;

answered Dec 22 '16 at 14:27

hope288

725
12
23

You took the long way round. You get the same thing directly from PROC CORR SSCP. – data _null_ Dec 22 '16 at 15:45
Using your method I still have to subset, but yes, your way is shorter. I just have to figure out if I were to create an automatic script, someone may still need to look at the data to subset on the correct rows and columns and it might be a different variable name each time so a macro variable would need to be created for that I believe. Here is the subset code. – hope288 Dec 22 '16 at 16:15
`/*Proc CORR Method*/ proc corr data=FULL_JOIN_ALL3 noprint nocorr sscp out=sscp; run; DATA Adjacency_Matrix (KEEP=Tables human_table alien_table wizard_table); SET sscp (rename=(_name_=Tables)); If (Tables = 'Intercept' or Tables = 'employee_id') THEN DELETE; IF (_TYPE_ = 'SSCP') THEN OUTPUT; RUN; proc print data=Adjacency_Matrix; run;` – hope288 Dec 22 '16 at 16:16
@hope288 If you add the variables to PROC CORR you're only filtering out the intercept and blanks. Notice that you still type out the vars once in your process (first_step) so it's no more automated that the other solution. – Reeza Dec 22 '16 at 23:48

SAS Adjacency Matrix Creation

3 Answers3