BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
somebody
Lapis Lazuli | Level 10
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
1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

2 REPLIES 2
Reeza
Super User

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

 

PGStats
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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