# Creating square matrix with obs counts from 2 non unique id variables

#### Joego

##### New Member
Hello, everyone,
I've been trying to create a square matrix with counts of observations from two non unique ID variables. In this case, one id variable has the code for an organization an individual was and one for where the individual is. That is:

Code:
Observation    ID1         ID2
1.                  10          12
2.                  10          11
3.                  11          12
4.                  11          10
In the example above, observation 1 was at organization #10 at one time and organization #12 at another. Observation 2 was at 10 and then 11. And so on.

From that, I want to create a square matrix that would look something like:

Code:
              10           11           12

10           0              1            1
11           1              0            1
12           0              0            0
So that I could have all the counts of each combination of organizations.

The main problem is getting a square matrix in the end. I can create a non square matrix in this case by using
tab ID2, gen(newID)
collapse (sum) newID, by(ID1)

but the problem is that this won't be a square matrix. In the example above it would be missing a line (since not every ID number present in ID1 is present in ID2, and vice versa).

So how could I get a square matrix from the situation above? I've found commands to create similar matrices, but they require unique IDs and only provide binary results for the existing combinations.

Thanks!
Ps: I also have SPSS available to me, if that would be easier.