Hi,
I have two tables which has many-to-many relationships with this TEAM_1 and TEAM_2:
TEAM_1
TEAM_2
TEAM_C
J_DT
J_ID
CINDY
BOB
JOB3
14-01-22
J3
CINDY
BOB
JOB4
14-01-22
J4
TEAM_1
TEAM_2
A_DT
A_ID
B_DT
B_ID
D_DT
D_ID
CINDY
BOB
02-01-22
A2
03-01-22
B1
.
CINDY
BOB
05-01-22
A3
.
.
I want to show the after join they only return unique J_DT, J_ID as follow:
TEAM_1
TEAM_2
A_DT
A_ID
B_DT
B_ID
D_DT
D_ID
TEAM_C
J_DT
J_ID
CINDY
BOB
02-01-22
A2
03-01-22
B1
.
JOB3
14-01-22
J3
CINDY
BOB
05-01-22
A3
.
.
CINDY
BOB
02-01-22
A2
03-01-22
B1
.
JOB4
14-01-22
J4
CINDY
BOB
05-01-22
A3
.
.
I did the left join and come out wrong output like this:
Here is my working code
data have;
input TEAM_1 $ TEAM_2 $ EVENT_NM $ EVENT_DT :mmddyy. EVENT_ID $ ;
format event_dt yymmdd10.;
cards;
ALICE JIMMY A 1-1-2022 A1
CINDY BOB A 1-2-2022 A2
CINDY BOB B 1-3-2022 B1
CINDY BOB A 1-5-2022 A3
ELLA JACK B 1-8-2022 B4
ELLA JACK D 1-9-2022 D1
AMBER DANIEL E 1-10-2022 E1
KITTY ARRON F 1-11-2022 F1
;
data GOT;
input TEAM_1 $ TEAM_2 $ TEAM_C $ J_DT :mmddyy. J_ID $ ;
format J_DT yymmdd10.;
cards;
ALICE JIMMY JOB1 1-12-2022 J1
ALICE JIMMY JOB2 1-13-2022 J2
CINDY BOB JOB3 1-14-2022 J3
CINDY BOB JOB4 1-14-2022 J4
AMBER DANIEL JOB5 1-16-2022 J5
AMBER DANIEL JOB6 1-17-2022 J6
KITTY ARRON JOB7 1-18-2022 J7
;
data groups1;
set have;
by team_1 team_2 notsorted ;
group + (EVENT_NM <= lag(EVENT_NM) );
if first.team_2 then group=1;
run;
proc transpose data=groups1 out=wide1(DROP=_NAME_);
by team_1 team_2 group group notsorted;
id EVENT_NM ;
var EVENT_ID;
run;
proc print data=wide1;
run;
data groups2;
set have;
by team_1 team_2 notsorted ;
group + (EVENT_NM <= lag(EVENT_NM) );
if first.team_2 then group=1;
run;
proc transpose data=groups2 out=wide2(DROP=_NAME_);
by team_1 team_2 group group notsorted;
id EVENT_NM;
var EVENT_dt;
run;
proc print data=wide2;
run;
PROC SQL;
CREATE TABLE EVENT_DT AS
SELECT
TEAM_1,
TEAM_2,
GROUP,
A AS A_DT,
B AS B_DT,
/*C AS C_DT,*/
D AS D_DT
/*E AS E_DT,*/
/*F AS F_DT*/
FROM WIDE2;
QUIT;
PROC SQL;
CREATE TABLE EVENT_ID AS
SELECT
TEAM_1,
TEAM_2,
GROUP,
A AS A_ID,
B AS B_ID,
/*C AS C_ID,*/
D AS D_ID
/*E AS E_ID,*/
/*F AS F_ID*/
FROM WIDE1;
QUIT;
PROC SQL;
CREATE TABLE ID_DT AS
SELECT
A.TEAM_1,
A.TEAM_2,
B.A_DT,
A.A_ID,
B.B_DT,
A.B_ID,
/*B.C_DT,*/
/*A.C_ID,*/
B.D_DT,
A.D_ID
/*B.E_DT,*/
/*A.E_ID,*/
/**/
/*B.F_DT,*/
/*A.F_ID*/
FROM EVENT_ID A FULL OUTER JOIN EVENT_DT B
ON A.TEAM_1=B.TEAM_1 AND A.TEAM_2 = B.TEAM_2 and A.GROUP=B.GROUP;
QUIT;
PROC SQL;
CREATE TABLE ID_DT_JOB AS
SELECT DISTINCT * FROM ID_DT A
LEFT JOIN GOT B
ON A.TEAM_1=B.TEAM_1 AND A.TEAM_2 = B.TEAM_2
ORDER BY B.J_DT;
QUIT;
PROC SQL;
CREATE TABLE ID_DT_JOBt_DST AS
SELECT DISTINCT
A.TEAM_1,
A.TEAM_2,
B.TEAM_C
FROM ID_DT A
LEFT JOIN GOT B
ON A.TEAM_1=B.TEAM_1 AND A.TEAM_2 = B.TEAM_2;
QUIT;
How can I fix this?
Appriciate your help!
... View more
