DATA Step, Macro, Functions and more

prob in left join

Reply
Super Contributor
Posts: 266

prob in left join

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

Super User
Posts: 7,766

Re: prob in left join

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 266

Re: prob in left join

Posted in reply to KurtBremser

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

Super User
Posts: 7,766

Re: prob in left join

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 266

Re: prob in left join

Posted in reply to KurtBremser

no,, that are already unique...

Super User
Posts: 7,766

Re: prob in left join

Then it's probably coming from

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 266

Re: prob in left join

Posted in reply to KurtBremser

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?

Super User
Posts: 7,766

Re: prob in left join

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,426

Re: prob in left join

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.

Data never sleeps
Respected Advisor
Posts: 4,920

Re: prob in left join

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

PG
Contributor
Posts: 27

Re: prob in left join


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

Super User
Super User
Posts: 7,039

Re: prob in left join

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.

Ask a Question
Discussion stats
  • 11 replies
  • 320 views
  • 0 likes
  • 6 in conversation