SQL UNION operator

req41273 — Mon, 24 Jun 2019 04:40:19 GMT

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;

Re: SQL UNION operator

koyelghosh — Mon, 24 Jun 2019 05:51:29 GMT

I don't have access to the data. So I can not tell for sure but PM and MI are reserved words while MAD is an inbuilt function. Did you try substituting PM with PM_1, MI with MI_1 and MAD with MAD_1 .. or anything of your choice. The idea is to avoid reserved words.
Please let me know if it works.
Thank you

Re: SQL UNION operator

Kurt_Bremser — Mon, 24 Jun 2019 06:48:46 GMT

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.

Re: SQL UNION operator

ballardw — Mon, 24 Jun 2019 17:36:39 GMT

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?

topic Re: SQL UNION operator in SAS Programming

SQL UNION operator

Re: SQL UNION operator

Re: SQL UNION operator

Re: SQL UNION operator