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;
Post the full logs.
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.
You changed type of JOIN from INNER to RIGHT.
If the date in test3 is not the required one, you'll get no obs.
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 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.