How to contrast two matrices?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

How to contrast two matrices?

In my study, school students were asked to name other students they have friendship connections. Data were collected in two waves and may look like this:

Wave I:                                               Wave II:

StudentID     FriendsID                         StudentID     FriendsID

1000               1001                              1000               1002

1000               1002                              1000               1003

1001               1000                              1000               1004

1001               1004                              1002               1000

1002               1004                              1002               1004   

1004               1001                              1003               1000                                           

1004               1002                              1004               1002

                                                         

                                                         

The basic idea is that at wave II, some students leave (ID 1001) and some join (ID 1003). Students can nominate people out of school but we skip that fact for now.

Form these original data, we can create adjacency matrices of friendship with 1s indicating the existence of friendship and 0s otherwise:

Wave I:

          1000     1001     1002     1004            

1000     0          1          1          0       

1001     1          0          0          1       

1002     0          0          0          1       

1004     0          1          1          0       

Wave II:

          1000     1002     1003     1004   

1000     0          1          1          1       

1002     1          0          0          1       

1003     1          0          0          0       

1004     0          1          0          0

Now I want to contrast two matrices (or two original data sets, whichever easier as I don't know) and fill the joiners in matrix I and the leavers in matrix II. That way, they look like:

Wave I:

          1000     1001     1002     1004     1003            

1000     0          1          1          0           10      

1001     1          0          0          1           10

1002     0          0          0          1           10

1004     0          1          1          0           10

1003     10        10         10       10          10

Wave II:

          1000     1002     1003     1004     1001   

1000     0          1          1          1           10      

1002     1          0          0          1           10

1003     1          0          0          0           10

1004     0          1          0          0           10

1001     10        10        10        10          10

So, ID 1003 is added to matrix I and ID 1001 is added to matrix II. Their values are assigned as 10 to indicate no connection with other cases by mechanism (structural zeros). Is there some way to do it?


Accepted Solutions
Solution
‎10-14-2013 01:22 AM
Respected Advisor
Posts: 4,926

Re: How to contrast two matrices?

Posted in reply to NonSleeper

Here is a non-IML way to do the operation you requested from this non-IML Community:

data have;
w = 1;
input sId fId @@;
output;
w = 2;
input sId fId;
output;
datalines;
1000               1001                              1000               1002
1000               1002                              1000               1003
1001               1000                              1000               1004
1001               1004                              1002               1000
1002               1004                              1002               1004   
1004               1001                              1003               1000                                           
1004               1002                              1004               1002
;

proc sql;
create table waves as select unique w from have;
create table iDs as
select sId as id from have union select fId from have;
create table comb as
select w, a.id as sId, b.id as fId
from waves cross join iDs as a cross join iDs as b;
create table wantList as
select
     a.*,
     case
          when not exists (select * from have where w=a.w and sId=a.sId) then .
          when not exists (select * from have where w=a.w and sId=a.fId) then .
          else not missing(b.w)
          end as value
from comb as a natural left join have as b
order by w, sId, fId;
drop table waves, iDs, comb;
quit;

/* If you prefer the Table format */

proc transpose data=wantList out=wantTable(drop=_Smiley Happy prefix=F_;
by w sId;
id fId;
var value;
run;

proc print data=wantList noobs; run;
proc print data=wantTable noobs; run;

Note: the code will work for any number of waves.

PG

PG

View solution in original post


All Replies
Solution
‎10-14-2013 01:22 AM
Respected Advisor
Posts: 4,926

Re: How to contrast two matrices?

Posted in reply to NonSleeper

Here is a non-IML way to do the operation you requested from this non-IML Community:

data have;
w = 1;
input sId fId @@;
output;
w = 2;
input sId fId;
output;
datalines;
1000               1001                              1000               1002
1000               1002                              1000               1003
1001               1000                              1000               1004
1001               1004                              1002               1000
1002               1004                              1002               1004   
1004               1001                              1003               1000                                           
1004               1002                              1004               1002
;

proc sql;
create table waves as select unique w from have;
create table iDs as
select sId as id from have union select fId from have;
create table comb as
select w, a.id as sId, b.id as fId
from waves cross join iDs as a cross join iDs as b;
create table wantList as
select
     a.*,
     case
          when not exists (select * from have where w=a.w and sId=a.sId) then .
          when not exists (select * from have where w=a.w and sId=a.fId) then .
          else not missing(b.w)
          end as value
from comb as a natural left join have as b
order by w, sId, fId;
drop table waves, iDs, comb;
quit;

/* If you prefer the Table format */

proc transpose data=wantList out=wantTable(drop=_Smiley Happy prefix=F_;
by w sId;
id fId;
var value;
run;

proc print data=wantList noobs; run;
proc print data=wantTable noobs; run;

Note: the code will work for any number of waves.

PG

PG
Frequent Contributor
Posts: 75

Re: How to contrast two matrices?

It works in creating matrices. How can I transpose without creating prefix, i.e. keeping the same values? I remove the prefix part out of the statement but then it doesn't give good results.

Respected Advisor
Posts: 4,926

Re: How to contrast two matrices?

Posted in reply to NonSleeper

SAS variable names cannot start with a number or a space character. The underscore is allowed. - PG

PG
Respected Advisor
Posts: 3,799

Re: How to contrast two matrices?

Posted in reply to NonSleeper

You can use VALIDVARNAME=ANY and create variables names that do not follow the V7 rules for SASNames. But then you have to use name literals '1001'n and that can be a bit onerous.

Why don't you just use the IDLABEL statment in PROC TRANSPOSE to create variable LABELS from the numeric values of FLD.


Frequent Contributor
Posts: 75

Re: How to contrast two matrices?

When I try to relax the specifications a little, i.e. allowing for out-of-school nominations (and unequal observations between two waves), the results seem undesirable. Let's say the new data looks like:

datalines;

1000               1001                              1000               1002

1000               1002                              1000               1003

1001               1000                              1000               1004

1001               1004                              1002               1000

1002               1004                              1002               1004   

1004               1001                              1003               1000                                           

1004               1002                              1004               1002

1004               1005

;


This way in wave I, the fId 1005 is considered out of school and does not appear in wave II. Then in wantList, it returns that the value of 1004 to 1005 in wave I is missing. The same thing happens if the pair 1004-1005 is moved to wave II, which looks like the following (the missing pair is there and removed later, to make the 1004-1005 pair on wave II):


datalines;

1000               1001                              1000               1002

1000               1002                              1000               1003

1001               1000                              1000               1004

1001               1004                              1002               1000

1002               1004                              1002               1004   

1004               1001                              1003               1000                                           

1004               1002                              1004               1002

   .                    .                                  1004               1005

;


Can you help modify the program so that it can handle this specification?

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 276 views
  • 1 like
  • 3 in conversation