When I tried to execute the join transformation, I'm receiving the error as mentioned below. Appreciate if someone of you help me understand the likely cause for the issue.
ERROR: Correlated reference to column PRODUCER_SK is not contained within a subquery.
2392 udmtcom.F_PRM_WRITTEN_PREMIUM inner join
2393 udmtcom.D_POL_PRODUCT
2394 on
2395 (
2396 F_PRM_WRITTEN_PREMIUM.PRODUCER_SK = D_PTY_PRODUCER.PRODUCER_SK
2397 and F_PRM_WRITTEN_PREMIUM.PRODUCT_SK = D_POL_PRODUCT.PRODUCT_SK
2398 and F_PRM_WRITTEN_PREMIUM.CUSTOMER_SK = D_PTY_PARTY.PARTY_SK
2399 and F_PRM_WRITTEN_PREMIUM.POLICY_SK = D_POL_POLICY.POLICY_SK
2400 ),
2401 udmtcom.D_PTY_PARTY,
2402 udmtcom.D_POL_POLICY,
2403 udmtcom.D_REF_DATE,
2404 udmtcom.D_PTY_PRODUCER
2405 ;
ERROR: Correlated reference to column PRODUCER_SK is not contained within a subquery.
ERROR: Correlated reference to column PARTY_SK is not contained within a subquery.
ERROR: Correlated reference to column POLICY_SK is not contained within a subquery.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
If in the join properties IMPLICIT is set to NO then you need to formulate the join conditions per source available to the join. You can't just add all the logic to a single JOIN node as you've done it.
In below flow for the first JOIN table CLASS_2 is not available and though you can't add a join condition which references this table. If you do so then you'll get the error you observe and what the SAS Note referenced by @Kurt_Bremser explains.
Alternatively set the JOIN property to IMPLICIT = YES or right click on the JOIN node and select it from the drop down list. Then add a WHERE node and formulate all your join conditions there.
See here: http://support.sas.com/kb/32/646.html
Note that this is the first hit of a google search for "sas ERROR: Correlated reference to column PRODUCER_SK is not contained within a subquery". See Maxim 6.
Inner joins can be more easily written as:
udmtcom.F_PRM_WRITTEN_PREMIUM,
udmtcom.D_POL_PRODUCT,
udmtcom.D_PTY_PARTY,
udmtcom.D_POL_POLICY,
udmtcom.D_REF_DATE,
udmtcom.D_PTY_PRODUCER
where
F_PRM_WRITTEN_PREMIUM.PRODUCER_SK = D_PTY_PRODUCER.PRODUCER_SK
and F_PRM_WRITTEN_PREMIUM.PRODUCT_SK = D_POL_PRODUCT.PRODUCT_SK
and F_PRM_WRITTEN_PREMIUM.CUSTOMER_SK = D_PTY_PARTY.PARTY_SK
and F_PRM_WRITTEN_PREMIUM.POLICY_SK = D_POL_POLICY.POLICY_SK
;
Note that you have not yet specified a correlation for D_REF_DATE
If in the join properties IMPLICIT is set to NO then you need to formulate the join conditions per source available to the join. You can't just add all the logic to a single JOIN node as you've done it.
In below flow for the first JOIN table CLASS_2 is not available and though you can't add a join condition which references this table. If you do so then you'll get the error you observe and what the SAS Note referenced by @Kurt_Bremser explains.
Alternatively set the JOIN property to IMPLICIT = YES or right click on the JOIN node and select it from the drop down list. Then add a WHERE node and formulate all your join conditions there.
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.