Proc SQL- Joining 5 tables

[Oalmend]

I need to join variables from 5 tables, they all have Key ID in common (STATEID).  The code below is not working.  See sas log also.

PROC SQL;
CREATE TABLE PROBLEMSPN AS
SELECT C.*,
W.ST,
R.REASONTEXT,
R.SPECIES,
S.DATE_TO_CDC,
S.SPECIES,
SR.DATERCVD
FROM cms_st2 C,
SPN.SPNWGS W,
SPN.REASONS R,
SPN.SHIPMENTS S,
STAR.STARLIMS SR
WHERE C.STATEID = W.STATEID AND
C.STATEID = R.REASONTEXT AND 
C.STATEID = S.SHIPMENTS AND 
C.STATEID = SR.STARLIMS;
QUIT;

188 PROC SQL;

189 CREATE TABLE PROBLEMSPN AS

190 SELECT C.*,

191 W.ST,

192 R.REASONTEXT,

193 R.SPECIES,

194 S.DATE_TO_CDC,

195 S.SPECIES,

196 SR.DATERCVD

197 FROM cms_st2 C,

198 SPN.SPNWGS W,

199 SPN.REASONS R,

200 SPN.SHIPMENTS S,

201 STAR.STARLIMS SR

202 WHERE C.STATEID = W.STATEID AND

203 C.STATEID = R.REASONTEXT AND

204 C.STATEID = S.SHIPMENTS AND

205 C.STATEID = SR.STARLIMS;

ERROR: Column SHIPMENTS could not be found in the table/view identified with the correlation

name S.

ERROR: Column STARLIMS could not be found in the table/view identified with the correlation

name SR.

206 QUIT;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.20 seconds

cpu time 0.04 seconds

[Astounding · Accepted solution]

It looks like someone went home for the day and never really finished editing the program.  Look at all the conditions in the WHERE clause.  Do you really expect STATEID from one data set will be equal to REASONTEXT from another data set? 

[Oalmend]

Yes, this was the problem, I guess I was sleeping at the end.  I fixed that and it works, thanks!

[ballardw]

You are asking for variables that don't exist from your referenced data sets. You need to double check the names of the variables in SPN.SHIPMENTS  and STAR.STARLIMS .

A guess is that the variable names don't end in S, Shipment and StarLim instead of Shipments and Starlims.