Where condition

[Scott86]

Hi everyone,

I looking at doing a left join with two tables. I have a data set 'X' that I want to left join to another data set where a trading group ID matches to data set 'Y' and the date modified from table 'X' is >= than start_date in table 'Y' and less than end_date in table 'Y' or end_date is blank.

I hope the above makes sense. Below is an attempt at a portion of the code to show you what I'm trying to do:

on a.trading_group_id = b.trading_group_id and a.date_modified >= b.start_date and a.date_modified < end_date or end_date = '';

Not to sure on how to do this. Maybe a where data step first to order by dates. 

Any help is appreciated.

Thanks

[Reeza]

For this issue, I'll usually recode the blank end dates and make them 2099.
Then you can use the following, which is easier to follow IMO.

And date between start_date and end_date

One thing to make sure when you're dealing with multiple conditions is to enclose them with parentheses especially if you're mixing AND/OR logic.

[Scott86]

Thanks Reeza that is a good tip.

Where should I be adding parentheses?

Please rewrite the code below with the parentheses:

on a.trading_group_id = b.trading_group_id and a.date_modified >= b.start_date and a.date_modified < b.end_date or end_date = '';

Thanks mate

[Reeza]

---

@Scott86 wrote:

Please rewrite the code below with the parentheses:

---

I don't know what logic you want....you do 🙂

Make a small data set and test it out, which you should be doing anyways really.

You can work with subsets of your data by using the OBS= option.

option obs=1000;

[Tom]

Is your END_DATE variable a character varaible? If so then how are you able to make a greater than or less than comparison between two values?  If your variable is coded to look like 'YYYY-MM-DD' then relative order will be preserved when comparing character versions of dates, but if you are using 'MM-DD-YYYY' or 'DD-MM-YYYY' or even 'ddMONyyyy' format then the order as character strings will not match the order as dates.

I assume you want to group your comparisons in this way so that you do not include every record with a missing end_date. Note the MISSING() function will work with both numeric and character variables.

on (a.trading_group_id = b.trading_group_id)
 and ( (a.date_modified >= b.start_date
         and (a.date_modified < b.end_date or missing(end_date))
       )
     )
;

If your varaibles are actual date values then perhaps you can simplify using BETWEEN and COALESCE()? Use the -1 to make the upper bound comparison of the BETWEEN match the < used in your original query instead of <= that is normally used.

on a.trading_group_id = b.trading_group_id
 and a.date_modified between b.start_date and coalesce(b.end_date,mdy(2099,1,1))-1