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 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
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
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.
PS the union all is, of course, working as documented. What is "not working" here is your join design.
@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
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
;
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
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.