Solved
Contributor
Posts: 44

# creating pairs of observations that has common values

``````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``````

Accepted Solutions
Solution
‎12-05-2017 10:51 PM
Posts: 5,234

## Re: creating pairs of observations that has common values

Posted in reply to ducman1611

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;
``````
PG

All Replies
Super User
Posts: 21,936

## Re: creating pairs of observations that has common values

Posted in reply to ducman1611

Try a SQL Cross Join

ducman1611 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``````

Solution
‎12-05-2017 10:51 PM
Posts: 5,234

## Re: creating pairs of observations that has common values

Posted in reply to ducman1611

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;
``````
PG
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
• 2 replies
• 113 views
• 0 likes
• 3 in conversation