DATA Step, Macro, Functions and more

Very Strange join issue for PROC SQL

Reply
Occasional Contributor
Posts: 9

Very Strange join issue for PROC SQL

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;

Super User
Posts: 17,912

Re: Very Strange join issue for PROC SQL

Post the full logs. 

 

PROC Star
Posts: 258

Re: Very Strange join issue for PROC SQL

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.

Trusted Advisor
Posts: 1,401

Re: Very Strange join issue for PROC SQL

You changed type of JOIN from INNER to RIGHT.

 

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

Ask a Question
Discussion stats
  • 3 replies
  • 140 views
  • 0 likes
  • 4 in conversation