10-12-2016 08:41 PM - edited 10-12-2016 10:32 PM
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;
10-12-2016 10:24 PM
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
10-12-2016 10:50 PM
... 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;