## How to contrast two matrices?

Solved
Frequent Contributor
Posts: 75

# 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
Posts: 5,529

## Re: How to contrast two matrices?

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=_ 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

All Replies
Solution
‎10-14-2013 01:22 AM
Posts: 5,529

## Re: How to contrast two matrices?

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=_ 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.

Posts: 5,529

## Re: How to contrast two matrices?

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

PG
Posts: 3,852

## Re: How to contrast two matrices?

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.