96 -------------------------------------------------------------------------------------------*/ 97 98 %hospice(memtable=Member_Elig,measure=APC,excl_rsns=NONE); MPRINT(HOSPICE): data _null_; MPRINT(HOSPICE): retain var; MPRINT(HOSPICE): length var $100 word $15; MPRINT(HOSPICE): i=1; MPRINT(HOSPICE): do until (scan("NONE",i,"|") = " " or (i>99)) ; MPRINT(HOSPICE): word = scan("NONE" ,i,"|"); MPRINT(HOSPICE): if i = 1 then var = "'" || trim(word) || "'"; MPRINT(HOSPICE): else var = trim(var) || "," || "'" || trim(word) || "'" ; MPRINT(HOSPICE): i+1; MPRINT(HOSPICE): end; MPRINT(HOSPICE): call symput("exrsn",trim(var)); MPRINT(HOSPICE): run; NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds MPRINT(HOSPICE): proc sql; MPRINT(HOSPICE): options nomprint nosymbolgen MPRINT(HOSPICE): EXECUTE( update HDMPRD.Member_Elig set Exclusion_Rsn = 'HOSPICE' where Measure_Tag = substr('APC',1,3) and Hedis_Id in (select Hedis_Id from HDMAPP.Hospice) and MR_Com_Ind in ('M','A', 'C', 'P', 'E') and Exclusion_Rsn in ('NONE') ) by HDMconnect; NOTE: No data found/modified. MPRINT(HOSPICE): title2 "Count of Unique Exclusion Reasons from Member_Elig table "; MPRINT(HOSPICE): SELECT * FROM CONNECTION TO HDMCONNECT (select MR_Com_Ind , Exclusion_Rsn , count(*) as Exclusion_count from HDMPRD.Member_Elig where Measure_Tag = substr('APC',1,3) group by rollup(MR_Com_Ind, Exclusion_Rsn) order by MR_Com_Ind, Exclusion_Rsn ); MPRINT(HOSPICE): disconnect from HDMconnect; MPRINT(HOSPICE): quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.47 seconds cpu time 0.06 seconds 99 100 /******************************************************************************************* 101 Step 2 102 Use proc sql to establish db2 HDM connection. Review what is in Member_Elig. 103 *********************************************************************************************/ 104 105 proc sql; 106 options nomprint nosymbolgen; 107 connect to odbc as HDMConnect (datasrc=&datasrc user=&userid password=&password); 108 option mprint; 109 %let cl=freqs in Member_Elig table; 110 TITLE2 "Freq of Excl Reasons in MEMBER ELIG table prior to any numerator update"; 111 SELECT * FROM CONNECTION TO HDMCONNECT 112 (SELECT Exclusion_Rsn, COUNT(Exclusion_Rsn) as Exclusion_Rsn_Counts 113 FROM &memschema..Member_Elig 114 WHERE Measure_Tag = 'APC' 115 GROUP BY Exclusion_Rsn 116 ); 117 118 /*------------------------------------------------------------------------------------------- 119 Step 3 - Pull all patient prescription drug events. 120 -------------------------------------------------------------------------------------------*/ 121 122 EXECUTE( 123 declare global temporary table APC_treat_date 124 ( Hedis_Id char (29) 125 , DRUG_ID CHAR (20) 126 , START_Dt date 127 , END_Dt date) 128 in "&sestblspc" 129 on commit preserve rows not logged 130 ) by HDMconnect; 131 132 EXECUTE( 133 insert into session.APC_treat_date 134 select distinct EV.Hedis_Id, 135 EV.EVENT, 136 EV.EVENT_START_DT, 137 EV.EVENT_END_DT 138 from &memschema..Member_EVNT EV 139 where EV.measure_tag='APC' AND 140 EV.HEDIS_ID IN (SELECT DISTINCT ML.HEDIS_ID FROM &memschema..Member_ELIG ml 141 where ml.Measure_Tag = 'APC' 142 and ml.Exclusion_Rsn = 'NONE') 143 ) by HDMconnect; 144 145 create table APC_treat_date as 146 select * from connection to HDMconnect 147 (select * from SESSION.APC_treat_date 148 ORDER BY HEDIS_ID,START_DT ); NOTE: Table WORK.APC_TREAT_DATE created, with 1593 rows and 4 columns. 149 150 /*-------------------DIAGNOSTIC PRINTS------------------------------------------------------- */ 151 %TBL_COUNTS(TBL=SESSION.APC_treat_date); MPRINT(TBL_COUNTS): TITLE2 "Record counts in SESSION.APC_treat_date table"; MPRINT(TBL_COUNTS): SELECT * FROM CONNECTION TO HDMCONNECT (SELECT COUNT(*) as Record_Count , COUNT(distinct Hedis_Id) as Unique_Hedis_Id FROM SESSION.APC_treat_date ); 152 %TBL_PRINT(TBL=SESSION.APC_treat_date, ORDER="Hedis_Id, START_Dt"); MPRINT(TBL_PRINT): TITLE2 "Limited print of SESSION.APC_treat_date table"; MPRINT(TBL_PRINT): SELECT * FROM CONNECTION TO HDMCONNECT (select * from SESSION.APC_treat_date order by Hedis_Id, START_Dt fetch first 35 rows only ); 153 154 155 disconnect from HDMconnect; 156 quit; NOTE: PROCEDURE SQL used (Total process time): real time 1.53 seconds cpu time 0.18 seconds 157 158 159 /*to get the rx period for hedis_id drilling down to date*/ 160 data Apc_treat_date2; 161 do until (last.hedis_id); 162 set Apc_treat_date; 163 by hedis_id START_DT; 164 if last.hedis_id then rxend=END_DT; 165 end; 166 167 168 do until (last.hedis_id); 169 set Apc_treat_date; 170 by hedis_id START_DT; 171 if first.hedis_id then 172 do dt=START_DT by 1 to rxend; 173 output; 174 end; 175 end; 176 format dt mmddyy10.; 177 keep hedis_id dt; 178 run; NOTE: There were 1593 observations read from the data set WORK.APC_TREAT_DATE. NOTE: There were 1593 observations read from the data set WORK.APC_TREAT_DATE. NOTE: The data set WORK.APC_TREAT_DATE2 has 106460 observations and 2 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.00 seconds 179 180 181 *-------------------------------- Diagnostic Prints --------------------------------; 182 Title2 "count from work.Apc_treat_date2 "; 183 proc sql; 184 select count(*) as Total_Record_Count, count(distinct HEDIS_ID) as Unique_HEDIS_Count 185 from Apc_treat_date2 186 ; 187 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.07 seconds cpu time 0.03 seconds 188 189 Title2 "print from work.Apc_treat_date2"; 190 proc print data=work.Apc_treat_date2 (obs=30); 191 run; NOTE: There were 30 observations read from the data set WORK.APC_TREAT_DATE2. NOTE: PROCEDURE PRINT used (Total process time): real time 0.02 seconds cpu time 0.01 seconds 192 Title2 ; 193 194 195 /*flag each date with the concurrent rx, the dimension of the temp array is equal to the number of types of rxname, this is 195! the KEY step*/ 196 data Apc_treat_date3; 197 if _n_=1 then do; 198 if 0 then set Apc_treat_date; 199 declare hash h(dataset:'Apc_treat_date', multidata:'y'); 200 h.definekey('hedis_id'); 201 h.definedata(all:'y'); 202 h.definedone(); 203 end; 204 205 set Apc_treat_date2; 206 length concrx $100.; 207 array con(10) $6. _temporary_; 208 do rc=h.find() by 0 while (rc=0); 209 if START_DT <= dt <= END_DT and DRUG_ID not in con then do; 210 i+1; 211 con(i)=DRUG_ID; 212 end; 213 rc=h.find_next(); 214 end; 215 call sortc (of con(*)); 216 concrx=cats(of con(*)); 217 call missing (of con(*)); 218 i=0; 219 format dt date9.; 220 keep HEDIS_id dt concrx; NOTE: There were 1593 observations read from the data set WORK.APC_TREAT_DATE. NOTE: There were 106460 observations read from the data set WORK.APC_TREAT_DATE2. NOTE: The data set WORK.APC_TREAT_DATE3 has 106460 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 0.12 seconds cpu time 0.09 seconds 221 proc sort nodup ; 222 by HEDIS_id dt concrx ; 223 run; NOTE: There were 106460 observations read from the data set WORK.APC_TREAT_DATE3. NOTE: 0 duplicate observations were deleted. NOTE: The data set WORK.APC_TREAT_DATE3 has 106460 observations and 3 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.06 seconds cpu time 0.06 seconds 224 225 proc sort DATA=Apc_treat_date3; 226 by HEDIS_id concrx ; 227 run; NOTE: There were 106460 observations read from the data set WORK.APC_TREAT_DATE3. NOTE: The data set WORK.APC_TREAT_DATE3 has 106460 observations and 3 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.04 seconds cpu time 0.12 seconds 228 229 230 *-------------------------------- Diagnostic Prints --------------------------------; 231 Title2 "count from work.Apc_treat_date3 "; 232 proc sql; 233 select count(*) as Total_Record_Count, count(distinct HEDIS_ID) as Unique_HEDIS_Count 234 from Apc_treat_date3 235 ; 236 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.02 seconds cpu time 0.03 seconds 237 238 Title2 "print from work.Apc_treat_date3"; 239 proc print data=work.Apc_treat_date3 (obs=30); 240 run; NOTE: There were 30 observations read from the data set WORK.APC_TREAT_DATE3. NOTE: PROCEDURE PRINT used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 241 Title2 ; 242 243 244 /* keep only concurrent medications along with date*/ 245 246 data Apc_treat_date4 ; 247 set Apc_treat_date3 ; 248 by HEDIS_id concrx; 249 if lengthn(concrx)>6 then output; 250 run; NOTE: There were 106460 observations read from the data set WORK.APC_TREAT_DATE3. NOTE: The data set WORK.APC_TREAT_DATE4 has 1929 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.03 seconds 251 252 data Apc_treat_date5 ; 253 set Apc_treat_date4 ; 254 by HEDIS_id concrx; 255 format st_dt ed_dt date9.; 256 if first.concrx then do ; 257 st_dt=dt; 258 output; 259 end; 260 if last.concrx then do; 261 ed_dt=dt; 262 output; 263 end; 264 265 run; NOTE: There were 1929 observations read from the data set WORK.APC_TREAT_DATE4. NOTE: The data set WORK.APC_TREAT_DATE5 has 86 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 266 267 268 data Apc_treat_date6 ; 269 set Apc_treat_date5 ; 270 by HEDIS_id concrx; 271 format st_dt_1 date9.; 272 st_dt_1=lag(st_dt); 273 run; NOTE: There were 86 observations read from the data set WORK.APC_TREAT_DATE5. NOTE: The data set WORK.APC_TREAT_DATE6 has 86 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds 274 275 data Apc_treat_date7 (rename =(st_dt_1=st_dt)); 276 retain hedis_id concrx st_dt_1; 277 set Apc_treat_date6 (drop=dt st_dt); 278 by HEDIS_id concrx; 279 where st_dt_1 <>. and ed_dt <>.; NOTE: The "<>" operator is interpreted as "not equals". NOTE: There were 43 observations read from the data set WORK.APC_TREAT_DATE6. WHERE (st_dt_1 not = .) and (ed_dt not = .); NOTE: The data set WORK.APC_TREAT_DATE7 has 43 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 280 proc sort ;by HEDIS_id st_dt concrx; run; NOTE: There were 43 observations read from the data set WORK.APC_TREAT_DATE7. NOTE: The data set WORK.APC_TREAT_DATE7 has 43 observations and 4 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 281 282 data Apc_treat_date8 ; 283 set Apc_treat_date7 ; 284 by HEDIS_id st_dt concrx; 285 format ED_dt_1 date9.; 286 concrx_days=(ed_dt-st_dt) +1; 287 ED_DT_1=lag(ED_DT); 288 if first.HEDIS_ID then ED_DT_1 = . ; 289 GAP= (ST_DT - ED_DT_1)-1; 290 run; NOTE: Missing values were generated as a result of performing an operation on missing values. Each place is given by: (Number of times) at (Line):(Column). 34 at 289:13 NOTE: There were 43 observations read from the data set WORK.APC_TREAT_DATE7. NOTE: The data set WORK.APC_TREAT_DATE8 has 43 observations and 7 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds 291 292 proc summary data = Apc_treat_date8(where=(gap <=15 )); 293 class hedis_id; 294 var concrx_days; 295 output out=Apc_concrx_days /* (drop=_type_ _freq_) */ sum=; 296 run; NOTE: There were 41 observations read from the data set WORK.APC_TREAT_DATE8. WHERE gap<=15; NOTE: The data set WORK.APC_CONCRX_DAYS has 35 observations and 4 variables. NOTE: PROCEDURE SUMMARY used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 297 298 data Apc_treat_date9; 299 retain conrx_day_tot; 300 set Apc_treat_date8 (keep= hedis_id concrx st_dt ed_dt concrx_days gap); 301 by HEDIS_id st_dt concrx; 302 303 if first.hedis_id then do ; 304 conrx_day_tot=concrx_days; 305 output; 306 end; 307 if gap>=0 and gap<=15 then do; 308 conrx_day_tot=conrx_day_tot+concrx_days; 309 output; 310 end; 311 else if gap>15 then do; 312 conrx_day_tot=concrx_days; 313 output; 314 end; 315 316 run; NOTE: There were 43 observations read from the data set WORK.APC_TREAT_DATE8. NOTE: The data set WORK.APC_TREAT_DATE9 has 40 observations and 7 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds 317 318 proc sort ;by HEDIS_id conrx_day_tot; run; NOTE: There were 40 observations read from the data set WORK.APC_TREAT_DATE9. NOTE: The data set WORK.APC_TREAT_DATE9 has 40 observations and 7 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 319 320 321 *-------------------------------- Diagnostic Prints --------------------------------; 322 Title2 "count from work.Apc_treat_date9 "; 323 proc sql; 324 select count(*) as Total_HEDIS_Count, count(distinct HEDIS_ID) as Unique_HEDIS_Count 325 from work.Apc_treat_date9 326 ; 327 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 328 329 Title2 "print from work.Apc_treat_date9 "; 330 proc print data=work.Apc_treat_date9 (obs=35); 331 run; NOTE: There were 35 observations read from the data set WORK.APC_TREAT_DATE9. NOTE: PROCEDURE PRINT used (Total process time): real time 0.02 seconds cpu time 0.01 seconds 332 Title2 ; 333 334 data Apc_treat_date10; 335 set Apc_treat_date9; 336 by HEDIS_id conrx_day_tot; 337 if last.hedis_id; 338 run; NOTE: There were 40 observations read from the data set WORK.APC_TREAT_DATE9. NOTE: The data set WORK.APC_TREAT_DATE10 has 34 observations and 7 variables. NOTE: DATA statement used (Total process time): real time 0.03 seconds cpu time 0.00 seconds 339 340 341 proc sql; 342 create table Apc_treat_date11 as 343 select distinct a.Hedis_Id, b.concrx, b.st_DT, b.conrx_day_tot 344 345 from Apc_treat_date as a left join Apc_treat_date10 as b 346 on a.hedis_id=b.hedis_id 347 ; NOTE: Table WORK.APC_TREAT_DATE11 created, with 702 rows and 4 columns. 348 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.03 seconds cpu time 0.03 seconds 349 350 351 352 proc sql; 353 options nomprint nosymbolgen; 354 connect to odbc as HDMconnect (datasrc=&datasrc user=&userid password=&password); 355 option mprint; 356 /******************************************************************************************** 357 Step 4 - 358 Create an empty temp table in HDM 359 *********************************************************************************************/ 360 361 EXECUTE( drop table &TmpSchema..APC_NUM) BY HDMconnect; ERROR: CLI execute error: [IBM][CLI Driver][DB2/AIX64] SQL0204N "HDMAPP.APC_NUM" is an undefined name. SQLSTATE=42704 362 363 EXECUTE( 364 create table &TmpSchema..APC_NUM 365 ( Hedis_Id char(29) 366 , concrx char(100) 367 , st_DT DATE 368 , conrx_day_tot INTEGER) 369 in &twotblspc 370 not logged initially 371 ) BY HDMconnect; 372 373 374 disconnect from HDMconnect; 375 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.74 seconds cpu time 0.03 seconds 376 377 options nomprint nosymbolgen; 378 libname Hedisdb2 odbc datasrc=&datasrc schema=&tmpschema user=&userid password=&password; NOTE: Libref HEDISDB2 was successfully assigned as follows: Engine: ODBC Physical Name: PHEDISRP 379 option mprint; 380 proc datasets; 381 append base=HEDISdb2.APC_NUM 382 data=Apc_treat_date11; 383 RUN; NOTE: Appending WORK.APC_TREAT_DATE11 to HEDISDB2.APC_NUM. NOTE: There were 702 observations read from the data set WORK.APC_TREAT_DATE11. NOTE: 702 observations added. NOTE: The data set HEDISDB2.APC_NUM has . observations and 4 variables. 384 385 386 /* ------------------------------------------------------------------------------------------- 387 Step 6 - 388 Populate the Member_Cmpl table with the appropriate NonCmpl_Rsn value. 389 Members with a prescription for Asthma medication during the measurement year get a 390 NonCmpl_Rsn = 'NONE' for meeting the Numerator criteria (medication ratio is 0.50 or greater). 391 Members not meeting that criteria get a NonCmpl_Rsn = 'LOWRATIO' 392 393 Member_Cmpl columns: 394 Hedis_Id 395 Measure_Tag 396 Mbr_Elig_Key 397 Cmpl_Rule_Id 398 MR_Com_Ind 399 Non_Cmpl_Rsn 400 Eval_Dt 401 Origin_Dt 402 --------------------------------------------------------------------------------------------- */ NOTE: PROCEDURE DATASETS used (Total process time): real time 1.20 seconds cpu time 0.09 seconds 403 proc sql; 404 options nomprint nosymbolgen; *JTran 20Jul2015: suppress password written to log; 405 connect to odbc as HDMconnect (datasrc=&datasrc user=&userid password=&password); 406 option mprint; 407 408 /* --------------------- DIAGNOSTIC PRINTS -------------------------------------------------- */ 409 410 %TBL_COUNTS(TBL=&TmpSchema..APC_NUM,COL=Hedis_Id); MPRINT(TBL_COUNTS): TITLE2 "Record counts in HDMAPP.APC_NUM table"; MPRINT(TBL_COUNTS): SELECT * FROM CONNECTION TO HDMCONNECT (SELECT COUNT(*) as Record_Count , COUNT(distinct Hedis_Id) as Unique_Hedis_Id FROM HDMAPP.APC_NUM ); 411 %TBL_PRINT(TBL=&TmpSchema..APC_NUM,ORDER="Hedis_Id"); MPRINT(TBL_PRINT): TITLE2 "Limited print of HDMAPP.APC_NUM table"; MPRINT(TBL_PRINT): SELECT * FROM CONNECTION TO HDMCONNECT (select * from HDMAPP.APC_NUM order by Hedis_Id fetch first 35 rows only ); 412 413 EXECUTE( Delete from &memschema..Member_Cmpl 414 where Measure_Tag = 'APC' 415 and Cmpl_Rule_Id = 'NUM01' 416 ) by HDMconnect; NOTE: No data found/modified. 417 418 EXECUTE( insert into &memschema..Member_Cmpl 419 select distinct 420 A.Hedis_Id 421 , 'APC' 422 , B.Mbr_Elig_Key 423 , 'NUM01' 424 , B.MR_Com_Ind 425 , case 426 when A.conrx_day_tot >=90 427 then 'NONE' 428 else 'NO_CONC_RX' 429 end 430 , case 431 when 1=2 432 then current date 433 end 434 , current date 435 from &TmpSchema..APC_NUM A 436 JOIN &memschema..Member_ELIG B 437 ON A.HEDIS_ID=B.HEDIS_ID AND B.MEASURE_TAG='APC' 438 439 ) by HDMconnect; 440 441 /*-------------------DIAGNOSTIC PRINTS------------------------------------------------------- */ 442 443 %TBL_COUNTS(TBL=&memschema..Member_Cmpl,Measure=APC,COL=Hedis_Id,Group="MR_Com_Ind"); MPRINT(TBL_COUNTS): TITLE2 "Record counts in HDMPRD.Member_Cmpl table for Measure APC"; MPRINT(TBL_COUNTS): SELECT * FROM CONNECTION TO HDMCONNECT (SELECT MR_Com_Ind, COUNT(*) as Record_Count , COUNT(distinct Hedis_Id) as Unique_Hedis_Id FROM HDMPRD.Member_Cmpl WHERE Measure_Tag = 'APC' GROUP BY MR_Com_Ind ); 444 %TBL_PRINT(TBL=&memschema..Member_Cmpl,ORDER="Hedis_Id,Eval_Dt",MEASURE=APC); MPRINT(TBL_PRINT): TITLE2 "Limited print of HDMPRD.Member_Cmpl table for Measure APC"; MPRINT(TBL_PRINT): SELECT * FROM CONNECTION TO HDMCONNECT (select * from HDMPRD.Member_Cmpl where Measure_Tag = 'APC' order by Hedis_Id,Eval_Dt fetch first 35 rows only ); 445 446 447 /* ---------------------------------------------------------------------------------------- 448 Drop the transient table used during program prior to running final report and 449 disconnecting from the HDM. 450 -----------------------------------------------------------------------------------------*/ 451 452 453 EXECUTE ( drop table &TmpSchema..APC_NUM ) by HDMconnect; 454 455 456 /*------------------------------------------------------------------------------------------- 457 Step 7 - Generate report for each age grouping reported in the 458 Data Submission Tool to NCQA . 459 -------------------------------------------------------------------------------------------*/ 460 %TBL_COUNTS(TBL=&memschema..Member_elig,Measure=APC,COL=Hedis_Id,Group="MR_Com_Ind"); MPRINT(TBL_COUNTS): TITLE2 "Record counts in HDMPRD.Member_elig table for Measure APC"; MPRINT(TBL_COUNTS): SELECT * FROM CONNECTION TO HDMCONNECT (SELECT MR_Com_Ind, COUNT(*) as Record_Count , COUNT(distinct Hedis_Id) as Unique_Hedis_Id FROM HDMPRD.Member_elig WHERE Measure_Tag = 'APC' GROUP BY MR_Com_Ind ); 461 %TBL_PRINT(TBL=&memschema..Member_elig,ORDER="Hedis_Id",MEASURE=APC); MPRINT(TBL_PRINT): TITLE2 "Limited print of HDMPRD.Member_elig table for Measure APC"; MPRINT(TBL_PRINT): SELECT * FROM CONNECTION TO HDMCONNECT (select * from HDMPRD.Member_elig where Measure_Tag = 'APC' order by Hedis_Id fetch first 35 rows only ); 462 463 %let cl=counts in &memschema..Member_Elig; 464 TITLE2 "Record counts for APC in &memschema..Member_Elig"; 465 SELECT * FROM CONNECTION TO HDMCONNECT 466 (SELECT MR_COM_Ind 467 , Exclusion_Rsn 468 , count(DISTINCT Hedis_Id) as Unique_Hedis_Ids 469 , count(*) as Record_Counts 470 , count(Exclusion_Rsn) as Exclusion_Rsn_Counts 471 from &memschema..Member_Elig 472 where Measure_Tag = 'APC' 473 group by rollup (MR_COM_Ind, Exclusion_Rsn) 474 order by MR_COM_Ind, Exclusion_Rsn 475 ); 476 477 create table countdata2 as 478 SELECT * FROM CONNECTION TO HDMCONNECT 479 (select 480 ab.MR_Com_Ind as Model 481 , Age_Band 482 , mc.exclusion_Rsn 483 , count (*) as exclusion_Rsn_count 484 from &memschema..Member_Elig mc 485 join &memschema..Member_Cntl mn 486 on mc.Hedis_Id = mn.Hedis_Id 487 join &ABTschema..ABcontrol ab 488 on mn.Measure_Age = ab.Age 489 and mc.MR_Com_Ind = ab.MR_Com_Ind 490 and mn.Gender = ab.Gender 491 where mc.Measure_Tag = 'APC' 492 and mn.Measure_Tag = 'APC' 493 and ab.Measure_Tag = 'APC' 494 group by ab.MR_Com_Ind, Age_Band, mc.exclusion_Rsn 495 order by Model, Age_Band, mc.exclusion_Rsn 496 ); NOTE: Table WORK.COUNTDATA2 created, with 8 rows and 4 columns. 497 498 /* -------- create SAS table of all possible class variable values -------------------------- */ 499 500 create table classes as 501 select * from connection to HDMconnect 502 (select distinct 503 ab.MR_Com_Ind as Model 504 , Age_Band 505 , Non_Cmpl_Rsn 506 from &memschema..Member_Cmpl mc 507 join &memschema..Member_Cntl mn 508 on mc.Hedis_Id = mn.Hedis_Id 509 join &ABTschema..ABcontrol ab 510 on mn.Measure_Age = ab.Age 511 and mc.MR_Com_Ind = ab.MR_Com_Ind 512 and mn.Gender = ab.Gender 513 where mc.Measure_Tag = 'APC' 514 and mn.Measure_Tag = 'APC' 515 and ab.Measure_Tag = 'APC' 516 and Cmpl_Rule_Id = 'NUM01' 517 group by ab.MR_Com_Ind, Age_Band, Non_Cmpl_Rsn 518 order by Model, Age_Band, Non_Cmpl_Rsn 519 ); NOTE: Table WORK.CLASSES created, with 8 rows and 3 columns. 520 521 /* -------- create SAS table of Service counts --------------------------------------------- */ 522 523 create table countdata as 524 select * from connection to HDMconnect 525 (select 526 ab.MR_Com_Ind as Model 527 , Age_Band 528 , Non_Cmpl_Rsn 529 , count (*) as Non_Cmpl_Rsn_count 530 from &memschema..Member_Cmpl mc 531 join &memschema..Member_Cntl mn 532 on mc.Hedis_Id = mn.Hedis_Id 533 join &ABTschema..ABcontrol ab 534 on mn.Measure_Age = ab.Age 535 and mc.MR_Com_Ind = ab.MR_Com_Ind 536 and mn.Gender = ab.Gender 537 where mc.Measure_Tag = 'APC' 538 and mn.Measure_Tag = 'APC' 539 and ab.Measure_Tag = 'APC' 540 and Cmpl_Rule_Id = 'NUM01' 541 group by ab.MR_Com_Ind, Age_Band, Non_Cmpl_Rsn 542 order by Model, Age_Band, Non_Cmpl_Rsn 543 ); NOTE: Table WORK.COUNTDATA created, with 8 rows and 4 columns. 544 545 disconnect from HDMconnect; 546 quit; NOTE: PROCEDURE SQL used (Total process time): real time 3.27 seconds cpu time 0.46 seconds 547 548 %let cl=counts in &memschema..Member_Elig; 549 TITLE2 "Record counts for APC in &memschema..Member_Elig"; 550 proc tabulate data=countdata2 ; 551 class Model Age_Band exclusion_Rsn; 552 var exclusion_rsn_count; 553 table Model 554 , (Age_Band='Age Band' ALL='TOTALS')*(exclusion_Rsn='Exclusion Reason' ALL='Totals') 555 , exclusion_Rsn_count='Exclusion_Rsn Count' *sum='' *f=comma10. 556 / printmiss misstext='0'; 557 %let CL=Report; 558 run; NOTE: There were 8 observations read from the data set WORK.COUNTDATA2. NOTE: PROCEDURE TABULATE used (Total process time): real time 0.15 seconds cpu time 0.10 seconds 559 560 %let cl=counts in &memschema..Member_Elig; 561 TITLE2 "Record counts for APC in &memschema..Member_Elig"; 562 TITLE3 'DST - Eligibility'; 563 proc tabulate data=countdata2 ; 564 where Exclusion_Rsn <> 'NOENROLL'; NOTE: The "<>" operator is interpreted as "not equals". 565 class Model Age_Band exclusion_Rsn; 566 var exclusion_rsn_count; 567 table Model 568 , (Age_Band='Age Band' ALL='TOTALS')*(exclusion_Rsn='Exclusion Reason' ALL='Totals') 569 , exclusion_Rsn_count='Exclusion_Rsn Count' *sum='' *f=comma10. 570 / printmiss misstext='0'; 571 %let CL=Report; 572 run; NOTE: There were 8 observations read from the data set WORK.COUNTDATA2. WHERE Exclusion_Rsn not = 'NOENROLL'; NOTE: PROCEDURE TABULATE used (Total process time): real time 0.06 seconds cpu time 0.06 seconds 573 574 proc tabulate data=countdata /* tabular report on services and months */ 575 classdata=classes; 576 class Model Age_Band Non_Cmpl_Rsn; 577 var Non_Cmpl_Rsn_count; 578 table Model 579 , (Age_Band='Age Band' ALL='TOTALS')*(Non_Cmpl_Rsn='Non-Compliance Reason' ALL='Totals') 580 , Non_Cmpl_Rsn_count='Compliance Count' *sum='' *f=comma10. 581 / printmiss misstext='0'; 582 %let CL=Report; 583 title2 "Age Band Report"; 584 TITLE3 'DST - Denominator and Numerator'; 585 run; NOTE: There were 8 observations read from the data set WORK.COUNTDATA. NOTE: There were 8 observations read from the data set WORK.CLASSES. NOTE: PROCEDURE TABULATE used (Total process time): real time 0.04 seconds cpu time 0.04 seconds 586 587 /*---------------------------------------------------------------------------------------- 588 Run general macros prior to closing out session. 589 Output log to the documentation folder. 590 Close PDF output. 591 ----------------------------------------------------------------------------------------*/ 592 593 %CCT_Parms(measure=APC,run=NUM); MPRINT(CCT_PARMS): data _null_; MPRINT(CCT_PARMS): call symput("mfolder",(substr("APC",1,1) || ' ' || substr("APC",2,1) || ' ' || substr("APC",3,1)) ); MPRINT(CCT_PARMS): run; NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds MPRINT(CCT_PARMS): libname n "K:\HSE\HEDIS\2018\Documentation\Measures\A P C"; NOTE: Libref N was successfully assigned as follows: Engine: V9 Physical Name: K:\HSE\HEDIS\2018\Documentation\Measures\A P C MPRINT(CCT_PARMS): proc sql; MPRINT(CCT_PARMS): options nomprint nosymbolgen MPRINT(CCT_PARMS): create table n.APC_NUM_HDMPRD_CCT as SELECT * FROM CONNECTION TO HDMCONNECT (select 'APC' as Measure_Tag , cc.Criteria_Tag , Source , Field_Type , Value_Start , Value_Finish , current date as Run_Date from HDMSHR.CodingControl cc join HDMSHR.CCT_Mapping ccm on cc.Criteria_Tag = ccm.Criteria_Tag where ccm.Measure_Tag = 'APC' and ccm.DenNum_Tag = 'NUM' and Field_Type <> 3 and Usage <> 'S' order by Criteria_Tag , Source , Field_Type , Value_Start , Value_Finish ); NOTE: Table N.APC_NUM_HDMPRD_CCT created, with 1 rows and 7 columns. MPRINT(CCT_PARMS): create table ndccodes as select * from connection to HDMconnect (select distinct Field_Type from HDMSHR.CodingControl cc join HDMSHR.CCT_Mapping ccm on cc.Criteria_Tag = ccm.Criteria_Tag where ccm.Measure_Tag = 'APC' and ccm.DenNum_Tag = 'NUM' and Field_Type = 3 ); NOTE: Table WORK.NDCCODES created, with 1 rows and 1 columns. MPRINT(CCT_PARMS): title2 'First 35 NDC codes from CCT'; MPRINT(CCT_PARMS): select * from connection to HDMconnect (select 'APC' as Measure_Tag , cc.Criteria_Tag , Source , Field_Type , Value_Start , Value_Finish , current date as Run_Date from HDMSHR.CodingControl cc join HDMSHR.CCT_Mapping ccm on cc.Criteria_Tag = ccm.Criteria_Tag where ccm.Measure_Tag = 'APC' and ccm.DenNum_Tag = 'NUM' and Field_Type = 3 and Usage <> 'S' order by Criteria_Tag , Source , Field_Type , Value_Start , Value_Finish fetch first 35 rows only ); MPRINT(CCT_PARMS): title2 'count of NDC codes from CCT'; MPRINT(CCT_PARMS): select * from connection to HDMconnect (select cc.Criteria_Tag , current date as Run_Date , count(*) from HDMSHR.CodingControl cc join HDMSHR.CCT_Mapping ccm on cc.Criteria_Tag = ccm.Criteria_Tag where ccm.Measure_Tag = 'APC' and ccm.DenNum_Tag = 'NUM' and Field_Type = 3 and Usage <> 'S' group by cc.Criteria_Tag, current date ); MPRINT(CCT_PARMS): quit; NOTE: PROCEDURE SQL used (Total process time): real time 1:20.43 cpu time 0.15 seconds 594 595 596 %finish_time; /* generates a separate report page */ MPRINT(FINISH_TIME): data _null_; MPRINT(FINISH_TIME): call symput('finish_time',put(datetime(),datetime20.)); MPRINT(FINISH_TIME): run; NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds MPRINT(FINISH_TIME): data _null_; MPRINT(FINISH_TIME): title2 "Program Run Times"; MPRINT(FINISH_TIME): file print; MPRINT(FINISH_TIME): put // " Job Run Time " // "Start Time : 19JUL2017:09:34:53" / "Finish Time : 19JUL2017:09:37:32"; MPRINT(FINISH_TIME): run; NOTE: 6 lines were written to file PRINT. NOTE: DATA statement used (Total process time): real time 0.03 seconds cpu time 0.04 seconds