WHERE Condition Advice

[buechler66]

Hi. I need to merge these two tables by IMB_CODE and spm_calc_batch_date, but the problem is that spm_calc_batch_date IS NOT always the same on both tables for the same IMB_CODE. This being the case I can't join like this:

WHERE a.imb_code = b.imb_code

     AND a.spm_calc_batch_date = b.spm_calc_batch_date.

I still need to match based on IMB_CODE, but only when they have spm_calc_batch_dates are within 30 days of one another. Is this possible?

I'm not sure how to write this type of conditional WHERE condition. Can anyone advise me on how to alter my WHERE statement below to account for this 30 day variance in spm_calc_batch_date variable values?  (Note: IMB_CODE and spm_calc_batch_date on both tables will never be missing).

I would greatly appreciate any help!

proc sql;
create table QueryData&ZIP5 as
select a.actual_dlvry_date,
     a.imb_code length = 31,
     a.imb_dlvry_zip_5,
     CASE
           WHEN (A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL ) THEN trim("ACTUAL DELIVERY DATE MISSING IN IV ")
           WHEN (A.ACTUAL_DLVRY_DATE > B.ACTUAL_DLVRY_DATE ) THEN trim("ACTUAL DELIVERY DATE LATER IN IV ")
     ELSE ' '
     END as RULE_NM,
     CASE
           WHEN (A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL ) THEN 1.0
           WHEN (A.ACTUAL_DLVRY_DATE > B.ACTUAL_DLVRY_DATE ) THEN 1.5
     ELSE .
     END as Rule_Order
from mp_bi as a, mp_iv b
where a.imb_code=b.imb_code;
quit;                             

[Patrick]

I guess then you should only match over codes within a time range of 30 days.

on a.imb_code=b.imb_code and abs(a.spm_calc_batch_date-b.spm_calc_batch_date)<=30

[buechler66]

Ok, I will try this. Ty.

[PGStats]

Or

...
from 
	mp_bi as a inner join
	mp_iv as b
		on a.imb_code = b.imb_code and
			 a.spm_calc_batch_date between b.spm_calc_batch_date-30 and b.spm_calc_batch_date+30;
quit;