topic Re: prob in left join in SAS Programming

prob in left join

Aman4SAS — Wed, 11 Mar 2015 07:10:42 GMT

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

Re: prob in left join

Kurt_Bremser — Wed, 11 Mar 2015 09:08:27 GMT

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.

Re: prob in left join

Aman4SAS — Wed, 11 Mar 2015 10:53:04 GMT

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..

Re: prob in left join

Kurt_Bremser — Wed, 11 Mar 2015 11:03:49 GMT

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

Re: prob in left join

Aman4SAS — Wed, 11 Mar 2015 11:08:17 GMT

no,, that are already unique...

Re: prob in left join

Kurt_Bremser — Wed, 11 Mar 2015 11:16:28 GMT

Then it's probably coming from

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

Re: prob in left join

Aman4SAS — Wed, 11 Mar 2015 11:35:35 GMT

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?

Re: prob in left join

Kurt_Bremser — Wed, 11 Mar 2015 11:43:36 GMT

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.

Re: prob in left join

LinusH — Thu, 12 Mar 2015 20:25:31 GMT

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.

Re: prob in left join

morgalr — Thu, 12 Mar 2015 20:36:18 GMT

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.)

Re: prob in left join

PGStats — Thu, 12 Mar 2015 22:09:55 GMT

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.

Re: prob in left join

Tom — Thu, 12 Mar 2015 23:31:43 GMT

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.