Seeking assistance on why errors on generating on below program. 1 The SAS System 14:42 Wednesday, September 11, 2019 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL='Program (2)'; 4 %LET _CLIENTPROCESSFLOWNAME='Process Flow'; 5 %LET _CLIENTPROJECTPATH='C:\Users\PSTENNIS\Documents\ALL\ADHOC REQUEST\TINA LOCH\CLAIMS_PULL_PROC_SCHEDULES.egp'; 6 %LET _CLIENTPROJECTNAME='CLAIMS_PULL_PROC_SCHEDULES.egp'; 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 25 option obs=max; 26 27 proc sql noprint /*INOBS=100*/; 28 create table claims as 29 Select * FROM 30 (Select Distinct 31 CH.CLAIM_NUMBER as ClaimID 32 ,CH.PAT_CONTROL_NO as PCN 33 , CD.LINE_NUMBER as ClaimLine 34 , CD.SUB_LINE_CODE as LineCode 35 , CD.RESERVED_LOCAL_USE_DET AS WCN 36 , CD.DETAIL_SVC_DATE as FromDate 37 , . as ThruDate 38 , CD.PROCEDURE_CODE as CPT 39 , ' ' as AltCPT 40 , CH.PLAN_CODE as PlanCode 41 , CD.HCPCS_MODIFIER_1 as ModCode 42 , CD.HCPCS_MODIFIER_2 as ModCode2 43 , CD.HCPCS_MODIFIER_3 as ModCode3 44 , CH.DIAGNOSIS_1 as Diag1 45 , CH.DIAGNOSIS_2 as Diag2 46 , CH.DIAGNOSIS_3 as Diag3 47 , ' ' as BillClass 48 , ' ' as PatientStatus 49 , CH.PLACE_OF_SERVICE_1 as Location 50 , CH.TOTAL_BILLED_AMT as HeaderBilledAmount 51 , CH.TOTAL_NET_AMT as TotalNetAmount 52 , CH.DATE_RECEIVED 53 , CH.INSERT_DATETIME 54 , CD.BILLED_AMT as LineBilledAmount 55 , CD.PRICE_SCHEDULE as PriceScheduleAmount 56 , CD.NET_AMT as ClaimLinePaidAmount 57 , CD.PAID_NET_AMT as ClaimLineAmtPaidwithInt 2 The SAS System 14:42 Wednesday, September 11, 2019 58 , CD.CLAIM_STATUS as ClaimStatus 59 , CD.PROCESSING_STATUS as ProcessingStatus 60 , CD.POST_DATE as PostDate 61 , CD.CHECK_DATE as CheckDate 62 , CD.ALLOWED_AMT as AllowedAmount 63 , CD.QUANTITY as Quantity 64 , CD.NOT_COVERED_AMT as NotcoveredAmount 65 , CD.NOT_COV_QTY as NotCoveredQuantity 66 , CD.NDC_CODE 67 , MM.SEQ_MEMB_ID as MemberID 68 , MM.LAST_NAME as MemberLastName 69 , MM.FIRST_NAME as MemberFirstName 70 , MM.DATE_OF_BIRTH as MemberDOB 71 , MM.GENDER as M_F 72 , MM.MEDICAID_NO as MedicaidID 73 , PM.SEQ_PROV_ID as ProviderID 74 , PM.LAST_NAME as PROVIDERLASTNAME 75 , PM.FIRST_NAME as PROVIDERFIRSTNAME 76 , CH.PROVIDER_NPI as NATIONAL_PROVIDER_ID 77 , CH.PROVIDER_PAR_STAT as ParStatus 78 , CH.PROVIDER_TYPE as ProviderType 79 , CH.PROVIDER_SPEC as ProviderSpecialty 80 , CH.PROVIDER_PCP_FLAG as PCPFlag 81 , VM.SEQ_VEND_ID as VendorID 82 , VM.IRS_TAX_ID as VendorTaxID 83 , VM.FULL_NAME as VendorFullName 84 , CD.ALLOWED_REASON as AllowedReason 85 , CD.NOT_COVERED_REASON as NotCoveredReason 86 , CD.HOLD_REASON_1 87 , CD.HOLD_REASON_2 88 , CD.HOLD_REASON_3 89 , CH.AUTH_NUMBER 90 , CH.AUTH_TYPE 91 , CH.LINE_OF_BUSINESS as LOB 92 , 'P' as ClaimType 93 FROM 94 HSDREPT.PROFSVC_CLAIM_HEADER CH, 95 HSDREPT.PROFSVC_CLAIM_DETAIL CD, 96 HSDREPT.PROV_MASTER PM, 97 HSDREPT.MEMBER_MASTER MM, 98 HSDREPT.VENDOR_MASTER VM 99 WHERE 100 CH.SEQ_CLAIM_ID = CD.SEQ_CLAIM_ID 101 AND CH.SEQ_MEMB_ID = MM.SEQ_MEMB_ID 102 AND CH.SEQ_PROV_ID = PM.SEQ_PROV_ID 103 AND CH.SEQ_VEND_ID = VM.SEQ_VEND_ID 104 AND CD.POST_DATE >= '1JUL2019:0:0:0'dt 105 AND CH.LINE_OF_BUSINESS = 'NED' 106 /*and &beg <= datepart(CD.POST_DATE) <= &end*/ 107 108 UNION ALL 109 SELECT DISTINCT 110 CH.CLAIM_NUMBER as ClaimID 111 , CH.PATIENT_CONTROL_NO as PCN 112 , CD.LINE_NUMBER as ClaimLine 113 , CD.SUB_LINE_CODE as LineCode 114 , CH.STATE_UNLABELED_3 AS WCN 115 , CD.DETAIL_SVC_DATE as FROMDATE 3 The SAS System 14:42 Wednesday, September 11, 2019 116 , CD.DETAIL_THRU_DATE as THRUDATE 117 , CD.PROCEDURE_CODE as CPT 118 , CD.ALTERNATE_PROC_CODE as AltCPT 119 , CH.PLAN_CODE as PlanCode 120 , CD.PROCEDURE_MODIFIER as ModCode 121 , CD.PROCEDURE_MODIFIER_2 as ModCode2 122 , CD.PROCEDURE_MODIFIER_3 as ModCode3 123 , CH.DIAGNOSIS_1 as Diag1 124 , CH.DIAGNOSIS_2 as Diag2 125 , CH.DIAGNOSIS_3 as Diag3 126 , CH.BILL_TYPE as BillClass 127 , CH.PATIENT_STATUS as PatientStatus 128 , CH.PLACE_OF_SERVICE_1 as Location 129 , CH.TOTAL_BILLED_AMT as HeaderBilledAmount 130 , CH.TOTAL_NET_AMT as TotalNetAmount 131 , CH.DATE_RECEIVED 132 , CH.INSERT_DATETIME 133 , CD.BILLED_AMT as LineBilledAmount 134 , CD.PRICE_SCHEDULE as PriceScheduleAmount 135 , CD.NET_AMT as ClaimLinePaidAmount 136 , CD.PAID_NET_AMT as ClaimLineAmtPaidwithInt 137 , CD.CLAIM_STATUS as ClaimStatus 138 , CD.PROCESSING_STATUS as ProcessingStatus 139 , CD.POST_DATE as PostDate 140 , CD.CHECK_DATE as CheckDate 141 , CD.ALLOWED_AMT as AllowedAmount 142 , CD.PRICE_SCHEDULE as PriceScheduleAmount 143 , CD.QUANTITY as Quantity 144 , CD.NOT_COVERED_AMT as NotcoveredAmount 145 , CD.NOT_COV_QTY as NotCoveredQuantity 146 , CD.NDC_CODE 147 , MM.SEQ_MEMB_ID as MemberID 148 , MM.LAST_NAME as MemberLastName 149 , MM.FIRST_NAME as MemberFirstName 150 , MM.DATE_OF_BIRTH as MemberDOB 151 , MM.GENDER as M_F 152 , MM.MEDICAID_NO as MedicaidID 153 , PM.SEQ_PROV_ID as ProviderID 154 , PM.LAST_NAME as PROVIDERLASTNAME 155 , PM.FIRST_NAME as PROVIDERFIRSTNAME 156 , CH.INST_PROV_NPI as NATIONAL_PROVIDER_ID 157 , CH.PROVIDER_PAR_STAT as ParStatus 158 , CH.PROVIDER_TYPE as ProviderType 159 , CH.PROVIDER_SPEC as ProviderSpecialty 160 , CH.PROVIDER_PCP_FLAG as PCPFlag 161 , VM.SEQ_VEND_ID as VendorID 162 , VM.IRS_TAX_ID as VendorTaxID 163 , VM.FULL_NAME as VendorFullName 164 , CD.ALLOWED_REASON as AllowedReason 165 , CD.NOT_COVERED_REASON as NotCoveredReason 166 , CD.HOLD_REASON_1 167 , CD.HOLD_REASON_2 168 , CD.HOLD_REASON_3 169 , CH.AUTH_NUMBER 170 , CH.AUTH_TYPE 171 , CH.LINE_OF_BUSINESS as LOB 172 , 'I' as ClaimType 173 FROM 4 The SAS System 14:42 Wednesday, September 11, 2019 174 HSDREPT.INST_CLAIM_HEADER CH, 175 HSDREPT.INST_CLAIM_DETAIL CD, 176 HSDREPT.PROV_MASTER PM, 177 HSDREPT.MEMBER_MASTER MM, 178 HSDREPT.VENDOR_MASTER VM 179 WHERE 180 CH.SEQ_CLAIM_ID = CD.SEQ_CLAIM_ID 181 AND CH.SEQ_MEMB_ID = MM.SEQ_MEMB_ID 182 AND CH.SEQ_PROV_ID = PM.SEQ_PROV_ID 183 AND CH.SEQ_VEND_ID = VM.SEQ_VEND_ID 184 AND CD.POST_DATE >= '1JUL2019:0:0:0'dt 185 AND CH.LINE_OF_BUSINESS = 'NED' 186 ); WARNING: A table has been extended with null columns to perform the UNION ALL set operation. ERROR: Column 33 from the first contributor of UNION ALL is not the same type as its counterpart from the second. ERROR: Column 36 from the first contributor of UNION ALL is not the same type as its counterpart from the second. ERROR: Numeric expression requires a numeric format. ERROR: Column 37 from the first contributor of UNION ALL is not the same type as its counterpart from the second. ERROR: Column 38 from the first contributor of UNION ALL is not the same type as its counterpart from the second. ERROR: Column 40 from the first contributor of UNION ALL is not the same type as its counterpart from the second. ERROR: Column 41 from the first contributor of UNION ALL is not the same type as its counterpart from the second. ERROR: Column 43 from the first contributor of UNION ALL is not the same type as its counterpart from the second. ERROR: Column 44 from the first contributor of UNION ALL is not the same type as its counterpart from the second. ERROR: Column 51 from the first contributor of UNION ALL is not the same type as its counterpart from the second. ERROR: Column 52 from the first contributor of UNION ALL is not the same type as its counterpart from the second. ERROR: Column 59 from the first contributor of UNION ALL is not the same type as its counterpart from the second. ERROR: Column 60 from the first contributor of UNION ALL is not the same type as its counterpart from the second. ERROR: Numeric expression requires a numeric format. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 187 /*and &beg <= datepart(CD.POST_DATE) <= &end);*/ 188 /*ORDER BY claimid claimline;*/ 189 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.10 seconds cpu time 0.05 seconds 190 run; 191 192 DATA claims1; 193 SET claims; ERROR: File WORK.CLAIMS.DATA does not exist. 194 format FromDate THRUDATE PostDate CheckDate MemberDOB DATE_RECEIVED INSERT_DATETIME 195 datetime.; 196 RUN; NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.CLAIMS1 may be incomplete. When this step was stopped there were 0 observations and 7 variables. WARNING: Data set WORK.CLAIMS1 was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 0.06 seconds cpu time 0.00 seconds 197 198 5 The SAS System 14:42 Wednesday, September 11, 2019 199 200 201 GOPTIONS NOACCESSIBLE; 202 %LET _CLIENTTASKLABEL=; 203 %LET _CLIENTPROCESSFLOWNAME=; 204 %LET _CLIENTPROJECTPATH=; 205 %LET _CLIENTPROJECTNAME=; 206 %LET _SASPROGRAMFILE=; 207 208 ;*';*";*/;quit;run; 209 ODS _ALL_ CLOSE; 210 211 212 QUIT; RUN; 213 ... View more

Any help on why this error 73-322 is generating? _ proc sql noprint; 121 create table MBR as 122 select a.*, b.description as PROV_SPECIALTY_DESCRIP 123 from MBR a left join sasave.prov_type_master_map to_prov_spe b _ 73 76 ERROR 73-322: Expecting an ON. ERROR 76-322: Syntax error, statement will be ignored. 124 on a.prov_specialty_type = b.type_or_spec_code; NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 125 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.00 seconds 126 run; 127 128 data MBR(drop=PROV_SPECIALTY_DESCRIP); 129 set MBR; 130 131 description=PROV_SPECIALTY_DESCRIP; 132 run; 76 ... View more

I'm query for specific case statements regarding 389 and 391. after running the code, the table is created but I'm not seeing the records with missing values in the MP.PHNUMBER or MPH.PHNUMBER in my output table. Is it possible to do another left join using same left join that I have below to force the records to get pull. There are missing records from MM.MEDICAID_NO. MP.PHNUMBER is character field. If trying missing () what would I put in the parenthesis as my argument proc sql create table NED_Members 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 NED_HOMEPHONE , MA.MOBILE_PHONE AS NED_MOBILE_PHOME , 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 ,MP.ATTRRECNO AS ATTRRECNO ,MPH.ATTRRECNO AS ATTRRECNO2 ,MID.IDNUMBER AS SUBSCRIBER_NO ,MP.PHNUMBER AS NEHII_HOME_PHONE ,MPH.PHNUMBER AS NEHII_MOBILE_PHONE ,MP.MEMRECNO AS MEMRECNO1 ,MPH.MEMRECNO AS MEMRECNO2 ,CASE WHEN (MP.ATTRRECNO = 389) THEN MP.PHNUMBER ELSE '' END AS NEHII_HOME_PHONE, CASE WHEN (MPH.ATTRRECNO = 391) THEN MPH.PHNUMBER ELSE'' END AS NEHII_MOBILE_PHONE ,MAD.STLINE1 AS NEHII_STREET_ADDRESS1 ,MAD.CITY AS NEHII_CITY ,MAD.STATE AS NEHII_STATE ,MAD.COUNTY AS NEHII_COUNTY ,MAD.ZIPCODE AS NEHII_ZIPCODE 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 LEFT JOIN MDM.MPI_MEMIDENTWC AS MID on MM.MEDICAID_NO = MID.IDNUMBER INNER JOIN MDM.MPI_MEMADDRWC as MAD on MAD.MEMRECNO = MID.MEMRECNO INNER JOIN MDM.MPI_MEMPHONEWC as MP on MAD.MEMRECNO = MP.MEMRECNO INNER JOIN MDM.MPI_MEMPHONEWC AS MPH on MP.MEMRECNO = MPH.MEMRECNO WHERE MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID AND MA.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID AND MEH.LINE_OF_BUSINESS = 'NED' AND MEH.TERM_DATE IS NULL AND MA.ADDRESS_TYPE = 'R5' AND MA.TERM_DATE IS NULL AND MP.ATTRRECNO = 389 AND MPH.ATTRRECNO = 391 AND MP.MEMRECNO = MAD.MEMRECNO AND MAD.ATTRRECNO = 390; quit; run; ... View more

I'm aiming for a result set where I'll see a column a in the MDM.MPI_MEMPHONEWC Table two times. Therefore, I've put in my code an inner join on that table that joins the tables to itself. My question is is what I've got below follow true to what I'm trying to accomplish with MDM.MPI.MEMPHONEWC table. NI'm not looking for replies to know what my table column values are. I'm looking for just an overall code review oof does it make sense. 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 LEFT JOIN MDM.MPI_MEMIDENTWC AS MID on MM.SUBSCRIBER_ID = MID.IDNUMBER inner join MDM.MPI_MEMADDRWC as MAD on MAD.MEMRECNO = MID.MEMRECNO inner join MDM.MPI_MEMPHONEWC as MP on MAD.MEMRECNO = MP.MEMRECNO inner join MDM.MPI_MEMPHONEWC2 AS MPH on MP.MEMRECNO = MPH.MEMRECNO ... View more

1. I should be getting back around  the 78885 records  in my final result set (see my left join statement) .  I'm only getting the 55981 records back.  Why is this   2)  Also, why is my union all not creating a final output with both a column called NEHII_Home PHone and a column called NEHII_Mobile _Phone 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL='Program'; 4 %LET _CLIENTPROCESSFLOWNAME='Process Flow'; 5 %LET _CLIENTPROJECTPATH='C:\Users\PSTENNIS\Documents\ALL\NED_NEHII_GOOD_ONE_06232019.egp'; 6 %LET _CLIENTPROJECTNAME='NED_NEHII_GOOD_ONE_06232019.egp'; 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 option obs=max; 25 26 proc sql noprint /*INOBS=100*/; 27 create table NED_Members as 28 Select * From 29 (SELECT DISTINCT 30 MM.SEQ_MEMB_ID as MemberID 31 , MM.LAST_NAME as MemberLastName 32 , MM.FIRST_NAME as MemberFirstName 33 , MM.DATE_OF_BIRTH as MemberDOB 34 , MM.GENDER as M_F 35 , MM.MEDICAID_NO as MedicaidID 36 , MM.ETHNICITY_CODE 37 , MM.USER_DATE_7 38 , meh.line_of_business as LOB 39 , meh.SEQ_PROV_ID 40 , meh.IPA_ID 41 , MA.ADDRESS_LINE_1 AS ADDRESS1 42 , MA.ADDRESS_LINE_2 AS ADDRESS2 43 , MA.CITY AS CITY 44 , MA.STATE AS STATE 45 , MA.ZIP_CODE AS ZIP 46 , MA.COUNTY AS COUNTY 47 , MA.HOME_PHONE_NUMBER AS NED_Homephone 48 , MA.MOBILE_PHONE AS NED_Mobile_Phone 49 , MEH.EFFECTIVE_DATE 50 , MEH.TERM_DATE 51 , MEH.PLAN_CODE 52 , PM.PROVIDER_ID 2 The SAS System 23:03 Sunday, June 23, 2019 53 ,PM.LAST_NAME AS PROVIDER_LAST_NAME 54 ,PM.FIRST_NAME AS PROVIDER_FIRST_NAME 55 ,PM.PROVIDER_TYPE 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.RECSTAT AS RECSTAT 62 ,MP.PHNUMBER AS NEHII_HOME_PHONE 63 ,MP.MEMRECNO AS MEMRECNO 64 ,MAD.STLINE1 AS NEHII_STREET_ADDRESS1 65 ,MAD.CITY AS NEHII_CITY 66 ,MAD.STATE AS NEHII_STATE 67 ,MAD.COUNTY AS NEHII_COUNTY 68 ,MAD.ZIPCODE AS NEHII_ZIPCODE 69 FROM 70 HSDREPT.MEMBER_ADDRESS as MA 71 inner join HSDREPT.MEMBER_MASTER as MM 72 on MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID 73 inner join HSDREPT.member_elig_history as meh 74 on MM.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID 75 inner join HSDREPT.PROV_MASTER as PM 76 on PM.SEQ_PROV_ID = MEH.SEQ_PROV_ID 77 inner join HSDREPT.PROV_ADDRESS as PA 78 on PM.SEQ_PROV_ID = PA.SEQ_PROV_ID 79 LEFT JOIN MDM.MPI_MEMIDENTWC AS MI 80 on MM.MEDICAID_NO = MI.IDNUMBER 81 inner join MDM.MPI_MEMADDRWC as MAD 82 on MAD.MEMRECNO = MI.MEMRECNO 83 inner join MDM.MPI_MEMPHONEWC as MP 84 on MAD.MEMRECNO = MP.MEMRECNO 85 86 WHERE 87 MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID 88 AND MA.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID 89 AND MEH.LINE_OF_BUSINESS = 'NED' 90 AND MEH.TERM_DATE IS NULL 91 AND MA.ADDRESS_TYPE = 'R5' 92 AND MA.TERM_DATE IS NULL 93 AND MI.MEMRECNO = MP.MEMRECNO AND MP.ATTRRECNO = 389 94 AND MP.MEMRECNO = MAD.MEMRECNO AND MAD.ATTRRECNO = 390 95 96 UNION ALL 97 98 SELECT DISTINCT 99 MM.SEQ_MEMB_ID as MemberID 100 , MM.LAST_NAME as MemberLastName 101 , MM.FIRST_NAME as MemberFirstName 102 , MM.DATE_OF_BIRTH as MemberDOB 103 , MM.GENDER as M_F 104 , MM.MEDICAID_NO as MedicaidID 105 , MM.ETHNICITY_CODE 106 , MM.USER_DATE_7 107 , meh.line_of_business as LOB 108 , meh.SEQ_PROV_ID 109 , meh.IPA_ID 110 , MA.ADDRESS_LINE_1 AS ADDRESS1 3 The SAS System 23:03 Sunday, June 23, 2019 111 , MA.ADDRESS_LINE_2 AS ADDRESS2 112 , MA.CITY AS CITY 113 , MA.STATE AS STATE 114 , MA.ZIP_CODE AS ZIP 115 , MA.COUNTY AS COUNTY 116 , MA.HOME_PHONE_NUMBER AS NED_Homephone 117 , MA.MOBILE_PHONE AS NED_Mobile_Phone 118 , MEH.EFFECTIVE_DATE 119 , MEH.TERM_DATE 120 , MEH.PLAN_CODE 121 , PM.PROVIDER_ID 122 ,PM.LAST_NAME AS PROVIDER_LAST_NAME 123 ,PM.FIRST_NAME AS PROVIDER_FIRST_NAME 124 ,PM.PROVIDER_TYPE 125 ,PM.LICENSE 126 ,PA.COUNTY AS PROV_COUNTY 127 ,PA.ADDR_CATEGORY_CODE 128 ,MI.IDNUMBER AS SUBSCRIBER_ID 129 ,MP.ATTRRECNO AS ATTRRECNO 130 ,MP.RECSTAT AS RECSTAT 131 ,MP.PHNUMBER AS NEHII_MOBILE_PHONE 132 ,MP.MEMRECNO AS MEMRECNO 133 ,MAD.STLINE1 AS NEHII_STREET_ADDRESS1 134 ,MAD.CITY AS NEHII_CITY 135 ,MAD.STATE AS NEHII_STATE 136 ,MAD.COUNTY AS NEHII_COUNTY 137 ,MAD.ZIPCODE AS NEHII_ZIPCODE 138 FROM 139 HSDREPT.MEMBER_ADDRESS as MA 140 inner join HSDREPT.MEMBER_MASTER as MM 141 on MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID 142 inner join HSDREPT.member_elig_history as meh 143 on MM.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID 144 inner join HSDREPT.PROV_MASTER as PM 145 on PM.SEQ_PROV_ID = MEH.SEQ_PROV_ID 146 inner join HSDREPT.PROV_ADDRESS as PA 147 on PM.SEQ_PROV_ID = PA.SEQ_PROV_ID 148 LEFT JOIN MDM.MPI_MEMIDENTWC AS MI 149 on MM.MEDICAID_NO = MI.IDNUMBER 150 inner join MDM.MPI_MEMADDRWC as MAD 151 on MAD.MEMRECNO = MI.MEMRECNO 152 inner join MDM.MPI_MEMPHONEWC as MP 153 on MAD.MEMRECNO = MP.MEMRECNO 154 155 WHERE 156 MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID 157 AND MA.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID 158 AND MEH.LINE_OF_BUSINESS = 'NED' 159 AND MEH.TERM_DATE IS NULL 160 AND MA.ADDRESS_TYPE = 'R5' 161 AND MA.TERM_DATE IS NULL 162 AND MI.MEMRECNO = MP.MEMRECNO AND MP.ATTRRECNO = 391 163 AND MP.MEMRECNO = MAD.MEMRECNO AND MAD.ATTRRECNO = 390 164 ); NOTE: Table WORK.NED_MEMBERS created, with 78845 rows and 39 columns. 165 quit; NOTE: PROCEDURE SQL used (Total process time): 4 The SAS System 23:03 Sunday, June 23, 2019 real time 11:08.03 cpu time 6:20.71 166 run; 167 168 proc sort data = NED_Members NODUPKEY; 169 by MemberID; 170 171 GOPTIONS NOACCESSIBLE; 172 %LET _CLIENTTASKLABEL=; 173 %LET _CLIENTPROCESSFLOWNAME=; 174 %LET _CLIENTPROJECTPATH=; 175 %LET _CLIENTPROJECTNAME=; 176 %LET _SASPROGRAMFILE=; 177 178 ;*';*";*/;quit; NOTE: There were 78845 observations read from the data set WORK.NED_MEMBERS. NOTE: 22864 observations with duplicate key values were deleted. NOTE: The data set WORK.NED_MEMBERS has 55981 observations and 39 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.27 seconds cpu time 0.12 seconds 178 ! run; 179 ODS _ALL_ CLOSE; 180 181 182 QUIT; RUN; 183     ... View more

Any help with 1) why I'm not getting all expected records returned in my final result set.  Should be around 78   total records.  I need all records from the MEMBER MASTER TABLE -VARIABLE (MM.MEMB_SEQ_ID) as indicated by my left join.     2)  My desired result in doing UNION ALL is to have a final result data set that contains both a NEHII_Home_Phone and a NEHII _Mobile-Phone columns.  I'm only getting the NEHII_Home_Phone back in the final table.   thanks   option obs=max; proc sql noprint /*INOBS=100*/; create table NED_Members as Select * From (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 NED_Homephone , MA.MOBILE_PHONE AS NED_Mobile_Phone , 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.RECSTAT AS RECSTAT ,MP.PHNUMBER AS NEHII_HOME_PHONE ,MP.MEMRECNO AS MEMRECNO ,MAD.STLINE1 AS NEHII_STREET_ADDRESS1 ,MAD.CITY AS NEHII_CITY ,MAD.STATE AS NEHII_STATE ,MAD.COUNTY AS NEHII_COUNTY ,MAD.ZIPCODE AS NEHII_ZIPCODE 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 LEFT JOIN MDM.MPI_MEMIDENTWC AS MI on MM.MEDICAID_NO = MI.IDNUMBER inner join MDM.MPI_MEMADDRWC as MAD on MAD.MEMRECNO = MI.MEMRECNO inner join MDM.MPI_MEMPHONEWC as MP on MAD.MEMRECNO = MP.MEMRECNO WHERE MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID AND MA.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID AND MEH.LINE_OF_BUSINESS = 'NED' AND MEH.TERM_DATE IS NULL AND MA.ADDRESS_TYPE = 'R5' AND MA.TERM_DATE IS NULL AND MI.MEMRECNO = MP.MEMRECNO AND MP.ATTRRECNO = 389 AND MP.MEMRECNO = MAD.MEMRECNO AND MAD.ATTRRECNO = 390; quit; run; proc sort data = NED_Members NODUPKEY; by MemberID; ... View more