PROC SQL UNION ALL OPERATOR NOT WORKING

[req41273]

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        

[req41273]

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    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        

[Kurt_Bremser]

Maxim 3: Know Your Data.

Step 1: run your joins separately, and control either for multiple entries per memberid.

Step 2: remove the "all" from the union, in case you get duplicates in the two sub-joins.

Keep in mind that the distinct covers all columns, not just the first; you will have to inspect all single joins to find those that do have a one-to-many relationship instead of a one-to-one.

[Kurt_Bremser]

PS the union all is, of course, working as documented. What is "not working" here is your join design.

[Kurt_Bremser]

AND STOP DOUBLE-POSTING!

[req41273]

Maxims 3?

[Kurt_Bremser · Accepted solution]

---

@req41273 wrote:
Maxims 3?

---

Follow the first link of my footnotes. Or read https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2019/3062-2019.pdf

[koyelghosh]

May be the suggestion at this link will work ... https://communities.sas.com/t5/SAS-Programming/SQL-UNION-operator/m-p/568275

[Tom]

Not sure what you questions are but re the number of observations it looks like you are eliminating a lot of observations by using the NODUPKEY option on the PROC SORT step that follows the join.

I did not check your two queries that closely but you might want to add the CORRESPONDING (abbreviation CORR) keyword to your UNION clause to make sure that you are combing the right columns together between the two sub queries.  Without the CORR option you need to make sure that the variables are selected in the exactly the same order in the two queries that are being joined.

create table test as
select name,age from sashelp.class
union corr all
select age,name from sashelp.class
;

[Reeza]

Read the log again. After the union all you have the correct number of records:

NOTE: Table WORK.NED_MEMBERS created, with 78845 rows and 39 columns.

165        quit;

However, after that step you used a NODUPKEY On the PROC SORT, removing many of the records. So either don't remove the duplicates or determine why you have duplicates and modify your original SQL query.

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

---

@req41273 wrote:

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        

---