BookmarkSubscribeRSS Feed
Aman4SAS
Obsidian | Level 7

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

11 REPLIES 11
Kurt_Bremser
Super User

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
Obsidian | Level 7

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

Aman4SAS
Obsidian | Level 7

no,, that are already unique...

Aman4SAS
Obsidian | Level 7

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
Super User

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
Tourmaline | Level 20

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
PGStats
Opal | Level 21

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
morgalr
Obsidian | Level 7


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
Super User Tom
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1227 views
  • 0 likes
  • 6 in conversation