Error in Join transformation

[Babloo]

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.

[Kurt_Bremser]

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.

[Babloo]

I'm unable to understand the solution for the issue. Could you please tell
me what should I do to resolve the issue?

[Kurt_Bremser]

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

[Patrick · Accepted solution]

@Babloo

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.