 Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.

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

 From Diogo L Pinheiro To statalist@hsphsun2.harvard.edu Subject st: Creating square matrix with obs counts from 2 non unique id variables Date Tue, 19 Apr 2011 11:47:34 -0400

```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. For example:
```

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:

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, as not every ID number shows up in both variables.. 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!
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/
```