JHelp:joining multiple Tables with SQL Languages

[CorinneT]

Hi, 

Please, somebody could help me? I don't undersand the LOG. I have no result, because zero rows! Thanks in advance.

Corinne.

Subject: I would like to join 4 tables, see below:

 sq.statepopulation                  sq.regioncode           sq.divisioncode                    sq.statecode

Region (the region code)        RegionCode            DivisionCode                      StateCode

Division (the division code)    RegionName           DivisionName                      StateName

Name (the state code)   

Births3 (an indicator

of state-lvel,

three-year projected births)   

My code is: 

proc sql feedback;
/*create table toto as*/
select s.Births3 format=ddmmyy10.
,r.RegionName
,d.DivisionName
,t.StateCode
from sq.statepopulation as s inner join
sq.regioncode as r
on s.Region=r.RegionCode inner join
sq.divisioncode as d
on s.Division=d.DivisionCode inner join
sq.statecode as t /*** <--------ISSUE THERE!*/
on s.Name=t.StateName;
quit;

My Log says:

OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

68

69 proc sql feedback;

70 /*create table toto as*/

71 select s.Births3 format=ddmmyy10.

72 ,r.RegionName

73 ,d.DivisionName

74 ,t.StateCode

75 from sq.statepopulation as s inner join

76 sq.regioncode as r

77 on s.Region=r.RegionCode inner join

78 sq.divisioncode as d

79 on s.Division=d.DivisionCode inner join

80 sq.statecode as t

81 on s.Name=t.StateName;

NOTE: Statement transforms to:

select S.Births3 format=DDMMYY10.0, R.RegionName, D.DivisionName, T.StateCode

from SQ.STATEPOPULATION S inner join SQ.REGIONCODE R on S.Region = R.RegionCode inner join SQ.DIVISIONCODE D on

S.Division = D.DivisionCode inner join SQ.STATECODE T on S.Name = T.StateName;

NOTE: No rows were selected.

82 quit;

NOTE: PROCEDURE SQL a utilisé (Durée totale du traitement) :

real time 0.01 seconds

user cpu time 0.01 seconds

system cpu time 0.00 seconds

memory 6436.18k

OS Memory 31156.00k

Timestamp 08/10/2021 01:48:53 PM

Step Count 89 Switch Count 0

Page Faults 0

Page Reclaims 342

Page Swaps 0

Voluntary Context Switches 18

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 16

[ballardw · Accepted solution]

Try doing the joins one at a time and examining the results. That should tell you which particular join is causing problems. I suspect you might be assuming the issue is with your T table. You don't show any specific reason, such as VALUES of any variables that makes T the specific issue.

Make sure that the Name and Statename have the same spellling and case. If one has "NEW YORK" and the other has "New York" then there is no match. Watch for things like leading spaces as well.

You might consider left joins instead of inner. That way you can see if anything from the Statepopulation table has matches on any of the values.

[CorinneT]

Thanks, for these tips.
I found my mistake 🙂