topic Re: Proc SQL- Joining 5 tables in SAS Data Management

Proc SQL- Joining 5 tables

Oalmend — Thu, 28 Sep 2017 16:57:55 GMT

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

Re: Proc SQL- Joining 5 tables

Astounding — Thu, 28 Sep 2017 17:18:51 GMT

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?

Re: Proc SQL- Joining 5 tables

ballardw — Thu, 28 Sep 2017 17:19:50 GMT

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.

Re: Proc SQL- Joining 5 tables

Oalmend — Thu, 28 Sep 2017 18:10:13 GMT

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