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;
And like your other almost identical post you are using
proc sort data = NED_Members NODUPKEY; by MemberID;
So, how many records are removed by the Proc Sort?
No way to tell without seeing the data (and having it available in easily usable form - data steps with datalines - for testing).
As I mentioned in your other post, it boils down to Maxim 3.
And like your other almost identical post you are using
proc sort data = NED_Members NODUPKEY; by MemberID;
So, how many records are removed by the Proc Sort?
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.