topic Re: Very Strange join issue for PROC SQL in SAS Programming

Very Strange join issue for PROC SQL

noetsi — Tue, 16 May 2017 23:34:48 GMT

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;

Re: Very Strange join issue for PROC SQL

Reeza — Tue, 16 May 2017 23:47:38 GMT

Post the full logs.

Re: Very Strange join issue for PROC SQL

kiranv_ — Wed, 17 May 2017 00:44:30 GMT

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.

Re: Very Strange join issue for PROC SQL

Shmuel — Wed, 17 May 2017 00:46:57 GMT

You changed type of JOIN from INNER to RIGHT.

If the date in test3 is not the required one, you'll get no obs.