Very Strange join issue for PROC SQL

[noetsi]

I am completely baffled by this one which is being run in a code window inside Enterprise Guide. There is a great deal of data in work.test3 - I want all customers in this table.

I run this:

Proc SQL;

Create table work.test7 as select distinct cs.cusprofile_An, t1.customerid,

"VR" || substr(cats ("0000000", put(CUSProfile_AN,7.)),length (strip (cats ("0000000", put(CUSProfile_AN,7.))))-6,7) as VRID

from rimsrpt.rptcasesummary cs

inner join

work.test3 t1 on cs.customerid = t1.customerid

where cs.extractdate = '30Apr2017'd

order by t1.customerid;

run;

and I get all but one person in wor.test3 which is what I would expect. But I run the following (which only creates a right join) and I get nothing. I can not figure out how that is possible. There should be more records with the outer join.

Proc SQL;

Create table work.test7b as select distinct cs.cusprofile_An, t1.customerid,

"VR" || substr(cats ("0000000", put(CUSProfile_AN,7.)),length (strip (cats ("0000000", put(CUSProfile_AN,7.))))-6,7) as VRID

from rimsrpt.rptcasesummary cs

right join

work.test3 t1 on cs.customerid = t1.customerid

where cs.extractdate = '30Apr2017'd

order by t1.customerid;

run;

[Reeza]

Post the full logs. 

[kiranv_]

change 'where'  condition to  'and' your results will change.  i had this issues before.  where condition  and 'and' work exactly same in inner join. it has tremendous impact on outer joins. Where condition  is applied first and returning rows are used for your outer jois. Still your results should be similar to inner join. As @Reeza suggests please post some more info.

[Shmuel]

You changed type of JOIN from INNER to RIGHT.

If the date in test3 is not the required one, you'll get no obs.