SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Proc SQL- Joining 5 tables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Proc SQL- Joining 5 tables

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


Accepted Solutions
Solution
‎09-28-2017 02:06 PM
Super User
Posts: 6,644

Re: Proc SQL- Joining 5 tables

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? 

View solution in original post


All Replies
Solution
‎09-28-2017 02:06 PM
Super User
Posts: 6,644

Re: Proc SQL- Joining 5 tables

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? 

Occasional Contributor
Posts: 7

Re: Proc SQL- Joining 5 tables

Posted in reply to Astounding

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

Super User
Posts: 13,358

Re: Proc SQL- Joining 5 tables

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 271 views
  • 1 like
  • 3 in conversation