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
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
@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;
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
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.