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.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.