WHERE Condition Advice

buechler66 — Thu, 13 Oct 2016 02:32:45 GMT

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;

Re: WHERE Condition Fuzzy Merge Advice

Patrick — Thu, 13 Oct 2016 02:24:28 GMT

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

Re: WHERE Condition Advice

buechler66 — Thu, 13 Oct 2016 02:41:49 GMT

Ok, I will try this. Ty.

Re: WHERE Condition Advice

PGStats — Thu, 13 Oct 2016 02:50:22 GMT

...
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;

topic Re: WHERE Condition Advice in SAS Programming

WHERE Condition Advice

Re: WHERE Condition Fuzzy Merge Advice

Re: WHERE Condition Advice

Re: WHERE Condition Advice