creating table but not including null values in case statement proc sql

req41273 — Thu, 27 Jun 2019 03:57:26 GMT

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;

Re: creating table but not including null values in case statement proc sql

koyelghosh — Thu, 27 Jun 2019 04:21:33 GMT

Away from desktop. So I have not tested the code. What if you add "NOT MISSING(ANY) and" just after the WHERE statement. This is just a guess. May or may not work.

Re: creating table but not including null values in case statement proc sql

koyelghosh — Thu, 27 Jun 2019 04:26:44 GMT

Sorry you are rather looking for NULL and not MISSING. So rather try "NOT ISNULL(ANY) and".

Re: creating table but not including null values in case statement proc sql

Kurt_Bremser — Thu, 27 Jun 2019 06:50:26 GMT

You do not need the CASE's, as you already select such records only in the WHERE.

topic Re: creating table but not including null values in case statement proc sql in SAS Programming

creating table but not including null values in case statement proc sql

Re: creating table but not including null values in case statement proc sql

Re: creating table but not including null values in case statement proc sql

Re: creating table but not including null values in case statement proc sql