BookmarkSubscribeRSS Feed
noetsi
Obsidian | Level 7

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;

1 REPLY 1
Kurt_Bremser
Super User

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.

sas-innovate-2024.png

📢

ANNOUNCEMENT

The early bird rate has been extended! Register by March 18 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 643 views
  • 0 likes
  • 2 in conversation