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
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?
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?
Yes, this was the problem, I guess I was sleeping at the end. I fixed that and it works, thanks!
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.