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-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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