I need to do inner joins now of all my where conditions in the code below. This is due to the fact I need to do a left join of tables after all my where inner join conditions are processed.
Please see my code and let me know how to change this properly using proc sql in my sas program.
Proc sql noprint /*INOBS=100*/;
Create table NED_MEMBERS_OUTREACH _W_NEHII 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 HOMEPHONE
, 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.ATTRRRECNO AS ATTRRECNO1
,MP.RECSTAT AS RECSTAT
,MP.MEMRECNO AS MEMRECNO
,MP.PHNUMBER LABEL = "NEHII_HOME_PHONE" AS NEHII_HOME_PHONE
,MP.PHNUMBER LABEL = "NEHII_MOBILE_PHNUMBER" AS NEHII_MOBILE_PHNUMBER
,MA.STLINE1 AS NEHII_STREET_ADDRESS1
,MA.CITY AS NEHII_CITY
,MA.STATE AS NEHII_STATE
,MA.COUNTY AS NEHII_COUNTY
,MA.ZIPCODE AS NEHII_ZIPCODE
FROM
HSDREPT.MEMBER_ADDRESS MA,
HSDREPT.MEMBER_MASTER MM,
HSDREPT.member_elig_history meh,
HSDREPT.PROV_MASTER PM,
HSDREPT.PROV_ADDRESS PA,
MDM.MPI_MEMPHONEWC MP,
MDM.MPI_MEMADDRWC MA,
MDM.MPI_MEMIDENTWC MI
WHERE
MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID
AND PM.SEQ_PROV_ID = MEH.SEQ_PROV_ID
AND PM.SEQ_PROV_ID = PA.SEQ_PROV_ID
AND MEH.LINE_OF_BUSINESS = 'NED' AND MEH.TERM_DATE IS NULL AND MA.ADDRESS_TYPE = 'R5'
AND MP.ATTRRECNO = 389
AND MP.ATTRRECNO1 = 391
MA.MEMRECNO = MI.MEMRECNO = MP.MEMRECNO
AND MP.MEMRECNO = MA.MEMRECNO AND MA.ATTRRECNO = 390
AND MA.TERM_DATE IS NULL;
SAS is clearing marking the syntax error.
HSDREPT.MEMBER_MASTER.MEDICAID_NO
You refer to variable is SQL code using either the variable name alone (if it is obvious to the compiler which variable that is) or with an alias for a table followed by a period and then the variable name.
So in your code you are either asking for a variable with a period in its name or trying to reference a dataset using an alias that has a period in it. Neither is allowed.
You set up MM as the alias for HSDREPT.MEMBER_MASTER so use that to reference the variable.
mm.MEDICAID_NO
Something like:
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 inner join
MDM.MPI_MEMPHONEWC as MP
on MA.MEMRECNO = MP.MEMRECNO inner join
MDM.MPI_MEMADDRWC as MA
on MA.MEMRECNO = MP.MEMRECNO inner join
MDM.MPI_MEMIDENTWC as MI
on MA.MEMRECNO = MI.MEMRECNO
WHERE
MEH.LINE_OF_BUSINESS = 'NED' AND
MEH.TERM_DATE IS NULL AND
MA.ADDRESS_TYPE = 'R5' AND
MP.ATTRRECNO = 389 AND
MP.ATTRRECNO1 = 391 AND
MA.ATTRRECNO = 390 AND
MA.TERM_DATE IS NULL;
Thanks for replying.
I'm on a good start.
Can you please review my code now and see why I'm getting the syntax errors?
thanks
1 The SAS System 09:51 Thursday, June 6, 2019
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='';
6 %LET _CLIENTPROJECTNAME='';
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 Proc sql noprint /*INOBS=100*/;
25
26
27 Create table NED_Members as
28
29 SELECT DISTINCT
30
31 MM.SEQ_MEMB_ID as MemberID
32 , MM.LAST_NAME as MemberLastName
33 , MM.FIRST_NAME as MemberFirstName
34 , MM.DATE_OF_BIRTH as MemberDOB
35 , MM.GENDER as M_F
36 , MM.MEDICAID_NO as MedicaidID
37 , MM.ETHNICITY_CODE
38 , MM.USER_DATE_7
39 , meh.line_of_business as LOB
40 , meh.SEQ_PROV_ID
41 , meh.IPA_ID
42 , MA.ADDRESS_LINE_1 AS ADDRESS1
43 , MA.ADDRESS_LINE_2 AS ADDRESS2
44 , MA.CITY AS CITY
45 , MA.STATE AS STATE
46 , MA.ZIP_CODE AS ZIP
47 , MA.COUNTY AS COUNTY
48 , MA.HOME_PHONE_NUMBER AS HOMEPHONE
49 , MEH.EFFECTIVE_DATE
50 , MEH.TERM_DATE
51 , MEH.PLAN_CODE
52 , PM.PROVIDER_ID
53 ,PM.LAST_NAME AS PROVIDER_LAST_NAME
54 ,PM.FIRST_NAME AS PROVIDER_FIRST_NAME
55 ,PM.PROVIDER_TYPE
2 The SAS System 09:51 Thursday, June 6, 2019
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.ATTRRRECNO AS ATTRRECNO1
62 ,MP.RECSTAT AS RECSTAT
63 ,MP.MEMRECNO AS MEMRECNO
64 ,MP.PHNUMBER LABEL = "NEHII_HOME_PHONE" AS NEHII_HOME_PHONE
65 ,MP.PHNUMBER LABEL = "NEHII_MOBILE_PHNUMBER" AS NEHII_MOBILE_PHNUMBER
66 ,MA.STLINE1 AS NEHII_STREET_ADDRESS1
67 ,MA.CITY AS NEHII_CITY
68 ,MA.STATE AS NEHII_STATE
69 ,MA.COUNTY AS NEHII_COUNTY
70 ,MA.ZIPCODE AS NEHII_ZIPCODE
71
72
73 FROM
74 HSDREPT.MEMBER_ADDRESS as MA
75 inner join HSDREPT.MEMBER_MASTER as MM
76 on MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID
77 inner join HSDREPT.member_elig_history as meh
78 on MM.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID
79 inner join HSDREPT.PROV_MASTER as PM
80 on PM.SEQ_PROV_ID = MEH.SEQ_PROV_ID
81 inner join HSDREPT.PROV_ADDRESS as PA
82 on PM.SEQ_PROV_ID = PA.SEQ_PROV_ID
83 inner join MDM.MPI_MEMPHONEWC as MP
84 on MA.MEMRECNO = MP.MEMRECNO
85 inner join MDM.MPI_MEMADDRWC as MA
86 on MA.MEMRECNO = MP.MEMRECNO
87 inner join MDM.MPI_MEMIDENTWC as MI
88 on MA.MEMRECNO = MI.MEMRECNO
89 LEFT JOIN MDM.MPI_MEMIDENTWC as MI
90 on HSDREPT.MEMBER_MASTER.MEDICAID_NO = MDM.MPI_MEMIDENTWC.IDNUMBER
_
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND,
ANSIMISS, BETWEEN, CONTAINS, CROSS, EQ, EQT, EXCEPT, FULL, GE, GET, GROUP, GT, GTT, HAVING, IN, INNER, INTERSECT, IS,
JOIN, LE, LEFT, LET, LIKE, LT, LTT, NATURAL, NE, NET, NOMISS, NOT, NOTIN, OR, ORDER, OUTER, RIGHT, UNION, WHERE, ^,
^=, |, ||, ~, ~=.
ERROR 76-322: Syntax error, statement will be ignored.
91
92 WHERE
93 MEH.LINE_OF_BUSINESS = 'NED'
94 AND MEH.TERM_DATE IS NULL
95 AND MA.ADDRESS_TYPE = 'R5'
96 AND MP.ATTRRECNO = 389
97 AND MP.ATTRRECNO1 = 391
98 MA.MEMRECNO = MI.MEMRECNO = MP.MEMRECNO
99 AND MA.ATTRRECNO = 390
100 AND MA.TERM_DATE IS NULL;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
101
102 quit;
3 The SAS System 09:51 Thursday, June 6, 2019
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.06 seconds
cpu time 0.01 seconds
103 run;
104
105 proc sort data = NED_Members NODUPKEY;
ERROR: File WORK.NED_MEMBERS.DATA does not exist.
106 by MemberID;
107
108 GOPTIONS NOACCESSIBLE;
109 %LET _CLIENTTASKLABEL=;
110 %LET _CLIENTPROCESSFLOWNAME=;
111 %LET _CLIENTPROJECTPATH=;
112 %LET _CLIENTPROJECTNAME=;
113 %LET _SASPROGRAMFILE=;
114
115 ;*';*";*/;quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
115 ! run;
116 ODS _ALL_ CLOSE;
117
118
119 QUIT; RUN;
120
SAS is clearing marking the syntax error.
HSDREPT.MEMBER_MASTER.MEDICAID_NO
You refer to variable is SQL code using either the variable name alone (if it is obvious to the compiler which variable that is) or with an alias for a table followed by a period and then the variable name.
So in your code you are either asking for a variable with a period in its name or trying to reference a dataset using an alias that has a period in it. Neither is allowed.
You set up MM as the alias for HSDREPT.MEMBER_MASTER so use that to reference the variable.
mm.MEDICAID_NO
1 The SAS System 09:51 Thursday, June 6, 2019
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='';
6 %LET _CLIENTPROJECTNAME='';
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 Proc sql noprint /*INOBS=100*/;
25
26
27 Create table NED_Members as
28
29 SELECT DISTINCT
30
31 MM.SEQ_MEMB_ID as MemberID
32 , MM.LAST_NAME as MemberLastName
33 , MM.FIRST_NAME as MemberFirstName
34 , MM.DATE_OF_BIRTH as MemberDOB
35 , MM.GENDER as M_F
36 , MM.MEDICAID_NO as MedicaidID
37 , MM.ETHNICITY_CODE
38 , MM.USER_DATE_7
39 , meh.line_of_business as LOB
40 , meh.SEQ_PROV_ID
41 , meh.IPA_ID
42 , MA.ADDRESS_LINE_1 AS ADDRESS1
43 , MA.ADDRESS_LINE_2 AS ADDRESS2
44 , MA.CITY AS CITY
45 , MA.STATE AS STATE
46 , MA.ZIP_CODE AS ZIP
47 , MA.COUNTY AS COUNTY
48 , MA.HOME_PHONE_NUMBER AS HOMEPHONE
49 , MEH.EFFECTIVE_DATE
50 , MEH.TERM_DATE
51 , MEH.PLAN_CODE
52 , PM.PROVIDER_ID
53 ,PM.LAST_NAME AS PROVIDER_LAST_NAME
54 ,PM.FIRST_NAME AS PROVIDER_FIRST_NAME
55 ,PM.PROVIDER_TYPE
2 The SAS System 09:51 Thursday, June 6, 2019
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.ATTRRRECNO AS ATTRRECNO1
62 ,MP.RECSTAT AS RECSTAT
63 ,MP.MEMRECNO AS MEMRECNO
64 ,MP.PHNUMBER LABEL = "NEHII_HOME_PHONE" AS NEHII_HOME_PHONE
65 ,MP.PHNUMBER LABEL = "NEHII_MOBILE_PHNUMBER" AS NEHII_MOBILE_PHNUMBER
66 ,MA.STLINE1 AS NEHII_STREET_ADDRESS1
67 ,MA.CITY AS NEHII_CITY
68 ,MA.STATE AS NEHII_STATE
69 ,MA.COUNTY AS NEHII_COUNTY
70 ,MA.ZIPCODE AS NEHII_ZIPCODE
71
72
73 FROM
74 HSDREPT.MEMBER_ADDRESS as MA
75 inner join HSDREPT.MEMBER_MASTER as MM
76 on MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID
77 inner join HSDREPT.member_elig_history as meh
78 on MM.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID
79 inner join HSDREPT.PROV_MASTER as PM
80 on PM.SEQ_PROV_ID = MEH.SEQ_PROV_ID
81 inner join HSDREPT.PROV_ADDRESS as PA
82 on PM.SEQ_PROV_ID = PA.SEQ_PROV_ID
83 inner join MDM.MPI_MEMPHONEWC as MP
84 on MA.MEMRECNO = MP.MEMRECNO
85 inner join MDM.MPI_MEMADDRWC as MA
86 on MA.MEMRECNO = MP.MEMRECNO
87 inner join MDM.MPI_MEMIDENTWC as MI
88 on MA.MEMRECNO = MI.MEMRECNO
89 LEFT JOIN MDM.MPI_MEMIDENTWC as MI
90 on MM.MEDICAID_NO = MI.IDNUMBER
91
92 WHERE
93 MEH.LINE_OF_BUSINESS = 'NED'
94 AND MEH.TERM_DATE IS NULL
95 AND MA.ADDRESS_TYPE = 'R5'
96 AND MP.ATTRRECNO = 389
97 AND MP.ATTRRECNO1 = 391
98 AND MA.MEMRECNO = MI.MEMRECNO = MP.MEMRECNO
99 AND MA.ATTRRECNO = 390
100 AND MA.TERM_DATE IS NULL;
ERROR: Column MEMRECNO could not be found in the table/view identified with the correlation name MA.
ERROR: Column MI.IDNUMBER was found in more than one table in the same scope.
ERROR: Column MA.CITY was found in more than one table in the same scope.
ERROR: Column MA.STATE was found in more than one table in the same scope.
ERROR: Column MA.COUNTY was found in more than one table in the same scope.
ERROR: Column MI.IDNUMBER was found in more than one table in the same scope.
ERROR: Column ATTRRRECNO could not be found in the table/view identified with the correlation name MP.
ERROR: Column MA.CITY was found in more than one table in the same scope.
ERROR: Column MA.STATE was found in more than one table in the same scope.
ERROR: Column MA.COUNTY was found in more than one table in the same scope.
ERROR: Column ATTRRECNO1 could not be found in the table/view identified with the correlation name MP.
ERROR: Column MI.MEMRECNO was found in more than one table in the same scope.
ERROR: Column MI.MEMRECNO was found in more than one table in the same scope.
3 The SAS System 09:51 Thursday, June 6, 2019
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
101
102 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.09 seconds
cpu time 0.03 seconds
103 run;
104
105 proc sort data = NED_Members NODUPKEY;
ERROR: File WORK.NED_MEMBERS.DATA does not exist.
106 by MemberID;
107
108 GOPTIONS NOACCESSIBLE;
109 %LET _CLIENTTASKLABEL=;
110 %LET _CLIENTPROCESSFLOWNAME=;
111 %LET _CLIENTPROJECTPATH=;
112 %LET _CLIENTPROJECTNAME=;
113 %LET _SASPROGRAMFILE=;
114
115 ;*';*";*/;quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.02 seconds
cpu time 0.00 seconds
115 ! run;
116 ODS _ALL_ CLOSE;
117
118
119 QUIT; RUN;
120
Is there something wrong with my join order or tables?
1 The SAS System 09:51 Thursday, June 6, 2019
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='';
6 %LET _CLIENTPROJECTNAME='';
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 Proc sql noprint /*INOBS=100*/;
25
26
27 Create table NED_Members as
28
29 SELECT DISTINCT
30
31 MM.SEQ_MEMB_ID as MemberID
32 , MM.LAST_NAME as MemberLastName
33 , MM.FIRST_NAME as MemberFirstName
34 , MM.DATE_OF_BIRTH as MemberDOB
35 , MM.GENDER as M_F
36 , MM.MEDICAID_NO as MedicaidID
37 , MM.ETHNICITY_CODE
38 , MM.USER_DATE_7
39 , meh.line_of_business as LOB
40 , meh.SEQ_PROV_ID
41 , meh.IPA_ID
42 , MA.ADDRESS_LINE_1 AS ADDRESS1
43 , MA.ADDRESS_LINE_2 AS ADDRESS2
44 , MA.CITY AS CITY
45 , MA.STATE AS STATE
46 , MA.ZIP_CODE AS ZIP
47 , MA.COUNTY AS COUNTY
48 , MA.HOME_PHONE_NUMBER AS HOMEPHONE
49 , MEH.EFFECTIVE_DATE
50 , MEH.TERM_DATE
51 , MEH.PLAN_CODE
52 , PM.PROVIDER_ID
53 ,PM.LAST_NAME AS PROVIDER_LAST_NAME
54 ,PM.FIRST_NAME AS PROVIDER_FIRST_NAME
55 ,PM.PROVIDER_TYPE
2 The SAS System 09:51 Thursday, June 6, 2019
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.ATTRRRECNO AS ATTRRECNO1
62 ,MP.RECSTAT AS RECSTAT
63 ,MP.MEMRECNO AS MEMRECNO
64 ,MP.PHNUMBER LABEL = "NEHII_HOME_PHONE" AS NEHII_HOME_PHONE
65 ,MP.PHNUMBER LABEL = "NEHII_MOBILE_PHNUMBER" AS NEHII_MOBILE_PHNUMBER
66 ,MA.STLINE1 AS NEHII_STREET_ADDRESS1
67 ,MA.CITY AS NEHII_CITY
68 ,MA.STATE AS NEHII_STATE
69 ,MA.COUNTY AS NEHII_COUNTY
70 ,MA.ZIPCODE AS NEHII_ZIPCODE
71
72
73 FROM
74 HSDREPT.MEMBER_ADDRESS as MA
75 inner join HSDREPT.MEMBER_MASTER as MM
76 on MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID
77 inner join HSDREPT.member_elig_history as meh
78 on MM.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID
79 inner join HSDREPT.PROV_MASTER as PM
80 on PM.SEQ_PROV_ID = MEH.SEQ_PROV_ID
81 inner join HSDREPT.PROV_ADDRESS as PA
82 on PM.SEQ_PROV_ID = PA.SEQ_PROV_ID
83 inner join MDM.MPI_MEMPHONEWC as MP
84 on MA.MEMRECNO = MP.MEMRECNO
85 inner join MDM.MPI_MEMADDRWC as MA
86 on MA.MEMRECNO = MP.MEMRECNO
87 inner join MDM.MPI_MEMIDENTWC as MI
88 on MA.MEMRECNO = MI.MEMRECNO
89 LEFT JOIN MDM.MPI_MEMIDENTWC as MI
90 on MM.MEDICAID_NO = MI.IDNUMBER
91
92 WHERE
93 MEH.LINE_OF_BUSINESS = 'NED'
94 AND MEH.TERM_DATE IS NULL
95 AND MA.ADDRESS_TYPE = 'R5'
96 AND MP.ATTRRECNO = 389
97 AND MP.ATTRRECNO1 = 391
98 AND MA.MEMRECNO = MI.MEMRECNO = MP.MEMRECNO
99 AND MA.ATTRRECNO = 390
100 AND MA.TERM_DATE IS NULL;
ERROR: Column MEMRECNO could not be found in the table/view identified with the correlation name MA.
ERROR: Column MI.IDNUMBER was found in more than one table in the same scope.
ERROR: Column MA.CITY was found in more than one table in the same scope.
ERROR: Column MA.STATE was found in more than one table in the same scope.
ERROR: Column MA.COUNTY was found in more than one table in the same scope.
ERROR: Column MI.IDNUMBER was found in more than one table in the same scope.
ERROR: Column ATTRRRECNO could not be found in the table/view identified with the correlation name MP.
ERROR: Column MA.CITY was found in more than one table in the same scope.
ERROR: Column MA.STATE was found in more than one table in the same scope.
ERROR: Column MA.COUNTY was found in more than one table in the same scope.
ERROR: Column ATTRRECNO1 could not be found in the table/view identified with the correlation name MP.
ERROR: Column MI.MEMRECNO was found in more than one table in the same scope.
ERROR: Column MI.MEMRECNO was found in more than one table in the same scope.
3 The SAS System 09:51 Thursday, June 6, 2019
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
101
102 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.09 seconds
cpu time 0.03 seconds
103 run;
104
105 proc sort data = NED_Members NODUPKEY;
ERROR: File WORK.NED_MEMBERS.DATA does not exist.
106 by MemberID;
107
108 GOPTIONS NOACCESSIBLE;
109 %LET _CLIENTTASKLABEL=;
110 %LET _CLIENTPROCESSFLOWNAME=;
111 %LET _CLIENTPROJECTPATH=;
112 %LET _CLIENTPROJECTNAME=;
113 %LET _SASPROGRAMFILE=;
114
115 ;*';*";*/;quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.02 seconds
cpu time 0.00 seconds
115 ! run;
116 ODS _ALL_ CLOSE;
117
118
119 QUIT; RUN;
120
You have two different types of errors there. Let's just look at one example of each.
ERROR: Column MEMRECNO could not be found in the table/view identified with the correlation name MA. ERROR: Column MI.IDNUMBER was found in more than one table in the same scope.
So the first one is saying that you tried to find the variable MEMRECNO in the dataset that you are referencing as MA.
So check what dataset you assigned the alias of MA to and then check the variables in that dataset. Perhaps you have a typo in your variable name or you are using the wrong alias so you are pointing SAS at the wrong table.
The second is saying that MI.IDNUMBER refers to more than one actual variable. Normally I see that when I use an ambiguous reference by just using the variable name without adding the alias to let SQL know which dataset the variable is in. But the confusing is explained when I looked for which table the alias MI is referencing. So these errors are caused by you trying to use the same alias for multiple datasets. You have two datasets referenced as MA and two referenced as MI. All of the datasets you are using need to have a distinct alias. Even if you are joining a dataset with itself each place it appears needs to have a different alias so it is clear which variable you want to use.
New error message. Any thoughts on why I'm getting this message.
1 The SAS System 09:51 Thursday, June 6, 2019
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='';
6 %LET _CLIENTPROJECTNAME='';
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 Proc sql noprint /*INOBS=100*/;
25
26
27 Create table NED_Members as
28
29 SELECT DISTINCT
30
31 MM.SEQ_MEMB_ID as MemberID
32 , MM.LAST_NAME as MemberLastName
33 , MM.FIRST_NAME as MemberFirstName
34 , MM.DATE_OF_BIRTH as MemberDOB
35 , MM.GENDER as M_F
36 , MM.MEDICAID_NO as MedicaidID
37 , MM.ETHNICITY_CODE
38 , MM.USER_DATE_7
39 , meh.line_of_business as LOB
40 , meh.SEQ_PROV_ID
41 , meh.IPA_ID
42 , MA.ADDRESS_LINE_1 AS ADDRESS1
43 , MA.ADDRESS_LINE_2 AS ADDRESS2
44 , MA.CITY AS CITY
45 , MA.STATE AS STATE
46 , MA.ZIP_CODE AS ZIP
47 , MA.COUNTY AS COUNTY
48 , MA.HOME_PHONE_NUMBER AS HOMEPHONE
49 , MEH.EFFECTIVE_DATE
50 , MEH.TERM_DATE
51 , MEH.PLAN_CODE
52 , PM.PROVIDER_ID
53 ,PM.LAST_NAME AS PROVIDER_LAST_NAME
54 ,PM.FIRST_NAME AS PROVIDER_FIRST_NAME
55 ,PM.PROVIDER_TYPE
2 The SAS System 09:51 Thursday, June 6, 2019
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.ATTRRECNO AS ATTRRECNO1
62 ,MP.RECSTAT AS RECSTAT
63 ,MP.MEMRECNO AS MEMRECNO
64 ,MP.PHNUMBER LABEL = "NEHII_HOME_PHONE" AS NEHII_HOME_PHONE
65 ,MP.PHNUMBER LABEL = "NEHII_MOBILE_PHNUMBER" AS NEHII_MOBILE_PHNUMBER
66 ,MAD.STLINE1 AS NEHII_STREET_ADDRESS1
67 ,MAD.CITY AS NEHII_CITY
68 ,MAD.STATE AS NEHII_STATE
69 ,MAD.COUNTY AS NEHII_COUNTY
70 ,MAD.ZIPCODE AS NEHII_ZIPCODE
71
72
73 FROM
74 HSDREPT.MEMBER_ADDRESS as MA
75 inner join HSDREPT.MEMBER_MASTER as MM
76 on MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID
77 inner join HSDREPT.member_elig_history as meh
78 on MM.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID
79 inner join HSDREPT.PROV_MASTER as PM
80 on PM.SEQ_PROV_ID = MEH.SEQ_PROV_ID
81 inner join HSDREPT.PROV_ADDRESS as PA
82 on PM.SEQ_PROV_ID = PA.SEQ_PROV_ID
83 inner join MDM.MPI_MEMPHONEWC as MP
84 on MAD.MEMRECNO = MP.MEMRECNO
85 inner join MDM.MPI_MEMADDRWC as MAD
86 on MAD.MEMRECNO = MP.MEMRECNO
87 inner join MDM.MPI_MEMIDENTWC as MI
88 on MAD.MEMRECNO = MI.MEMRECNO
89 LEFT JOIN MDM.MPI_MEMIDENTWC AS MID
90 on MM.MEDICAID_NO = MI.IDNUMBER
91
92 WHERE
93 MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID
94 AND MEH.LINE_OF_BUSINESS = 'NED'
95 AND MEH.TERM_DATE IS NULL
96 AND MA.ADDRESS_TYPE = 'R5'
97 AND MA.TERM_DATE IS NULL
98 AND MAD.MEMRECNO = MI.MEMRECNO = MP.MEMRECNO AND MP.ATTRRECNO IN (389,391);
ERROR: Correlated reference to column MEMRECNO is not contained within a subquery.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
99
100
101
102 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.15 seconds
cpu time 0.03 seconds
103 run;
104
3 The SAS System 09:51 Thursday, June 6, 2019
105 proc sort data = NED_Members NODUPKEY;
ERROR: File WORK.NED_MEMBERS.DATA does not exist.
106 by MemberID;
107
108 GOPTIONS NOACCESSIBLE;
109 %LET _CLIENTTASKLABEL=;
110 %LET _CLIENTPROCESSFLOWNAME=;
111 %LET _CLIENTPROJECTPATH=;
112 %LET _CLIENTPROJECTNAME=;
113 %LET _SASPROGRAMFILE=;
114
115 ;*';*";*/;quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.03 seconds
cpu time 0.00 seconds
115 ! run;
116 ODS _ALL_ CLOSE;
117
118
119 QUIT; RUN;
120
thanks.
The join condition:
inner join MDM.MPI_MEMPHONEWC as MP
on MAD.MEMRECNO = MP.MEMRECNO
mentions the alias MAD before defining it.
Thanks,
I'll re-order the join and try that.
By the way, how do I read the inner joins table flow in my code? Meaning is the pattern
from table 1
inner join table 2 on table1.column 1 = table 2.column1, etc.? When it's multiple tables, it's kind of hard to follow for a new beginner.
Start small and build from there.
Your last join also looks suspicious. In
LEFT JOIN MDM.MPI_MEMIDENTWC AS MID
90 on MM.MEDICAID_NO = MI.IDNUMBER
the join condition doesn't refer to table alias MID...
So, how would I define it and when? I tried rearranging the join (see code box)
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
inner join MDM.MPI_MEMADDRWC as MAD
on MAD.MEMRECNO = MP.MEMRECNO
inner join MDM.MPI_MEMPHONEWC as MP
on MAD.MEMRECNO = MP.MEMRECNO
inner join MDM.MPI_MEMIDENTWC as MI
on MAD.MEMRECNO = MI.MEMRECNO
LEFT JOIN MDM.MPI_MEMIDENTWC AS MID
on MM.MEDICAID_NO = MI.IDNUMBER
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.