1 The SAS System 11:24 Friday, June 21, 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 create table NED_Members as
26
27 SELECT DISTINCT
28 MM.SEQ_MEMB_ID as MemberID
29 , MM.LAST_NAME as MemberLastName
30 , MM.FIRST_NAME as MemberFirstName
31 , MM.DATE_OF_BIRTH as MemberDOB
32 , MM.GENDER as M_F
33 , MM.MEDICAID_NO as MedicaidID
34 , MM.ETHNICITY_CODE
35 , MM.USER_DATE_7
36 , meh.line_of_business as LOB
37 , meh.SEQ_PROV_ID
38 , meh.IPA_ID
39 , MA.ADDRESS_LINE_1 AS ADDRESS1
40 , MA.ADDRESS_LINE_2 AS ADDRESS2
41 , MA.CITY AS CITY
42 , MA.STATE AS STATE
43 , MA.ZIP_CODE AS ZIP
44 , MA.COUNTY AS COUNTY
45 , MA.HOME_PHONE_NUMBER AS NED_HOMEPHONE
46 , MEH.EFFECTIVE_DATE
47 , MEH.TERM_DATE
48 , MEH.PLAN_CODE
49 , PM.PROVIDER_ID
50 ,PM.LAST_NAME AS PROVIDER_LAST_NAME
51 ,PM.FIRST_NAME AS PROVIDER_FIRST_NAME
52 ,PM.PROVIDER_TYPE
53 ,PM.LICENSE
54 ,PA.COUNTY AS PROV_COUNTY
55 ,PA.ADDR_CATEGORY_CODE
2 The SAS System 11:24 Friday, June 21, 2019
56 ,MI.IDNUMBER AS SUBSCRIBER_ID
57 ,MP.ATTRRECNO AS ATTRRECNO
58 ,MP.RECSTAT AS RECSTAT
59 ,MP.MEMRECNO AS MEMRECNO
60 ,MP.PHNUMBER AS NEHII_PHNUMBER
61 ,MAD.STLINE1 AS NEHII_STREET_ADDRESS1
62 ,MAD.CITY AS NEHII_CITY
63 ,MAD.STATE AS NEHII_STATE
64 ,MAD.COUNTY AS NEHII_COUNTY
65 ,MAD.ZIPCODE AS NEHII_ZIPCODE
66
67
68 FROM
69 HSDREPT.MEMBER_ADDRESS as MA
70 inner join HSDREPT.MEMBER_MASTER as MM
71 on MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID
72 inner join HSDREPT.member_elig_history as meh
73 on MM.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID
74 inner join HSDREPT.PROV_MASTER as PM
75 on PM.SEQ_PROV_ID = MEH.SEQ_PROV_ID
76 inner join HSDREPT.PROV_ADDRESS as PA
77 on PM.SEQ_PROV_ID = PA.SEQ_PROV_ID
78 inner join MDM.MPI_MEMADDRWC as MAD
79 on MAD.MEMRECNO = MI.MEMRECNO
80 inner join MDM.MPI_MEMPHONEWC as MP
81 on MAD.MEMRECNO = MP.MEMRECNO
82 LEFT JOIN MDM.MPI_MEMIDENTWC AS MI
83 on MM.MEDICAID_NO = MI.IDNUMBER
84
85 WHERE
86 MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID
87 AND MA.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID
88 AND MEH.LINE_OF_BUSINESS = 'NED'
89 AND MEH.TERM_DATE IS NULL
90 AND MA.ADDRESS_TYPE = 'R5'
91 AND MA.TERM_DATE IS NULL
92 AND MI.MEMRECNO = MP.MEMRECNO AND MP.ATTRRECNO IN (389,391)
93 AND MP.MEMRECNO = MAD.MEMRECNO AND MAD.ATTRRECNO = 390;
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.
94
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.00 seconds
95 proc sort data = NED_Members NODUPKEY;
ERROR: File WORK.NED_MEMBERS.DATA does not exist.
96 by MemberID;
97
98
99
100
101 GOPTIONS NOACCESSIBLE;
102 %LET _CLIENTTASKLABEL=;
103 %LET _CLIENTPROCESSFLOWNAME=;
104 %LET _CLIENTPROJECTPATH=;
3 The SAS System 11:24 Friday, June 21, 2019
105 %LET _CLIENTPROJECTNAME=;
106 %LET _SASPROGRAMFILE=;
107
108 ;*';*";*/;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
108 ! run;
109 ODS _ALL_ CLOSE;
110
111
112 QUIT; RUN;
113
