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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.