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

Sorry,    the post messed up my output file.  The 391  under the ATTRRECNO variable should display the MP.PHNUMBER data under the NEHII_Mobile_Phnumber, and the 389 under the ATTRRECNO variable should display the MP.PHNUMBER data under the NEHII_HOMEPHONENUMBER. ... View more

Thanks for the responses.  What my ultimate objective is, I need my output file to look like what is indicated below.     The input dataset for the ATTRRECNO and MP.PHNUMBER looks like so:   MemberId       MemberLastName         MemberFirstNmae         ATTRRECNO        MP.PHNUMBER 1845555               Jones                             Rob                                  389               1308850932 1832333                Smith                           Anita                                  391               1582393838 3828383               Lee                               Bill                                     389               1308484848 1545558              Walker                          Lou                                    389                 1390838484 1545558             Walker                            Lou                                  391                   1309484838     I want my output results dataset to look like, or better yet, not display the ATTRRECNO field or the ATTRRECNO1 field at all.     MemberID MemberLastName MemberFirstName ATTRRECNO ATTRRECNO1 NEHII_HOME_PHONE NEHII_MOBILE_PHNUMBER 1845555     Jones                           Rob                        389                                   1308850932 1845555     Smith                           Anita                                               391                                                                             1582393838 3828383        Lee                           Bill                          389                                    1383994848 1545558        Walker                     Lou                         389                     391         1390838484                                            1309484838       Code is in code box. 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 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 09:13 Wednesday, June 19, 2019 56 ,MI.IDNUMBER AS SUBSCRIBER_ID 57 ,MP.ATTRRECNO AS ATTRRECNO 58 ,MP.ATTRRECNO AS ATTRRECNO1 59 ,MP.RECSTAT AS RECSTAT 60 ,MP.MEMRECNO AS MEMRECNO 61 , CASE 62 WHEN (MP.ATTRRECNO = 381) THEN MP.PHNUMBER 63 ELSE null 64 END AS NEHII_HOME_PHONE, 65 CASE 66 WHEN (MP.ATTRRECNO = 391) THEN MP.PHNUMBER 67 ELSE null 68 END AS NEHII_MOBILE_PHNUMBER 69 ,MP.PHNUMBER LABEL = "NEHII_MOBILE_PHNUMBER" AS NEHII_MOBILE_PHNUMBER 70 ,MAD.STLINE1 AS NEHII_STREET_ADDRESS1 71 ,MAD.CITY AS NEHII_CITY 72 ,MAD.STATE AS NEHII_STATE 73 ,MAD.COUNTY AS NEHII_COUNTY 74 ,MAD.ZIPCODE AS NEHII_ZIPCODE 75 76 77 FROM 78 HSDREPT.MEMBER_ADDRESS as MA 79 inner join HSDREPT.MEMBER_MASTER as MM 80 on MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID 81 inner join HSDREPT.member_elig_history as meh 82 on MM.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID 83 inner join HSDREPT.PROV_MASTER as PM 84 on PM.SEQ_PROV_ID = MEH.SEQ_PROV_ID 85 inner join HSDREPT.PROV_ADDRESS as PA 86 on PM.SEQ_PROV_ID = PA.SEQ_PROV_ID 87 LEFT JOIN MDM.MPI_MEMIDENTWC AS MI 88 on MM.MEDICAID_NO = MI.IDNUMBER 89 inner join MDM.MPI_MEMADDRWC as MAD 90 on MAD.MEMRECNO = MI.MEMRECNO 91 inner join MDM.MPI_MEMPHONEWC as MP 92 on MAD.MEMRECNO = MP.MEMRECNO 93 94 WHERE 95 MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID 96 AND MA.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID 97 AND MEH.LINE_OF_BUSINESS = 'NED' 98 AND MEH.TERM_DATE IS NULL 99 AND MA.ADDRESS_TYPE = 'R5' 100 AND MA.TERM_DATE IS NULL 101 AND MAD.MEMRECNO = MI.MEMRECNO 102 AND MI.MEMRECNO = MP.MEMRECNO AND MP.ATTRRECNO IN (389,391) 103 AND MP.MEMRECNO = MAD.MEMRECNO AND MAD.ATTRRECNO = 390; ERROR: The following columns were not found in the contributing tables: null. ... View more

So, how would I define it and when?  I tried rearranging the join (see code box) 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 ... View more

Thanks, I'll re-order the join and try that.   By the way, how do I read the inner joins table flow in my code?  Meaning is the pattern from table 1 inner join table 2 on table1.column 1 = table 2.column1, etc.?  When it's multiple tables, it's kind of hard to follow for a new beginner. ... View more

New error message.  Any thoughts on why I'm getting this message.  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=&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   thanks. ... View more

Is there something wrong with my join order or tables? 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=&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 ... View more