topic Re: Where condition in SAS Enterprise Guide

Where condition

Scott86 — Sat, 02 Sep 2017 23:50:22 GMT

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

Re: Where condition

Reeza — Sun, 03 Sep 2017 01:03:10 GMT

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.

Re: Where condition

Scott86 — Sun, 03 Sep 2017 05:41:46 GMT

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

Re: Where condition

Tom — Sun, 03 Sep 2017 17:41:23 GMT

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

Re: Where condition

Reeza — Mon, 04 Sep 2017 03:31:18 GMT

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