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;
You do not need the CASE's, as you already select such records only in the WHERE.
Sorry you are rather looking for NULL and not MISSING. So rather try "NOT ISNULL(ANY) and".
You do not need the CASE's, as you already select such records only in the WHERE.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.