topic Re: Joining on different columns in same table. in SAS Programming

Joining on different columns in same table.

india2016 — Mon, 27 Nov 2017 09:34:48 GMT

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.

Re: Joining on different columns in same table.

RW9 — Mon, 27 Nov 2017 09:42:37 GMT

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;

Re: Joining on different columns in same table.

Kurt_Bremser — Mon, 27 Nov 2017 09:52:21 GMT

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

Re: Joining on different columns in same table.

india2016 — Mon, 27 Nov 2017 09:54:45 GMT

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?