https://communities.sas.com/t5/New-SAS-User/where-statements-changed-to-inner-joins/m-p/564295#M11023 <P>So, how would I define it and when?&nbsp; I tried rearranging the join (see code box)</P><PRE><CODE class=" language-sas">FROM HSDREPT.MEMBER_ADDRESS as MA inner join HSDREPT.MEMBER_MASTER as MM on MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID inner join HSDREPT.member_elig_history as meh on MM.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID inner join HSDREPT.PROV_MASTER as PM on PM.SEQ_PROV_ID = MEH.SEQ_PROV_ID inner join HSDREPT.PROV_ADDRESS as PA on PM.SEQ_PROV_ID = PA.SEQ_PROV_ID inner join MDM.MPI_MEMADDRWC as MAD on MAD.MEMRECNO = MP.MEMRECNO inner join MDM.MPI_MEMPHONEWC as MP on MAD.MEMRECNO = MP.MEMRECNO inner join MDM.MPI_MEMIDENTWC as MI on MAD.MEMRECNO = MI.MEMRECNO LEFT JOIN MDM.MPI_MEMIDENTWC AS MID on MM.MEDICAID_NO = MI.IDNUMBER</CODE></PRE> Thu, 06 Jun 2019 20:27:44 GMT req41273 2019-06-06T20:27:44Z https://communities.sas.com/t5/New-SAS-User/where-statements-changed-to-inner-joins/m-p/563946#M10964 <P>I need to do inner joins now of all my where conditions in the code below.&nbsp; This is due to the fact I need to do a left join of tables after all my where inner join conditions are processed.&nbsp;&nbsp;</P><P>Please see my code and let me know how to change this properly using proc sql&nbsp; in my sas program.</P><PRE><CODE class=" language-sas">Proc sql noprint /*INOBS=100*/; Create table NED_MEMBERS_OUTREACH _W_NEHII as SELECT DISTINCT MM.SEQ_MEMB_ID as MemberID , MM.LAST_NAME as MemberLastName , MM.FIRST_NAME as MemberFirstName , MM.DATE_OF_BIRTH as MemberDOB , MM.GENDER as M_F , MM.MEDICAID_NO as MedicaidID , MM.ETHNICITY_CODE , MM.USER_DATE_7 , meh.line_of_business as LOB , meh.SEQ_PROV_ID , meh.IPA_ID , MA.ADDRESS_LINE_1 AS ADDRESS1 , MA.ADDRESS_LINE_2 AS ADDRESS2 , MA.CITY AS CITY , MA.STATE AS STATE , MA.ZIP_CODE AS ZIP , MA.COUNTY AS COUNTY , MA.HOME_PHONE_NUMBER AS HOMEPHONE , MEH.EFFECTIVE_DATE , MEH.TERM_DATE , MEH.PLAN_CODE , PM.PROVIDER_ID ,PM.LAST_NAME AS PROVIDER_LAST_NAME ,PM.FIRST_NAME AS PROVIDER_FIRST_NAME ,PM.PROVIDER_TYPE ,PM.LICENSE ,PA.COUNTY AS PROV_COUNTY ,PA.ADDR_CATEGORY_CODE ,MI.IDNUMBER AS SUBSCRIBER_ID ,MP.ATTRRECNO AS ATTRRECNO ,MP.ATTRRRECNO AS ATTRRECNO1 ,MP.RECSTAT AS RECSTAT ,MP.MEMRECNO AS MEMRECNO ,MP.PHNUMBER LABEL = "NEHII_HOME_PHONE" AS NEHII_HOME_PHONE ,MP.PHNUMBER LABEL = "NEHII_MOBILE_PHNUMBER" AS NEHII_MOBILE_PHNUMBER ,MA.STLINE1 AS NEHII_STREET_ADDRESS1 ,MA.CITY AS NEHII_CITY ,MA.STATE AS NEHII_STATE ,MA.COUNTY AS NEHII_COUNTY ,MA.ZIPCODE AS NEHII_ZIPCODE FROM HSDREPT.MEMBER_ADDRESS MA, HSDREPT.MEMBER_MASTER MM, HSDREPT.member_elig_history meh, HSDREPT.PROV_MASTER PM, HSDREPT.PROV_ADDRESS PA, MDM.MPI_MEMPHONEWC MP, MDM.MPI_MEMADDRWC MA, MDM.MPI_MEMIDENTWC MI WHERE MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID AND PM.SEQ_PROV_ID = MEH.SEQ_PROV_ID AND PM.SEQ_PROV_ID = PA.SEQ_PROV_ID AND MEH.LINE_OF_BUSINESS = 'NED' AND MEH.TERM_DATE IS NULL AND MA.ADDRESS_TYPE = 'R5' AND MP.ATTRRECNO = 389 AND MP.ATTRRECNO1 = 391 MA.MEMRECNO = MI.MEMRECNO = MP.MEMRECNO AND MP.MEMRECNO = MA.MEMRECNO AND MA.ATTRRECNO = 390 AND MA.TERM_DATE IS NULL;</CODE></PRE> Thu, 06 Jun 2019 03:01:22 GMT https://communities.sas.com/t5/New-SAS-User/where-statements-changed-to-inner-joins/m-p/563946#M10964 req41273 2019-06-06T03:01:22Z https://communities.sas.com/t5/New-SAS-User/where-statements-changed-to-inner-joins/m-p/563954#M10965 <P>Something like:</P> <P>&nbsp;</P> <PRE><CODE class=" language-sas">FROM HSDREPT.MEMBER_ADDRESS as MA inner join HSDREPT.MEMBER_MASTER as MM on MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID inner join HSDREPT.member_elig_history as meh on MM.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID inner join HSDREPT.PROV_MASTER as PM on PM.SEQ_PROV_ID = MEH.SEQ_PROV_ID inner join HSDREPT.PROV_ADDRESS as PA on PM.SEQ_PROV_ID = PA.SEQ_PROV_ID inner join MDM.MPI_MEMPHONEWC as MP on MA.MEMRECNO = MP.MEMRECNO inner join MDM.MPI_MEMADDRWC as MA on MA.MEMRECNO = MP.MEMRECNO inner join MDM.MPI_MEMIDENTWC as MI on MA.MEMRECNO = MI.MEMRECNO WHERE MEH.LINE_OF_BUSINESS = 'NED' AND MEH.TERM_DATE IS NULL AND MA.ADDRESS_TYPE = 'R5' AND MP.ATTRRECNO = 389 AND MP.ATTRRECNO1 = 391 AND MA.ATTRRECNO = 390 AND MA.TERM_DATE IS NULL;</CODE></PRE> Thu, 06 Jun 2019 03:49:30 GMT https://communities.sas.com/t5/New-SAS-User/where-statements-changed-to-inner-joins/m-p/563954#M10965 PGStats 2019-06-06T03:49:30Z https://communities.sas.com/t5/New-SAS-User/where-statements-changed-to-inner-joins/m-p/564151#M10991 <P>Thanks for replying.</P><P>I'm on a good start.</P><P>Can you please review my code now and see why I'm getting the syntax errors?&nbsp;</P><P>&nbsp;</P><P>thanks</P><PRE><CODE class=" language-sas">1 The SAS System 09:51 Thursday, June 6, 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=&amp;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 26 27 Create table NED_Members as 28 29 SELECT DISTINCT 30 31 MM.SEQ_MEMB_ID as MemberID 32 , MM.LAST_NAME as MemberLastName 33 , MM.FIRST_NAME as MemberFirstName 34 , MM.DATE_OF_BIRTH as MemberDOB 35 , MM.GENDER as M_F 36 , MM.MEDICAID_NO as MedicaidID 37 , MM.ETHNICITY_CODE 38 , MM.USER_DATE_7 39 , meh.line_of_business as LOB 40 , meh.SEQ_PROV_ID 41 , meh.IPA_ID 42 , MA.ADDRESS_LINE_1 AS ADDRESS1 43 , MA.ADDRESS_LINE_2 AS ADDRESS2 44 , MA.CITY AS CITY 45 , MA.STATE AS STATE 46 , MA.ZIP_CODE AS ZIP 47 , MA.COUNTY AS COUNTY 48 , MA.HOME_PHONE_NUMBER AS HOMEPHONE 49 , MEH.EFFECTIVE_DATE 50 , MEH.TERM_DATE 51 , MEH.PLAN_CODE 52 , PM.PROVIDER_ID 53 ,PM.LAST_NAME AS PROVIDER_LAST_NAME 54 ,PM.FIRST_NAME AS PROVIDER_FIRST_NAME 55 ,PM.PROVIDER_TYPE 2 The SAS System 09:51 Thursday, June 6, 2019 56 ,PM.LICENSE 57 ,PA.COUNTY AS PROV_COUNTY 58 ,PA.ADDR_CATEGORY_CODE 59 ,MI.IDNUMBER AS SUBSCRIBER_ID 60 ,MP.ATTRRECNO AS ATTRRECNO 61 ,MP.ATTRRRECNO AS ATTRRECNO1 62 ,MP.RECSTAT AS RECSTAT 63 ,MP.MEMRECNO AS MEMRECNO 64 ,MP.PHNUMBER LABEL = "NEHII_HOME_PHONE" AS NEHII_HOME_PHONE 65 ,MP.PHNUMBER LABEL = "NEHII_MOBILE_PHNUMBER" AS NEHII_MOBILE_PHNUMBER 66 ,MA.STLINE1 AS NEHII_STREET_ADDRESS1 67 ,MA.CITY AS NEHII_CITY 68 ,MA.STATE AS NEHII_STATE 69 ,MA.COUNTY AS NEHII_COUNTY 70 ,MA.ZIPCODE AS NEHII_ZIPCODE 71 72 73 FROM 74 HSDREPT.MEMBER_ADDRESS as MA 75 inner join HSDREPT.MEMBER_MASTER as MM 76 on MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID 77 inner join HSDREPT.member_elig_history as meh 78 on MM.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID 79 inner join HSDREPT.PROV_MASTER as PM 80 on PM.SEQ_PROV_ID = MEH.SEQ_PROV_ID 81 inner join HSDREPT.PROV_ADDRESS as PA 82 on PM.SEQ_PROV_ID = PA.SEQ_PROV_ID 83 inner join MDM.MPI_MEMPHONEWC as MP 84 on MA.MEMRECNO = MP.MEMRECNO 85 inner join MDM.MPI_MEMADDRWC as MA 86 on MA.MEMRECNO = MP.MEMRECNO 87 inner join MDM.MPI_MEMIDENTWC as MI 88 on MA.MEMRECNO = MI.MEMRECNO 89 LEFT JOIN MDM.MPI_MEMIDENTWC as MI 90 on HSDREPT.MEMBER_MASTER.MEDICAID_NO = MDM.MPI_MEMIDENTWC.IDNUMBER _ 22 76 ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &amp;, *, **, +, ',', -, /, &lt;, &lt;=, &lt;&gt;, =, &gt;, &gt;=, ?, AND, ANSIMISS, BETWEEN, CONTAINS, CROSS, EQ, EQT, EXCEPT, FULL, GE, GET, GROUP, GT, GTT, HAVING, IN, INNER, INTERSECT, IS, JOIN, LE, LEFT, LET, LIKE, LT, LTT, NATURAL, NE, NET, NOMISS, NOT, NOTIN, OR, ORDER, OUTER, RIGHT, UNION, WHERE, ^, ^=, |, ||, ~, ~=. ERROR 76-322: Syntax error, statement will be ignored. 91 92 WHERE 93 MEH.LINE_OF_BUSINESS = 'NED' 94 AND MEH.TERM_DATE IS NULL 95 AND MA.ADDRESS_TYPE = 'R5' 96 AND MP.ATTRRECNO = 389 97 AND MP.ATTRRECNO1 = 391 98 MA.MEMRECNO = MI.MEMRECNO = MP.MEMRECNO 99 AND MA.ATTRRECNO = 390 100 AND MA.TERM_DATE IS NULL; NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 101 102 quit; 3 The SAS System 09:51 Thursday, June 6, 2019 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.01 seconds 103 run; 104 105 proc sort data = NED_Members NODUPKEY; ERROR: File WORK.NED_MEMBERS.DATA does not exist. 106 by MemberID; 107 108 GOPTIONS NOACCESSIBLE; 109 %LET _CLIENTTASKLABEL=; 110 %LET _CLIENTPROCESSFLOWNAME=; 111 %LET _CLIENTPROJECTPATH=; 112 %LET _CLIENTPROJECTNAME=; 113 %LET _SASPROGRAMFILE=; 114 115 ;*';*";*/;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 115 ! run; 116 ODS _ALL_ CLOSE; 117 118 119 QUIT; RUN; 120 </CODE></PRE> Thu, 06 Jun 2019 15:09:03 GMT https://communities.sas.com/t5/New-SAS-User/where-statements-changed-to-inner-joins/m-p/564151#M10991 req41273 2019-06-06T15:09:03Z https://communities.sas.com/t5/New-SAS-User/where-statements-changed-to-inner-joins/m-p/564168#M10994 <P>SAS is clearing marking the syntax error.</P> <PRE>HSDREPT.MEMBER_MASTER.MEDICAID_NO </PRE> <P>You refer to variable is SQL code using either the variable name alone (if it is obvious to the compiler which variable that is) or with an alias for a table followed by a period and then the variable name.</P> <P>&nbsp;</P> <P>So in your code you are either asking for a variable with a period in its name or trying to reference a dataset using an alias that has a period in it. Neither is allowed.</P> <P>&nbsp;</P> <P>You set up MM as the alias for&nbsp;HSDREPT.MEMBER_MASTER so use that to reference the variable.</P> <PRE>mm.MEDICAID_NO</PRE> <P>&nbsp;</P> Thu, 06 Jun 2019 15:31:42 GMT https://communities.sas.com/t5/New-SAS-User/where-statements-changed-to-inner-joins/m-p/564168#M10994 Tom 2019-06-06T15:31:42Z https://communities.sas.com/t5/New-SAS-User/where-statements-changed-to-inner-joins/m-p/564204#M11003 <PRE><CODE class=" language-sas">1 The SAS System 09:51 Thursday, June 6, 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=&amp;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 26 27 Create table NED_Members as 28 29 SELECT DISTINCT 30 31 MM.SEQ_MEMB_ID as MemberID 32 , MM.LAST_NAME as MemberLastName 33 , MM.FIRST_NAME as MemberFirstName 34 , MM.DATE_OF_BIRTH as MemberDOB 35 , MM.GENDER as M_F 36 , MM.MEDICAID_NO as MedicaidID 37 , MM.ETHNICITY_CODE 38 , MM.USER_DATE_7 39 , meh.line_of_business as LOB 40 , meh.SEQ_PROV_ID 41 , meh.IPA_ID 42 , MA.ADDRESS_LINE_1 AS ADDRESS1 43 , MA.ADDRESS_LINE_2 AS ADDRESS2 44 , MA.CITY AS CITY 45 , MA.STATE AS STATE 46 , MA.ZIP_CODE AS ZIP 47 , MA.COUNTY AS COUNTY 48 , MA.HOME_PHONE_NUMBER AS HOMEPHONE 49 , MEH.EFFECTIVE_DATE 50 , MEH.TERM_DATE 51 , MEH.PLAN_CODE 52 , PM.PROVIDER_ID 53 ,PM.LAST_NAME AS PROVIDER_LAST_NAME 54 ,PM.FIRST_NAME AS PROVIDER_FIRST_NAME 55 ,PM.PROVIDER_TYPE 2 The SAS System 09:51 Thursday, June 6, 2019 56 ,PM.LICENSE 57 ,PA.COUNTY AS PROV_COUNTY 58 ,PA.ADDR_CATEGORY_CODE 59 ,MI.IDNUMBER AS SUBSCRIBER_ID 60 ,MP.ATTRRECNO AS ATTRRECNO 61 ,MP.ATTRRRECNO AS ATTRRECNO1 62 ,MP.RECSTAT AS RECSTAT 63 ,MP.MEMRECNO AS MEMRECNO 64 ,MP.PHNUMBER LABEL = "NEHII_HOME_PHONE" AS NEHII_HOME_PHONE 65 ,MP.PHNUMBER LABEL = "NEHII_MOBILE_PHNUMBER" AS NEHII_MOBILE_PHNUMBER 66 ,MA.STLINE1 AS NEHII_STREET_ADDRESS1 67 ,MA.CITY AS NEHII_CITY 68 ,MA.STATE AS NEHII_STATE 69 ,MA.COUNTY AS NEHII_COUNTY 70 ,MA.ZIPCODE AS NEHII_ZIPCODE 71 72 73 FROM 74 HSDREPT.MEMBER_ADDRESS as MA 75 inner join HSDREPT.MEMBER_MASTER as MM 76 on MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID 77 inner join HSDREPT.member_elig_history as meh 78 on MM.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID 79 inner join HSDREPT.PROV_MASTER as PM 80 on PM.SEQ_PROV_ID = MEH.SEQ_PROV_ID 81 inner join HSDREPT.PROV_ADDRESS as PA 82 on PM.SEQ_PROV_ID = PA.SEQ_PROV_ID 83 inner join MDM.MPI_MEMPHONEWC as MP 84 on MA.MEMRECNO = MP.MEMRECNO 85 inner join MDM.MPI_MEMADDRWC as MA 86 on MA.MEMRECNO = MP.MEMRECNO 87 inner join MDM.MPI_MEMIDENTWC as MI 88 on MA.MEMRECNO = MI.MEMRECNO 89 LEFT JOIN MDM.MPI_MEMIDENTWC as MI 90 on MM.MEDICAID_NO = MI.IDNUMBER 91 92 WHERE 93 MEH.LINE_OF_BUSINESS = 'NED' 94 AND MEH.TERM_DATE IS NULL 95 AND MA.ADDRESS_TYPE = 'R5' 96 AND MP.ATTRRECNO = 389 97 AND MP.ATTRRECNO1 = 391 98 AND MA.MEMRECNO = MI.MEMRECNO = MP.MEMRECNO 99 AND MA.ATTRRECNO = 390 100 AND MA.TERM_DATE IS NULL; ERROR: Column MEMRECNO could not be found in the table/view identified with the correlation name MA. ERROR: Column MI.IDNUMBER was found in more than one table in the same scope. ERROR: Column MA.CITY was found in more than one table in the same scope. ERROR: Column MA.STATE was found in more than one table in the same scope. ERROR: Column MA.COUNTY was found in more than one table in the same scope. ERROR: Column MI.IDNUMBER was found in more than one table in the same scope. ERROR: Column ATTRRRECNO could not be found in the table/view identified with the correlation name MP. ERROR: Column MA.CITY was found in more than one table in the same scope. ERROR: Column MA.STATE was found in more than one table in the same scope. ERROR: Column MA.COUNTY was found in more than one table in the same scope. ERROR: Column ATTRRECNO1 could not be found in the table/view identified with the correlation name MP. ERROR: Column MI.MEMRECNO was found in more than one table in the same scope. ERROR: Column MI.MEMRECNO was found in more than one table in the same scope. 3 The SAS System 09:51 Thursday, June 6, 2019 NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 101 102 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.09 seconds cpu time 0.03 seconds 103 run; 104 105 proc sort data = NED_Members NODUPKEY; ERROR: File WORK.NED_MEMBERS.DATA does not exist. 106 by MemberID; 107 108 GOPTIONS NOACCESSIBLE; 109 %LET _CLIENTTASKLABEL=; 110 %LET _CLIENTPROCESSFLOWNAME=; 111 %LET _CLIENTPROJECTPATH=; 112 %LET _CLIENTPROJECTNAME=; 113 %LET _SASPROGRAMFILE=; 114 115 ;*';*";*/;quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SORT used (Total process time): real time 0.02 seconds cpu time 0.00 seconds 115 ! run; 116 ODS _ALL_ CLOSE; 117 118 119 QUIT; RUN; 120 </CODE></PRE> Thu, 06 Jun 2019 16:47:30 GMT https://communities.sas.com/t5/New-SAS-User/where-statements-changed-to-inner-joins/m-p/564204#M11003 req41273 2019-06-06T16:47:30Z https://communities.sas.com/t5/New-SAS-User/where-statements-changed-to-inner-joins/m-p/564205#M11004 <P>Is there something wrong with my join order or tables?</P><PRE><CODE class=" language-sas">1 The SAS System 09:51 Thursday, June 6, 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=&amp;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 26 27 Create table NED_Members as 28 29 SELECT DISTINCT 30 31 MM.SEQ_MEMB_ID as MemberID 32 , MM.LAST_NAME as MemberLastName 33 , MM.FIRST_NAME as MemberFirstName 34 , MM.DATE_OF_BIRTH as MemberDOB 35 , MM.GENDER as M_F 36 , MM.MEDICAID_NO as MedicaidID 37 , MM.ETHNICITY_CODE 38 , MM.USER_DATE_7 39 , meh.line_of_business as LOB 40 , meh.SEQ_PROV_ID 41 , meh.IPA_ID 42 , MA.ADDRESS_LINE_1 AS ADDRESS1 43 , MA.ADDRESS_LINE_2 AS ADDRESS2 44 , MA.CITY AS CITY 45 , MA.STATE AS STATE 46 , MA.ZIP_CODE AS ZIP 47 , MA.COUNTY AS COUNTY 48 , MA.HOME_PHONE_NUMBER AS HOMEPHONE 49 , MEH.EFFECTIVE_DATE 50 , MEH.TERM_DATE 51 , MEH.PLAN_CODE 52 , PM.PROVIDER_ID 53 ,PM.LAST_NAME AS PROVIDER_LAST_NAME 54 ,PM.FIRST_NAME AS PROVIDER_FIRST_NAME 55 ,PM.PROVIDER_TYPE 2 The SAS System 09:51 Thursday, June 6, 2019 56 ,PM.LICENSE 57 ,PA.COUNTY AS PROV_COUNTY 58 ,PA.ADDR_CATEGORY_CODE 59 ,MI.IDNUMBER AS SUBSCRIBER_ID 60 ,MP.ATTRRECNO AS ATTRRECNO 61 ,MP.ATTRRRECNO AS ATTRRECNO1 62 ,MP.RECSTAT AS RECSTAT 63 ,MP.MEMRECNO AS MEMRECNO 64 ,MP.PHNUMBER LABEL = "NEHII_HOME_PHONE" AS NEHII_HOME_PHONE 65 ,MP.PHNUMBER LABEL = "NEHII_MOBILE_PHNUMBER" AS NEHII_MOBILE_PHNUMBER 66 ,MA.STLINE1 AS NEHII_STREET_ADDRESS1 67 ,MA.CITY AS NEHII_CITY 68 ,MA.STATE AS NEHII_STATE 69 ,MA.COUNTY AS NEHII_COUNTY 70 ,MA.ZIPCODE AS NEHII_ZIPCODE 71 72 73 FROM 74 HSDREPT.MEMBER_ADDRESS as MA 75 inner join HSDREPT.MEMBER_MASTER as MM 76 on MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID 77 inner join HSDREPT.member_elig_history as meh 78 on MM.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID 79 inner join HSDREPT.PROV_MASTER as PM 80 on PM.SEQ_PROV_ID = MEH.SEQ_PROV_ID 81 inner join HSDREPT.PROV_ADDRESS as PA 82 on PM.SEQ_PROV_ID = PA.SEQ_PROV_ID 83 inner join MDM.MPI_MEMPHONEWC as MP 84 on MA.MEMRECNO = MP.MEMRECNO 85 inner join MDM.MPI_MEMADDRWC as MA 86 on MA.MEMRECNO = MP.MEMRECNO 87 inner join MDM.MPI_MEMIDENTWC as MI 88 on MA.MEMRECNO = MI.MEMRECNO 89 LEFT JOIN MDM.MPI_MEMIDENTWC as MI 90 on MM.MEDICAID_NO = MI.IDNUMBER 91 92 WHERE 93 MEH.LINE_OF_BUSINESS = 'NED' 94 AND MEH.TERM_DATE IS NULL 95 AND MA.ADDRESS_TYPE = 'R5' 96 AND MP.ATTRRECNO = 389 97 AND MP.ATTRRECNO1 = 391 98 AND MA.MEMRECNO = MI.MEMRECNO = MP.MEMRECNO 99 AND MA.ATTRRECNO = 390 100 AND MA.TERM_DATE IS NULL; ERROR: Column MEMRECNO could not be found in the table/view identified with the correlation name MA. ERROR: Column MI.IDNUMBER was found in more than one table in the same scope. ERROR: Column MA.CITY was found in more than one table in the same scope. ERROR: Column MA.STATE was found in more than one table in the same scope. ERROR: Column MA.COUNTY was found in more than one table in the same scope. ERROR: Column MI.IDNUMBER was found in more than one table in the same scope. ERROR: Column ATTRRRECNO could not be found in the table/view identified with the correlation name MP. ERROR: Column MA.CITY was found in more than one table in the same scope. ERROR: Column MA.STATE was found in more than one table in the same scope. ERROR: Column MA.COUNTY was found in more than one table in the same scope. ERROR: Column ATTRRECNO1 could not be found in the table/view identified with the correlation name MP. ERROR: Column MI.MEMRECNO was found in more than one table in the same scope. ERROR: Column MI.MEMRECNO was found in more than one table in the same scope. 3 The SAS System 09:51 Thursday, June 6, 2019 NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 101 102 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.09 seconds cpu time 0.03 seconds 103 run; 104 105 proc sort data = NED_Members NODUPKEY; ERROR: File WORK.NED_MEMBERS.DATA does not exist. 106 by MemberID; 107 108 GOPTIONS NOACCESSIBLE; 109 %LET _CLIENTTASKLABEL=; 110 %LET _CLIENTPROCESSFLOWNAME=; 111 %LET _CLIENTPROJECTPATH=; 112 %LET _CLIENTPROJECTNAME=; 113 %LET _SASPROGRAMFILE=; 114 115 ;*';*";*/;quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SORT used (Total process time): real time 0.02 seconds cpu time 0.00 seconds 115 ! run; 116 ODS _ALL_ CLOSE; 117 118 119 QUIT; RUN; 120 </CODE></PRE> Thu, 06 Jun 2019 16:48:57 GMT https://communities.sas.com/t5/New-SAS-User/where-statements-changed-to-inner-joins/m-p/564205#M11004 req41273 2019-06-06T16:48:57Z https://communities.sas.com/t5/New-SAS-User/where-statements-changed-to-inner-joins/m-p/564210#M11005 <P>You have two different types of errors there. Let's just look at one example of each.</P> <PRE>ERROR: Column MEMRECNO could not be found in the table/view identified with the correlation name MA. ERROR: Column MI.IDNUMBER was found in more than one table in the same scope.</PRE> <P>So the first one is saying that you tried to find the variable MEMRECNO in the dataset that you are referencing as MA.&nbsp;&nbsp;</P> <P>So check what dataset you assigned the alias of MA to and then check the variables in that dataset.&nbsp; Perhaps you have a typo in your variable name or you are using the wrong alias so you are pointing SAS at the wrong table.</P> <P>&nbsp;</P> <P>The second is saying that MI.IDNUMBER refers to more than one actual variable. Normally I see that when I use an ambiguous reference by just using the variable name without adding the alias to let SQL know which dataset the variable is in.&nbsp; But the confusing is explained when I looked for which table the alias MI is referencing.&nbsp; So these errors are caused by you trying to use the same alias for multiple datasets.&nbsp; You have two datasets&nbsp; referenced as MA and two referenced as MI.&nbsp; All of the datasets you are using need to have a distinct alias.&nbsp; Even if you are joining a dataset with itself each place it appears needs to have a different alias so it is clear which variable you want to use.</P> <P>&nbsp;</P> <P>&nbsp;</P> Thu, 06 Jun 2019 17:03:26 GMT https://communities.sas.com/t5/New-SAS-User/where-statements-changed-to-inner-joins/m-p/564210#M11005 Tom 2019-06-06T17:03:26Z https://communities.sas.com/t5/New-SAS-User/where-statements-changed-to-inner-joins/m-p/564277#M11016 <P>New error message.&nbsp; Any thoughts on why I'm getting this message.&nbsp;</P><PRE><CODE class=" language-sas">1 The SAS System 09:51 Thursday, June 6, 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=&amp;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 26 27 Create table NED_Members as 28 29 SELECT DISTINCT 30 31 MM.SEQ_MEMB_ID as MemberID 32 , MM.LAST_NAME as MemberLastName 33 , MM.FIRST_NAME as MemberFirstName 34 , MM.DATE_OF_BIRTH as MemberDOB 35 , MM.GENDER as M_F 36 , MM.MEDICAID_NO as MedicaidID 37 , MM.ETHNICITY_CODE 38 , MM.USER_DATE_7 39 , meh.line_of_business as LOB 40 , meh.SEQ_PROV_ID 41 , meh.IPA_ID 42 , MA.ADDRESS_LINE_1 AS ADDRESS1 43 , MA.ADDRESS_LINE_2 AS ADDRESS2 44 , MA.CITY AS CITY 45 , MA.STATE AS STATE 46 , MA.ZIP_CODE AS ZIP 47 , MA.COUNTY AS COUNTY 48 , MA.HOME_PHONE_NUMBER AS HOMEPHONE 49 , MEH.EFFECTIVE_DATE 50 , MEH.TERM_DATE 51 , MEH.PLAN_CODE 52 , PM.PROVIDER_ID 53 ,PM.LAST_NAME AS PROVIDER_LAST_NAME 54 ,PM.FIRST_NAME AS PROVIDER_FIRST_NAME 55 ,PM.PROVIDER_TYPE 2 The SAS System 09:51 Thursday, June 6, 2019 56 ,PM.LICENSE 57 ,PA.COUNTY AS PROV_COUNTY 58 ,PA.ADDR_CATEGORY_CODE 59 ,MI.IDNUMBER AS SUBSCRIBER_ID 60 ,MP.ATTRRECNO AS ATTRRECNO 61 ,MP.ATTRRECNO AS ATTRRECNO1 62 ,MP.RECSTAT AS RECSTAT 63 ,MP.MEMRECNO AS MEMRECNO 64 ,MP.PHNUMBER LABEL = "NEHII_HOME_PHONE" AS NEHII_HOME_PHONE 65 ,MP.PHNUMBER LABEL = "NEHII_MOBILE_PHNUMBER" AS NEHII_MOBILE_PHNUMBER 66 ,MAD.STLINE1 AS NEHII_STREET_ADDRESS1 67 ,MAD.CITY AS NEHII_CITY 68 ,MAD.STATE AS NEHII_STATE 69 ,MAD.COUNTY AS NEHII_COUNTY 70 ,MAD.ZIPCODE AS NEHII_ZIPCODE 71 72 73 FROM 74 HSDREPT.MEMBER_ADDRESS as MA 75 inner join HSDREPT.MEMBER_MASTER as MM 76 on MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID 77 inner join HSDREPT.member_elig_history as meh 78 on MM.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID 79 inner join HSDREPT.PROV_MASTER as PM 80 on PM.SEQ_PROV_ID = MEH.SEQ_PROV_ID 81 inner join HSDREPT.PROV_ADDRESS as PA 82 on PM.SEQ_PROV_ID = PA.SEQ_PROV_ID 83 inner join MDM.MPI_MEMPHONEWC as MP 84 on MAD.MEMRECNO = MP.MEMRECNO 85 inner join MDM.MPI_MEMADDRWC as MAD 86 on MAD.MEMRECNO = MP.MEMRECNO 87 inner join MDM.MPI_MEMIDENTWC as MI 88 on MAD.MEMRECNO = MI.MEMRECNO 89 LEFT JOIN MDM.MPI_MEMIDENTWC AS MID 90 on MM.MEDICAID_NO = MI.IDNUMBER 91 92 WHERE 93 MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID 94 AND MEH.LINE_OF_BUSINESS = 'NED' 95 AND MEH.TERM_DATE IS NULL 96 AND MA.ADDRESS_TYPE = 'R5' 97 AND MA.TERM_DATE IS NULL 98 AND MAD.MEMRECNO = MI.MEMRECNO = MP.MEMRECNO AND MP.ATTRRECNO IN (389,391); 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. 99 100 101 102 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.15 seconds cpu time 0.03 seconds 103 run; 104 3 The SAS System 09:51 Thursday, June 6, 2019 105 proc sort data = NED_Members NODUPKEY; ERROR: File WORK.NED_MEMBERS.DATA does not exist. 106 by MemberID; 107 108 GOPTIONS NOACCESSIBLE; 109 %LET _CLIENTTASKLABEL=; 110 %LET _CLIENTPROCESSFLOWNAME=; 111 %LET _CLIENTPROJECTPATH=; 112 %LET _CLIENTPROJECTNAME=; 113 %LET _SASPROGRAMFILE=; 114 115 ;*';*";*/;quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SORT used (Total process time): real time 0.03 seconds cpu time 0.00 seconds 115 ! run; 116 ODS _ALL_ CLOSE; 117 118 119 QUIT; RUN; 120 </CODE></PRE><P>&nbsp;</P><P>thanks.</P> Thu, 06 Jun 2019 19:32:57 GMT https://communities.sas.com/t5/New-SAS-User/where-statements-changed-to-inner-joins/m-p/564277#M11016 req41273 2019-06-06T19:32:57Z https://communities.sas.com/t5/New-SAS-User/where-statements-changed-to-inner-joins/m-p/564288#M11020 <P>The join condition:</P> <P>&nbsp;</P> <PRE><CODE class=" language-sas">inner join MDM.MPI_MEMPHONEWC as MP on MAD.MEMRECNO = MP.MEMRECNO</CODE></PRE> <P>mentions the alias MAD before defining it.</P> Thu, 06 Jun 2019 20:08:54 GMT https://communities.sas.com/t5/New-SAS-User/where-statements-changed-to-inner-joins/m-p/564288#M11020 PGStats 2019-06-06T20:08:54Z https://communities.sas.com/t5/New-SAS-User/where-statements-changed-to-inner-joins/m-p/564291#M11021 <P>Thanks,</P><P>I'll re-order the join and try that.</P><P>&nbsp;</P><P>By the way, how do I read the inner joins table flow in my code?&nbsp; Meaning is the pattern</P><P>from table 1</P><P>inner join table 2 on table1.column 1 = table 2.column1, etc.?&nbsp; When it's multiple tables, it's kind of hard to follow for a new beginner.</P> Thu, 06 Jun 2019 20:21:25 GMT https://communities.sas.com/t5/New-SAS-User/where-statements-changed-to-inner-joins/m-p/564291#M11021 req41273 2019-06-06T20:21:25Z https://communities.sas.com/t5/New-SAS-User/where-statements-changed-to-inner-joins/m-p/564295#M11023 <P>So, how would I define it and when?&nbsp; I tried rearranging the join (see code box)</P><PRE><CODE class=" language-sas">FROM HSDREPT.MEMBER_ADDRESS as MA inner join HSDREPT.MEMBER_MASTER as MM on MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID inner join HSDREPT.member_elig_history as meh on MM.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID inner join HSDREPT.PROV_MASTER as PM on PM.SEQ_PROV_ID = MEH.SEQ_PROV_ID inner join HSDREPT.PROV_ADDRESS as PA on PM.SEQ_PROV_ID = PA.SEQ_PROV_ID inner join MDM.MPI_MEMADDRWC as MAD on MAD.MEMRECNO = MP.MEMRECNO inner join MDM.MPI_MEMPHONEWC as MP on MAD.MEMRECNO = MP.MEMRECNO inner join MDM.MPI_MEMIDENTWC as MI on MAD.MEMRECNO = MI.MEMRECNO LEFT JOIN MDM.MPI_MEMIDENTWC AS MID on MM.MEDICAID_NO = MI.IDNUMBER</CODE></PRE> Thu, 06 Jun 2019 20:27:44 GMT https://communities.sas.com/t5/New-SAS-User/where-statements-changed-to-inner-joins/m-p/564295#M11023 req41273 2019-06-06T20:27:44Z https://communities.sas.com/t5/New-SAS-User/where-statements-changed-to-inner-joins/m-p/564298#M11025 <P>Start small and build from there.</P> <P>&nbsp;</P> <P>Your last join also looks suspicious. In</P> <P>&nbsp;</P> <PRE class=" language-sas"><CODE class=" language-sas"><SPAN class="token function">LEFT</SPAN> JOIN MDM<SPAN class="token punctuation">.</SPAN>MPI_MEMIDENTWC AS MID <SPAN class="token number">90</SPAN> on MM<SPAN class="token punctuation">.</SPAN>MEDICAID_NO <SPAN class="token operator">=</SPAN> <SPAN class="token procnames">MI</SPAN><SPAN class="token punctuation">.</SPAN>IDNUMBER</CODE></PRE> <P>the join condition doesn't refer to table alias MID... </P> Thu, 06 Jun 2019 20:35:48 GMT https://communities.sas.com/t5/New-SAS-User/where-statements-changed-to-inner-joins/m-p/564298#M11025 PGStats 2019-06-06T20:35:48Z