topic Re: creating pairs of observations that has common values in SAS Data Management

creating pairs of observations that has common values

somebody — Wed, 06 Dec 2017 00:40:40 GMT

I would like to create pairs of observation that have the same value in a variable. Say if students A, B, C, D are in the same class then I would create some pairs AB,AC, AD, BC,BD, CD. pair with itself (AA, BB, CC) is ok.
My input dataset is:
class	student	var1
1	A	11
1	B	12
1	C	13
1	D	14
2	A	11
2	B	12
2	E	1
 	 	 
Wanted Output is:
class	student1	student2	var1	var2
1	A	A	11	11
1	A	B	11	12
1	A	C	11	13
1	A	D	11	14
1	B	A	12	11
1	B	B	12	12
1	B	C	12	13
1	B	D	12	14
1	C	A	13	11
1	C	B	13	12
1	C	C	13	13
1	C	D	13	14
1	D	A	14	11
1	D	B	14	12
1	D	C	14	13
1	D	D	14	14
2	A	A	11	11
2	A	B	11	12
2	A	E	11	1
2	B	A	12	11
2	B	B	12	12
2	B	E	12	1
2	E	A	1	11
2	E	B	1	12
2	E	E	1	1

Re: creating pairs of observations that has common values

Reeza — Wed, 06 Dec 2017 00:52:50 GMT

Try a SQL Cross Join

@somebody wrote:


My input dataset is:
class	student	var1
1	A	11
1	B	12
1	C	13
1	D	14
2	A	11
2	B	12
2	E	1
 	 	 
Wanted Output is:
class	student1	student2	var1	var2
1	A	A	11	11
1	A	B	11	12
1	A	C	11	13
1	A	D	11	14
1	B	A	12	11
1	B	B	12	12
1	B	C	12	13
1	B	D	12	14
1	C	A	13	11
1	C	B	13	12
1	C	C	13	13
1	C	D	13	14
1	D	A	14	11
1	D	B	14	12
1	D	C	14	13
1	D	D	14	14
2	A	A	11	11
2	A	B	11	12
2	A	E	11	1
2	B	A	12	11
2	B	B	12	12
2	B	E	12	1
2	E	A	1	11
2	E	B	1	12
2	E	E	1	1

Re: creating pairs of observations that has common values

PGStats — Wed, 06 Dec 2017 01:21:16 GMT

A simple SQL join will do:

data have;
input class	student $ var1;
datalines;
1	A	11
1	B	12
1	C	13
1	D	14
2	A	11
2	B	12
2	E	1
;

proc sql;
create table want as
select 
    a.class,
    a.student as student1,
    b.student as student2,
    a.var1,
    b.var1 as var2
from 
    have as a inner join 
    have as b on a.class=b.class;
select * from want;
quit;