PROC SQL JOIN ISSUE

[noetsi]

I posted this last week, but it seems to have vanished. I am baffled by the following error. I did this with a right join, the data I really want is in the right table, and I get no data at all. I do an inner join and I get all the data I want. I can't understand why an inner join would work here and a right join would not. A right join should pull everything from the right table (where there is data if that is a question) and what joins between the table. It should have the same or more data not less.

This is the code that worked.

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;

The code that did not work is

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

right join

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

where cs.extractdate = '30Apr2017'd

order by t1.customerid;

run;

[Kurt_Bremser]

The right join can't give you more observations than the inner join because of the where condition. The condition can only be true if a matching observation from the "left" dataset with a correct date is found.

If you actually get zero records, post the complete log of the step.