Help using Base SAS procedures

PROC SQL JOIN ISSUE

Reply
Occasional Contributor
Posts: 9

PROC SQL JOIN ISSUE

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;

Super User
Posts: 6,936

Re: PROC SQL JOIN ISSUE

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 1 reply
  • 115 views
  • 0 likes
  • 2 in conversation