prob in left join

[Aman4SAS]

Hi, I am having issue with one of my sql query where i m getting same result using inner join and left join

proc sql;
create table reps_act_details as
select distinct a.mud_id ,a.rep_id ,c.indi_id ,c.evtype ,c.detpos ,c.DETPROD ,c.yrmo ,c.event_date
from reps1 a
left join
dw.sas_indi_calls c
on a.rep_id=c.rep_id
where c.yrmo between 201411 and 201502 and evtype = "C" ;

quit;

here i m getting only common one .

plz suggest

[Kurt_Bremser]

If you have a match for every record of reps1 in dw.sas_indi_calls, left and inner joins produce the same result. Only if some matches are missing from the second dataset will an inner join produce less records.

And if you have more than one match in the second dataset, you will get one record per match in the output dataset, increasing the number of records from the first input dataset.

[Aman4SAS]

i have 431 rep_id in reps1 dataset and 10000 of reps in sas_indi_calls dataset, and i want result only for 431 reps which are available in reps1 dataset. it doesnt matter they are avalible in sas_indi_calls dataset ..

but i m getting 429 rep_id..

plz suggest..

[Kurt_Bremser]

You probably have a repeat of rep_id in reps1, which is canceled out with the distinct.

[Aman4SAS]

no,, that are already unique...

[Kurt_Bremser]

Then it's probably coming from

where c.yrmo between 201411 and 201502 and evtype = "C" ;

[Aman4SAS]

but i need to put these conditions too, is there anyway to get my desired output with these conditions. , when i m using merge then i m getting my desired output,,, so is it possible with proc sql?

[Kurt_Bremser]

Inspect your data. Make subsets and see which subset shows the same effect. Play around with the condition(s) until you get a clue what causes the effect.

Unless you want to post your .sas7bdat files here for testing.

[LinusH]

Having where clause on the right table in a left join can give you what can seem unpredictable results. Have the where in a previous step or in an in line view, depending on what you want to achieve.

[PGStats]

The WHERE clause is applied to the result of the join. When there is no match in the right table, the WHERE condition is always false. As   suggested, you can use an inline query as your right table :

proc sql;

create table reps_act_details as

select distinct a.mud_id , a.rep_id , c.indi_id , c.evtype , c.detpos , c.DETPROD , c.yrmo , c.event_date

from

  reps1 as a left join

  (select * from dw.sas_indi_calls where yrmo between 201411 and 201502 and evtype = "C") as c

on a.rep_id=c.rep_id ;

quit;

That way, the condition is applied before the join is performed.

PG

[morgalr]

You can make a table with only the list of reps you want, this will not have anything other than the reps, then you can do outer joins to the tables you are using with the conditions. This will give you two result tables both containing the reps you need and null data, except rep id's, for missing data.

Since both of the tables have been prequalified, then you can join them and get the result you seek. (yes, ugly and messy way of it.)

[Tom]

Change the WHERE to AND to make the additional condition part of what determines if the value from the right hand table meets the join criteria.