Hello,
I need to create an edge list from a table that includes the affiliation of each doctor to up to 5 hospitals. Data looks like below:
Obs
doctor
hosp1
hosp2
hosp3
hosp4
hosp5
1
1003000522
100072
.
2
1003002106
100072
.
3
1003004870
.
.
4
1003005810
100128
.
5
1003006552
100121
100157
.
6
1003008533
100018
100012
100244
.
Doctors and hospitals are all identified by unique codes. In the example above, I have 6 doctors and 7 hospitals (100018, 100012, 100244, 100121, 100157, 100128, and 100072) . Note that doctor in row 3 are not affiliated with any hospitals.
I need to create a new table, with 3 columns. First and second column should show pairs of hospitals and the third column show show the number of doctors that are shared between them. In this example, as shown in row 5, hospitals 100121 and 100157 share 1 physician with each other, also, as shown in row 6, hospitals 100018, 10012 and 100244 each share one physician with each other, therefore the resulting data set should look like this, also please note that in the output, where both hospitals in column 1 and 2 are eqaul, the value in column 3 shows the total number of physicians that are affiliated with that hospital. For example, hospital code 100072 has 2 physicians affiliated with it (1003000522 and 1003002106)
Hosp1 Hosp2 #Shared_Docs
100072 100072 2
100128 100128 1
100121 100121 1
100121 100157 1
100018 100018 1
100018 100012 1
100018 100244 1
100012 100012 1
100012 100244 1
100244 100244 1
... View more