Inner Join brings all the records that are common in both the tables. You may have multiple records per COMGRP in your COM_CODES table. You may need to bring only one record from the table your joining.
Check this example:
data tableA;
Format Order_date mmddyy10.;
input id item $ Order_date mmddyy10.;
datalines;
1 A 05/24/2018
2 A 05/25/2018
;
run;
data tableB;
Format del_date mmddyy10.;
input id item $ del_date mmddyy10.;
datalines;
1 A 05/30/2018
1 A 05/26/2018
;
run;
PROC SQl;
create table want as
select a.id,a.item,a.Order_date,b.del_date
from tableA a
inner join TableB b on (a.id=b.id and a.item=b.item)
;
quit;
/* Joining tableA with TableB having only one record per group */
PROC SQl;
create table want as
select a.id,a.item,a.Order_date,b.del_date
from tableA a
inner join (select *
from TableB
group by id,item
having max(del_date)=del_date) b on (a.id=b.id and a.item=b.item)
;
quit;
... View more
