Hi,
I have a table(in sas server) for a job needs two teams to work on it.
TABLE A, Jobs_All
TEAM_1 | TEAM_2 | JOB_LIST | J_DT | J_ID |
ELLA | JACK | |||
ALICE | JIMMY | JOB1 | 12-01-22 | J1 |
ALICE | JIMMY | JOB2 | 13-01-22 | J2 |
CINDY | BOB | JOB3 | 14-01-22 | J3 |
CINDY | BOB | JOB4 | 14-01-22 | J4 |
AMBER | DANIEL | JOB5 | 16-01-22 | J5 |
AMBER | DANIEL | JOB6 | 17-01-22 | J6 |
KITTY | ARRON |
A manage doesn't know who is team_1 or team_2, he wanted to know who did the job in the past, for the list below
TABLE B, query_list
TEAM_1 | TEAM_2 |
JACK | |
ELLA | JACK |
ALICE | |
CINDY | |
BOB | |
CINDY | BOB |
KITTY | ARRON |
CINDY | JIMMY |
The expected output should look like this:
TEAM_1 | TEAM_2 | JOB_LIST | J_DT | J_ID |
JACK | ||||
ELLA | JACK | |||
ALICE | JOB1 | 12-01-22 | J1 | |
ALICE | JOB2 | 13-01-22 | J2 | |
CINDY | JOB3 | 14-01-22 | J3 | |
JOB4 | 14-01-22 | J4 | ||
BOB | JOB3 | 14-01-22 | J3 | |
JOB4 | 14-01-22 | J4 | ||
CINDY | BOB | JOB3 | 14-01-22 | J3 |
CINDY | BOB | JOB4 | 14-01-22 | J4 |
KITTY | ARRON | |||
CINDY | JIMMY |
I did the
left join B.TEAM_1=A.TEAM_1 ->TEAM_1_JOB (table C)
left join B.TEAM_2=A.TEAM_2 ->TEAM_2_JOB (table D)
full outer join on C.J_ID=D.J_ID
what I get is like below
TEAM_1 | TEAM_2 | JOB_LIST | J_DT | J_ID |
ELLA | JACK | |||
ALICE | JIMMY | JOB1 | 12-01-22 | J1 |
ALICE | JIMMY | JOB2 | 13-01-22 | J2 |
CINDY | BOB | JOB3 | 14-01-22 | J3 |
CINDY | BOB | JOB4 | 14-01-22 | J4 |
KITTY | ARRON |
How can I get the expected output table which can take either one or two input columns as a join condition, or other way to do that?
Kindly advise
Thanks
The logic of that expected output is difficult to understand for me...why the JOB4 appears with empty TEAM1 and TEAM2, but the JOB3 appears always with at least one team member? The situation seems pretty symmetrical for both in the original tables...
Sorry, the logic was wrong. I will figure out in a bit. @AlexBennasar , thank you!
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: