Desktop productivity for business analysts and programmers

Teradata Error: SPool space Issue?? Line after 337 - No more spool space in IWSAS001

Reply
Frequent Contributor
Posts: 104

Teradata Error: SPool space Issue?? Line after 337 - No more spool space in IWSAS001

1                                                          The SAS System                                 15:37 Sunday, May 27, 2018

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='HEDIS_Diabetes';
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 =ffffffffffff;
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/ffffffffffff/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/ffffffffffff/HEDIS/NDCDIAB.TXT";
39         FILENAME diabexc "&cOrgNameDataAnalytics./users/ffffffffffff/HEDIS/Diabetes_List_for_AS.TXT";
40         LIBNAME ndc_diab "&cOrgNameDataAnalytics./users/ffffffffffff/HEDIS";
NOTE: Libref NDC_DIAB was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /opt/t7iw/sasdata/users/ffffffffffff/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/ffffffffffff/HEDIS/Tapes.
42         
43         /*LIBNAME HEDIS "&cOrgNameDataAnalytics./users/&FileNameUserID./&FileNameDate./&FileNameHEDIS."; */
44         
45         
46         %LET SCHIZ = 			/*Schizophrenia Value*/
47         					('F200','F201','F202','F203','F205','F2081',
2                                                          The SAS System                                 15:37 Sunday, May 27, 2018

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

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

      real time           33.21 seconds
      cpu time            27.49 seconds
      

157        
158        /*Bring in the provided medlist*/
159        
160        data HEDIS.ndcdiab;
161           set ndc_diab.ndcdiab;
162        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.01 seconds
      cpu time            0.00 seconds
      

163        
164        /* Changing 'NDC Code'n from numeric to character and rename to NDC */
165        
166         DATA HEDIS.ndcdiab;
167            SET HEDIS.ndcdiab;
168          /*  ATTRIB NDC LENGTH = $11;  */
169            NDC = PUT('NDC Code'n, $11.);
WARNING: Variable 'NDC Code'n has already been defined as numeric.
170        	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.01 seconds
      

171        
172        
173         PROC SORT DATA=HEDIS.NDCDIAB OUT=HEDIS.NDCDIAB;
174               by NDC;
175        	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.00 seconds
      

176        
177         PROC SORT DATA=HEDIS.Rx OUT=HEDIS.Rx;
178               by NDC;
179        	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           14.12 seconds
      cpu time            34.00 seconds
5                                                          The SAS System                                 15:37 Sunday, May 27, 2018

      

180        
181         /*  put Rx and medlist together  */
182           DATA HEDIS.RxNDC;
183           MERGE HEDIS.Rx (IN = INA)
184                 HEDIS.ndcdiab (IN = INB);
185              BY  NDC ;
186              IF INA and INB;
187        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.29 seconds
      cpu time            3.29 seconds
      

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

223          %PUT &SQLXMSG &SQLXRC;
0
6                                                          The SAS System                                 15:37 Sunday, May 27, 2018

224          QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           1.96 seconds
      cpu time            0.36 seconds
      

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

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

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

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

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

NOTE: There were 222796 observations read from the data set HEDIS.ENROLL2.
NOTE: The data set HEDIS.ENROLL3 has 220812 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                                 15:37 Sunday, May 27, 2018

      

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

308                CLM_LINE_FACT INNER JOIN DGNS_DMNSN ON
309                  (DGNS_DMNSN.DGNS_DMNSN_ID
310                   =CLM_LINE_FACT.DGNS_DMNSN_ID)
311                INNER JOIN CLM_HDR_DMNSN ON
312                  (CLM_LINE_FACT.CLM_HDR_ID
313                   =CLM_HDR_DMNSN.CLM_HDR_ID)
314                INNER JOIN CLNDR_TIME_DMNSN ON
315                  (CLM_LINE_FACT.SRVC_DT_ID
316                   =CLNDR_TIME_DMNSN.CLNDR_TIME_ID)
317                INNER JOIN PLAN_ADMINR_DMNSN ON
318                  (CLM_LINE_FACT.PLAN_ADMINR_ID
319                   =PLAN_ADMINR_DMNSN.PLAN_ADMINR_ID)
320                INNER JOIN PRCDR_DMNSN ON
321                  (CLM_LINE_FACT.PRCDR_DMNSN_ID
322                   =PRCDR_DMNSN.PRCDR_DMNSN_ID)
323            WHERE
324              CLNDR_TIME_DMNSN.CLNDR_DATE BETWEEN '2017-02-01' AND '2018-01-31'
325        	  AND CLM_HDR_DMNSN.PTNT_BRTH_DT BETWEEN  '1953-02-01' and '1999-02-01'
326        	  AND DGNS_DMNSN.DGNS_1_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_1_CD IN &BIPOLAR OR
327        		DGNS_DMNSN.DGNS_2_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_2_CD IN &BIPOLAR OR
328        			DGNS_DMNSN.DGNS_3_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_3_CD IN &BIPOLAR OR
329        			DGNS_DMNSN.DGNS_4_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_4_CD IN &BIPOLAR OR
330        			DGNS_DMNSN.DGNS_5_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_5_CD IN &BIPOLAR OR
331        			DGNS_DMNSN.DGNS_6_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_6_CD IN &BIPOLAR OR
332        			DGNS_DMNSN.DGNS_7_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_7_CD IN &BIPOLAR OR
333        			DGNS_DMNSN.DGNS_8_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_8_CD IN &BIPOLAR OR
334        			DGNS_DMNSN.DGNS_9_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_9_CD IN &BIPOLAR OR
335        			DGNS_DMNSN.DGNS_10_CD IN &SCHIZ OR DGNS_DMNSN.DGNS_10_CD IN &BIPOLAR OR
336        		PRCDR_DMNSN.NDC_CD IS NOT NULL
337            );
ERROR: Teradata row not delivered (trget): No more spool space in IWSAS001.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
338            %PUT &SQLXMSG &SQLXRC;
No more spool space in IWSAS001  2646
339            QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           21:08.60
      cpu time            0.02 seconds
      
340        


341        PROC SORT DATA=HEDIS.CLMSPULL OUT=HEDIS.CLMSPULL;
342                by NDC;
ERROR: Variable NDC not found.
343        	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.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      


9                                                          The SAS System                                 15:37 Sunday, May 27, 2018

344         PROC SORT DATA=HEDIS.ndcdiab OUT=HEDIS.ndcdiab;
345            by NDC;
346        
347        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
      

348                                           /* adding medlist to claims  */
349        
350           DATA HEDIS.CLMS_NDC;
351           MERGE HEDIS.CLMSPULL (IN = INA)
352                 HEDIS.ndcdiab (IN = INB);
353              BY  NDC ;
354              IF INA ;
355        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
      

356        
357        ** EG - ADD CORRECTED DEERS PATIENT ID TO CLAIMS -- IN PULL, MOST OFTEN ZEROES PLUS PATIENT ID **;
358        PROC SORT DATA=HEDIS.CLMS_NDC;
359         BY SPONSSN PTNTID;
ERROR: Variable SPONSSN not found.
ERROR: Variable PTNTID not found.
360         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
      
361        


362         PROC SORT DATA=HEDIS.ENROLL3;
363         BY SPONSSN PTNTID;
364          RUN;

NOTE: There were 220812 observations read from the data set HEDIS.ENROLL3.
NOTE: The data set HEDIS.ENROLL3 has 220812 observations and 16 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.10 seconds
      cpu time            0.10 seconds
      

10                                                         The SAS System                                 15:37 Sunday, May 27, 2018

365        
366          DATA HEDIS.CLMS_NDC2;
367          MERGE HEDIS.CLMS_NDC (IN=CLM) HEDIS.ENROLL3 (IN=ENR);
368         BY SPONSSN PTNTID;
369        IF CLM AND ENR; /*KEEPS THOSE CIV IN AGE RANGE*/
370         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
      

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

11                                                         The SAS System                                 15:37 Sunday, May 27, 2018

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

412        
413        /*At least two outpatient visits*/
414        
415        PROC SQL;
416        CREATE TABLE DENOM1B2 AS
417        SELECT
418        A.BENE,
419        COUNT(DISTINCT A.CLAIMSERVICEDATE) AS VISITCT
420        FROM HEDIS.DENOM1B1 AS A
421        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.
422        RUN;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
423        
NOTE: The SAS System stopped processing this step because of errors.
12                                                         The SAS System                                 15:37 Sunday, May 27, 2018

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
424        DATA DENOM1B;

425        SET DENOM1B2;
ERROR: File WORK.DENOM1B2.DATA does not exist.
426        WHERE VISITCT GE 2;
427        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.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

428        
429        	/*Denominator Step 1 - Part C*/
430        DATA HEDIS.DENOM1C1;
431        	SET HEDIS.CLMS_NDC2;
432          IF (DIAG1 IN &BIPOLAR OR
433          	DIAG2 IN &BIPOLAR OR
434        	DIAG3 IN &BIPOLAR OR
435        	DIAG4 IN &BIPOLAR OR
436        	DIAG5 IN &BIPOLAR OR
437        	DIAG6 IN &BIPOLAR OR
438        	DIAG7 IN &BIPOLAR OR
439        	DIAG8 IN &BIPOLAR OR
440        	DIAG9 IN &BIPOLAR OR
441        	DIAG10 IN &BIPOLAR)
442        	AND (CPT4CODE IN &BHSAOP OR REV IN &BHSAOP
443        	OR (CPT4CODE IN &BHOPPHIOP AND POS IN &BHOPPOS)
444        	OR CPT4CODE IN &ED OR REV IN &ED OR (CPT4CODE IN &BHED AND POS IN &EDPOS)
445        	OR CPT4CODE IN &BHSANONINP OR REV IN &BHSANONINP OR (CPT4CODE IN &BHNONINP AND POS IN
446        	&BHNONINPPOS));
447        
448        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.DENOM1C1 has 0 observations and 43 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
13                                                         The SAS System                                 15:37 Sunday, May 27, 2018

      

449        
450        /*VISIT GREATER THAN OR EQ TO 2*/
451        
452        PROC SQL;
453        CREATE TABLE DENOM1C2 AS
454        SELECT
455        A.BENE,
456        COUNT(DISTINCT A.CLAIMSERVICEDATE) AS VISITCT
457        FROM HEDIS.DENOM1C1 AS A
458        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.
459        RUN;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
460        
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.01 seconds
      
461        DATA DENOM1C;

462        SET DENOM1C2;
ERROR: File WORK.DENOM1C2.DATA does not exist.
463        WHERE VISITCT GE 2;
464        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.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

465        
466        
467        DATA DENOM1;
468        SET HEDIS.DENOM1A HEDIS.DENOM1B HEDIS.DENOM1C;
ERROR: File HEDIS.DENOM1B.DATA does not exist.
ERROR: File HEDIS.DENOM1C.DATA does not exist.
469        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.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

470        
471        PROC SORT DATA=DENOM1;
472        BY BENE;
473        RUN;

NOTE: Input data set is empty.
NOTE: The data set WORK.DENOM1 has 0 observations and 43 variables.
14                                                         The SAS System                                 15:37 Sunday, May 27, 2018

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

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

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
      483:19   483: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
      

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

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):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
503        data HEDIS.COUNT;

504          set HEDIS.CLMSQL2a;
505          count + 1;
506          by BENEID;
15                                                         The SAS System                                 15:37 Sunday, May 27, 2018

507          if first.beneid then count = 1;
508        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
      

509        /* Denominator Step 1  */
510        
511          DATA HEDIS.CLMSQL3;
512             SET HEDIS.COUNT;
513        
514        
515                /* ED CPT code(s) jw */
516        
517              IF (CPT4CODE IN ('99281' '99282' '99283' '99284' '99285')
518                              OR
519        
520        		/* ED REV Code(s) jw */
521        
522                  REV IN     ('0450' '0451' '0452' '0456' '0459' '0981')
523                  )
524                             OR
525        
526        	    /* Acute Inpatient CPT code(s) jw */
527        
528                 (CPT4CODE IN ('99221' '99222' '99223' '99231' '99232'
529                              '99233' '99238' '99239' '99251' '99252'
530                              '99253' '99254' '99255' '99291' )
531                              OR
532        					
533                  /* Acute Inpatient REV Code(s) jw */
534        
535                  REV IN     ('0100' '0101' '0110' '0111' '0112' '0113'
536                              '0114' '0119' '0120' '0121' '0122' '0123'
537                              '0124' '0129' '0130' '0131' '0132' '0133'
538                              '0134' '0139' '0140' '0141' '0142' '0143'
539                              '0144' '0149' '0150' '0151' '0152' '0153'
540                              '0154' '0159' '0160' '0164' '0167' '0169'
541                              '0200' '0201' '0202' '0203' '0204' '0206'
542                              '0207' '0208' '0209' '0210' '0211' '0212'
543                              '0213' '0214' '0219' '0720' '0721' '0722'
544                              '0723' '0724' '0729' '0987')
545                           )
546        					 OR
547        
548        		  /* Outpatient CPT code(s) jw */
549        
550        		 (CPT4CODE IN ('99201' '99202' '99203' '99204' '99205'
551                              '99211' '99212' '99213' '99214' '99215'
552                              '99241' '99242' '99243' '99244' '99245'
553                              '99341' '99342' '99343' '99344' '99345'
554        					  '99347' '99348' '99349' '99350' '99381'
555        					  '99382' '99383' '99384' '99385' '99386'
556        					  '99387' '99391' '99392' '99393' '99394'
16                                                         The SAS System                                 15:37 Sunday, May 27, 2018

557        					  '99395' '99396' '99397' '99401' '99402'
558        				      '99403' '99404' '99411' '99412' '99429'
559        					  '99455' '99456')
560                             OR
561        
562                  /* Outpatient REV Code(s) jw */
563        
564                  REV IN     ('0510' '0511' '0512' '0513' '0514' '0515'
565                              '0516' '0517' '0519' '0520' '0521' '0522'
566                              '0523' '0526' '0527' '0528' '0529' '0982'
567                              '0983')
568        
569                              OR
570        					
571                  /* Outpatient HCPCS Code(s) jw */
572        
573                  HCPCSCODE IN ('G0402' 'G0438' 'G0439' 'G0463' 'T1015')
574        		              and
575                              count > 3
576                         )
577                               OR
578        
579                 /* Observation CPT code(s) jw */
580        
581        		 (CPT4CODE IN ('99217' '99218' '99219' '99220')
582        		 and
583        		 count > 3
584                 )
585        
586                             THEN OUTPUT;
587        
588              *EPISODEDATE = CLMBEGINDATE;
589              *EPISODEDATE = CLAIMSERVICEDATE;
590            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.00 seconds
      

591        
592        
593        
594          PROC SORT DATA=HEDIS.CLMSQL3 OUT=HEDIS.CLM1SRT;
595          BY BENEID;
596          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
      
17                                                         The SAS System                                 15:37 Sunday, May 27, 2018


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

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

603        
604          PROC SORT NODUPKEY DATA=HEDIS.ELIG1;
605          BY BENEID;
606          RUN;

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

607        
608          * MERGE CONTINUOUS CIV ENROLLED DATA TO CLAMIS DATA;
609        
610          PROC SQL;
611            CREATE TABLE HEDIS.CLMENR AS
612              SELECT
613                A.*, B.*
614              FROM HEDIS.CLM1SRT AS A JOIN HEDIS.ELIG1 AS B
615              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.

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

617        
618         *MEND SKIP;
619        
620           **  CREATE TEXT VARABLE FOR DOB FOR MERGING               **;
621            ** CONTINUOUS ENROLLMENT FOR THE MEASUREMENT YEAR       **;
622              DATA HEDIS.CLMENR1;
623               SET HEDIS.CLMENR;
624                ATTRIB ENROLLVALID LENGTH = $1 FORMAT=$1. INFORMAT=$1.;
625                ATTRIB DOBTEXT LENGTH = $9;
626                IF (ENRL_EFF_DT GE '01FEB2016'D) AND (ENRL_EFF_DT LE '31JAN2018'D) AND
18                                                         The SAS System                                 15:37 Sunday, May 27, 2018

627                   (DISENRL_DT EQ '31DEC9999'D OR
628                    MISSING(DISENRL_DT)) THEN ENROLLVALID = 'Y';
629                ELSE
630                   ENROLLVALID = 'N';
631                IF ENROLLVALID = 'Y';
632                DOBTEXT = PUT(DOB, DATE9.);
633               *IF MISSING(DOB) THEN DELETE;
634               *IF MISSING(SPONSSN) THEN DELETE;
635              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
      

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

644        
645             %PUT THE ASTHMA ELIGIBLE POPULATION IS
646                    &ELIGPOP;
THE ASTHMA ELIGIBLE POPULATION IS                    0
647        
648        
649        
650           *------------------------------------------------------------*;
651           **  JS040: READ IN PHARMACY DATA                             *;
652           *                                                            *;
653           *------------------------------------------------------------*;
654        
655             %LET F_DOS1='2015-04-01'; *** BEG DOS 1 **;
656             %LET L_DOS1='2016-03-31'; *** END DOS 1 **;
657        
658        
659        
660        DATA HEDIS.RXSQL (KEEP=RXDATE SPONSSNRX NDC DEA DDS DOBRX);
661        
662           SET HEDIS.RX;
663        
664           RENAME SPSSN = SPONSSNRX;
665           RENAME BEGDT = RXDATE;
666           RENAME PTDOB = DOBRX;
667           RENAME UNIVPTID = DDS;
668        
669          WHERE BEGDT BETWEEN '01FEB2017'D AND '31JAN2018'D;
670        
19                                                         The SAS System                                 15:37 Sunday, May 27, 2018

671        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           3.89 seconds
      cpu time            3.89 seconds
      

672        
673        
674        
675        
676        /* JW  made copy to pull from tape will need to un commint if we get RX table in IW
677        
678        proc sql ;
679           CONNECT TO Teradata as tera (authdomain="T2017_IW_Prod" TDPID="edwfsp.fs.centene.com");
680           CREATE TABLE HEDIS.RXSQL AS
681                SELECT * FROM CONNECTION TO tera
682        
683        (SELECT
684        
685        
686        
687               PHRMCY.SPNSR_SSN                 AS SPONSSNRX,
688               PHRMCY.DSPNSD_KEY_DT             AS RXDATE,
689               PHRMCY.NATL_DRUG_CD              AS NDC,
690               PHRMCY.DEA_NUM                   AS DEA,
691               PHRMCY.DEERS_DPNDNT_SFX_CD       AS DDS,
692               PHRMCY.PTNT_BIRTH_DT             AS DOBRX
693             FROM
694              PHRMCY
695             WHERE
696              PHRMCY.DSPNSD_KEY_DT BETWEEN '2018-01-01' AND '2018-03-31'
697        	 /* JW
698              PHRMCY.DSPNSD_KEY_DT BETWEEN &F_DOS1 AND &L_DOS1
699        JW */
700        
701          /*JW    );  need to un commint once we get RX in IW  */
702        
703           /*JW need to un commint once we get RX table in IW  */
704        
705              %PUT &SQLXMSG &SQLXRC;
No more spool space in IWSAS001  2646
706        
707         *MEND SKIP;
708        
709          ** DELETE OF BLANK SPONSSNRX & DOBRX **;
710           DATA HEDIS.RXSQL1;
711            SET HEDIS.RXSQL;
712            ATTRIB DOBRXTEXT LENGTH = $9;
713            DOBRXTEXT = PUT(DOBRX, DATE9.);
714            IF MISSING(DOBRX) THEN DELETE;
715            IF MISSING (SPONSSNRX) THEN DELETE;
716           RUN;

NOTE: There were 17831602 observations read from the data set HEDIS.RXSQL.
20                                                         The SAS System                                 15:37 Sunday, May 27, 2018

NOTE: The data set HEDIS.RXSQL1 has 17806703 observations and 7 variables.
NOTE: DATA statement used (Total process time):
      real time           4.87 seconds
      cpu time            4.88 seconds
      

717        
718          ** USE NEWLY CREATED TEXT VARIABLES FOR DOB IN MERGE       **;
719          ** MERGE PHARMACY DATA TO CLAIMS BY SPONSSN + DOB AFTER **;
720          **  MERGING ENROLLMENT TO CLAIMS TO HAVE ALL RESTRICTIONS          **;
721           PROC SQL;
722             CREATE TABLE HEDIS.CLMENRX AS
723               SELECT
724                 A.*, B.NDC,
725                      B.DEA,
726                      B.DDS,
727                      B.RXDATE
728               FROM HEDIS.CLMENR1 AS A JOIN HEDIS.RXSQL1 AS B
729                ON TRIM(LEFT(A.SPONSSN))!!TRIM(LEFT(A.DOBTEXT)) =
730                   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.
731             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
      
732        


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

734             out=shoes
735             dbms=csv
736             replace;
737             getnames=no;
738        run;
739        
740        
741        
742        
743           * READ IN NDC DATA FROM TEXT FILE;


744            DATA HEDIS.NDCDI;
745            INFILE DENTXT MISSOVER FIRSTOBS=1 DLM=' ';
21                                                         The SAS System                                 15:37 Sunday, May 27, 2018

746            INPUT   NDC                    :$11.;
747        
748           *PROC PRINT DATA=HEDIS.NDCDI;
749        
750            *MERGE ALL NDC DATA WITH PHARMACY DATA;
751        

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
      
752            PROC SQL;

753              CREATE TABLE HEDIS.ENRXNDC AS
754                SELECT
755                  A.*, B.*
756                FROM HEDIS.CLMENRX   AS A JOIN HEDIS.NDCDI AS B
757                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.
758             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
      
759        
760         *MEND SKIP;
761        
762          ** DETERMINE THE NUMBER OF BENES DISPENSED RX FOR Asthms **;
763          **  AND PUT INTO MACRO VARIABLE                                **;


764           PROC SQL;
765               SELECT COUNT(DISTINCT BENEID)
766               INTO :BENECOUNTRA
767               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.
768           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
      
769        
770         %PUT Asthms BENES COUNT - DISPENSED PRESCRIPTION IS &BENECOUNTRA  ;
WARNING: Apparent symbolic reference BENECOUNTRA not resolved.
Asthms BENES COUNT - DISPENSED PRESCRIPTION IS &BENECOUNTRA
771        
772        
773        
774        
775        
22                                                         The SAS System                                 15:37 Sunday, May 27, 2018

776        
777        
778        
779        
780        
781        
782        GOPTIONS NOACCESSIBLE;
783        %LET _CLIENTTASKLABEL=;
784        %LET _CLIENTPROCESSFLOWNAME=;
785        %LET _CLIENTPROJECTPATH=;
786        %LET _CLIENTPROJECTNAME=;
787        %LET _SASPROGRAMFILE=;
788        
789        ;*';*";*/;quit;run;
790        ODS _ALL_ CLOSE;
791        
792        
793        QUIT; RUN;
794        
Super User
Posts: 6,644

Re: Teradata Error: SPool space Issue?? Line after 337 - No more spool space in IWSAS001

It's a long program, and difficult to tell where to begin.  Let's start with a few issues.

 

Before that error is reached, there is already a warning that shouldn't be ignored, and a note about a variable being uninitialized that also shouldn't be ignored.  Did you miss seeing those, or were you just not sure what to do about them?

 

Immediately after the step that produces the error noted on line 337, there is an attempt to sort the output data set (HEDIS.CLMSPULL) by NDC, which does not exist.  Did you mean to pull it in, as part of the previous step (the step that generated the error)?

 

You will need to address these issues, regardless of whether or not they clean up the problem on line 337.  It's better to fix them first, so we know that they are not part of the problem.

Respected Advisor
Posts: 4,702

Re: Teradata Error: SPool space Issue?? Line after 337 - No more spool space in IWSAS001

[ Edited ]

@essdee

It's great that you've chosen an explanatory subject line and also that you've provided the SAS log. I suggest that you add the log next time as an attachment and only post the relevant bit directly into the text. Makes things a bit friendlier to read.

 

Now for your actual problem: That's purely a Teradata issue and if you Google you'll find in Teradata forums that there can be different reasons for such an error condition (inefficient query, skewed table, statistics not up-to-date).

 

We can only see the SQL query code here. Not knowing your table volumes and relationships I can only give you some hints what's eventually not that efficient in your query.

 

1: Missing bracket

AND is stronger than OR. I believe you need to have a bracket around your OR conditions as else they will all get applied independently and if only one of them is true you'll select the records.

WHERE CLNDR_TIME_DMNSN.CLNDR_DATE BETWEEN '2017-02-01'
    AND '2018-01-31'
    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 .....

      )

 

2. Reduce data volumes as early as possible

Instead of only filtering the result set of your query in the Where clause, move selections into the ON clause where possible. Looking into your code I believe you can move everything in your where clause directly into the ON clauses. Below one of the cases.

FROM CLM_LINE_FACT
INNER JOIN DGNS_DMNSN
  ON 
    (
    DGNS_DMNSN.DGNS_DMNSN_ID = CLM_LINE_FACT.DGNS_DMNSN_ID
    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
      )
    )

If tweaking the SQL for efficiency doesn't resolve the issue for you then you're likely at the point where you need Teradata DBA support.

Ask a Question
Discussion stats
  • 2 replies
  • 121 views
  • 1 like
  • 3 in conversation