ERROR: Correlated reference to column MEMRECNO is not contained within a subquery.

[req41273]

1                                                          The SAS System                                11:24 Friday, June 21, 2019

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTNAME='';
7          %LET _SASPROGRAMFILE=;
8          
9          ODS _ALL_ CLOSE;
10         OPTIONS DEV=ACTIVEX;
11         GOPTIONS XPIXELS=0 YPIXELS=0;
12         FILENAME EGSR TEMP;
13         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
14             STYLE=HtmlBlue
15             STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SAS%20EG%209.4.1/x86/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
16             NOGTITLE
17             NOGFOOTNOTE
18             GPATH=&sasworklocation
19             ENCODING=UTF8
20             options(rolap="on")
21         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22         
23         GOPTIONS ACCESSIBLE;


24         proc sql noprint /*INOBS=100*/;
25         create table NED_Members as
26         
27         SELECT DISTINCT
28           MM.SEQ_MEMB_ID as MemberID
29         , MM.LAST_NAME as MemberLastName
30         , MM.FIRST_NAME as MemberFirstName
31         , MM.DATE_OF_BIRTH as MemberDOB
32         , MM.GENDER as M_F
33         , MM.MEDICAID_NO as MedicaidID
34         , MM.ETHNICITY_CODE
35         , MM.USER_DATE_7
36         , meh.line_of_business as LOB
37         , meh.SEQ_PROV_ID
38         , meh.IPA_ID
39         , MA.ADDRESS_LINE_1 AS ADDRESS1
40         , MA.ADDRESS_LINE_2 AS ADDRESS2
41         , MA.CITY AS CITY
42         , MA.STATE AS STATE
43         , MA.ZIP_CODE AS ZIP
44         , MA.COUNTY AS COUNTY
45         , MA.HOME_PHONE_NUMBER AS NED_HOMEPHONE
46         , MEH.EFFECTIVE_DATE
47         , MEH.TERM_DATE
48         , MEH.PLAN_CODE
49         , PM.PROVIDER_ID
50         ,PM.LAST_NAME AS PROVIDER_LAST_NAME
51         ,PM.FIRST_NAME AS PROVIDER_FIRST_NAME
52         ,PM.PROVIDER_TYPE
53         ,PM.LICENSE
54         ,PA.COUNTY AS PROV_COUNTY
55         ,PA.ADDR_CATEGORY_CODE
2                                                          The SAS System                                11:24 Friday, June 21, 2019

56         ,MI.IDNUMBER AS SUBSCRIBER_ID
57         ,MP.ATTRRECNO AS ATTRRECNO
58         ,MP.RECSTAT AS RECSTAT
59         ,MP.MEMRECNO AS MEMRECNO
60         ,MP.PHNUMBER AS NEHII_PHNUMBER
61         ,MAD.STLINE1 AS NEHII_STREET_ADDRESS1
62         ,MAD.CITY AS NEHII_CITY
63         ,MAD.STATE AS NEHII_STATE
64         ,MAD.COUNTY AS NEHII_COUNTY
65         ,MAD.ZIPCODE AS NEHII_ZIPCODE
66         
67         
68         FROM
69         HSDREPT.MEMBER_ADDRESS as MA
70         inner join HSDREPT.MEMBER_MASTER as MM
71             on MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID
72         inner join HSDREPT.member_elig_history  as meh
73             on MM.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID
74         inner join HSDREPT.PROV_MASTER as PM
75             on PM.SEQ_PROV_ID = MEH.SEQ_PROV_ID
76         inner join HSDREPT.PROV_ADDRESS as PA
77             on PM.SEQ_PROV_ID = PA.SEQ_PROV_ID
78         inner join MDM.MPI_MEMADDRWC as MAD
79             on MAD.MEMRECNO = MI.MEMRECNO
80         inner join MDM.MPI_MEMPHONEWC as MP
81             on MAD.MEMRECNO = MP.MEMRECNO
82         LEFT JOIN MDM.MPI_MEMIDENTWC  AS MI
83             on MM.MEDICAID_NO = MI.IDNUMBER
84         
85         WHERE
86         MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID
87         AND MA.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID
88         AND MEH.LINE_OF_BUSINESS = 'NED'
89         AND MEH.TERM_DATE IS NULL
90         AND MA.ADDRESS_TYPE = 'R5'
91         AND MA.TERM_DATE IS NULL
92         AND MI.MEMRECNO = MP.MEMRECNO  AND MP.ATTRRECNO IN (389,391)
93         AND MP.MEMRECNO = MAD.MEMRECNO AND MAD.ATTRRECNO = 390;
ERROR: Correlated reference to column MEMRECNO is not contained within a subquery.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
94         
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.06 seconds
      cpu time            0.00 seconds
      
95         proc sort data = NED_Members NODUPKEY;

ERROR: File WORK.NED_MEMBERS.DATA does not exist.
96         by MemberID;
97         
98         
99         
100        
101        GOPTIONS NOACCESSIBLE;
102        %LET _CLIENTTASKLABEL=;
103        %LET _CLIENTPROCESSFLOWNAME=;
104        %LET _CLIENTPROJECTPATH=;
3                                                          The SAS System                                11:24 Friday, June 21, 2019

105        %LET _CLIENTPROJECTNAME=;
106        %LET _SASPROGRAMFILE=;
107        
108        ;*';*";*/;quit;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      
108      !                run;
109        ODS _ALL_ CLOSE;
110        
111        
112        QUIT; RUN;
113        

[req41273]

Looking for help on why this error message is generating in my code.

[Kurt_Bremser · Accepted solution]

Why are you repeating the join conditions in the WHERE? That is not necessary, as you already run inner joins.

Your ERROR happens here:

78         inner join MDM.MPI_MEMADDRWC as MAD
79             on MAD.MEMRECNO = MI.MEMRECNO
80         inner join MDM.MPI_MEMPHONEWC as MP
81             on MAD.MEMRECNO = MP.MEMRECNO
82         LEFT JOIN MDM.MPI_MEMIDENTWC  AS MI

You use the reference to MI in line 79, before you introduce it later in line 82.

This is taken from the third result of a Google search for "sas ERROR: Correlated reference to column MEMRECNO is not contained within a subquery."

See Maxim 6.

[req41273]

So, should I move the left join statement before the inner joins?  Suggestion on how to correct this ...thanks.  Also, I'm new to working with joins.

[req41273]

So, should I move the left join statement before the inner joins.   Suggestion on how to correct this ...thanks.  Also, I'm new to working with joins.