Joining on different columns in same table.

[india2016]

I have this data 

data KK;
input sub_id complain_id dept_id;
datalines;
101 1 11
102 2 12
104 4 13
105 5 15
107 7 14
108 8 16
109 9 17
;
run;

data PK;
input sub_id dept_id duration;
datalines;
104 13 10
101 11 20
102 12 30
107 14 40
105 19 50
0 14 100
0 13 60
0 15 70
0 11 80
0 12 90
;
run;

and now I want to join these two datasets with two diff conditions.

Condition 1:

If dataset 'PK' contains sub_id=0 then join should on dept_id 

condition 2:

if dataset 'PK' sub_id != 0 then join should on sub_id 

If any solution you have then please share the code for this query.

[RW9]

And what have you tried?  We are not here to do it all for your.  Some starting points:

Condition 1:

If dataset 'PK' contains sub_id=0 then join should on dept_id 

from  KK A
left join (select * from PK where index(put(SUB_ID,best.),"0") > 0) B
on     A.DEPT_ID=B.DEPT_ID;

condition 2:

if dataset 'PK' sub_id != 0 then join should on sub_id 

from  KK A
left join (select * from PK where SUB_ID ne 0) B
on     A.SUB_ID=B.SUB_ID;

[india2016]

Yes, I already tried this code separately(e.g. Split the data and join on different conditions) but now I want the respective 'Duration' in output in single code so I am trying to consolidate in the single code.

My final table output should be like this.

Complain_ID sub_ID dept_id Duration

1                    101         11         20

.                       .             .             .

.                      0              12       90

is it possible in single code?

[Kurt_Bremser]

I'd follow my maxim 29 (when in doubt, use brute force).

- split PK into two datasets, along sub_id = 0 or not; drop sub_id or dept_id, respectively

- sort those two dataset along their remaining id's

- sort kk along dept_id, and merge with the respective subset

- sort result along sub_id, and merge along sub_id with the other subset

- sort result along complain_id to get the original order