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;
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.