SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

creating pairs of observations that has common values

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

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
a week ago
Respected Advisor
Posts: 4,973

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

View solution in original post


All Replies
Super User
Posts: 20,224

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
a week ago
Respected Advisor
Posts: 4,973

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
  • 101 views
  • 0 likes
  • 3 in conversation