DATA Step, Macro, Functions and more

Joining on different columns in same table.

Reply
Frequent Contributor
Posts: 79

Joining on different columns in same table.

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.

Super User
Super User
Posts: 9,799

Re: Joining on different columns in same table.

Posted in reply to india2016

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;

 

 

 

 

 

 

 

 

Frequent Contributor
Posts: 79

Re: Joining on different columns in same table.

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?

 

 

 

 

 

Super User
Posts: 10,530

Re: Joining on different columns in same table.

Posted in reply to india2016

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

 

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 3 replies
  • 131 views
  • 0 likes
  • 3 in conversation