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;
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.