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;
