Left join with multiple and conditions

[ywon111]

Current codes run fine but multiple rows appear. May be due to the brackets.

proc sql ;

create table Want as

select t1.*,t2.ADATE

from t1

left join t2

on (t1.userid = t2.userid) and

(datepart(t2.ADATE) <= t1.BDATE) and

((t2.XDATE is null or datepart(t2.XDATE) > t1.BDATE));

quit;

Thanks

[Kurt_Bremser]

This depends on the data, so we need to know that.

Please supply examples of your datasets (that show the issue) in usable form (data steps with datalines). Please use the "little running man" button to post SAS code.

[ed_sas_member]

Hi @ywon111 

Please post a sample of your data in regular datalines.

When you look further at the output containing the multiples rows, which condition is not satisfied?

Best,

[ballardw]

Define clearly what is wrong with the result.

Best is with small example data sets.

If your T2 data set has multiple values of userid then it is very likely you would get some "multiple rows". Not so much the multiple conditions or brackets.

Consider this very small example. Note use of data step to provide example data and posting in a code box opened with the </> icon.

data example1;
   input id value;
datalines;
1 5
2 4
3 9
;

data example2;
   input id otherval;
datalines;
1 27
1 18
5 99
;

proc sql;
   create table exampleout as
   select a.*, b.otherval
   from example1 as a
        left join
        example2 as b
        on a.id=b.id
   ;
quit;

This is small enough you can quickly see the "multiple rows" are coming from duplicate ID in the second data set. That is what a Left Join does when there are multiple values in the second set. The exercise for an interested reader is to modify the Example1 data set to have 2 Id values of 1 and see the result as well.

The code boxes will keep the message windows from reformatting text. Also sometimes the main message window can cause issues with copying code from the forum that won't run because of changed positions of characters when reading data.

[DavePrinsloo]

Left Join creates a cartesian product of all records in the left table and matches each record in the right table that have a matching on condition.
So if the right table has 3 records that match the condition, the result for the the row in the left table will be 3 rows.
If you have a condition that does not single row on the left that matches the condition, then then that will match with all rows in right table that match the condition. For example, if you left join sashelp.class with itself on name you will not get duplicates. But if you join on age, then every 12 yera will be matched with very other 12 year old. So if you have 4 12-year olds, then the result will have 16 (4x4) 12-year olds.

[koladiyavitthal]

Try below techniques first and send me sample data.
First>> Use Distinct after Select
Second >> coalesce(t1.userid , t2.userid ) as ID
Third >> Use where Clause and apply that filter.