Desktop productivity for business analysts and programmers

What am I doing wrong? "IN" operator missing?

Reply
Frequent Contributor
Posts: 104

What am I doing wrong? "IN" operator missing?

    proc sql ;
25            CONNECT TO Teradata as tera (authdomain="TTTTTTTTT" TDPID="edwfsp.fs.ccccccc.com");
26            CREATE TABLE HEDIS.CLMSPULL AS
27                 SELECT * FROM CONNECTION TO tera
28         
29         (SELECT
30         
31                /* CLM_HDR_DMNSN.CLM_BASE_NUM          AS CLAIMBASEID,*//*may need this*/
32                 /*CLM_LINE_FACT.CLM_LINE_NUM          AS CLAIMLINE,*//*may need this*/
33                 /*CLM_HDR_DMNSN.CLM_ADJ_ID            AS CLAIMADJID,*/
34                 CLM_HDR_DMNSN.PTNT_BRTH_DT          AS DOB,
35                 /*CLM_HDR_DMNSN.CLM_BGN_SRVC_DT       AS CLMBEGINDATE,*/
36                 CLNDR_TIME_DMNSN.CLNDR_DATE         AS CLAIMSERVICEDATE,
37                 CLM_HDR_DMNSN.SPNSR_SSN             AS SPONSSN,
38                 CLM_HDR_DMNSN.PTNT_ID_NUM           AS PTNTID,
39         /*	    CLM_HDR_DMNSN.DEERS_UNIQ_PTNT_ID    AS DEERSID, */
40         	    CLM_HDR_DMNSN.DEERS_BENE_ID         AS DEERSBENEID,
41             /*    CLM_HDR_DMNSN.DEERS_FMLY_ID         AS DEERSFMLY,*//*may need this*/
42                 DGNS_DMNSN.DGNS_1_CD                AS DIAG1,
43                 DGNS_DMNSN.DGNS_2_CD                AS DIAG2,
44                 DGNS_DMNSN.DGNS_3_CD                AS DIAG3,
45                 DGNS_DMNSN.DGNS_4_CD                AS DIAG4,
46                 DGNS_DMNSN.DGNS_5_CD                AS DIAG5,
47                 DGNS_DMNSN.DGNS_6_CD                AS DIAG6,
48                 DGNS_DMNSN.DGNS_7_CD                AS DIAG7,
49                 DGNS_DMNSN.DGNS_8_CD                AS DIAG8,
50                 DGNS_DMNSN.DGNS_9_CD                AS DIAG9,
51                 DGNS_DMNSN.DGNS_10_CD               AS DIAG10,
52                 PRCDR_DMNSN.CPT4_PRCDR_CD           AS CPT4CODE,
53                 PRCDR_DMNSN.REV_CD                  AS REV,
54                 /*PRCDR_DMNSN.HCPCS_PRCDR_CD          AS HCPCSCODE,*/
55                 PRCDR_DMNSN.NDC_CD                  AS NDC,
56                 /*PRCDR_DMNSN.DRVD_PRCDR_CD           AS PROCEDURECODE,*/
57                 /*PRCDR_DMNSN.ICD_VRSN_IND            AS ICDIND,*/
2                                                          The SAS System                                 10:49 Friday, May 25, 2018

58                 /*CLM_LINE_FACT.OHI_PD_AMT            AS OHIPAIDAMT,*/
59                 /*PLAN_ADMINR_DMNSN.RSRC_SHRG_IND     AS RESOURCESHARE,
60                 PLAN_ADMINR_DMNSN.DRVD_MCS_OPTN_IND AS MCSOPTIONIND,
61                 PLAN_ADMINR_DMNSN.HOME_PLAN_CD AS PRIMEMTFCIVSTDEXT,*/
62                /* CLM_LINE_FACT.LINE_ALOWD_AMT        AS ALLOWEDAMT,*/
63                /* CLM_LINE_FACT.PD_AMT                AS PDAMT,*/ /*may need this?*/
64         		CLM_DMNSN.POS_CD 					AS POS
65             FROM
66                 CLM_LINE_FACT INNER JOIN DGNS_DMNSN ON
67                   (DGNS_DMNSN.DGNS_DMNSN_ID
68                    =CLM_LINE_FACT.DGNS_DMNSN_ID)
69                 INNER JOIN CLM_HDR_DMNSN ON
70                   (CLM_LINE_FACT.CLM_HDR_ID
71                    =CLM_HDR_DMNSN.CLM_HDR_ID)
72                 INNER JOIN CLNDR_TIME_DMNSN ON
73                   (CLM_LINE_FACT.SRVC_DT_ID
74                    =CLNDR_TIME_DMNSN.CLNDR_TIME_ID)
75                 /*INNER JOIN PLAN_ADMINR_DMNSN ON
76                   (CLM_LINE_FACT.PLAN_ADMINR_ID
77                    =PLAN_ADMINR_DMNSN.PLAN_ADMINR_ID)*/
78                 INNER JOIN PRCDR_DMNSN ON
79                   (CLM_LINE_FACT.PRCDR_DMNSN_ID
80                    =PRCDR_DMNSN.PRCDR_DMNSN_ID)
81             WHERE
82               CLNDR_TIME_DMNSN.CLNDR_DATE BETWEEN '2017-02-01' AND '2017-02-28'
83         	  AND CLM_HDR_DMNSN.PTNT_BRTH_DT BETWEEN  '1953-02-01' and '1999-02-01'
84         	  AND (DGNS_DMNSN.DGNS_1_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_1_CD IN &BIPOLAR OR
WARNING: Apparent symbolic reference SCHIZ not resolved.
WARNING: Apparent symbolic reference BIPOLAR not resolved.
85         			DGNS_DMNSN.DGNS_2_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_2_CD IN &BIPOLAR OR
WARNING: Apparent symbolic reference SCHIZ not resolved.
WARNING: Apparent symbolic reference BIPOLAR not resolved.
86         			DGNS_DMNSN.DGNS_3_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_3_CD IN &BIPOLAR OR
WARNING: Apparent symbolic reference SCHIZ not resolved.
WARNING: Apparent symbolic reference BIPOLAR not resolved.
87         			DGNS_DMNSN.DGNS_4_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_4_CD IN &BIPOLAR OR
WARNING: Apparent symbolic reference SCHIZ not resolved.
WARNING: Apparent symbolic reference BIPOLAR not resolved.
88         			DGNS_DMNSN.DGNS_5_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_5_CD IN &BIPOLAR OR
WARNING: Apparent symbolic reference SCHIZ not resolved.
WARNING: Apparent symbolic reference BIPOLAR not resolved.
89         			DGNS_DMNSN.DGNS_6_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_6_CD IN &BIPOLAR OR
WARNING: Apparent symbolic reference SCHIZ not resolved.
WARNING: Apparent symbolic reference BIPOLAR not resolved.
90         			DGNS_DMNSN.DGNS_7_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_7_CD IN &BIPOLAR OR
WARNING: Apparent symbolic reference SCHIZ not resolved.
WARNING: Apparent symbolic reference BIPOLAR not resolved.
91         			DGNS_DMNSN.DGNS_8_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_8_CD IN &BIPOLAR OR
WARNING: Apparent symbolic reference SCHIZ not resolved.
WARNING: Apparent symbolic reference BIPOLAR not resolved.
92         			DGNS_DMNSN.DGNS_9_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_9_CD IN &BIPOLAR OR
WARNING: Apparent symbolic reference SCHIZ not resolved.
WARNING: Apparent symbolic reference BIPOLAR not resolved.
93         			DGNS_DMNSN.DGNS_10_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_10_CD IN &BIPOLAR OR
WARNING: Apparent symbolic reference SCHIZ not resolved.
WARNING: Apparent symbolic reference BIPOLAR not resolved.
94         		PRCDR_DMNSN.NDC_CD IS NOT NULL)
95             );
3                                                          The SAS System                                 10:49 Friday, May 25, 2018

ERROR: Teradata prepare: Syntax error: expected something between the 'IN' keyword and the end of the request. SQL statement was: 
       SELECT CLM_HDR_DMNSN.PTNT_BRTH_DT AS DOB, CLNDR_TIME_DMNSN.CLNDR_DATE AS CLAIMSERVICEDATE, CLM_HDR_DMNSN.SPNSR_SSN AS 
       SPONSSN, CLM_HDR_DMNSN.PTNT_ID_NUM AS PTNTID, CLM_HDR_DMNSN.DEERS_BENE_ID AS DEERSBENEID, DGNS_DMNSN.DGNS_1_CD AS DIAG1, 
       DGNS_DMNSN.DGNS_2_CD AS DIAG2, DGNS_DMNSN.DGNS_3_CD AS DIAG3, DGNS_DMNSN.DGNS_4_CD AS DIAG4, DGNS_DMNSN.DGNS_5_CD AS DIAG5, 
       DGNS_DMNSN.DGNS_6_CD AS DIAG6, DGNS_DMNSN.DGNS_7_CD AS DIAG7, DGNS_DMNSN.DGNS_8_CD AS DIAG8, DGNS_DMNSN.DGNS_9_CD AS DIAG9, 
       DGNS_DMNSN.DGNS_10_CD AS DIAG10, PRCDR_DMNSN.CPT4_PRCDR_CD AS CPT4CODE, PRCDR_DMNSN.REV_CD AS REV, PRCDR_DMNSN.NDC_CD AS 
       NDC, CLM_DMNSN.POS_CD AS POS FROM CLM_LINE_FACT INNER JOIN DGNS_DMNSN ON (DGNS_DMNSN.DGNS_DMNSN_ID 
       =CLM_LINE_FACT.DGNS_DMNSN_ID) INNER JOIN CLM_HDR_DMNSN ON (CLM_LINE_FACT.CLM_HDR_ID =CLM_HDR_DMNSN.CLM_HDR_ID) INNER JOIN 
       CLNDR_TIME_DMNSN ON (CLM_LINE_FACT.SRVC_DT_ID =CLNDR_TIME_DMNSN.CLNDR_TIME_ID) INNER JOIN PRCDR_DMNSN ON 
       (CLM_LINE_FACT.PRCDR_DMNSN_ID =PRCDR_DMNSN.PRCDR_DMNSN_ID) WHERE CLNDR_TIME_DMNSN.CLNDR_DATE BETWEEN '2017-02-01' AND 
       '2017-02-28' AND CLM_HDR_DMNSN.PTNT_BRTH_DT BETWEEN '1953-02-01' and '1999-02-01' AND (DGNS_DMNSN.DGNS_1_CD IN &SCHIZ OR 
       DGNS_DMNSN.DGNS_1_CD IN &BIPOLAR OR DGNS_DMNSN.DGNS_2_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_2_CD IN &BIPOLAR OR 
       DGNS_DMNSN.DGNS_3_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_3_CD IN &BIPOLAR OR DGNS_DMNSN.DGNS_4_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_4_CD 
       IN &BIPOLAR OR DGNS_DMNSN.DGNS_5_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_5_CD IN &BIPOLAR OR DGNS_DMNSN.DGNS_6_CD IN &SCHIZ OR 
       DGNS_DMNSN.DGNS_6_CD IN &BIPOLAR OR DGNS_DMNSN.DGNS_7_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_7_CD IN &BIPOLAR OR 
       DGNS_DMNSN.DGNS_8_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_8_CD IN &BIPOLAR OR DGNS_DMNSN.DGNS_9_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_9_CD 
       IN &BIPOLAR OR DGNS_DMNSN.DGNS_10_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_10_CD IN &BIPOLAR OR PRCDR_DMNSN.NDC_CD IS NOT NULL).
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
96             %PUT &SQLXMSG &SQLXRC;
Syntax error: expected something between the 'IN' keyword and the end of the request  3706
97             QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.26 seconds
      cpu time            0.04 seconds
      
98         


99         PROC SORT DATA=HEDIS.CLMSPULL OUT=HEDIS.CLMSPULL;
ERROR: Libref HEDIS is not assigned.
ERROR: Libref HEDIS is not assigned.
100                by NDC;
101        	Run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      


102         PROC SORT DATA=HEDIS.ndcdiab OUT=HEDIS.ndcdiab;
ERROR: Libref HEDIS is not assigned.
ERROR: Libref HEDIS is not assigned.
103            by NDC;
104        
105        Run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
106        
107        GOPTIONS NOACCESSIBLE;
108        %LET _CLIENTTASKLABEL=;
109        %LET _CLIENTPROCESSFLOWNAME=;
4                                                          The SAS System                                 10:49 Friday, May 25, 2018

110        %LET _CLIENTPROJECTPATH=;
111        %LET _CLIENTPROJECTNAME=;
112        %LET _SASPROGRAMFILE=;
113        
114        ;*';*";*/;quit;run;
115        ODS _ALL_ CLOSE;
116        
117        
118        QUIT; RUN;
119        
Super User
Posts: 13,293

Re: What am I doing wrong? "IN" operator missing?

All of these messages are telling part of the story:

4         	  AND (DGNS_DMNSN.DGNS_1_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_1_CD IN &BIPOLAR OR
WARNING: Apparent symbolic reference SCHIZ not resolved.
WARNING: Apparent symbolic reference BIPOLAR not resolved.

You are using macro variables SCHIZ and BIPOLAR that are not defined at the time of the code execution.

 

 

Also the IN operator expects the values to be in parentheses

 

Variablename in ( <list of values>) so the operator knows when to end the comparison. Character values would be quoted.

 

Run these two data steps and examine the log.

data _null_;
   set sashelp.class;
   if sex in 'F' then put "Female";
run;

data _null_;
   set sashelp.class;
   if sex in ('F') then put "Female";
run;
Frequent Contributor
Posts: 104

Re: This is the entire run so far....Now I'm getting a different error and no idea why

1                                                          The SAS System                                 12:11 Friday, May 25, 2018

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='xxxxxxxx;
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/SASHome/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         
25         %GLOBAL FileNameUserID;
26         %GLOBAL FileNameDate;
27         %GLOBAL FileNameMailing;
28         
29         %LET FileNameUserID =888888;
30         /* %LET FileNameDate = %sysfunc(today(),YYMMDD.); */
31         %LET FileNameHEDIS = HEDIS;
32         
33         %LET TEST = &cOrgNameDataAnalytics./users/&FileNameUserID./&FileNameHEDIS.;
34         /* %LET TEST = &cOrgNameDataAnalytics./users/&FileNameUserID./&FileNameDate.; */
35         %PUT &TEST;
/opt/t7iw/sasdata/users/888888/HEDIS
36         
37         LIBNAME Rx "&cOrgNameDataAnalytics./shared/AK_West_Data/Data_Sets/2018-03";
NOTE: Libref RX was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /opt/t7iw/sasdata/shared/AK_West_Data/Data_Sets/2018-03
38         FILENAME ndcdiab "&cOrgNameDataAnalytics./users/fs115055/HEDIS/NDCDIAB.TXT";
39         FILENAME diabexc "&cOrgNameDataAnalytics./users/fs115055/HEDIS/Diabetes_List_for_AS.TXT";
40         LIBNAME ndc_diab "&cOrgNameDataAnalytics./users/fs115055/HEDIS";
NOTE: Libref NDC_DIAB was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /opt/t7iw/sasdata/users/fs115055/HEDIS
41         LIBNAME HEDIS "&cOrgNameDataAnalytics./users/&FileNameUserID./&FileNameHEDIS./Tapes.";
NOTE: Libref HEDIS was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /opt/t7iw/sasdata/users/888888/HEDIS/Tapes.
42         
43         /*LIBNAME HEDIS "&cOrgNameDataAnalytics./users/&FileNameUserID./&FileNameDate./&FileNameHEDIS."; */
44         
45         %LET SCHIZ = 			/*Schizophrenia Value*/
46         					('F20.0','F20.1','F20.2','F20.3','F20.5','F20.81',
47         					'F20.89','F20.9','F25.0','F25.1','F25.8','F25.9');
2                                                          The SAS System                                 12:11 Friday, May 25, 2018

48         
49         %LET BIPOLAR =			/*Bipolar Disorder Value Set*/
50         					('F30.10','F30.11','F30.12','F30.13','F30.2','F30.3',
51         					'F30.4','F30.8','F30.9','F31.0','F31.10','F31.11','F31.12','F31.13','F31.2','F31.30','F31.31',
52         					'F31.32','F31.4','F31.5','F31.60','F31.61','F31.62','F31.63','F31.64','F31.70','F31.71',
53         					'F31.72','F31.73','F31.74','F31.75','F31.76','F31.77','F31.78',
54         					/*Other Bipolar Disorder*/
55         					'F31.81','F31.89','F31.9');
56         
57         					/*BH Stand Alone Inpatient*/
58         %LET BHSAAINP = ('0100', '0101', '0110', '0111', '0112', '0113','0114',	'0119',	
59             	'0120',	'0121',	'0122',	'0123',	'0124',	'0129',	'0130',	'0131',	'0132',	'0133',	'0134',	
60         		'0139',	'0140',	'0141',	'0142',	'0143',	'0144',	'0149',	'0150',	'0151',	'0152',	'0153',	
61         		'0154',	'0159',	'0160',	'0164',	'0167',	'0169',	'0200',	'0201',	'0202',	'0203',	'0204',	
62         		'0206',	'0207',	'0208',	'0209',	'0210',	'0211',	'0212',	'0213',	'0214',	'0219',	'0720',	
63         		'0721',	'0722',	'0723',	'0724',	'0729',	'0987');
64         
65         %LET BHAIP = /*BH Acute Inpatient*/ ('90791','90792','90832','90833','90834','90836','90837','90838','90839','90840',
66         		'90845','90847','90849','90853','90867','90868','90869','90870','90875','90876','99221','99222',
67         		'99223','99231','99232','99233','99238','99239','99251','99252','99253','99254','99255','99291');
68         
69         		/*BH Acute Inpatient POS*/	
70         %LET BHAIPOS = ( '21', '51');
71         
72         	/*BH Stand Alone OP/PH/IOP*/
73         %LET BHSAOP = ('98960',	'98961',	'98962',	'99078',	'99201',	'99202',	'99203',
74         '99204',	'99205',	'99211',	'99212',	'99213',	'99214',	'99215',	'99217',
75         '99218',	'99219',	'99220',	'99241',	'99242',	'99243',	'99244',	'99245',
76         '99341',	'99342',	'99343',	'99344',	'99345',	'99347',	'99348',	'99349',	
77         '99350',	'99384',	'99385',	'99386',	'99387',	'99394',	'99395',	'99396',
78         '99397',	'99401',	'99402',	'99403',	'99404',	'99411',	'99412',	'99510',	
79         'G0155',	'G0176',	'G0177',	'G0409',	'G0410',	'G0411',	'G0463',	'H0002',	
80         'H0004',	'H0031',	'H0034',	'H0035',	'H0036',	'H0037',	'H0039',	'H0040',
81         'H2000',	'H2001',	'H2010',	'H2011',	'H2012',	'H2013',	'H2014',	'H2015',	
82         'H2016',	'H2017',	'H2018',	'H2019',	'H2020',	'M0064',	'S0201',	'S9480',
83         'S9484',	'S9485',	'T1015''0510',	'0513',	'0516',	'0517',	'0519',	'0520',	'0521',	'0522',	
84         '0523',	'0526',	'0527',	'0528',	'0529',	'0900',	'0901',	'0902',	'0903',	'0904',	'0905',	'0907',	
85         '0911',	'0912',	'0913',	'0914',	'0915',	'0916',	'0917',	'0919',	'0982',	'0983');
86         
87         /*BH Outpatient/PH/IOP/POS*/
88         %LET BHOPPOS = ('03',	'05',	'07',	'09',	'11',	'12',	'13',	'14',	'15',	'16',	'17',	
89         '18',	'19',	'20',	'22',	'24',	'33',	'49',	'50',	'52',	'53',	'71',	'72');
90         
91         /*BH Outpatient/PH/IOP*/
92         %LET BHOPPHIOP = ('90791','90792','90832','90833','90834','90836','90837',
93         '90838','90839','90840','90845','90847','90849','90853','90867','90868',	
94         '90869','90870','90875','90876','99221','99222','99223','99231','99232',	
95         '99233','99238','99239','99251','99252','99253','99254','99255','99291');
96         
97         
98         /*ED Values*/
99         %LET ED = ('99281',	'99282','99283','99284','99285', '0450',	
100        		'0451',	'0452',	'0456',	'0459',	'0981');
101        
102        
103        /*BH ED*/
104        %LET BHED = ('90791','90792','90832','90833','90834','90836','90837',	
105        			'90838','90839','90840','90845','90847','90849','90853','90867',	
3                                                          The SAS System                                 12:11 Friday, May 25, 2018

106        			'90868','90869','90870','90875','90876','99291');
107        
108        			/*BH Stand Alone Nonacute Inpatient*/
109        %LET BHSANONINP = ('99304',	'99305','99306','99307','99308','99309','99310',	
110        '99315','99316','99318','99324','99325','99326','99327','99328',	
111        '99334','99335','99336','99337','H0017','H0018','H0019','T2048'
112        '0118',	'0128',	'0138',	'0148',	'0158',	'0190',	'0191',	'0192',	'0193',	'0194',	'0199',	'0524',	
113        '0525',	'0550',	'0551',	'0552',	'0559',	'0660',	'0661',	'0662',	'0663',	'0669',	'1000',	'1001',	'1003',	
114        '1004',	'1005');
115        
116        
117        		/*BH Nonacute Inpatient*/
118        %LET BHNONINP = ('90791','90792','90832','90833','90834','90836','90837',
119        			'90838','90839','90840','90845','90847','90849','90853','90867',	
120        			'90868','90869','90870','90875','90876','99291');
121          /*ED POS*/
122        %LET EDPOS = ('23');
123        
124        /*BH Nonacute Inpatient POS*/
125        %LET BHNONINPPOS = ('31','32','56');
126        
127        
128        /*Exclusions*/
129        %LET HOSPICE =
130        		('99377','99378','G0182','G9473','G9474','G9475','G9476',
131        		'G9477','G9478','G9479','Q5003','Q5004','Q5005','Q5006','Q5007','Q5008','Q5010','S9126','T2042',
132        		'T2043','T2044','T2045','T2046','0143',	'0144',	'0149',	'0150',	'0151',	'0152',	'0153',	'0154',	
133        		'0159',	'0160',	'0164',	'0167',	'0169',	'0200',	'0201',	'0202',	'0203',	'0204',	'0206',	'0207',	
134        		'0208',	'0209',	'0210',	'0211',	'0212',	'0213',	'0214',	'0219',	'0720',	'0721',	'0722',	'0723',	
135        		'0724',	'0729',	'0987',	'99221','99222','99223','99231','99232','99233',	
136        		'99238','99239','99251','99252','99253','99254','99255','99291');
137        
138        		
139        %LET LAINJ = ('J0401','J1631','J2358','J2426','J2680','J2794');
140        
141        %LET HBA1C = ('3044F','3044F','3044F','3044F','3044F');
142        
143        %LET GLUCOSE = ('80047','80048','80050','80053','80069','82947','82950','82951');/*DOES NOT INCLUDE LOINC CODES*/
144        
145        
146        /*Bring in Rx files*/
147        


148        DATA HEDIS.Rx;
149           SET Rx.rx_detail_fy17_d180109
150               Rx.rx_detail_fy18_d180327 ;
151        
152        	   WHERE BEGDT BETWEEN '01feb2017'd AND '31jan2018'd;
153        
154        run;

NOTE: There were 11923336 observations read from the data set RX.RX_DETAIL_FY17_D180109.
      WHERE (BEGDT>='01FEB2017'D and BEGDT<='31JAN2018'D);
NOTE: There were 5908266 observations read from the data set RX.RX_DETAIL_FY18_D180327.
      WHERE (BEGDT>='01FEB2017'D and BEGDT<='31JAN2018'D);
NOTE: The data set HEDIS.RX has 17831602 observations and 64 variables.
NOTE: DATA statement used (Total process time):
4                                                          The SAS System                                 12:11 Friday, May 25, 2018

      real time           8.84 seconds
      cpu time            8.85 seconds
      

155        
156        /*Bring in the provided medlist*/
157        
158        data HEDIS.ndcdiab;
159           set ndc_diab.ndcdiab;
160        run;

NOTE: There were 2223 observations read from the data set NDC_DIAB.NDCDIAB.
NOTE: The data set HEDIS.NDCDIAB has 2223 observations and 13 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

161        
162        /* Changing 'NDC Code'n from numeric to character and rename to NDC */
163        
164         DATA HEDIS.ndcdiab;
165            SET HEDIS.ndcdiab;
166          /*  ATTRIB NDC LENGTH = $11;  */
167            NDC = PUT('NDC Code'n, $11.);
WARNING: Variable 'NDC Code'n has already been defined as numeric.
168        	RUN;

NOTE: There were 2223 observations read from the data set HEDIS.NDCDIAB.
NOTE: The data set HEDIS.NDCDIAB has 2223 observations and 14 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

169        
170        
171         PROC SORT DATA=HEDIS.NDCDIAB OUT=HEDIS.NDCDIAB;
172               by NDC;
173        	Run;

NOTE: There were 2223 observations read from the data set HEDIS.NDCDIAB.
NOTE: The data set HEDIS.NDCDIAB has 2223 observations and 14 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      

174        
175         PROC SORT DATA=HEDIS.Rx OUT=HEDIS.Rx;
176               by NDC;
177        	Run;

NOTE: There were 17831602 observations read from the data set HEDIS.RX.
NOTE: The data set HEDIS.RX has 17831602 observations and 64 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           17.64 seconds
      cpu time            35.40 seconds
5                                                          The SAS System                                 12:11 Friday, May 25, 2018

      

178        
179         /*  put Rx and medlist together  */
180           DATA HEDIS.RxNDC;
181           MERGE HEDIS.Rx (IN = INA)
182                 HEDIS.ndcdiab (IN = INB);
183              BY  NDC ;
184              IF INA and INB;
185        RUN;

NOTE: There were 17831602 observations read from the data set HEDIS.RX.
NOTE: There were 2223 observations read from the data set HEDIS.NDCDIAB.
NOTE: The data set HEDIS.RXNDC has 119548 observations and 77 variables.
NOTE: DATA statement used (Total process time):
      real time           3.17 seconds
      cpu time            3.17 seconds
      

186        
187        
188        
189        proc sql ;
190           CONNECT TO Teradata as tera (authdomain="T2017_IW_Prod" TDPID="edwfsp.fs.xccccc.com");
191           CREATE TABLE HEDIS.ENROLL AS
192                SELECT * FROM CONNECTION TO tera
193        
194        (SELECT
195        
196             PAT_ENR_HIST.SPNSR_SSN AS SPONSSN,
197             PAT_ENR_HIST.PTNT_ID AS PTNTID,
198             PATIENT.DEERS_PTNT_ID AS DEERSPTNTID,
199             PAT_PCM_HIST.PCM_PROV_ID,
200             PAT_PCM_HIST.PCM_THRU_DT,
201             PAT_ENR_HIST.HCDP_CD,
202             PAT_ENR_HIST.BENE_BIRTH_DT,
203             PAT_ENR_HIST.ENRL_EFF_DT,
204             PAT_ENR_HIST.UPDT_DT,
205             PAT_ENR_HIST.BENE_GNDR_CD,
206             PAT_ENR_HIST.DISENRL_DT,
207             PATIENT.DEERS_BENE_ID AS DEERSBENEID,
208             PATIENT.DEERS_FMLY_ID AS DEERSFMLYID
209          FROM
210           PATIENT RIGHT OUTER JOIN PAT_ENR_HIST
211             ON (PAT_ENR_HIST.PTNT_ID=PATIENT.PTNT_ID
212                  AND PAT_ENR_HIST.SPNSR_PPI=PATIENT.SPNSR_PPI)
213             INNER JOIN PAT_PCM_HIST
214            ON (PAT_ENR_HIST.PTNT_ID=PAT_PCM_HIST.PTNT_ID
215             AND PAT_ENR_HIST.ENRL_EFF_DT=PAT_PCM_HIST.ENRL_EFF_DT
216             AND PAT_ENR_HIST.SPNSR_PPI=PAT_PCM_HIST.SPNSR_PPI)
217          WHERE
218            PAT_ENR_HIST.BENE_BIRTH_DT  BETWEEN  '1953-02-01' and '1999-02-01' /*18-64 yr olds*/
219              AND PAT_PCM_HIST.PROV_LCTR_CD = '01'
220          );
NOTE: Table HEDIS.ENROLL created, with 222424 rows and 13 columns.

221          %PUT &SQLXMSG &SQLXRC;
0
6                                                          The SAS System                                 12:11 Friday, May 25, 2018

222          QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           1.14 seconds
      cpu time            0.30 seconds
      

223        
224        
225        ** PER JIM ELIMINATE TYA ENROLLMENT **;
226        DATA HEDIS.ENROLL1;
227          SET HEDIS.ENROLL;
228          ATTRIB BENEID LENGTH = $12 FORMAT=$12. INFORMAT=$12.;
229             IF HCDP_CD IN ('422','423','424','425','426','427','428',
230                            '429','430') THEN DELETE;
231             BENEID = TRIM(SPONSSN) !! TRIM(PTNTID); /*REMOVED UNDERSCORE, CREATED ALIAS IN ENROLL PULL*/
232             BENE = TRIM(DEERS_PTNT_ID); /*EDIPN*/
233        RUN;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
      232:18   
NOTE: Variable DEERS_PTNT_ID is uninitialized.
NOTE: There were 222424 observations read from the data set HEDIS.ENROLL.
NOTE: The data set HEDIS.ENROLL1 has 222422 observations and 16 variables.
NOTE: DATA statement used (Total process time):
      real time           0.06 seconds
      cpu time            0.06 seconds
      

234        
235           ** MUST MODIFY SORT BECAUSE WHEN BENE DISENROLLED THE **;
236           **  UPDATE DATE IS THE SAME ON SOME RECORDS                   **;
237            PROC SORT DATA=HEDIS.ENROLL1  OUT=HEDIS.ENROLL2;
238             BY BENEID DESCENDING UPDT_DT DESCENDING DISENRL_DT;
239           * BY BENEID DESCENDING UPDT_DT;                      **;
240            RUN;

NOTE: There were 222422 observations read from the data set HEDIS.ENROLL1.
NOTE: The data set HEDIS.ENROLL2 has 222422 observations and 16 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.19 seconds
      cpu time            0.20 seconds
      

241        
242            ** PER BRAD USE SIMPLIFIED ENROLLMENT CODE **;
243            ** PICK MOST RECENT ENROLLMENT UP DATE     **;
244             DATA HEDIS.ENROLL3;
245              SET HEDIS.ENROLL2;
246              BY BENEID DESCENDING UPDT_DT DESCENDING DISENRL_DT;
247            * BY BENEID DESCENDING UPDT_DT;
248              IF FIRST.BENEID THEN OUTPUT;
249             RUN;

NOTE: There were 222422 observations read from the data set HEDIS.ENROLL2.
NOTE: The data set HEDIS.ENROLL3 has 220492 observations and 16 variables.
NOTE: DATA statement used (Total process time):
      real time           0.07 seconds
      cpu time            0.08 seconds
7                                                          The SAS System                                 12:11 Friday, May 25, 2018

      

250        
251         *------------------------------------------------------------*;
252          **  JS020: READ IN CLAIMS DATA FROM CLAIMS MART              *;
253          *------------------------------------------------------------*;
254        /*data HEDIS.clmspull2;
255           set HEDIS.clmspull;
256          /* WHERE BEGDT BETWEEN '01feb2017'd AND '31jan2018'd;
257        run;
258        
259        proc datasets library=HEDIS nolist;
260        	repair CLMSPULL;
261        run;*/
262        
263        
264        proc sql ;
265           CONNECT TO Teradata as tera (authdomain="T2017_IW_Prod" TDPID="edwfsp.fs.xccccc.com");
266           CREATE TABLE HEDIS.CLMSPULL AS
267                SELECT * FROM CONNECTION TO tera
268        
269        (SELECT
270        
271               CLM_HDR_DMNSN.CLM_BASE_NUM          AS CLAIMBASEID,
272                CLM_LINE_FACT.CLM_LINE_NUM          AS CLAIMLINE,
273                CLM_HDR_DMNSN.CLM_ADJ_ID            AS CLAIMADJID,
274                CLM_HDR_DMNSN.PTNT_BRTH_DT          AS DOB,
275                CLM_HDR_DMNSN.CLM_BGN_SRVC_DT       AS CLMBEGINDATE,
276                CLNDR_TIME_DMNSN.CLNDR_DATE         AS CLAIMSERVICEDATE,
277                CLM_HDR_DMNSN.SPNSR_SSN             AS SPONSSN,
278                CLM_HDR_DMNSN.PTNT_ID_NUM           AS PTNTID,
279        	    CLM_HDR_DMNSN.DEERS_UNIQ_PTNT_ID    AS DEERSID,
280        	    CLM_HDR_DMNSN.DEERS_BENE_ID         AS DEERSBENEID,
281                CLM_HDR_DMNSN.DEERS_FMLY_ID         AS DEERSFMLY,
282                DGNS_DMNSN.DGNS_1_CD                AS DIAG1,
283                DGNS_DMNSN.DGNS_2_CD                AS DIAG2,
284                DGNS_DMNSN.DGNS_3_CD                AS DIAG3,
285                DGNS_DMNSN.DGNS_4_CD                AS DIAG4,
286                DGNS_DMNSN.DGNS_5_CD                AS DIAG5,
287                DGNS_DMNSN.DGNS_6_CD                AS DIAG6,
288                DGNS_DMNSN.DGNS_7_CD                AS DIAG7,
289                DGNS_DMNSN.DGNS_8_CD                AS DIAG8,
290                DGNS_DMNSN.DGNS_9_CD                AS DIAG9,
291                DGNS_DMNSN.DGNS_10_CD               AS DIAG10,
292                PRCDR_DMNSN.CPT4_PRCDR_CD           AS CPT4CODE,
293                PRCDR_DMNSN.REV_CD                  AS REV,
294                PRCDR_DMNSN.HCPCS_PRCDR_CD          AS HCPCSCODE,
295                PRCDR_DMNSN.NDC_CD                  AS NDC,
296                PRCDR_DMNSN.DRVD_PRCDR_CD           AS PROCEDURECODE,
297                PRCDR_DMNSN.ICD_VRSN_IND            AS ICDIND,
298                CLM_LINE_FACT.OHI_PD_AMT            AS OHIPAIDAMT,
299                PLAN_ADMINR_DMNSN.RSRC_SHRG_IND     AS RESOURCESHARE,
300                PLAN_ADMINR_DMNSN.DRVD_MCS_OPTN_IND AS MCSOPTIONIND,
301                PLAN_ADMINR_DMNSN.HOME_PLAN_CD AS PRIMEMTFCIVSTDEXT,
302               CLM_LINE_FACT.LINE_ALOWD_AMT        AS ALLOWEDAMT,
303               CLM_LINE_FACT.PD_AMT                AS PDAMT,
304        		CLM_DMNSN.POS_CD 					AS POS
305            FROM
8                                                          The SAS System                                 12:11 Friday, May 25, 2018

306                CLM_LINE_FACT INNER JOIN DGNS_DMNSN ON
307                  (DGNS_DMNSN.DGNS_DMNSN_ID
308                   =CLM_LINE_FACT.DGNS_DMNSN_ID)
309                INNER JOIN CLM_HDR_DMNSN ON
310                  (CLM_LINE_FACT.CLM_HDR_ID
311                   =CLM_HDR_DMNSN.CLM_HDR_ID)
312                INNER JOIN CLNDR_TIME_DMNSN ON
313                  (CLM_LINE_FACT.SRVC_DT_ID
314                   =CLNDR_TIME_DMNSN.CLNDR_TIME_ID)
315                INNER JOIN PLAN_ADMINR_DMNSN ON
316                  (CLM_LINE_FACT.PLAN_ADMINR_ID
317                   =PLAN_ADMINR_DMNSN.PLAN_ADMINR_ID)
318                INNER JOIN PRCDR_DMNSN ON
319                  (CLM_LINE_FACT.PRCDR_DMNSN_ID
320                   =PRCDR_DMNSN.PRCDR_DMNSN_ID)
321            WHERE
322              CLNDR_TIME_DMNSN.CLNDR_DATE BETWEEN '2017-02-01' AND '2018-01-31'
323        	  AND CLM_HDR_DMNSN.PTNT_BRTH_DT BETWEEN  '1953-02-01' and '1999-02-01'
324        	  AND DGNS_DMNSN.DGNS_1_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_1_CD IN &BIPOLAR OR
325        			DGNS_DMNSN.DGNS_2_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_2_CD IN &BIPOLAR OR
326        			DGNS_DMNSN.DGNS_3_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_3_CD IN &BIPOLAR OR
327        			DGNS_DMNSN.DGNS_4_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_4_CD IN &BIPOLAR OR
328        			DGNS_DMNSN.DGNS_5_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_5_CD IN &BIPOLAR OR
329        			DGNS_DMNSN.DGNS_6_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_6_CD IN &BIPOLAR OR
330        			DGNS_DMNSN.DGNS_7_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_7_CD IN &BIPOLAR OR
331        			DGNS_DMNSN.DGNS_8_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_8_CD IN &BIPOLAR OR
332        			DGNS_DMNSN.DGNS_9_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_9_CD IN &BIPOLAR OR
333        			DGNS_DMNSN.DGNS_10_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_10_CD IN &BIPOLAR OR
334        		PRCDR_DMNSN.NDC_CD IS NOT NULL)
335            );
               _
               22
               200
ERROR 22-322: Syntax error, expecting one of the following: a name, ;, ',', ANSIMISS, AS, CROSS, EXCEPT, FULL, GROUP, HAVING, 
              INNER, INTERSECT, JOIN, LEFT, NATURAL, NOMISS, ORDER, OUTER, RIGHT, UNION, WHERE.  

ERROR 200-322: The symbol is not recognized and will be ignored.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
336            %PUT &SQLXMSG &SQLXRC;
0
337            QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.10 seconds
      cpu time            0.02 seconds
      
338        


339        PROC SORT DATA=HEDIS.CLMSPULL OUT=HEDIS.CLMSPULL;
340                by NDC;
ERROR: Variable NDC not found.
341        	Run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set HEDIS.CLMSPULL may be incomplete.  When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set HEDIS.CLMSPULL was not replaced because this step was stopped.
9                                                          The SAS System                                 12:11 Friday, May 25, 2018

NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      


342         PROC SORT DATA=HEDIS.ndcdiab OUT=HEDIS.ndcdiab;
343            by NDC;
344        
345        Run;

NOTE: Input data set is already sorted; it has been copied to the output data set.
NOTE: There were 2223 observations read from the data set HEDIS.NDCDIAB.
NOTE: The data set HEDIS.NDCDIAB has 2223 observations and 14 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

346                                           /* adding medlist to claims  */
347        
348           DATA HEDIS.CLMS_NDC;
349           MERGE HEDIS.CLMSPULL (IN = INA)
350                 HEDIS.ndcdiab (IN = INB);
351              BY  NDC ;
352              IF INA ;
353        RUN;

ERROR: BY variable NDC is not on input data set HEDIS.CLMSPULL.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set HEDIS.CLMS_NDC may be incomplete.  When this step was stopped there were 0 observations and 14 variables.
WARNING: Data set HEDIS.CLMS_NDC was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

354        
355        ** EG - ADD CORRECTED DEERS PATIENT ID TO CLAIMS -- IN PULL, MOST OFTEN ZEROES PLUS PATIENT ID **;
356        PROC SORT DATA=HEDIS.CLMS_NDC;
357         BY SPONSSN PTNTID;
ERROR: Variable SPONSSN not found.
ERROR: Variable PTNTID not found.
358         RUN;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
359        


360         PROC SORT DATA=HEDIS.ENROLL3;
361         BY SPONSSN PTNTID;
362          RUN;

NOTE: There were 220492 observations read from the data set HEDIS.ENROLL3.
10                                                         The SAS System                                 12:11 Friday, May 25, 2018

NOTE: The data set HEDIS.ENROLL3 has 220492 observations and 16 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.09 seconds
      cpu time            0.11 seconds
      

363        
364          DATA HEDIS.CLMS_NDC2;
365          MERGE HEDIS.CLMS_NDC (IN=CLM) HEDIS.ENROLL3 (IN=ENR);
366         BY SPONSSN PTNTID;
367        IF CLM AND ENR; /*KEEPS THOSE CIV IN AGE RANGE*/
368         RUN;

ERROR: BY variable SPONSSN is not on input data set HEDIS.CLMS_NDC.
ERROR: BY variable PTNTID is not on input data set HEDIS.CLMS_NDC.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set HEDIS.CLMS_NDC2 may be incomplete.  When this step was stopped there were 0 observations and 30 variables.
WARNING: Data set HEDIS.CLMS_NDC2 was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

369        
370        
371         /**DENOMINATOR STEP 1 - PART A*/
372        
373            DATA HEDIS.DENOM1A; /* Identified as having schizo or bipolar disorder with at least ONE of the following */
374              SET HEDIS.CLMS_NDC2;
375                IF  ( DIAG1 IN  &SCHIZ OR DIAG1 IN &BIPOLAR OR
376        			  DIAG2 IN &SCHIZ OR DIAG2 IN &BIPOLAR OR
377        		      DIAG3 IN &SCHIZ OR DIAG3 IN &BIPOLAR OR
378        		      DIAG4 IN &SCHIZ OR DIAG4 IN &BIPOLAR OR
379        			  DIAG5 IN &SCHIZ OR DIAG5 IN &BIPOLAR OR
380        			DIAG6 IN &SCHIZ OR DIAG6 IN &BIPOLAR OR
381        			DIAG7 IN &SCHIZ OR DIAG7 IN &BIPOLAR OR
382        			DIAG8 IN &SCHIZ OR DIAG8 IN &BIPOLAR OR
383        			DIAG9 IN &SCHIZ OR DIAG9 IN &BIPOLAR OR
384        			DIAG10 IN &SCHIZ OR DIAG10 IN &BIPOLAR)
385        		AND (REV IN &BHSAAINP OR (CPT4CODE IN &BHAIP AND POS IN &BHAIPOS));
386        	
387        		
388            RUN;

NOTE: Variable DIAG1 is uninitialized.
NOTE: Variable DIAG2 is uninitialized.
NOTE: Variable DIAG3 is uninitialized.
NOTE: Variable DIAG4 is uninitialized.
NOTE: Variable DIAG5 is uninitialized.
NOTE: Variable DIAG6 is uninitialized.
NOTE: Variable DIAG7 is uninitialized.
NOTE: Variable DIAG8 is uninitialized.
NOTE: Variable DIAG9 is uninitialized.
NOTE: Variable DIAG10 is uninitialized.
NOTE: Variable REV is uninitialized.
NOTE: Variable CPT4CODE is uninitialized.
NOTE: Variable POS is uninitialized.
NOTE: There were 0 observations read from the data set HEDIS.CLMS_NDC2.
11                                                         The SAS System                                 12:11 Friday, May 25, 2018

NOTE: The data set HEDIS.DENOM1A has 0 observations and 43 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

389        
390        	/*Denominator Step 1 - Part B*/
391        DATA HEDIS.DENOM1B1;
392        	SET HEDIS.CLMS_NDC2;
393          IF (DIAG1 IN &SCHIZ OR
394          	DIAG2 IN &SCHIZ OR
395        	DIAG3 IN &SCHIZ OR
396        	DIAG4 IN &SCHIZ OR
397        	DIAG5 IN &SCHIZ OR
398        	DIAG6 IN &SCHIZ OR
399        	DIAG7 IN &SCHIZ OR
400        	DIAG8 IN &SCHIZ OR
401        	DIAG9 IN &SCHIZ OR
402        	DIAG10 IN &SCHIZ)
403        	AND (CPT4CODE IN &BHSAOP OR REV IN &BHSAOP
404        	OR (CPT4CODE IN &BHOPPHIOP AND POS IN &BHOPPOS)
405        	OR CPT4CODE IN &ED OR REV IN &ED OR (CPT4CODE IN &BHED AND POS IN &EDPOS)
406        	OR CPT4CODE IN &BHSANONINP OR REV IN &BHSANONINP OR (CPT4CODE IN &BHNONINP AND POS IN
407        	&BHNONINPPOS));
408        
409        RUN;

NOTE: Variable DIAG1 is uninitialized.
NOTE: Variable DIAG2 is uninitialized.
NOTE: Variable DIAG3 is uninitialized.
NOTE: Variable DIAG4 is uninitialized.
NOTE: Variable DIAG5 is uninitialized.
NOTE: Variable DIAG6 is uninitialized.
NOTE: Variable DIAG7 is uninitialized.
NOTE: Variable DIAG8 is uninitialized.
NOTE: Variable DIAG9 is uninitialized.
NOTE: Variable DIAG10 is uninitialized.
NOTE: Variable CPT4CODE is uninitialized.
NOTE: Variable REV is uninitialized.
NOTE: Variable POS is uninitialized.
NOTE: There were 0 observations read from the data set HEDIS.CLMS_NDC2.
NOTE: The data set HEDIS.DENOM1B1 has 0 observations and 43 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

410        
411        /*At least two outpatient visits*/
412        
413        PROC SQL;
414        CREATE TABLE DENOM1B2 AS
415        SELECT
416        A.BENE,
417        COUNT(DISTINCT A.CLAIMSERVICEDATE) AS VISITCT
418        FROM HEDIS.DENOM1B1 AS A
419        GROUP BY A.BENE;
12                                                         The SAS System                                 12:11 Friday, May 25, 2018

ERROR: Column CLAIMSERVICEDATE could not be found in the table/view identified with the correlation name A.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
420        RUN;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
421        
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
422        DATA DENOM1B;

423        SET DENOM1B2;
ERROR: File WORK.DENOM1B2.DATA does not exist.
424        WHERE VISITCT GE 2;
425        RUN;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.DENOM1B may be incomplete.  When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.DENOM1B was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

426        
427        	/*Denominator Step 1 - Part C*/
428        DATA HEDIS.DENOM1C1;
429        	SET HEDIS.CLMS_NDC2;
430          IF (DIAG1 IN &BIPOLAR OR
431          	DIAG2 IN &BIPOLAR OR
432        	DIAG3 IN &BIPOLAR OR
433        	DIAG4 IN &BIPOLAR OR
434        	DIAG5 IN &BIPOLAR OR
435        	DIAG6 IN &BIPOLAR OR
436        	DIAG7 IN &BIPOLAR OR
437        	DIAG8 IN &BIPOLAR OR
438        	DIAG9 IN &BIPOLAR OR
439        	DIAG10 IN &BIPOLAR)
440        	AND (CPT4CODE IN &BHSAOP OR REV IN &BHSAOP
441        	OR (CPT4CODE IN &BHOPPHIOP AND POS IN &BHOPPOS)
442        	OR CPT4CODE IN &ED OR REV IN &ED OR (CPT4CODE IN &BHED AND POS IN &EDPOS)
443        	OR CPT4CODE IN &BHSANONINP OR REV IN &BHSANONINP OR (CPT4CODE IN &BHNONINP AND POS IN
444        	&BHNONINPPOS));
445        
446        RUN;

NOTE: Variable DIAG1 is uninitialized.
NOTE: Variable DIAG2 is uninitialized.
NOTE: Variable DIAG3 is uninitialized.
NOTE: Variable DIAG4 is uninitialized.
NOTE: Variable DIAG5 is uninitialized.
NOTE: Variable DIAG6 is uninitialized.
NOTE: Variable DIAG7 is uninitialized.
NOTE: Variable DIAG8 is uninitialized.
NOTE: Variable DIAG9 is uninitialized.
NOTE: Variable DIAG10 is uninitialized.
NOTE: Variable CPT4CODE is uninitialized.
13                                                         The SAS System                                 12:11 Friday, May 25, 2018

NOTE: Variable REV is uninitialized.
NOTE: Variable POS is uninitialized.
NOTE: There were 0 observations read from the data set HEDIS.CLMS_NDC2.
NOTE: The data set HEDIS.DENOM1C1 has 0 observations and 43 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

447        
448        /*VISIT GREATER THAN OR EQ TO 2*/
449        
450        PROC SQL;
451        CREATE TABLE DENOM1C2 AS
452        SELECT
453        A.BENE,
454        COUNT(DISTINCT A.CLAIMSERVICEDATE) AS VISITCT
455        FROM HEDIS.DENOM1C1 AS A
456        GROUP BY A.BENE;
ERROR: Column CLAIMSERVICEDATE could not be found in the table/view identified with the correlation name A.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
457        RUN;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
458        
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
459        DATA DENOM1C;

460        SET DENOM1C2;
ERROR: File WORK.DENOM1C2.DATA does not exist.
461        WHERE VISITCT GE 2;
462        RUN;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.DENOM1C may be incomplete.  When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.DENOM1C was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

463        
464        
465        DATA DENOM1;
466        SET HEDIS.DENOM1A HEDIS.DENOM1B HEDIS.DENOM1C;
ERROR: File HEDIS.DENOM1B.DATA does not exist.
ERROR: File HEDIS.DENOM1C.DATA does not exist.
467        RUN;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.DENOM1 may be incomplete.  When this step was stopped there were 0 observations and 43 variables.
WARNING: Data set WORK.DENOM1 was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
14                                                         The SAS System                                 12:11 Friday, May 25, 2018

      

468        
469        PROC SORT DATA=DENOM1;
470        BY BENE;
471        RUN;

NOTE: Input data set is already sorted, no sorting done.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

472        
473        
474        /* End of Exclusion Value Set  jw */
475        
476            DATA HEDIS.CLMSQL2;
477           *FORMAT VISITTYPE   $5.;
478           *FORMAT ICN         $24.;
479             SET HEDIS.CLMSQL1a;
ERROR: File HEDIS.CLMSQL1A.DATA does not exist.
480              ATTRIB BENEID LENGTH = $12 FORMAT=$12. INFORMAT=$12.;
481              BENEID=TRIM(SPONSSN) !! TRIM(PTNTID);
482        
483              **  RESTRICT TO PRIME CIV LINKED    **;
484                  IF PRIMEMTFCIVSTDEXT = 'PRIME CIVILIAN';
485        
486          /*    IF OHIPAIDAMT > 0     THEN DELETE;     * NO OHI;    jw */
487          /*    IF RESOURCESHARE = 'Y'  THEN DELETE;     * NO RESOURCE SHARING;  jw */
488        
489               ** RESTRICT AGE BETWEEN 5-64;
490                  AGE=YRDIF(DOB, CLMBEGINDATE, 'AGE');
491                  IF AGE GE 5 AND AGE < 65 THEN OUTPUT;
492          RUN;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
      481:19   481:36   
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set HEDIS.CLMSQL2 may be incomplete.  When this step was stopped there were 0 observations and 7 variables.
WARNING: Data set HEDIS.CLMSQL2 was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

493        
494        /*  PROC PRINT DATA = HEDIS.CLMSQL2 (OBS=6);  */
495        
496          *MEND SKIP;
497        
498        proc sort data = HEDIS.CLMSQL2 out =  HEDIS.CLMSQL2a;
499           by BENEID;
500        

NOTE: Input data set is empty.
NOTE: The data set HEDIS.CLMSQL2A has 0 observations and 7 variables.
NOTE: PROCEDURE SORT used (Total process time):
15                                                         The SAS System                                 12:11 Friday, May 25, 2018

      real time           0.00 seconds
      cpu time            0.00 seconds
      
501        data HEDIS.COUNT;

502          set HEDIS.CLMSQL2a;
503          count + 1;
504          by BENEID;
505          if first.beneid then count = 1;
506        run;

NOTE: There were 0 observations read from the data set HEDIS.CLMSQL2A.
NOTE: The data set HEDIS.COUNT has 0 observations and 8 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

507        /* Denominator Step 1  */
508        
509          DATA HEDIS.CLMSQL3;
510             SET HEDIS.COUNT;
511        
512        
513                /* ED CPT code(s) jw */
514        
515              IF (CPT4CODE IN ('99281' '99282' '99283' '99284' '99285')
516                              OR
517        
518        		/* ED REV Code(s) jw */
519        
520                  REV IN     ('0450' '0451' '0452' '0456' '0459' '0981')
521                  )
522                             OR
523        
524        	    /* Acute Inpatient CPT code(s) jw */
525        
526                 (CPT4CODE IN ('99221' '99222' '99223' '99231' '99232'
527                              '99233' '99238' '99239' '99251' '99252'
528                              '99253' '99254' '99255' '99291' )
529                              OR
530        					
531                  /* Acute Inpatient REV Code(s) jw */
532        
533                  REV IN     ('0100' '0101' '0110' '0111' '0112' '0113'
534                              '0114' '0119' '0120' '0121' '0122' '0123'
535                              '0124' '0129' '0130' '0131' '0132' '0133'
536                              '0134' '0139' '0140' '0141' '0142' '0143'
537                              '0144' '0149' '0150' '0151' '0152' '0153'
538                              '0154' '0159' '0160' '0164' '0167' '0169'
539                              '0200' '0201' '0202' '0203' '0204' '0206'
540                              '0207' '0208' '0209' '0210' '0211' '0212'
541                              '0213' '0214' '0219' '0720' '0721' '0722'
542                              '0723' '0724' '0729' '0987')
543                           )
544        					 OR
545        
546        		  /* Outpatient CPT code(s) jw */
16                                                         The SAS System                                 12:11 Friday, May 25, 2018

547        
548        		 (CPT4CODE IN ('99201' '99202' '99203' '99204' '99205'
549                              '99211' '99212' '99213' '99214' '99215'
550                              '99241' '99242' '99243' '99244' '99245'
551                              '99341' '99342' '99343' '99344' '99345'
552        					  '99347' '99348' '99349' '99350' '99381'
553        					  '99382' '99383' '99384' '99385' '99386'
554        					  '99387' '99391' '99392' '99393' '99394'
555        					  '99395' '99396' '99397' '99401' '99402'
556        				      '99403' '99404' '99411' '99412' '99429'
557        					  '99455' '99456')
558                             OR
559        
560                  /* Outpatient REV Code(s) jw */
561        
562                  REV IN     ('0510' '0511' '0512' '0513' '0514' '0515'
563                              '0516' '0517' '0519' '0520' '0521' '0522'
564                              '0523' '0526' '0527' '0528' '0529' '0982'
565                              '0983')
566        
567                              OR
568        					
569                  /* Outpatient HCPCS Code(s) jw */
570        
571                  HCPCSCODE IN ('G0402' 'G0438' 'G0439' 'G0463' 'T1015')
572        		              and
573                              count > 3
574                         )
575                               OR
576        
577                 /* Observation CPT code(s) jw */
578        
579        		 (CPT4CODE IN ('99217' '99218' '99219' '99220')
580        		 and
581        		 count > 3
582                 )
583        
584                             THEN OUTPUT;
585        
586              *EPISODEDATE = CLMBEGINDATE;
587              *EPISODEDATE = CLAIMSERVICEDATE;
588            RUN;

NOTE: Variable CPT4CODE is uninitialized.
NOTE: Variable REV is uninitialized.
NOTE: Variable HCPCSCODE is uninitialized.
NOTE: There were 0 observations read from the data set HEDIS.COUNT.
NOTE: The data set HEDIS.CLMSQL3 has 0 observations and 11 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      

589        
590        
591        
592          PROC SORT DATA=HEDIS.CLMSQL3 OUT=HEDIS.CLM1SRT;
593          BY BENEID;
17                                                         The SAS System                                 12:11 Friday, May 25, 2018

594          RUN;

NOTE: Input data set is empty.
NOTE: The data set HEDIS.CLM1SRT has 0 observations and 11 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

595        
596          DATA HEDIS.ELIG1;
597             SET HEDIS.ENROLL2;
598        
599         /*  SET HEDIS.ELIGSAVE;   jw*/
600          RUN;

NOTE: There were 222422 observations read from the data set HEDIS.ENROLL2.
NOTE: The data set HEDIS.ELIG1 has 222422 observations and 16 variables.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.04 seconds
      

601        
602          PROC SORT NODUPKEY DATA=HEDIS.ELIG1;
603          BY BENEID;
604          RUN;

NOTE: There were 222422 observations read from the data set HEDIS.ELIG1.
NOTE: 1930 observations with duplicate key values were deleted.
NOTE: The data set HEDIS.ELIG1 has 220492 observations and 16 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.10 seconds
      cpu time            0.10 seconds
      

605        
606          * MERGE CONTINUOUS CIV ENROLLED DATA TO CLAMIS DATA;
607        
608          PROC SQL;
609            CREATE TABLE HEDIS.CLMENR AS
610              SELECT
611                A.*, B.*
612              FROM HEDIS.CLM1SRT AS A JOIN HEDIS.ELIG1 AS B
613              ON TRIM(LEFT(A.BENEID)) = TRIM(LEFT(B.BENEID));
WARNING: Variable SPONSSN already exists on file HEDIS.CLMENR.
WARNING: Variable PTNTID already exists on file HEDIS.CLMENR.
WARNING: Variable BENEID already exists on file HEDIS.CLMENR.
NOTE: Table HEDIS.CLMENR created, with 0 rows and 24 columns.

614           QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      

615        
616         *MEND SKIP;
18                                                         The SAS System                                 12:11 Friday, May 25, 2018

617        
618           **  CREATE TEXT VARABLE FOR DOB FOR MERGING               **;
619            ** CONTINUOUS ENROLLMENT FOR THE MEASUREMENT YEAR       **;
620              DATA HEDIS.CLMENR1;
621               SET HEDIS.CLMENR;
622                ATTRIB ENROLLVALID LENGTH = $1 FORMAT=$1. INFORMAT=$1.;
623                ATTRIB DOBTEXT LENGTH = $9;
624                IF (ENRL_EFF_DT GE '01FEB2016'D) AND (ENRL_EFF_DT LE '31JAN2018'D) AND
625                   (DISENRL_DT EQ '31DEC9999'D OR
626                    MISSING(DISENRL_DT)) THEN ENROLLVALID = 'Y';
627                ELSE
628                   ENROLLVALID = 'N';
629                IF ENROLLVALID = 'Y';
630                DOBTEXT = PUT(DOB, DATE9.);
631               *IF MISSING(DOB) THEN DELETE;
632               *IF MISSING(SPONSSN) THEN DELETE;
633              RUN;

NOTE: There were 0 observations read from the data set HEDIS.CLMENR.
NOTE: The data set HEDIS.CLMENR1 has 0 observations and 26 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

634        
635            **  DETERMINE ELIGIBLE POPULATION  *;
636            **  MACRO VARIABLE                 *;
637             PROC SQL;
638                 SELECT COUNT(DISTINCT BENEID)
639                 INTO :ELIGPOP
640                 FROM HEDIS.CLMENR1;
641             QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

642        
643             %PUT THE ASTHMA ELIGIBLE POPULATION IS
644                    &ELIGPOP;
THE ASTHMA ELIGIBLE POPULATION IS                    0
645        
646        
647        
648           *------------------------------------------------------------*;
649           **  JS040: READ IN PHARMACY DATA                             *;
650           *                                                            *;
651           *------------------------------------------------------------*;
652        
653             %LET F_DOS1='2015-04-01'; *** BEG DOS 1 **;
654             %LET L_DOS1='2016-03-31'; *** END DOS 1 **;
655        
656        
657        
658        DATA HEDIS.RXSQL (KEEP=RXDATE SPONSSNRX NDC DEA DDS DOBRX);
659        
660           SET HEDIS.RX;
19                                                         The SAS System                                 12:11 Friday, May 25, 2018

661        
662           RENAME SPSSN = SPONSSNRX;
663           RENAME BEGDT = RXDATE;
664           RENAME PTDOB = DOBRX;
665           RENAME UNIVPTID = DDS;
666        
667          WHERE BEGDT BETWEEN '01FEB2017'D AND '31JAN2018'D;
668        
669        RUN;

NOTE: There were 17831602 observations read from the data set HEDIS.RX.
      WHERE (BEGDT>='01FEB2017'D and BEGDT<='31JAN2018'D);
NOTE: The data set HEDIS.RXSQL has 17831602 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           4.07 seconds
      cpu time            4.08 seconds
      

670        
671        
672        
673        
674        /* JW  made copy to pull from tape will need to un commint if we get RX table in IW
675        
676        proc sql ;
677           CONNECT TO Teradata as tera (authdomain="T2017_IW_Prod" TDPID="edwfsp.fs.centene.com");
678           CREATE TABLE HEDIS.RXSQL AS
679                SELECT * FROM CONNECTION TO tera
680        
681        (SELECT
682        
683        
684        
685               PHRMCY.SPNSR_SSN                 AS SPONSSNRX,
686               PHRMCY.DSPNSD_KEY_DT             AS RXDATE,
687               PHRMCY.NATL_DRUG_CD              AS NDC,
688               PHRMCY.DEA_NUM                   AS DEA,
689               PHRMCY.DEERS_DPNDNT_SFX_CD       AS DDS,
690               PHRMCY.PTNT_BIRTH_DT             AS DOBRX
691             FROM
692              PHRMCY
693             WHERE
694              PHRMCY.DSPNSD_KEY_DT BETWEEN '2018-01-01' AND '2018-03-31'
695        	 /* JW
696              PHRMCY.DSPNSD_KEY_DT BETWEEN &F_DOS1 AND &L_DOS1
697        JW */
698        
699          /*JW    );  need to un commint once we get RX in IW  */
700        
701           /*JW need to un commint once we get RX table in IW  */
702        
703              %PUT &SQLXMSG &SQLXRC;
0
704        
705         *MEND SKIP;
706        
707          ** DELETE OF BLANK SPONSSNRX & DOBRX **;
708           DATA HEDIS.RXSQL1;
20                                                         The SAS System                                 12:11 Friday, May 25, 2018

709            SET HEDIS.RXSQL;
710            ATTRIB DOBRXTEXT LENGTH = $9;
711            DOBRXTEXT = PUT(DOBRX, DATE9.);
712            IF MISSING(DOBRX) THEN DELETE;
713            IF MISSING (SPONSSNRX) THEN DELETE;
714           RUN;

NOTE: There were 17831602 observations read from the data set HEDIS.RXSQL.
NOTE: The data set HEDIS.RXSQL1 has 17806703 observations and 7 variables.
NOTE: DATA statement used (Total process time):
      real time           5.06 seconds
      cpu time            5.08 seconds
      

715        
716          ** USE NEWLY CREATED TEXT VARIABLES FOR DOB IN MERGE       **;
717          ** MERGE PHARMACY DATA TO CLAIMS BY SPONSSN + DOB AFTER **;
718          **  MERGING ENROLLMENT TO CLAIMS TO HAVE ALL RESTRICTIONS          **;
719           PROC SQL;
720             CREATE TABLE HEDIS.CLMENRX AS
721               SELECT
722                 A.*, B.NDC,
723                      B.DEA,
724                      B.DDS,
725                      B.RXDATE
726               FROM HEDIS.CLMENR1 AS A JOIN HEDIS.RXSQL1 AS B
727                ON TRIM(LEFT(A.SPONSSN))!!TRIM(LEFT(A.DOBTEXT)) =
728                   TRIM(LEFT(B.SPONSSNRX))!!TRIM(LEFT(B.DOBRXTEXT));
ERROR: Function LEFT requires a character expression as argument 1.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
729             QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
730        


NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
NOTE: The SAS System stopped processing this step because of errors.
731        proc import datafile=HEDIS.NDCCSV;
                                ____________
                                22
                                201
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string.  

ERROR 201-322: The option is not recognized and will be ignored.

732             out=shoes
733             dbms=csv
734             replace;
735             getnames=no;
736        run;
737        
21                                                         The SAS System                                 12:11 Friday, May 25, 2018

738        
739        
740        
741           * READ IN NDC DATA FROM TEXT FILE;


742            DATA HEDIS.NDCDI;
743            INFILE DENTXT MISSOVER FIRSTOBS=1 DLM=' ';
744            INPUT   NDC                    :$11.;
745        
746           *PROC PRINT DATA=HEDIS.NDCDI;
747        
748            *MERGE ALL NDC DATA WITH PHARMACY DATA;
749        

ERROR: No logical assign for filename DENTXT.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set HEDIS.NDCDI may be incomplete.  When this step was stopped there were 0 observations and 1 variables.
WARNING: Data set HEDIS.NDCDI was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
750            PROC SQL;

751              CREATE TABLE HEDIS.ENRXNDC AS
752                SELECT
753                  A.*, B.*
754                FROM HEDIS.CLMENRX   AS A JOIN HEDIS.NDCDI AS B
755                ON TRIM(LEFT(A.NDC)) = TRIM(LEFT(B.NDC));
ERROR: File HEDIS.CLMENRX.DATA does not exist.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
756             QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
757        
758         *MEND SKIP;
759        
760          ** DETERMINE THE NUMBER OF BENES DISPENSED RX FOR Asthms **;
761          **  AND PUT INTO MACRO VARIABLE                                **;


762           PROC SQL;
763               SELECT COUNT(DISTINCT BENEID)
764               INTO :BENECOUNTRA
765               FROM HEDIS.ENRXNDC;
ERROR: File HEDIS.ENRXNDC.DATA does not exist.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
766           QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
767        
22                                                         The SAS System                                 12:11 Friday, May 25, 2018

768         %PUT Asthms BENES COUNT - DISPENSED PRESCRIPTION IS &BENECOUNTRA  ;
WARNING: Apparent symbolic reference BENECOUNTRA not resolved.
Asthms BENES COUNT - DISPENSED PRESCRIPTION IS &BENECOUNTRA
769        
770        
771        
772        
773        
774        
775        
776        
777        
778        
779        
780        GOPTIONS NOACCESSIBLE;
781        %LET _CLIENTTASKLABEL=;
782        %LET _CLIENTPROCESSFLOWNAME=;
783        %LET _CLIENTPROJECTPATH=;
784        %LET _CLIENTPROJECTNAME=;
785        %LET _SASPROGRAMFILE=;
786        
787        ;*';*";*/;quit;run;
788        ODS _ALL_ CLOSE;
789        
790        
791        QUIT; RUN;
792        
Super User
Posts: 13,293

Re: This is the entire run so far....Now I'm getting a different error and no idea why

The error you are getting is because SAS thinks you have an extra ) in the code.

If you go to the source file and find the line that is indicated by line 269

264        proc sql ;
265           CONNECT TO Teradata as tera (authdomain="T2017_IW_Prod" TDPID="edwfsp.fs.xccccc.com");
266           CREATE TABLE HEDIS.CLMSPULL AS
267                SELECT * FROM CONNECTION TO tera
268        
269        (SELECT

(following assumes you are using the SAS enhanced editor)

 

and place your cursor at the ( and then press ctrl-9  , hold down the ctrl key and press the 9 key [which as the ( as the shifted value] the cursor will move to the matching ) . The ctrl-0 may do as well.

Your cursor should move to line 334 and ) after the word NULL which is the ) that matches the (select on line 269

 

I suspect that you intended to have a ( on line 324 after AND and before the first DGNS_DMNSN.DGNS_1_CD. or possibly elsewhere in that very long WHERE clause. And since that one is missing the one on line 334 is treated as the end of the SELECT.

Ask a Question
Discussion stats
  • 3 replies
  • 142 views
  • 2 likes
  • 2 in conversation