DATA Step, Macro, Functions and more

WHERE Condition Advice

Reply
Regular Contributor
Posts: 212

WHERE Condition Advice

[ Edited ]

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;                             

 

Respected Advisor
Posts: 4,173

Re: WHERE Condition Fuzzy Merge Advice

Posted in reply to buechler66

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

 

Regular Contributor
Posts: 212

Re: WHERE Condition Advice

Posted in reply to buechler66
Ok, I will try this. Ty.
Respected Advisor
Posts: 4,920

Re: WHERE Condition Advice

Posted in reply to buechler66

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;
PG
Ask a Question
Discussion stats
  • 3 replies
  • 270 views
  • 3 likes
  • 3 in conversation