/*---------------------------------------------------------------------------------------------------*\ | PROGRAM NAME: Modular Program 7: Drug use, medical diagnoses and medical procedures before and | | after an exposure or event of interest | | | | DATE: 12/17/2013 | | VERSION: 5.0 | |-----------------------------------------------------------------------------------------------------| | | | The purpose of this program is to execute requests consistent with | | Modular Program 7 specifications | | | |-----------------------------------------------------------------------------------------------------| | | | Program inputs: | | As specified in Modular Program 7 documentation | | | | Program outputs: | | As specified in Modular Program 7 documentation | | | |-----------------------------------------------------------------------------------------------------| | | | Contact: | | Mini-Sentinel Coordinating Center | | info@mini-sentinel.org | | | \*---------------------------------------------------------------------------------------------------*/ /*System Options*/ options mprint linesize=150 pagesize=50 compress=yes reuse=no symbolgen ERRORS=0 noquotelenmax; /***********************/ /* User Inputs */ /***********************/ /* 1) Edit DPID and Site ID according to the table below */ %LET DPID=MS; %LET SITEID=OC; /*-------------------------------------------------------------------*\ || || || DATA PARTNERS DPID SITEID || || -----------------------------------------------------------------|| || Healthcore (one site) HC OS || || Humana (one site) HU OS || || Aetna (one site) AE OS || || Optum (one site) OP OS || || Vanderbilt University (one site) VB OS || || HMORN || || Group Health Cooperative HM GHC || || Fallon Community Health Plan HM MPCI || || Henry Ford Health System HM HFHS || || Lovelace Health System HM LCF || || Marshfield Clinic HM MCRF || || HealthPartners HM HPRF || || Harvard Pilgrim Health Care HM HPHC || || Kaiser Permanente || || Kaiser DCC KP DCC || || Kaiser Permanente Colorado KP CO || || Kaiser Permanente Georgia KP GA || || Kaiser Permanente Hawaii KP HI || || Kaiser Permanente Northern California KP NC || || Kaiser Permanente Northwest KP NW || || Kaiser Permanente Mid Atlantic KP MA || \*-------------------------------------------------------------------*/ /* 2) Edit this section to reflect your name for each Table/File (or View) */ %LET ENRTABLE=Enrollment; %LET DEMTABLE=Demographic; %LET DISTABLE=Dispensing; %LET DIATABLE=Diagnosis; %LET ENCTABLE=Encounter; %LET PROCTABLE=Procedure; /* 3) Edit this section to reflect locations for the Libraries and Folders for Mini-Sentinel */ /********** FOLDER CONTAINING INPUT DATA FILES AND MSCDM DATA ***************************************/ /* IMPORTANT NOTE: end of path separators are needed; */ /* Windows-based platforms: "\", e.g. "C:\user\sas\" and not "C:\user\sas"; */ /* Unix-based platforms: "/", e.g."/home/user/sas/" and not "/home/user/sas"; */ /* */ /********** FOLDER CONTAINING INPUT DATA FILES AND MSCDM DATA ***************************************/; /*Data in MSCDM Format*/ libname indata 'C:\Harvard\FLM'; /*NDC/ICD9 Codes File Location*/ %let infolder=C:\Harvard\MP7\InputFiles\; /*SAS Output Files*/ libname infolder "&infolder."; /********** FOLDER CONTAINING SUMMARY FILES TO BE EXPORTED TO Mini Sentinel Operations Center (MSOC)*/; /*CSV Output Files*/ %let MSOC=C:\Harvard\MP7\MSOC\; /*SAS Output Files*/ libname MSOC "&MSOC."; /*********** FOLDER CONTAINING FINAL DATASETS TO BE KEPT LOCAL AT THE PARTNER SITE (DPLocal)**********/; /*CSV Output Files*/ %let DPLocal=C:\Harvard\MP7\DPLocal\; /*SAS Output Files*/ libname DPLocal "&DPLocal."; /*---------------------------------------------------------------------------------------------------*/ /* End of User Inputs */ /*---------------------------------------------------------------------------------------------------*/ /*****************************************************************************************************/ /**************************** PLEASE DO NOT EDIT CODE BELOW THIS LINE ********************************/ /*****************************************************************************************************/ %MACRO MODULARPROGRAM7(REQUESTID=,RUNID=,ENROLGAP=,COVERAGE=,QUERYFROM=,QUERYTO=,QUERYFILE=,INCQUERYFILE=, NDCCODEMAP=,DIAGCODEMAP=,PROCCODEMAP=,OUTTABLESFILE=,AGESTRAT=); /*---------------------------------------------------*/ /* 01 -- Set up variables needed for the run */ /*---------------------------------------------------*/ %LET DPID=%LOWCASE(&DPID.); %LET SITEID=%LOWCASE(&SITEID.); %LET REQUESTID=%LOWCASE(&REQUESTID.); %LET RUNID=%LOWCASE(&RUNID.); %LET COVERAGE=%UPCASE(&COVERAGE.); %LET QUERYFILE=%LOWCASE(&QUERYFILE.); %LET INCQUERYFILE=%LOWCASE(&INCQUERYFILE.); %LET NDCCODEMAP=%LOWCASE(&NDCCODEMAP.); %LET DIAGCODEMAP=%LOWCASE(&DIAGCODEMAP.); %LET PROCCODEMAP=%LOWCASE(&PROCCODEMAP.); %LET OUTTABLESFILE=%LOWCASE(&OUTTABLESFILE.); proc printto log="&MSOC.&REQUESTID.&RUNID..log" new; run; %PUT "MODULARPROGRAM7_v5.0"; %let MPVer=5.0; %let MPNum=7; /******************************/ /* Preprocessing User Inputs */ /******************************/ /*Empty working files*/ proc datasets NOLIST NOWARN library=WORK; delete _:; quit; /*Set default values*/ %MACRO WRAPPER; %IF %STR("&ENROLGAP.")=%STR("") %THEN %DO; %LET ENROLGAP=0; %END; %IF %STR("&AGESTRAT.")=%STR("") %THEN %DO; %LET AGESTRAT=00-01 02-04 05-09 10-14 15-18 19-21 22-44 45-64 65-74 75+; %END; %IF %STR("&COVERAGE.")=%STR("") OR (%STR("&COVERAGE.") ne %STR("MD") AND %STR("&COVERAGE.") ne %STR("M") AND %STR("&COVERAGE.") ne %STR("D")) %THEN %DO; %LET COVERAGE=MD; %END; %MEND WRAPPER; %WRAPPER; /*Set macro variables for age groupings*/ %MACRO WORDNUMARG(VECTOR,OUTNAME); /*Macro to count words in a macro VECTOR*/ %GLOBAL &OUTNAME.; %LET NUMARG=0; %DO %WHILE(%QSCAN(&VECTOR,&NUMARG+1,%STR( )) ne %STR()); %LET NUMARG = %EVAL(&NUMARG+1); %END; %LET &OUTNAME.=&NUMARG.; %MEND WORDNUMARG; %WORDNUMARG(&AGESTRAT.,NUMAGECAT); /*Age Stratification*/ %PUT &NUMAGECAT.; data _null_; format AGETHRESH $200. AGETYP $200.; do i=1 to &NUMAGECAT.; _agetyp=compress(scan("&AGESTRAT.",i*2-1),'DWMQY','klu'); _agetyp=TRANWRD(UPCASE(_agetyp), 'D','Days'); _agetyp=TRANWRD(UPCASE(_agetyp), 'W','Weeks'); _agetyp=TRANWRD(UPCASE(_agetyp), 'Q','Quarters'); _agetyp=TRANWRD(UPCASE(_agetyp), 'M','Months'); _agetyp=TRANWRD(UPCASE(_agetyp), 'Y','Years'); if _agetyp ne '' then AGETYP=strip(AGETYP)||" "||strip(_AGETYP); else AGETYP=strip(AGETYP)||' Years'; AGETHRESH=strip(AGETHRESH)||" "||compress(scan("&AGESTRAT.",i*2-1),'DWMQY','lu'); if i=1 then MINAGE=AGETHRESH; if i=&NUMAGECAT. then do; MAXAGE=scan("&AGESTRAT.",i*2)*1; if MAXAGE='' then MAXAGE=99999; end; output; end; call symput('AGETHRESH',AGETHRESH); call symput('AGETYP',AGETYP); call symput('MINAGE',MINAGE); call symput('MAXAGE',MAXAGE); run; %PUT &AGETHRESH.; %PUT &AGETYP.; %PUT &NUMAGECAT.; %PUT &MINAGE; %PUT &MAXAGE.; /*Set macro variables for Query dates*/ data _NULL_; temp=DATETIME(); call symput('START',temp); call symput('STARTDATE',put(datepart(temp),date9.)); call symput('STARTTIME',put(timepart(temp),time4.)); run; Data _null_ ; Call Symput('QUERYFROMc',"&QUERYFROM."); Call Symput('QUERYTOc',"&QUERYTO."); Call Symput('QUERYFROM',put(input("&QUERYFROM",mmddyy10.),best12.)); Call Symput('QUERYTO',put(input("&QUERYTO",mmddyy10.),best12.)); run; %PUT &QUERYFROM; %PUT &QUERYTO; data _null_; call symput("NUMPER",trim(left(intck('Months',&QUERYFROM.,&QUERYTO.)+1))); call symput("NUMYEARS",trim(left(intck('Years',&QUERYFROM.,&QUERYTO.)+1))); call symput("NUMMONTHS",trim(left(intck('Months',&QUERYFROM.,&QUERYTO.)+1))); call symput("FROMY",trim(left(year(&QUERYFROM.)))); call symput("TOY",trim(left(year(&QUERYTO.)))); call symput("FROMMO",trim(left(month(&QUERYFROM.)))); run; %PUT &NUMPER.; %PUT &NUMYEARS.; %PUT &NUMMONTHS; %PUT &FROMY.; %PUT &TOY.; data _null_; call symput("FORMOD",trim(left(&FROMMO.-2))); if month(&QUERYFROM.) = 1 then call symput("YEARCHANGE",1); else if month(&QUERYFROM.) = 2 then call symput("YEARCHANGE",0); else call symput("YEARCHANGE",14-month(&QUERYFROM.)); run; %PUT &YEARCHANGE.; %PUT &FORMOD.; /*---------------------------------------------------------------------------*/ /* 02 -- Import / Export data */ /*---------------------------------------------------------------------------*/ /***************************************/ /* Importing Query file */ /***************************************/ /*Query File*/ %MACRO IMPORTFILES(var1,var2); %IF %INDEX(%UPCASE("&VAR1."),CPORT) %THEN %DO; proc cimport infile="&infolder.&VAR1." library=infolder memtype=data; run; %END; %LET &VAR2.=%SUBSTR(&VAR1.,1,%INDEX(%UPCASE(&VAR1.),.)-1 ); %MEND IMPORTFILES; %IMPORTFILES(&QUERYFILE.,QUERYFILE); /*Cleaning of QUERYFILE*/ data _&QUERYFILE.; set infolder.&QUERYFILE.; Group = compress(trim(left(Group))); Group = translate(Group,'_','-', '_','.', '_',',', '_','%', '_','$', '_','!', '_','*', '_','&', '_','#', '_','@'); WashTyp = Upcase(Washtyp); WashTypBfAf = Upcase(WashtypBfAf); /*Setting initial values*/ if Principal="" then Principal='NO'; if PrincipalBfAf="" then PrincipalBfAf='NO'; Principal=Upcase(Principal); PrincipalBfAf=Upcase(PrincipalBfAf); if BlackOutAf = . then BlackOutAf = 0; if NDCLengthBfAf not in(9,11) then NDCLengthBfAf = 9; if DiagLengthBfAf not in(1,2,3,4,5) then DiagLengthBfAf = 3; /*Doesn't make sense to have a washout for anniversary or hard coded target date since it can happen only once*/ if CodeCat in ('AN','DT') then do; WashPer=0; WashTyp='MIN'; end; CodeCat=upcase(CodeCat); CodeType=upcase(CodeType); if EnrDays0) %THEN %DO; PROC SQL Noprint; Create Table _procedures as Select prctb.PatId, prctb.Adate, prctb.EncType, compress(prctb.PX,'. ') as Code, proclist.Group, proclist.IGroup, 'S' as PDX, proclist.Query, proclist.Incid, proclist.CodeCat, proclist.CodeType, proclist.washper, proclist.washTyp, proclist.caresetting, proclist.principal From indata.&proctable.(where=(compress(PX,'.') in:(&VECT_proc.))) as prctb, _proc as proclist Where prctb.PX_codetype = proclist.codetype and ( (proclist.exact=-1 and substr(compress(prctb.PX,'.'),1,proclist.length) = proclist.code and substr(compress(prctb.PX,'.'),proclist.WildcardIndex+1,proclist.LengthCodeEnd) = proclist.CodeEnd) or (proclist.exact=0 and substr(compress(prctb.PX,'.'),1,proclist.length) = proclist.Code) or (proclist.exact=1 and compress(prctb.PX,'.')=proclist.code) ) and &QUERYTO. >= prctb.Adate >= &QUERYFROM. - proclist.WashPer - (upcase(proclist.washtyp)='MIN')*999999; /*The 999999 is to extend the washout period long enough in the case where washtyp = 'MIN'*/ quit; proc sort nodupkey data = _procedures; by _all_; run; Title1 "Procedure code frequency printout"; proc freq data = _procedures; tables Code; run; Title1 ""; %END; proc contents data=_diag noprint out=_tilt_ ; data _null_ ; set _tilt_ ; call symput('dnobs',trim(left(put(nobs,15.)))) ; run; %PUT &dnobs.; %IF %EVAL(&dnobs.>0) %THEN %DO; PROC SQL Noprint; Create Table _Diagnosis as Select diagtb.PatId, diagtb.Adate, diagtb.EncType, diagtb.pdx, compress(diagtb.DX,'. ') as Code, diaglist.Group, diaglist.IGroup, diaglist.Query, diaglist.Incid, diaglist.CodeCat, diaglist.CodeType, diaglist.washper, diaglist.washTyp, diaglist.caresetting, diaglist.principal From indata.&diatable.(where=(compress(DX,'.') in:(&VECT_diag.))) as diagtb, _diag as diaglist Where diagtb.DX_codetype = diaglist.codetype and ( (diaglist.exact=-1 and substr(compress(diagtb.DX,'.'),1,diaglist.length) = diaglist.code and substr(compress(diagtb.DX,'.'),diaglist.WildcardIndex+1,diaglist.LengthCodeEnd) = diaglist.CodeEnd) or (diaglist.exact=0 and substr(compress(diagtb.DX,'.'),1,diaglist.length) = diaglist.Code) or (diaglist.exact=1 and compress(diagtb.DX,'.')=diaglist.code) ) and &QUERYTO. >= diagtb.Adate >= &QUERYFROM. - diaglist.WashPer - (upcase(diaglist.washtyp)='MIN')*999999; /*The 999999 is to extend the washout period long enough in the case where washtyp = 'MIN'*/ quit; proc sort nodupkey data = _Diagnosis; by _all_; run; Title1 "Diagnosis code frequency printout"; proc freq data = _diagnosis; tables Code; run; Title1 ""; %END; %IF %EVAL(&pnobs.> 0 and &dnobs.>0) %THEN %DO; data _MasterQueryfile; set _procedures _diagnosis; by PatId; Clm = _N_; run; proc datasets library = work nolist nowarn; delete _procedures _diagnosis; quit; %END; %IF %EVAL(&pnobs.> 0 and &dnobs.=0) %THEN %DO; data _MasterQueryfile; set _procedures; by PatId; Clm = _N_; run; proc datasets library = work nolist nowarn; delete _procedures; quit; %END; %IF %EVAL(&pnobs.=0 and &dnobs.>0) %THEN %DO; data _MasterQueryfile; set _diagnosis; by PatId; Clm = _N_; run; proc datasets library = work nolist nowarn; delete _diagnosis; quit; %END; %MEND GETMEDS; %GETMEDS(); /*---------------------------------------------------------------------------*/ /* 05 -- Envelope outpatient diagnoses during inpatient stays */ /*---------------------------------------------------------------------------*/ /*****************************************************************/ /* Reduce diagnosis table according to selected care setting */ /* after having reclassified as inpatient all selected claims*/ /* within admission and discharge dates of an inpatient */ /* stay, when necessary */ /*****************************************************************/ %MACRO ENVEL(); %IF %SYSFUNC(exist(_MasterQueryfile))=1 %THEN %DO; proc contents data=_MasterQueryfile noprint out=_tilt_ ; data _null_ ; set _tilt_ ; call symput('nobs1',trim(left(put(nobs,15.)))) ; run; %END; %ELSE %DO; data _null_ ; call symput('nobs1',input('0',15.)) ; run; %END; %LET SOME=0; proc sort nodupkey data=_diag out=_diagCS(keep=caresetting); by caresetting; run; proc sort nodupkey data=_proc out=_procCS(keep=caresetting); by caresetting; run; data _null_; set _procCS _diagCS; caresetting=upcase(compress(caresetting,"'")); /*first determine whether the "all care settings" option was always chosen*/ if caresetting ne '' and not(indexw(caresetting,'OA') & indexw(caresetting,'IP') & indexw(caresetting,'ED') & indexw(caresetting,'AV') & indexw(caresetting,'IS') ) then call symput('SOME',input('1',1.)); /*this will trigger the envelope to run*/ else caresetting=''; run; %PUT &SOME.; %PUT %EVAL(&SOME.=1); %IF %EVAL(&nobs1.>0 and &SOME.=1) %THEN %DO; /*Reclassification as inpatient all selected claims within admission and discharge dates of an inpatient stay*/ /*Get Unique ADate-Ddate combinations*/ proc sort nodupkey data=indata.&ENCTABLE.(keep=PatId Adate Ddate EncType where=(EncType='IP')) out=_IPdates(keep=PatId Adate Ddate); by PatId Adate Ddate; run; proc sql noprint; create table _datematch as Select claimtb.Clm From _MasterQueryFile(where=(EncType not in('IP'))) as claimtb, _IPDates as datetb Where claimtb.PatId = datetb.PatId and datetb.ADate <= claimtb.ADate <= max(datetb.ADate,datetb.DDate); quit; proc sort nodupkey data = _datematch(keep=Clm); by Clm; run; /*For each record in Diagnosis matching an inpatient day date, recode encounter type as inpatient*/ data _MasterQueryFile(drop=EncType); merge _MasterQueryFile(in=a) _datematch(in=b); by Clm; EncType2=EncType; if b then do; if upcase(EncType) not in('IP') then pdx='S'; EncType2='IP'; end; run; proc datasets library = work nolist; modify _MasterQueryFile; rename EncType2=EncType; quit; %END; %IF %SYSFUNC(exist(_MasterQueryfile))=1 %THEN %DO; /*Filter claims with matching EncType and Principal status*/ data _MasterQueryFile(drop=EncType2 clm principal caresetting numarg i EncType PDX); set _MasterQueryFile; format EncType2 $2.; /*case where all care settings are wanted and necessarily that principal=NO*/ if caresetting = '' then output; else do; Numarg=length(compress(caresetting," '"))/2; do i=1 to Numarg; EncType2=compress(scan(caresetting,i),"'"); if EncType2 = EncType then do; if upcase(principal)='NO' then output; if upcase(principal)='YES' and upcase(PDX)='P' and upcase(EncType2) in('IP' 'ED') then output; end; end; end; run; %END; %MEND ENVEL; %ENVEL(); /*---------------------------------------------------------------------------*/ /* 06 -- Extract drug claims */ /*---------------------------------------------------------------------------*/ %MACRO GETDRUGS(); %LET ISCode9=0; %LET ISCode11=0; data _null_; set _ndc; if length=9 then call symput("ISCode9",1); if length=11 then call symput("ISCode11",1); run; %PUT &ISCode9; %PUT &ISCode11; /*Extract 9 and/or 11 digit Codes claims*/ %MACRO WRAPPER; %IF %EVAL(&ISCode9.>0) %THEN %DO; proc sql noprint; create table _drugs as select CodeList.Group, CodeList.IGroup, CodeList.Query, CodeList.Incid, CodeList.WashPer, CodeList.WashTyp, CodeList.CodeCat, CodeList.CodeType, CodeList.Code, Dispensing.Patid, Dispensing.RxDate as ADate from _ndc as CodeList, indata.&DISTABLE. as Dispensing where RxSup > 0 and substr(Dispensing.ndc,1,9) = CodeList.code and &QUERYTO. >= Dispensing.Rxdate >= &QUERYFROM. - CodeList.WashPer - (upcase(CodeList.washtyp)='MIN')*999999; quit; proc sort nodupkey data = _drugs; by _ALL_; run; Title1 "Nine digit NDC code frequency printout"; proc freq data = _drugs; tables Code; run; Title1 ""; %END; %IF %EVAL(&ISCode11.>0) %THEN %DO; proc sql noprint; create table _predrugs as select CodeList.Group, CodeList.IGroup, CodeList.Query, CodeList.Incid, CodeList.WashPer, CodeList.WashTyp, CodeList.CodeCat, CodeList.CodeType, CodeList.Code, Dispensing.Patid, Dispensing.RxDate as ADate from _ndc as CodeList, indata.&DISTABLE. as Dispensing where RxSup > 0 and substr(Dispensing.ndc,1,11) = CodeList.code and &QUERYTO. >=Dispensing.Rxdate >= &QUERYFROM. - CodeList.WashPer - (upcase(CodeList.washtyp)='MIN')*999999; quit; proc sort nodupkey data = _predrugs; by _ALL_; run; Title1 "Eleven digit NDC code frequency printout"; proc freq data = _predrugs; tables Code; run; Title1 ""; proc datasets library=work nolist nowarn; append base=_drugs data=_predrugs FORCE; delete _predrugs; quit; %END; %MEND WRAPPER; %WRAPPER; %MEND GETDRUGS; %GETDRUGS(); /*Add a dataset to MasterQueryFile*/ %MACRO ADDTOMASTERQUERYFILE(dataset); %IF %SYSFUNC(exist(_MasterQueryFile))=1 and %SYSFUNC(exist(&dataset.))=1 %THEN %DO; data _MasterQueryFile; format Code $11.; set _MasterQueryFile &dataset.; run; proc datasets library = work nolist nowarn; delete &dataset.; quit; %END; %IF %SYSFUNC(exist(_MasterQueryFile))=0 and %SYSFUNC(exist(&dataset.))=1 %THEN %DO; proc datasets library = work nolist; change &dataset.=_MasterQueryFile; quit; %END; %MEND ADDTOMASTERQUERYFILE; /*Add Drugs to MasterQueryFile*/ %ADDTOMASTERQUERYFILE(dataset=_Drugs); /*---------------------------------------------------------------------------*/ /* 07 -- Extract demographic query information */ /*---------------------------------------------------------------------------*/ %MACRO GETDEMOGS(); proc contents data=_demog noprint out=_tilt_ ; data _null_ ; set _tilt_ ; call symput('dnobs',trim(left(put(nobs,15.)))) ; run; %PUT &dnobs.; %IF %EVAL(&dnobs.>0) %THEN %DO; data _demog; format CodeType $10.; set _demog; if UPCASE(CodeType)='D' then CodeType='Days'; if UPCASE(CodeType)='W' then CodeType='Weeks'; if UPCASE(CodeType)='Q' then CodeType='Quarters'; if UPCASE(CodeType)='M' then CodeType='Months'; if UPCASE(CodeType)='Y' then CodeType='Years'; run; proc sql noprint; create table _demogs as /*anniversary date target*/ select CodeList.Group, CodeList.IGroup, 1 as Query, 0 as Incid, CodeList.WashPer, CodeList.WashTyp, CodeList.CodeCat, substr(CodeList.CodeType,1,1) as CodeType length=2 format=$2., CodeList.Code, Demographic.PatId, intnx(CodeList.CodeType,Demographic.birth_date,input(CodeList.Code,2.),'sameday') as ADate from _demog(where=(CodeCat='AN')) as CodeList, indata.&DEMTABLE. as Demographic where intnx(CodeList.CodeType,Demographic.birth_date,input(CodeList.Code, 2.),'sameday') >= &QUERYFROM. and intnx(CodeList.CodeType,Demographic.birth_date,input(CodeList.Code, 2.),'sameday') <= &QUERYTO. union /*Calendar Date target. Patients are necessarily unique in a query since the date can only be in one enrollment episode */ select CodeList.Group, CodeList.IGroup, 1 as Query, 0 as Incid, CodeList.WashPer, CodeList.WashTyp, CodeList.CodeCat, CodeList.CodeType as CodeType length=2 format=$2., CodeList.Code, enroll.PatId, input(CodeList.Code,mmddyy10.) as ADate from _demog(where=(CodeCat='DT')) as CodeList, indata.&ENRTABLE. as enroll where enroll.enr_start < input(CodeList.Code,mmddyy10.) < enroll.enr_end; quit; proc sort nodupkey data = _demogs; by _ALL_; run; data _demogs; set _demogs; format ADate mmddyy10.; run; Title1 "Demographic query frequency printout"; proc freq data = _demogs; tables Code; run; Title1 ""; %END; %MEND GETDEMOGS; %GETDEMOGS(); /*Add Demogs to MasterQueryFile*/ %ADDTOMASTERQUERYFILE(dataset=_Demogs); /*Verify if at least one claim was extracted from the CDM databases*/ %macro wrapper(); %if %sysfunc(exist(_MasterQueryFile))=0 %then %do; %put ERROR: No members meet cohort inclusion/exclusion criteria for all groups; %abort cancel; %end; %mend wrapper; %wrapper; /*Determine if required coverage If no diags or procs in Query, and no TopDiag and TopProc required, MEDCOV not required If no disp in Query, and no TopDisp required, DRUGCOV not required*/ /*Determine coverage required*/ %GLOBAL ENRCOV; %MACRO WRAPPER; %IF %UPCASE("&COVERAGE.") eq %STR("MD") or %UPCASE("&COVERAGE.") eq %STR("DM") %THEN %DO; %LET ENRCOV=upcase(DrugCov)='Y' and upcase(MedCov)='Y'; %END; %IF %UPCASE("&COVERAGE.") eq %STR("M") %THEN %DO; %LET ENRCOV=upcase(MedCov)='Y'; %END; %IF %UPCASE("&COVERAGE.") eq %STR("D") %THEN %DO; %LET ENRCOV=upcase(DrugCov)='Y'; %END; %MEND WRAPPER; %WRAPPER; %PUT &ENRCOV.; /*-------------------------------------------------------*/ /* 08 -- Determine continuously eligible patients */ /*-------------------------------------------------------*/ /*DenomInt*/ Proc SQL Noprint; Create Table _DenomInt as Select Demogs.PatId, Demogs.Birth_Date, Demogs.sex, Enrol.Enr_Start, Enrol.Enr_End From indata.&ENRTABLE.(where=(&ENRCOV.)) as Enrol, indata.&DEMTABLE.(where=(Birth_Date ne . and not missing(PatId))) as Demogs Where Demogs.PatId=Enrol.PatId and intnx(scan("&AGETYP.",1),Demogs.birth_date,&MINAGE.,'sameday') <= &QUERYTO. order Patid, Enr_Start, Enr_end; quit; /*Creating continuous elig episodes*/ data _DenomInt; set _DenomInt(where=(Enr_End >= Enr_Start)); by PatId; format Enr_Start Enr_End MinAgeDate MaxAgeDate mmddyy10.; MinAgeDate = intnx(scan("&AGETYP.",1),birth_date,&MINAGE.,'sameday'); if &MAXAGE.=99999 then MaxAgeDate=intnx('Years',birth_date,110,'sameday'); else MaxAgeDate= intnx(scan("&AGETYP.",&NUMAGECAT.),birth_date,&MAXAGE.+1,'sameday')-1; if Enr_Start-lag(Enr_end)-1 > &ENROLGAP. then episode=episode+1; if first.Patid then episode=1; retain episode; run; /*Reconciliation of elig episodes*/ proc means data=_DenomInt nway noprint; var Enr_Start Enr_end; class PatId episode; id Birth_Date sex MinAgeDate MaxAgeDate; output out=_DenomInt(drop=_:) min(Enr_Start)= max(Enr_end)=; run; data _DenomInt; set _DenomInt; if enr_end < MinAgeDate then delete; LastAgeGroup =&NUMAGECAT.; do i=&NUMAGECAT. to 1 by -1; Threshdate=intnx(scan("&AGETYP.",i),birth_date,scan("&AGETHRESH.",i),'sameday'); if Threshdate<= min(&QueryTO.,Enr_end) then do; LastAgeGroup =i; FirstAgeGroup=i; if Threshdate > max(&QueryFROM.,Enr_Start) then do; do j=i-1 to 1 by -1; if intnx(scan("&AGETYP.",j),birth_date,scan("&AGETHRESH.",j),'sameday') <= max(&QueryFROM.,Enr_Start) then do; FirstAgeGroup=j; leave; end; end; end; leave; end; end; drop i j Threshdate; run; proc datasets library=work; change _MasterQueryfile=_MasterQueryfile_; quit; proc sql noprint; create table _MasterQueryfile as Select mast.* from _MasterQueryfile_ as mast, _denomint as enrol where mast.PatId = enrol.PatId and enrol.Enr_Start <= mast.ADate <= enrol.Enr_End; quit; proc datasets library=work nowarn; delete _MasterQueryfile_; quit; /**********************/ /* Create _FdateTable */ /**********************/ /*Find Min date per QueryGroup Date (possible to have diags, drug, and procs in a same QueryGroup)*/ proc means noprint data =_MasterQueryFile nway; var ADate; class PatId Group; where &QUERYFROM. <= ADate <= &QUERYTO. and Query=1; output out=_FDateTable(drop=_:) min = MinDt; run; /*Create QueryGroup MinDate Variables*/ proc transpose data = _FDateTable out = _FDateTable(drop=_NAME_); id Group; var MinDt; by PatId; run; proc transpose data=_grouplist out=_Groups(drop=_NAME_); id Group; run; data _Groups; set _Groups; if _N_ = 0; run; data _FDateTable; set _FDateTable; if _N_=0 then set _Groups; format MinFdt MMDDYY10. MaxFdt MMDDYY10.; MinFdt = min(&GROUPVECT2.,.); MaxFdt = max(&GROUPVECT2.,.); run; /*Reduce enrollment to Query Patients*/ data _Enrollment(keep=PatId Enr_Start Enr_End sex birth_date); if 0 then set _FDateTable; declare hash ht (hashexp:16, dataset:'_FDateTable'); ht.definekey('PatId'); ht.definedata(ALL: 'YES'); ht.definedone(); do until(eof1); set _DenomInt end=eof1; if ht.find()=0 then do; output _Enrollment; end; end; stop; run; /*Some cleanup of no more useful datasets*/ proc datasets library=work nolist nowarn; delete _demog _diag _diagcs _ndc _proc _proccs _temp _ipdates; quit; /*---------------------------------------------*/ /* 09 -- Process data for each query group */ /*---------------------------------------------*/ %global NQUERYGROUP; %MACRO LOOPTHROUGH(); data _null_; set _GroupList end=fin; if fin then call symput('NQUERYGROUP',_n_); run; %PUT &NQUERYGROUP.; %DO i = 1 %TO &NQUERYGROUP.; data _null_; set _GroupList; if _n_ = &i. then do; call symput('ITGROUP',strip(Group)); call symput('TARGETDATEQUERY',TargetDateQuery); end; run; %PUT &ITGROUP; %PUT &TARGETDATEQUERY; data _null_; set _&QUERYFILE.(where=(Group in("&ITGROUP."))); if _N_ = 1 then do; call symput('ITDAYSBEF',DaysBf); call symput('ITDAYSAFT',DaysAf); call symput('ITWASHPERBFAF',WashPerBfAf); call symput('ITBLACKOUTAF',BlackOutAf); call symput('ITENRDAYS',EnrDays); call symput('ITENRDAYSBF',EnrDaysBf); call symput('ITENRDAYSAF',EnrDaysAf); end; run; %PUT &ITDAYSBEF; %PUT &ITDAYSAFT; %PUT &ITWASHPERBFAF; %PUT &ITBLACKOUTAF; %PUT &ITENRDAYS; %PUT &ITENRDAYSBF; %PUT &ITENRDAYSAF; data _loopsettings; set _grouplistsettings(where=(Group in("&ITGROUP."))); drop Group; run; data _SDFDateTable(keep=PatId WashTyp WashPer); set _FDateTable; if _N_ = 1 then set _loopsettings; if &ITGROUP.; run; /*Check for index date minus washout days for dispensings of query drugs or meds and identify dispensings of query drugs or meds between index date and end of query period*/ %MACRO WRAPPER(); data _Quantity; if 0 then set _SDFDateTable; declare hash ht (hashexp:16, dataset:'_SDFDateTable'); ht.definekey('PatId'); ht.definedata(ALL: 'YES'); ht.definedone(); do until(eof1); /*If reference date is calendar event, codes in INCQUERYFILE do not apply...*/ %IF &TARGETDATEQUERY. %THEN %DO; set _MasterQueryfile(where=(Group in("&ITGROUP.") and (query or incid))) end=eof1; %END; %ELSE %DO; set _MasterQueryfile(where=((Group in("&ITGROUP.") or IGroup in("&ITGROUP.","_ALLGROUPS_")) and (query or incid))) end=eof1; %END; if ht.find()=0 then do; InQuery = 0; if Group in("&ITGROUP.") and &QUERYFROM. <= ADate <= &QUERYTO. then InQuery = 1; output _Quantity; end; end; stop; run; %MEND WRAPPER; %WRAPPER; proc sort data = _Quantity; by PatId Adate descending InQuery; /*This sort is the key to consider codes in INCQUERYFILE correctly...*/ run; proc sort nodupkey data =_Quantity out =_IEpisode; by PatId Adate; run; data _IEpisode; set _IEpisode(keep=PatId WashPer WashTyp InQuery Adate CodeCat); by PatId; format lAdate mmddyy10. indexdt mmddyy10.; /*Drugs, Diags or Procs*/ if CodeCat in ('RX','DX','PX') then do; lAdate = lag(Adate); if first.PatId then do; lAdate = .; episode=0; indexdt=.; end; else diff = Adate - lAdate - 1; if WashTyp in('MIN') then do; if InQuery and diff = . then do; indexdt=ADate; episode=episode+1; output; end; end; if WashTyp in('MULT', 'SING') then do; if InQuery and (diff = . or diff >= WashPer) then do; indexdt=ADate; episode=episode+1; output; end; end; end; /*Anniversary and calendar target date*/ else if CodeCat in ('AN','DT') then do; indexdt=ADate; episode=1; output; end; retain episode; drop InQuery lAdate diff; run; /*Defensive coding*/ Proc SQL Noprint; Create Table _CleanAdates as Select IDates.PatId, IDates.ADate, IDates.Indexdt, (IDates.ADate - enrol.Enr_Start) as EnrNumDays From _IEpisode as IDates, _Enrollment as enrol Where IDates.PatId = enrol.PatId and enrol.Enr_Start <= IDates.ADate <= enrol.Enr_End Order by PatId, ADate; quit; proc sort nodupkey data = _Quantity out = _Quantity_; by PatId ADate; where Query and &QUERYFROM. <= ADate <= &QUERYTO.; run; proc sql noprint; create table _Quantity as Select qnt.*, enrol.birth_date, enrol.sex, (qnt.ADate - enrol.Enr_Start) as DBef, (enrol.Enr_End - qnt.ADate) as DAft from _Quantity_ as qnt, _Enrollment as enrol Where qnt.PatId = enrol.PatId and enrol.Enr_Start <= qnt.ADate <= enrol.Enr_End Order by PatId, ADate; quit; data _iMasterTable; merge _Quantity(in=a) _CleanAdates(in=b); by PatId ADate; if a; NoClaimsBef = 0; if b then NoClaimsBef = 1; Enr = 0; if EnrNumDays>=0 and EnrNumDays >= &ITENRDAYS. then Enr = 1; /*The patient can only be incident at the date of the indexdt Will also accommodate implementation of multiple index dates*/ if NoClaimsBef = 1 and Enr = 1 then incident = 1; else incident = 0; Year = Year(ADate); Month = Month(ADate); /* Ensure patient are sufficiently enrolled in the pre-index and post index period*/ if DBef >= (&ITENRDAYSBF.) and DAft >= (&ITENRDAYSAF.); /* if DBef >= (&ITDAYSBEF. + &ITWASHPERBFAF.) and DAft >= (&ITBLACKOUTAF. + &ITDAYSAFT. - 1); */ drop IGroup Code Query Incid CodeCat CodeType DBef DAft EnrNumDays NoClaimsBef IndexDt Enr:; run; data _premaster1 _premaster2; set _iMasterTable; if WashTyp = "SING" and Incident=1 then output _premaster1; else output _premaster2; run; proc sort data = _premaster1;; by Group PatId ADate; run; data _premaster1; set _premaster1; by Group PatId; if not first.PatID then Incident=0; run; data _iMasterTable; set _premaster1 _premaster2; run; %IF &I.=1 %THEN %DO; data DPLocal.&REQUESTID.&RUNID._MasterTable; set _iMasterTable; run; %END; %ELSE %DO; proc append base = DPLocal.&REQUESTID.&RUNID._MasterTable data = _iMasterTable force; run; %END; %END; %MEND LOOPTHROUGH; %LOOPTHROUGH(); /*Some cleanup of no more useful datasets*/ proc datasets library=work nolist nowarn; delete _cleanADates _fdatetable _iEpisode _iMasterTable _premaster1 _premaster2 _quantity _quantity_ _sdfdatetable; quit; /*---------------------------------------------*/ /* 10 -- Prepare Master table */ /*---------------------------------------------*/ /******************************************************************************************************** Clean the master table to meet specifications: For incident: if MIN washtyp, first claim date if incident; if SING washtyp, first incident claim date if MULT washtyp, first incident claim date (only one date to avoid overlapping of before and after periods). For prevalent: first claim date in query period (only one date to avoid overlapping of before and after periods). ********************************************************************************************************/ /*Clean MasterTable*/ data _CleanMasterTable; set DPLocal.&REQUESTID.&RUNID._MasterTable; where InQuery; drop InQuery; run; proc sort nodupkey data=_denomint (keep=PatId MinAgeDate MaxAgeDate) out=_MinMaxAge; by PatId MinAgeDate MaxAgeDate; run; proc sort data=_CleanMasterTable; by PatID; run; data _CleanMasterTable; merge _CleanMasterTable _MinMaxAge; by PatID; run; /*Find first incident and first prevalent claim*/ proc sort data=_CleanMasterTable; by Group PatId ADate; run; proc sort nodupkey data=_CleanMasterTable out=_FirstIncidDate(keep=Group PatId ADate); by Group PatId; where Incident and MinAgeDate <= ADate <= MaxAgeDate; run; proc sort nodupkey data=_CleanMasterTable out=_FirstPrevDate(keep=Group PatId ADate); by Group PatId; where MinAgeDate <= ADate <= MaxAgeDate; run; data _CleanMasterTable; merge _CleanMasterTable(in=a) _FirstIncidDate(in=b); by Group PatId ADate; FirstIncid=0; if b then FirstIncid=1; run; data _CleanMasterTable; merge _CleanMasterTable(in=a) _FirstPrevDate (in=b); by Group PatId ADate; FirstPrev=0; if b then FirstPrev=1; run; /*Add AgeGroup to CleanMasterTable*/ data DPLocal.&REQUESTID.&RUNID._CleanMasterTable; set _CleanMasterTable(rename=ADate=IndexDt); by Group PatId IndexDt; where FirstIncid or FirstPrev; format AgeGroup $20.; do j=&NUMAGECAT. to 1 by -1; if IndexDt >= intnx(scan("&AGETYP.",j),birth_date,scan("&AGETHRESH.",j),'sameday') then do; AgeGroup = scan("&AGESTRAT.",j,' '); NAgeGroup = j; leave; end; end; drop Incident Birth_Date j; run; /*For each patient in cleaned master table (those with an eligible reference date), find maximum history range to get among all topX queries*/ proc sort nodupkey data=_&QUERYFILE. out=_QueryTop_Unique(drop=Subgroup Codecat CodeType Code WashPer WashTyp Principal Caresetting); by Group; run; data _QueryTop_Master; merge DPLocal.&REQUESTID.&RUNID._CleanMasterTable(in=a Keep=PatId Group IndexDt AgeGroup Sex Year Month FirstIncid FirstPrev) _QueryTop_Unique(in=b drop=Top:); by Group; if a; format MinADate MaxADate mmddyy10.; MinADate = IndexDt - DaysBf - WashPerBfAf; MaxADate = IndexDt + (DaysAf + BlackOutAf - 1); if b then do; if WashTypBfAf in ('MULT','SING') then WashTypMinPresent=0; /*In that case, we won't need patients all past history*/ else WashTypMinPresent=1; /*In that case, we will need patients all past history*/ end; run; proc means data = _QueryTop_Master noprint nway; var MinADate MaxADate WashTypMinPresent; class PatId; output out=_QueryTop_Master_Unique(drop=_:) min(MinADate)= max(MaxADate WashTypMinPresent)=; run; /*---------------------------------------------------------------------------*/ /* 11 -- Determine denominators */ /*---------------------------------------------------------------------------*/ %MACRO DENOMLOOP(); data _null_; set _GroupList end=fin; if fin then call symput('NQUERYGROUP',_n_); run; %PUT &NQUERYGROUP.; %DO I = 1 %TO &NQUERYGROUP.; data _null_; set _GroupList; if _n_ = &i. then do; call symput('ITGROUP',compress(Group)); call symput('ITCODECAT',compress(CodeCat)); end; run; %PUT &ITGROUP; %PUT &ITCODECAT; data _null_; set _GroupListSettings; if Group = "&ITGROUP." then do; call symput('GROUPWASHOUT',compress(WashPer)); call symput('GROUPWASHTYP',compress(WashTyp)); end; run; %PUT &GROUPWASHOUT. &GROUPWASHTYP.; data _null_; set _&QUERYFILE.(where=(Group in("&ITGROUP."))); if _N_ = 1 then do; call symput('ITDAYSBEF',DaysBf); call symput('ITDAYSAFT',DaysAf); call symput('ITWASHPERBFAF',WashPerBfAf); call symput('ITBLACKOUTAF',BlackOutAf); call symput('ITENRDAYS',EnrDays); call symput('ITENRDAYSBF',EnrDaysBf); call symput('ITENRDAYSAF',EnrDaysAf); end; run; %PUT &ITDAYSBEF; %PUT &ITDAYSAFT; %PUT &ITWASHPERBFAF; %PUT &ITBLACKOUTAF; %PUT &ITENRDAYS; %PUT &ITENRDAYSBF; %PUT &ITENRDAYSAF; %IF %EVAL("&ITCODECAT." eq "") %THEN %DO; /*Creating Claims Dates Listing - One record per date per QueryGroup*/ proc sort nodupkey data=_MasterQueryfile out=_Claims(keep=PatId ADate Group); by PatId ADate; where (Group in("&ITGROUP.") or IGroup in("&ITGROUP.","_ALLGROUPS_")) and (Query or Incid); run; /*list for precleaning*/ proc sort nodupkey data=_Claims out=_ClaimList(keep=patid); by PatId; run; /*Creating Dates*/ data _AllMember; merge _DenomInt(in=a) _ClaimList(in=b); /*Sorted*/ by PatId; if a; withclaims=0; if a and b then withclaims=1; format LBracket UBracket QueryStartDate mmddyy10.; LBracket = Enr_Start + &ITENRDAYSBF.; UBracket = Enr_End - &ITENRDAYSAF.; if UBracket >= LBracket; /*First date at which member can be considered prevalent (included in the study)*/ QueryStartDate=Max(&QUERYFROM.,MinAgeDate,LBracket); /*enrollment start truncated to determine number of days a patient can be incident to the query group*/ Enr_Start=Max(QueryStartDate-Max(&GROUPWASHOUT.,&ITENRDAYS.),enr_start); Enr_End=min(&QUERYTO.,UBracket,MaxAgeDate); if enr_end >= enr_start; run; /*RECAP: . Elig episodes now have been resized not to exceed this QueryGroup washout period prior to the patients starting to be at risk of having an event . Adding birth dates and sex to claims and keeping claims within eligibility */ Proc SQL Noprint; Create Table _Claims2 as Select Enrol.*, claims.ADate, claims.Group From _AllMember(where=(withclaims=1)) as Enrol, _Claims as claims Where Enrol.PatId=claims.PatId and Enrol.enr_Start <= claims.ADate <= Enrol.enr_end order Patid, episode, Adate; /*remove claims outside of period BEFORE AND AFTER*/ quit; /*Create begin and end intervals (what if more than 1 enroll episode, will have duplicate claim when incident respective to own, should be ok)*/ data _DenomToLoop; set _AllMember(in=a keep=Patid episode Enr_Start Sex birth_date MinAgeDate MaxAgeDate QueryStartDate FirstAgeGroup LastAgeGroup rename=Enr_Start=Adate) _Claims2(keep=Patid episode ADate Sex birth_date MinAgeDate MaxAgeDate QueryStartDate FirstAgeGroup LastAgeGroup Group Enr_Start) _AllMember(in=b keep=Patid episode Enr_End Sex birth_date MinAgeDate MaxAgeDate QueryStartDate FirstAgeGroup LastAgeGroup Enr_Start rename=Enr_End=Adate); by Patid episode ADate; if a then do; Group="BEGINELIG"; Enr_Start=ADate; end; if b then Group="ENDELIG"; run; /*Creating HOI Free episodes from gapless enrolment sequences*/ data _FindEpisodes; set _DenomToLoop; by PatId episode; /*compute length of HOI free period*/ lADate=lag(Adate); lGroup=lag(Group); if first.episode then do; lADate=.; lGroup=.; end; if lADate ne . then diff=ADate-lADate+1; /*Compute StartDate and EndDate of HOI free period*/ format lADate StartDate EndDate mmddyy10.; StartDate=lADate+1-(lGroup eq "BEGINELIG"); EndDate=ADate; /*Adate can be either a QueryGroup Incid/HOI claim or the end of elig sequence*/ QueryStartDate=Max(QueryStartDate,StartDate); /*Minimum date the patient can have an HOI in this new broken down period*/ /*Patient must meet age criteria*/ if MinAgeDate <= EndDate and MaxAgeDate >= StartDate; /*removes first record of episode*/ if diff ne .; /*Keeping episodes overlapping the Query Period - left trucated in the event of a INICD claim*/ if EndDate >= &QUERYFROM. and StartDate <= &QUERYTO.; if QueryStartDate <= EndDate; /*Time increments*/ FirstPer=intck("Months",&QUERYFROM.,QueryStartDate)+1; LastPer=intck("Months",&QUERYFROM.,EndDate)+1; keep Patid birth_date sex StartDate QueryStartDate EndDate Enr_Start first: last:; /*RECALL: StartDate=Start Date of the HOI Free continuous elig period (can includes washout time that may fall outside Query Period) QueryStartDate= Start Date of the HOI Free episode overlapping the Query Period (for patient days calcs) EndDate=End Date of the HOI Free episode*/ run; /*Determine the date at which the patient can no longer be eligible*/ %MACRO WRAPPER(); %IF %STR("&GROUPWASHTYP.")=%STR("SING") %THEN %DO; proc means noprint data = Dplocal.&REQUESTID.&RUNID._MasterTable nway; var ADate; class PatId; where (Group in("&ITGROUP.")) and (Incident); output out=_TermDtTable(drop=_:) min=TermDt; run; %END; %ELSE %DO; proc means noprint data = _MasterQueryfile nway; var ADate; class PatId; where (Group in("&ITGROUP.") or IGroup in("&ITGROUP.","_ALLGROUPS_")) and (Query or Incid); output out=_TermDtTable(drop=_:) min=TermDt; run; %END; %MEND WRAPPER; %WRAPPER(); data _Patient_days_M _Patient_days_F _Patient_days_U; merge _FindEpisodes(in=a) _TermDtTable _firstinciddate(where=(Group in("&ITGROUP.")) rename=(ADate=FirstIncRefDate)) _firstprevdate(where=(Group in("&ITGROUP.")) rename=(ADate=FirstPrevRefDate)); by Patid; if a; washtyp="&GROUPWASHTYP."; if sex='M' then output _Patient_days_M; else if sex='F' then output _Patient_days_F; else if sex='U' then output _Patient_days_U; drop group; run; /*---------------------------------------------*/ /* 11.1 -- Accumulate data */ /*---------------------------------------------*/ %MACRO ACCUMDENOM(sex=); /*Here we will accumulate patients and patient-days to obtain up to 3 records at the end which will constitute the denominators for the modular program*/ data _Patient_days_&sex.; set _Patient_days_&sex. end=eof; by Patid; /*Naming convention of arrays: prefix _ means temporary vector prefix Inc = incident Suffix AG = Age group stratification Suffix Per = period stratification (e.g., weekly, monthly, quarterly) Suffix Y = year stratification When "Days" is not included in name = we`re counting "members" When "Days" is included in name = we`re counting "members-days"*/ /*Age Groups*/ array _IncAg(*) _IncAg1-_IncAg&NUMAGECAT. ; array _PrevAg(*) _PrevAg1-_PrevAg&NUMAGECAT.; array PrevAg(*) PrevAg1-PrevAg&NUMAGECAT.; array PrevDaysAg(*) PrevDaysAg1-PrevDaysAg&NUMAGECAT.; array IncAg(*) IncAg1-IncAg&NUMAGECAT.; array IncDaysAg(*) IncDaysAg1-IncDaysAg&NUMAGECAT.; /*Periodicity*/ array _PrevPer(*) _PrevPer1-_PrevPer&NUMPER.; array _IncPer(*) _IncPer1-_IncPer&NUMPER. ; array PrevPer(*) PrevPer1-PrevPer&NUMPER.; array PrevDaysPer(*) PrevDaysPer1-PrevDaysPer&NUMPER.; array IncPer(*) IncPer1-IncPer&NUMPER.; array IncDaysPer(*) IncDaysPer1-IncDaysPer&NUMPER.; /*Year*/ array _PrevY(*) _PrevY&FROMY.-_PrevY&TOY. ; array _IncY(*) _IncY&FROMY.-_IncY&TOY. ; array PrevY(*) PrevY&FROMY.-PrevY&TOY.; array PrevDaysY(*) PrevDaysY&FROMY.-PrevDaysY&TOY.; array IncY(*) IncY&FROMY.-IncY&TOY.; array IncDaysY(*) IncDaysY&FROMY.-IncDaysY&TOY.; if first.patid then do; call missing(of _Inc:); call missing(of _Prev:); end; do i=FirstAgeGroup to LastAgeGroup; format StartAgeDate EndAgeDate mmddyy10. ; /*Compute Strata Start/End date*/ StartAgeDate=intnx(scan("&AGETYP.",i),birth_date,scan("&AGETHRESH.",i),'sameday'); if i<&NUMAGECAT. then EndAgeDate=intnx(scan("&AGETYP.",i+1),birth_date,scan("&AGETHRESH.",i+1),'sameday') - 1; else if &MAXAGE.=99999 then EndAgeDate=intnx("years",birth_date,110,'sameday'); else EndAgeDate=intnx(scan("&AGETYP.",&NUMAGECAT.),birth_date,&MAXAGE.+1,'sameday')-1; if EndDate= &GROUPWASHOUT. and NumEnrDays >= &ITENRDAYS. then do; if WashTyp in('MULT') then do; _IncAg(i)=1; IncDaysag(i)=sum(min(NumWashOutDays-&GROUPWASHOUT.+1,NumDays,NumEnrDays-&ITENRDAYS. +1),IncDaysAg(i)); end; else if TermDt eq . or (TermDt ne . and StartDate<=TermDt) then do; /*WashTyp='Min' or 'Sing'*/ _IncAg(i)=1; IncDaysag(i)=sum(min(NumWashOutDays-&GROUPWASHOUT.+1,NumDays,NumEnrDays-&ITENRDAYS. +1),IncDaysAg(i)); end; end; end; end; end; do i=FirstPer to LastPer; format StartPerDate EndPerDate mmddyy10.; /*Here the use of the modulo operator has been chosen to avoid having to call the intnx() function to track year change as it was too much time consuming when this was tested in a synthetic real-life size problem*/ /*Year*/ if mod(i,12)=&YEARCHANGE. or i=FirstPer then do; year=int(&FROMY.+(i+&FORMOD.)/12); j=year-&FROMY.+1; /*Prevalence*/ NumDays= min(mdy(12,31,year),EndDate)-max(QueryStartDate,mdy(1,1,year))+1; /*Not eligible after first prev claim*/ if FirstPrevRefDate eq . or (FirstPrevRefDate ne . and StartDate<=FirstPrevRefDate) then do; _PrevY(j)=1; PrevDaysY(j) = sum(NumDays,PrevDaysY(j)); end; /*Incidence*/ NumWashOutDays=min(mdy(12,31,year),EndDate)-StartDate; NumEnrDays=min(mdy(12,31,year),EndDate)-Enr_Start; if FirstIncRefDate eq . or (FirstIncRefDate ne . and StartDate<=FirstIncRefDate) then do; if NumWashOutDays >= &GROUPWASHOUT. and NumEnrDays >= &ITENRDAYS. then do; if WashTyp in('MULT') then do; _IncY(j)=1; IncDaysY(j)=sum(min(NumWashOutDays-&GROUPWASHOUT.+1,NumDays,NumEnrDays-&ITENRDAYS. +1),IncDaysY(j)); end; else if TermDt eq . or (TermDt ne . and StartDate<=TermDt) then do; /*WashTyp='Min' or 'Sing'*/ _IncY(j)=1; IncDaysY(j)=sum(min(NumWashOutDays-&GROUPWASHOUT.+1,NumDays,NumEnrDays-&ITENRDAYS. +1),IncDaysY(j)); end; end; end; end; /*Year/Month*/ /*Compute Strata Start/End date*/ if i=FirstPer then StartPerdate=mdy(mod(i+&FORMOD.,12)+1,1,int(&FROMY.+(i+&FORMOD.)/12)); else StartPerdate=EndPerDate+1; EndPerdate=mdy(mod(i+&FORMOD.+1,12)+1,1,int(&FROMY.+(i+&FORMOD.+1)/12))-1; if EndDate= &GROUPWASHOUT. and NumEnrDays >= &ITENRDAYS. then do; if WashTyp in('MULT') then do; _IncPer(i)=1; IncDaysPer(i)=sum(min(NumWashOutDays-&GROUPWASHOUT.+1,NumDays,NumEnrDays-&ITENRDAYS. +1),IncDaysPer(i)); end; else if TermDt eq . or (TermDt ne . and StartDate<=TermDt) then do; /*WashTyp='Min' or 'Sing'*/ _IncPer(i)=1; IncDaysPer(i)=sum(min(NumWashOutDays-&GROUPWASHOUT.+1,NumDays,NumEnrDays-&ITENRDAYS. +1),IncDaysPer(i)); end; end; end; end; end; if last.patid then do; PrevALL=sum(PrevALL,max(of _PrevY:,.)); IncALL=sum(IncALL,max(of _IncY:,.)); do i=FirstAgeGroup to LastAgeGroup; PrevAg(i)=sum(PrevAg(i),_PrevAg(i)); IncAg(i)=sum(IncAg(i),_IncAg(i)); end; do i = 1 to &NUMYEARS.; PrevY(i)=sum(PrevY(i),_PrevY(i)); IncY(i)=sum(IncY(i),_IncY(i)); end; do i = 1 to &NUMPER.; PrevPer(i)=sum(PrevPer(i),_PrevPer(i)); IncPer(i)=sum(IncPer(i),_IncPer(i)); end; end; if eof then output; retain _Inc: _Prev: Inc: Prev:; keep Patid birth_date Sex StartDate EndDate QueryStartDate Prev: Inc:; run; %MEND ACCUMDENOM; %ACCUMDENOM(sex=M); %ACCUMDENOM(sex=F); %ACCUMDENOM(sex=U); %MACRO SQUARE(file=,Sex=); proc contents data=&file. noprint out=_tilt_ ; data _null_ ; set _tilt_ ; call symput('nobs',trim(left(put(nobs,15.)))) ; run; %PUT &pnobs.; %IF %EVAL(&nobs.=0) %THEN %DO; proc sql noprint; insert into &file (Sex) values("&sex."); quit; %END; %MEND SQUARE; %SQUARE(file=_Patient_days_F,sex=F); %SQUARE(file=_Patient_days_M,sex=M); %SQUARE(file=_Patient_days_U,sex=U); data _MasterDenomTable; set _Patient_days_F(in=a) _Patient_days_M(in=b) _Patient_days_U(in=c); PrevDaysALL=sum(of PrevDaysY:,.); /*a single presence is required*/ IncDaysALL=sum(of IncDaysY:,.); keep sex prev: inc:; run; proc transpose data = _MasterDenomTable out = _MasterDenomTable(rename=(_NAME_ = Segment COL1=count)); by sex; run; proc sort data = _MasterDenomTable; by segment sex; run; data _MasterDenomTable; format Group $30. Segment $16. count; set _MasterDenomTable; Group = "&ITGROUP."; label Segment='Segment'; run; %END; %ELSE %DO; data _andenom; set DPLocal.&REQUESTID.&RUNID._cleanmastertable(where=(Group in("&ITGROUP.")) drop=PatId IndexDt AgeGroup); do i=1 to &NUMPER.; if Month = mod(i+&FORMOD.,12)+1 and Year = int(&FROMY.+(i+&FORMOD.)/12) then do; Per = i; leave; end; end; drop Group i; run; data _square; format Sex $1.; Sex = "F"; output; Sex = "M"; output; run; data _square; format Segment $16. Sex $1.; set _square; Count = .; Segment = compress("Inc"||"ALL"); output; Segment = compress("Prev"||"ALL"); output; Segment = compress("Inc"||"Days"||"ALL"); output; Segment = compress("Prev"||"Days"||"ALL"); output; do i = 1 to &NUMPER.; Segment = compress("Inc"||"Per"||compress(put(i,3.))); output; Segment = compress("Prev"||"Per"||compress(put(i,3.))); output; Segment = compress("Inc"||"Days"||"Per"||compress(put(i,3.))); output; Segment = compress("Prev"||"Days"||"Per"||compress(put(i,3.))); output; end; do i = 1 to &NUMAGECAT; Segment = compress("Inc"||"Ag"||compress(put(i,3.))); output; Segment = compress("Prev"||"Ag"||compress(put(i,3.))); output; Segment = compress("Inc"||"Days"||"Ag"||compress(put(i,3.))); output; Segment = compress("Prev"||"Days"||"Ag"||compress(put(i,3.))); output; end; do i = &FROMY. to &TOY.; Segment = compress("Inc"||"Y"||compress(put(i,4.))); output; Segment = compress("Prev"||"Y"||compress(put(i,4.))); output; Segment = compress("Inc"||"Days"||"Y"||compress(put(i,4.))); output; Segment = compress("Prev"||"Days"||"Y"||compress(put(i,4.))); output; end; drop i; run; proc means noprint data = _andenom; var FirstIncid FirstPrev; class Sex Year Per NAgeGroup; types Sex Sex*NAgeGroup Sex*Per Sex*Year; output out=_andenom(drop=_:) sum = FirstInc FirstPrev; run; proc sort data = _andenom; by Sex Year Per NAgeGroup; run; proc transpose data = _andenom out = _andenom; by Sex Year Per NAgeGroup; run; data _andenom; set _andenom; format Segment $16.; if nmiss(Year,Per,NAgeGroup) = 3 then do; Segment = compress(substr(_NAME_,6)||"ALL"); output; Segment = compress(substr(_NAME_,6)||"Days"||"ALL"); output; end; if Per ne . then do; Segment = compress(substr(_NAME_,6)||"Per"||compress(put(per,3.))); output; Segment = compress(substr(_NAME_,6)||"Days"||"Per"||compress(put(per,3.))); output; end; if NAgeGroup ne . then do; Segment = compress(substr(_NAME_,6)||"Ag"||compress(put(NAgeGroup,3.))); output; Segment = compress(substr(_NAME_,6)||"Days"||"Ag"||compress(put(NAgeGroup,3.))); output; end; if Year ne . then do; Segment = compress(substr(_NAME_,6)||"Y"||compress(put(Year,4.))); output; Segment = compress(substr(_NAME_,6)||"Days"||"Y"||compress(put(Year,4.))); output; end; rename col1 = Count; run; data _MasterDenomTable; format Group $30. Segment $16. Sex $1.; set _andenom _square; Group = "&ITGROUP."; label Segment='Segment'; if missing(count) then count=0; keep Group Segment count Sex; run; proc sort data = _MasterDenomTable; by Sex Segment descending count; run; proc sort nodupkey data = _MasterDenomTable; by Sex Segment; run; %END; %IF &I.=1 %THEN %DO; data DPLocal.&REQUESTID.&RUNID._MasterDenomTable; set _MasterDenomTable; run; %END; %ELSE %DO; proc append base = DPLocal.&REQUESTID.&RUNID._MasterDenomTable data = _MasterDenomTable force; run; %END; %END; %MEND DENOMLOOP; %DENOMLOOP(); /*Some cleanup of no more useful datasets*/ proc datasets library=work nolist nowarn; delete _allmember _andenom _cleanmastertable _denomint _denomtoloop _firstinciddate _firstprevdate _findepisodes _masterdenomtable _masterqueryfile _patient_days: _termdttable; quit; /*---------------------------------------------------------------------------*/ /* 12 -- Post process denominator information */ /*---------------------------------------------------------------------------*/ /*Modular program specific*/ %GLOBAL CUR_QUERY_PROCESSED; /*Current query number being processed*/ %GLOBAL TOP_VALUE; /*Value for the current topX data processed (TopDrug TopDiag or TopProc)*/ %GLOBAL ENVEL_TOP_NEEDED; /*Flag to determine if running the envelope is needed, globally and for each query*/ /***********************************************************************************************************/ /* Extraction of dispensings, diagnosis, procedures and encounter(if needed by an envelope execution) */ /* for intervals before and after reference date. */ /* Limit to patients in cleaned master table (those with a reference date meeting all query criteria). */ /***********************************************************************************************************/ /*Extract dispensings for each eligible patient, keeping only one claim per day*/ proc sql noprint; create table _QueryTop_&DISTABLE. as select disp.PatID, disp.NDC, disp.RxDate as ADate from indata.&DISTABLE. as disp, _QueryTop_Master_Unique as mastTop where disp.RxSup>0 and disp.PatId=mastTop.PatId and mastTop.MinADate - mastTop.WashTypMinPresent*999999 <= disp.RxDate <= mastTop.MaxADate /*The 999999 is to extend the washout period long enough in the case where washtyp = 'MIN'*/ group by disp.PatId, NDC, ADate having FREQ(ADate) GE 1; quit; /*Map NDC lookup to dispensings if required, otherwise keep NDC first nine digits*/ %MACRO WRAPPER; %IF %UPCASE("&NDCCODEMAP.") ne %STR("") %THEN %DO; data _QueryTop_&DISTABLE.; if 0 then set infolder.&NDCCODEMAP.; declare hash ht (hashexp:16, dataset:"infolder.&NDCCODEMAP."); ht.definekey('ndc'); ht.definedata(ALL: 'YES'); ht.definedone(); do until(eof1); set _QueryTop_&DISTABLE. end=eof1; if ht.find() ne 0 then do; genericname="_notfound_"; drugclass="_notfound_"; end; output; end; stop; run; %END; %MEND WRAPPER; %WRAPPER; /*Extract diagnosis for each eligible patient, keeping only one claim per day*/ proc sql noprint; create table _QueryTop_&DIATABLE. as select diag.PatID, diag.DX_CodeType, compress(diag.DX,'.') as DX, diag.ADate, diag.EncType, diag.PDX from indata.&DIATABLE. as diag, _QueryTop_Master_Unique as mastTop where diag.PatId=mastTop.PatId and mastTop.MinADate - mastTop.WashTypMinPresent*999999 <= diag.ADate <= mastTop.MaxADate /*The 999999 is to extend the washout period long enough in the case where washtyp = 'MIN'*/ group by diag.PatId, DX_CodeType, DX, ADate, EncType, PDX having FREQ(ADate) GE 1; quit; /*Map DX codes lookup to diagnosis if required, otherwise keep DX first three digits Keep only ICD9 codes (faster to do it there than in the previous SQL proc)*/ %MACRO WRAPPER; %IF %UPCASE("&DIAGCODEMAP.") ne %STR("") %THEN %DO; /*Change here*/ proc datasets library=work; change _QueryTop_&DIATABLE.=_diagextract; quit; data _diagcodemap; set infolder.&DIAGCODEMAP.; DX=compress(DX,'.'); /*Change Here*/ OrigDX=DX; exact=index(DX,'*')=0; if exact=1 then do; DX=compress(DX,'*'); end; else do; WildcardIndex=index(DX,'*'); if WildcardIndex=length(DX) then do; /*the star is at the end*/ DX=compress(DX,'*'); end; else do; /*the star is in a middle position*/ exact=-1; LengthDXEnd=length(DX)-WildcardIndex; DXEnd=substr(DX,WildcardIndex+1,LengthDXEnd); DX=substr(DX,1,WildcardIndex-1); end; end; length=length(DX); run; proc sql noprint; Create Table _QueryTop_&DIATABLE. as Select diagtb.PatId, /*MODIF WAVE3*/ diagtb.DX_CodeType, diagtb.DX, diagtb.ADate, diagtb.EncType, diagtb.pdx, coalesce(diaglist.GeneralName,'_notfound_') as GeneralName From _diagextract as diagtb left join _diagcodemap as diaglist on diagtb.DX_CodeType = diaglist.DX_CodeType and ( (diaglist.exact=-1 and substr(diagtb.DX,1,diaglist.length) = diaglist.DX and substr(diagtb.DX,diaglist.WildcardIndex+1,diaglist.LengthDXEnd) = diaglist.DXEnd) or (diaglist.exact=0 and substr(diagtb.DX,1,diaglist.length) = diaglist.DX) or (diaglist.exact=1 and diagtb.DX=diaglist.DX) ); quit; proc datasets library=work nowarn; delete _diagextract; quit; %END; %MEND WRAPPER; %WRAPPER; /*Extract procedures for each eligible patient, keeping only one claim per day*/ proc sql noprint; create table _QueryTop_&PROCTABLE. as select proc.PatID, proc.PX_CodeType, compress(proc.PX,'.') as PX, proc.ADate, proc.EncType, 'S' as PDX from indata.&PROCTABLE.(where=(PX_CodeType in ('09','C4','HC'))) as proc, _QueryTop_Master_Unique as mastTop where proc.PatId=mastTop.PatId and mastTop.MinADate - mastTop.WashTypMinPresent*999999 <= proc.ADate <= mastTop.MaxADate /*The 999999 is to extend the washout period long enough in the case where washtyp = 'MIN'*/ group by proc.PatId, PX_CodeType, PX, ADate, EncType having FREQ(ADate) GE 1; quit; /* Map PX codes lookup to procedures if required Keep only ICD9, CPT and HCPCS codes (faster to do it there than in the previous SQL proc)*/ %MACRO WRAPPER; %IF %UPCASE("&PROCCODEMAP.") ne %STR("") %THEN %DO; /*Change here*/ proc datasets library=work; change _QueryTop_&PROCTABLE.=_procextract; quit; data _proccodemap; set infolder.&PROCCODEMAP.; PX=compress(PX,'.'); /*Change Here*/ OrigPX=PX; exact=index(PX,'*')=0; if exact=1 then do; PX=compress(PX,'*'); end; else do; WildcardIndex=index(PX,'*'); if WildcardIndex=length(PX) then do; /*the star is at the end*/ PX=compress(PX,'*'); end; else do; /*the star is in a middle position*/ exact=-1; LengthPXEnd=length(PX)-WildcardIndex; PXEnd=substr(PX,WildcardIndex+1,LengthPXEnd); PX=substr(PX,1,WildcardIndex-1); end; end; length=length(PX); run; proc sql noprint; Create Table _QueryTop_&PROCTABLE. as Select proctb.PatId, /*MODIF WAVE3*/ proctb.PX_CodeType, proctb.PX, proctb.ADate, proctb.EncType, proctb.pdx, coalesce(proclist.GeneralName,'_notfound_') as GeneralName From _procextract as proctb left join _proccodemap as proclist on proctb.PX_CodeType = proclist.PX_CodeType and ( (proclist.exact=-1 and substr(proctb.PX,1,proclist.length) = proclist.PX and substr(proctb.PX,proclist.WildcardIndex+1,proclist.LengthPXEnd) = proclist.PXEnd) or (proclist.exact=0 and substr(proctb.PX,1,proclist.length) = proclist.PX) or (proclist.exact=1 and proctb.PX=proclist.PX) ); quit; proc datasets library=work nowarn; delete _procextract; quit; %END; %MEND WRAPPER; %WRAPPER; /****************************************************************************************************************/ /* Verify if running the envelope for the topX results will be needed. */ /* If so, prepare the required datasets (extraction of encounter table). */ /****************************************************************************************************************/ %MACRO ENVEL_TOP_NEEDED(); %LET ENVEL_TOP_NEEDED=0; data _CSlist_Top; set _QueryTop_Unique; EnvelNeeded=0; /*First determine whether the "all care settings" option was always chosen*/ DiagCareSettingBfAf=compress(DiagCareSettingBfAf,"'"); if DiagCareSettingBfAf ne '' and not(indexw(DiagCareSettingBfAf,'OA') & indexw(DiagCareSettingBfAf,'IP') & indexw(DiagCareSettingBfAf,'ED') & indexw(DiagCareSettingBfAf,'AV') & indexw(DiagCareSettingBfAf,'IS') ) then do; call symput('ENVEL_TOP_NEEDED',input('1',1.)); /*this will trigger the envelope to run*/ EnvelNeeded=1; end; else DiagCareSettingBfAf=''; ProcCareSettingBfAf=compress(ProcCareSettingBfAf,"'"); if ProcCareSettingBfAf ne '' and not(indexw(ProcCareSettingBfAf,'OA') & indexw(ProcCareSettingBfAf,'IP') & indexw(ProcCareSettingBfAf,'ED') & indexw(ProcCareSettingBfAf,'AV') & indexw(ProcCareSettingBfAf,'IS') ) then do; call symput('ENVEL_TOP_NEEDED',input('1',1.)); /*this will trigger the envelope to run*/ EnvelNeeded=1; end; else ProcCareSettingBfAf=''; keep Group PrincipalBfAf DiagCareSettingBfAf ProcCareSettingBfAf EnvelNeeded; run; %IF %EVAL(&ENVEL_TOP_NEEDED.=1) %THEN %DO; /*Get Unique ADate-DDate combinations*/ proc sort nodupkey data=indata.&ENCTABLE.(keep=PatId Adate Ddate EncType where=(EncType='IP')) out=_IPdates_Top(keep=PatId Adate Ddate); by PatId Adate Ddate; run; /*Restrict to queries eligible patients*/ data _IPdates_Top; merge _QueryTop_Master_Unique(in=a keep=PatId) _IPdates_Top; by PatId; if a; run; %END; %PUT &ENVEL_TOP_NEEDED.; %MEND ENVEL_TOP_NEEDED; %ENVEL_TOP_NEEDED(); /****************************************************************************************************************/ /* Reduce a table according to selected caresetting after having reclassified as inpatient all selected */ /* claims within admission and discharge dates of an inpatient stay, when necessary. */ /****************************************************************************************************************/ %MACRO ENVEL_TOP(indataset=,caresettingField=,isProcDataset=); data &indataset.; set &indataset.; Clm = _N_; Group="&ITGROUP."; run; /*Reclassification as inpatient all selected claims within admission and discharge dates of an inpatient stay*/ proc sql noprint; create table _datematch as Select claimtb.Clm From &indataset.(where=(upcase(EncType) not in('IP'))) as claimtb, _IPDates_Top as datetb Where claimtb.PatId = datetb.PatId and datetb.ADate <= claimtb.ADate <= max(datetb.ADate,datetb.DDate); quit; proc sort nodupkey data=_datematch(keep=Clm); by Clm; run; /*For each record matching an inpatient day date, recode encounter type as inpatient*/ data &indataset.(drop=EncType); merge &indataset.(in=a) _datematch(in=b); by Clm; EncType2=EncType; if b then do; if upcase(EncType) not in('IP') then pdx='S'; EncType2='IP'; end; run; proc datasets library = work nolist; modify &indataset.; rename EncType2=EncType; quit; /*Filter claims with matching EncType and Principal status*/ data &indataset.(drop=Group EncType2 clm principal: &caresettingField. numarg i EncType PDX EnvelNeeded); if 0 then set _CSlist_Top; declare hash ht (hashexp:16, dataset:'_CSlist_Top'); ht.definekey('Group'); ht.definedata(ALL:'YES'); ht.definedone(); do until(eof1); set &indataset. end=eof1; if ht.find()=0 then do; /*Case where all care settings are wanted and necessarily that principal=NO*/ if &caresettingField. = '' then output; else do; Numarg=length(compress(&caresettingField.," '"))/2; do i=1 to Numarg; EncType2=compress(scan(&caresettingField.,i),"'"); if EncType2 = EncType then do; /*Case where specific care setting(s) (other than all) have been specified and principal=YES is possible*/ if upcase(principalBfAf)='NO' or &isProcDataset.=1 then output; if upcase(principalBfAf)='YES' and upcase(PDX)='P' and upcase(EncType2) in('IP','ED') then output; end; end; end; end; end; stop; run; %MEND ENVEL_TOP; /*-------------------------------------------------------------------------*/ /* 13 -- Prepare for counting information */ /*-------------------------------------------------------------------------*/ /*-------------------------------------------------------------------------*/ /* 13.1 -- Rankings for counts */ /*-------------------------------------------------------------------------*/ /****************************************************************************************************************/ /* Get ranking for CodeCount and PatCount for a specific prior and post type of claims */ /* */ /* inclaims: dataset containing all prior and post claims of a certain type (NDC, DX3...) for a specific query */ /* claimvar: type of claim being compiled (NDC, DX3...) */ /* outclaims: output dataset for compilation ranking */ /****************************************************************************************************************/ %MACRO COMPILECLAIMS(inclaims=,claimvar=,/*MODIF WAVE3*/dedupvar=,outclaims=); /*Keep only one record per claim variable per patid per day*/ proc sort nodupkey data=&inclaims. out=_TMP0; by Combination /*MODIF WAVE3 &claimvar.*/ &dedupvar. PatId ADate; run; %MACRO COMPILECLAIMSTABLES(strats=,rankvar=); proc means noprint data = _TMP0 nway; var Patient; class &strats. &claimvar. PatId; output out=_TMP(drop=_:) sum = CodeCount; run; proc means noprint data = _TMP nway; var CodeCount; class &strats. &claimvar.; output out=_TMP(drop=_:) sum=CodeCount N=PatCount; run; proc sort data=_TMP; by &strats. descending CodeCount; run; %IF %SYSFUNC(exist(&outclaims._byCodeCount))=0 %THEN %DO; data &outclaims._byCodeCount; format DPID $2. SITEID $4. Group $30.; set _tmp; by &strats.; DPID="&DPID."; SITEID="&SITEID."; Group="&group."; if first.&rankvar. then Rank = 0; Rank = Rank + 1; retain Rank; if Rank <= &TOP_VALUE. then output; run; %END; %ELSE %DO; data _tmp1; format DPID $2. SITEID $4. Group $30.; set _tmp; by &strats.; DPID="&DPID."; SITEID="&SITEID."; Group="&group."; if first.&rankvar. then Rank = 0; Rank = Rank + 1; retain Rank; if Rank <= &TOP_VALUE. then output; run; data &outclaims._byCodeCount;; set &outclaims._byCodeCount _tmp1; run; %END; proc sort data=_TMP; by &strats. descending PatCount; run; %IF %SYSFUNC(exist(&outclaims._byPatCount))=0 %THEN %DO; data &outclaims._byPatCount; format DPID $2. SITEID $4. Group $30.; set _tmp; by &strats.; DPID="&DPID."; SITEID="&SITEID."; Group="&group."; if first.&rankvar. then Rank = 0; Rank = Rank + 1; retain Rank; if Rank <= &TOP_VALUE. then output; run; %END; %ELSE %DO; data _tmp1; format DPID $2. SITEID $4. Group $30.; set _tmp; by &strats.; DPID="&DPID."; SITEID="&SITEID."; Group="&group."; if first.&rankvar. then Rank = 0; Rank = Rank + 1; retain Rank; if Rank <= &TOP_VALUE. then output; run; data &outclaims._byPatCount; set &outclaims._byPatCount _tmp1; run; %END; %MEND COMPILECLAIMSTABLES; %COMPILECLAIMSTABLES(strats=combination InDaysBf,rankvar=InDaysBf); %COMPILECLAIMSTABLES(strats=combination InDaysBf Year,rankvar=Year); %COMPILECLAIMSTABLES(strats=combination InDaysBf Year Month,rankvar=Month); %COMPILECLAIMSTABLES(strats=combination InDaysBf Sex,rankvar=Sex); %COMPILECLAIMSTABLES(strats=combination InDaysBf AgeGroup,rankvar=AgeGroup); %COMPILECLAIMSTABLES(strats=combination InDaysBf Sex AgeGroup,rankvar=AgeGroup); %MEND COMPILECLAIMS; /*-------------------------------------------*/ /* 13.2 -- Get UNIQUE counts */ /*-------------------------------------------*/ /****************************************************************************************************************/ /* Get unique CodeCount and unique PatCount for a specific prior and post type of claims */ /* */ /* inclaims: dataset containing all prior and post claims of a certain type (NDC, DX3...) for a specific query */ /* claimvar: type of claim being compiled (NDC, DX3...) */ /* outuniqueCode: output dataset for compilation of unique codes */ /* outSummary: output dataset for compilation of summary (claims counts, unique codes, unique patients) */ /****************************************************************************************************************/ %MACRO COMPILEUNIQUE(inclaims=,claimvar=,outuniqueCode=,outsummary=); /*Keep only one record per claim variable per patid*/ proc sort nodupkey data = &inclaims. out=_TMP0; by Combination InDaysBf &claimvar. PatId; run; %MACRO COMPILEUNIQUETABLES(strats=); proc means noprint data = _TMP0 nway; var Patient; class &strats. PatId; output out=_TMP(drop=_:) sum = UCodeCount; run; proc freq noprint data = _TMP; tables UCodeCount / out = _TMP(drop=percent); by &strats.; run; %IF %SYSFUNC(exist(&outuniqueCode.))=0 %THEN %DO; data &outuniqueCode.; format DPID $2. SITEID $4. Group $30.; set _tmp; DPID="&DPID."; SITEID="&SITEID."; Group="&group."; run; %END; %ELSE %DO; data _tmp1; format DPID $2. SITEID $4. Group $30.; set _tmp; DPID="&DPID."; SITEID="&SITEID."; Group="&group."; run; data &outuniqueCode.; set &outuniqueCode. _tmp1; run; %END; %MEND COMPILEUNIQUETABLES; %COMPILEUNIQUETABLES(strats=combination InDaysBf); %COMPILEUNIQUETABLES(strats=combination InDaysBf Year); %COMPILEUNIQUETABLES(strats=combination InDaysBf Year Month); %COMPILEUNIQUETABLES(strats=combination InDaysBf Sex); %COMPILEUNIQUETABLES(strats=combination InDaysBf AgeGroup); %COMPILEUNIQUETABLES(strats=combination InDaysBf Sex AgeGroup); /*Produce summary and generate same strata as those to be generated in denominators*/ proc sql noprint; create table &outsummary. as select Combination, InDaysBf, '' as Sex format $1., '' as AgeGroup format $20., . as Year format best12., . as Month format best12., 'ALL' as Strata format $10., count(&claimvar.) as ClaimsCount format best12., count(distinct &claimvar.) as UCodeCount format best12., count(distinct PatId) as UPatCount format best12. from &inclaims. group by Combination, InDaysBf union select Combination, InDaysBf, '' as Sex format $1., '' as AgeGroup format $20., Year format best12., . as Month format best12., 'Year' as Strata format $10., count(&claimvar.) as ClaimsCount format best12., count(distinct &claimvar.) as UCodeCount format best12., count(distinct PatId) as UPatCount format best12. from &inclaims. group by Combination, InDaysBf, Year union select Combination, InDaysBf, '' as Sex format $1., '' as AgeGroup format $20., Year format best12., Month format best12., 'Period' as Strata format $10., count(&claimvar.) as ClaimsCount format best12., count(distinct &claimvar.) as UCodeCount format best12., count(distinct PatId) as UPatCount format best12. from &inclaims. group by Combination, InDaysBf, Year, Month union select Combination, InDaysBf, Sex format $1., '' as AgeGroup format $20., . as Year format best12., . as Month format best12., 'Sex' as Strata format $10., count(&claimvar.) as ClaimsCount format best12., count(distinct &claimvar.) as UCodeCount format best12., count(distinct PatId) as UPatCount format best12. from &inclaims. group by Combination, InDaysBf, Sex union select Combination, InDaysBf, '' as Sex format $1., AgeGroup format $20., . as Year format best12., . as Month format best12., 'Age' as Strata format $10., count(&claimvar.) as ClaimsCount format best12., count(distinct &claimvar.) as UCodeCount format best12., count(distinct PatId) as UPatCount format best12. from &inclaims. group by Combination, InDaysBf, AgeGroup union select Combination, InDaysBf, Sex format $1., AgeGroup format $20., . as Year format best12., . as Month format best12., 'AgeSex' as Strata format $10., count(&claimvar.) as ClaimsCount format best12., count(distinct &claimvar.) as UCodeCount format best12., count(distinct PatId) as UPatCount format best12. from &inclaims. group by Combination, InDaysBf, Sex, AgeGroup order by Combination, Sex, AgeGroup, Year, Month, Strata; quit; /*Transpose prior/post incidence and prior/post prevalence*/ data &outsummary.; format Group $30. IncRefDate best12.; merge &outsummary.(where=(InDaysBf) rename=ClaimsCount=ClaimsCountBf rename=UCodeCount=UCodeCountBf rename=UPatCount=UPatCountBf) &outsummary.(where=(not InDaysBf) rename=ClaimsCount=ClaimsCountAf rename=UCodeCount=UCodeCountAf rename=UPatCount=UPatCountAf); by Combination Sex AgeGroup Year Month Strata; /*Unique in both files!*/ Group="&group."; if substr(Combination,1,1)='I' then IncRefDate=1; else IncRefDate=0; drop InDaysBf; run; proc sort data=&outsummary.; by IncRefDate Sex AgeGroup Year Month Strata; run; data &outsummary.; merge &outsummary.(where=(Combination in ('II' 'PI')) rename=ClaimsCountBf=IncClaimsCountBf rename=UCodeCountBf=IncUCodeCountBf rename=UPatCountBf=IncUPatCountBf rename=ClaimsCountAf=IncClaimsCountAf rename=UCodeCountAf=IncUCodeCountAf rename=UPatCountAf=IncUPatCountAf) &outsummary.(where=(Combination in ('IP' 'PP')) rename=ClaimsCountBf=PrevClaimsCountBf rename=UCodeCountBf=PrevUCodeCountBf rename=UPatCountBf=PrevUPatCountBf rename=ClaimsCountAf=PrevClaimsCountAf rename=UCodeCountAf=PrevUCodeCountAf rename=UPatCountAf=PrevUPatCountAf); by IncRefDate Sex AgeGroup Year Month Strata; /*Unique in both files!*/ drop Combination; run; %MEND COMPILEUNIQUE; /*---------------------------------------------------*/ /* 13.3 -- Get counts for all claims */ /*---------------------------------------------------*/ /****************************************************************************************************************/ /* Get CodeCount and PatCount, unique CodeCount and Summary information */ /* for a specific prior and post type of claims */ /* */ /* inclaims: dataset containing all prior and post claims of a certain type (NDC, DX3...) for a specific query */ /* claimvar: type of claim being compiled (NDC, DX3...) */ /* outclaims: output dataset base name for compilation */ /****************************************************************************************************************/ %MACRO COMPILEALLCLAIMS(inclaims=,group=,claimvar=,/*MODIF WAVE3*/dedupvar=,outclaims=); /*Add Sex AgeGroup Year and Month for stratification*/ proc sort data=&inclaims.; by PatId IndexDt; run; data &inclaims.; merge &inclaims.(in=a) _QueryTop_Master(in=b where=(Group="&ITGROUP.") keep=Group PatId IndexDt Sex AgeGroup Year Month); by PatId IndexDt; if a; drop Group; run; /*Generate combinations (IncidRefDate*IncidPriorPostClaims, IncidRefDate*PrevPriorPostClaims, etc)*/ data &inclaims.; format Combination $2.; set &inclaims.(in=a where=(FirstIncid and TopIncid)) &inclaims.(in=b where=(FirstIncid and TopPrev)) &inclaims.(in=c where=(FirstPrev and TopIncid)) &inclaims.(in=d where=(FirstPrev and TopPrev)); if a then combination='II'; if b then combination='IP'; if c then combination='PI'; if d then combination='PP'; drop FirstIncid FirstPrev TopIncid TopPrev; run; /*proc sort data=&inclaims.; by Combination InDaysBf Sex AgeGroup Year Month &claimvar. PatId ADate; run;*/ /*Claims counts (codes and patients)*/ %COMPILECLAIMS(inclaims=&inclaims.,claimvar=&claimvar.,/*MODIF WAVE3*/dedupvar=&dedupvar.,outclaims=_&outclaims.); /*Unique counts (codes) and summary*/ %COMPILEUNIQUE(inclaims=&inclaims.,claimvar=&claimvar., outuniqueCode=_&outclaims._UCodeCount,outsummary=_&outclaims._summary); %IF &CUR_QUERY_PROCESSED.=1 %THEN %DO; data MSOC.&REQUESTID.&RUNID._&outclaims._byCodeCount; set _&outclaims._byCodeCount; run; data MSOC.&REQUESTID.&RUNID._&outclaims._byPatCount; set _&outclaims._byPatCount; run; data MSOC.&REQUESTID.&RUNID._&outclaims._UCodeCount; set _&outclaims._UCodeCount; run; data MSOC.&REQUESTID.&RUNID._&outclaims._summary; set _&outclaims._summary; run; %END; %ELSE %DO; proc append base = MSOC.&REQUESTID.&RUNID._&outclaims._byCodeCount data = _&outclaims._byCodeCount force; run; proc append base = MSOC.&REQUESTID.&RUNID._&outclaims._byPatCount data = _&outclaims._byPatCount force; run; proc append base = MSOC.&REQUESTID.&RUNID._&outclaims._UCodeCount data = _&outclaims._UCodeCount force; run; proc append base = MSOC.&REQUESTID.&RUNID._&outclaims._summary data = _&outclaims._summary force; run; %END; %MEND COMPILEALLCLAIMS; /*-------------------------------------------*/ /* 13.3 -- Determine incidence */ /*-------------------------------------------*/ /****************************************************************************************************************/ /* For a specific query and type of claims (NDC, DX3...), determine incidence */ /* and if prior or post, restrict to prior and post query period and ensure enrollment eligibility. */ /****************************************************************************************************************/ %MACRO getOneQueryPriorPostClaims(inclaims=,claimvar=,/*MODIF WAVE3*/dedupvar=); /*Identifies if claims are in the period before or after the reference date*/ data &inclaims.; set &inclaims.; InDaysBf=0; InDaysAf=0; if IndexDt - DaysBf <= ADate < IndexDt then InDaysBf=1; if (IndexDt + BlackOutAf) <= ADate < (IndexDt + DaysAf + BlackOutAf) then InDaysAf=1; drop DaysBf DaysAf; run; /*Keep only one type of claim per day*/ proc sort nodupkey data=&inclaims.; by PatId FirstIncid FirstPrev /*MODIF WAVE3 &claimvar.*/ &dedupvar. Adate; run; /*Calculate number of days from enrollment to assess incidence correctly...*/ /*Keep only claims for which enrollment is respected AND in the prior-post index date period*/ proc sql noprint; create table _ClaimsTop as Select clm.*, clm.ADate - enrol.Enr_Start as WNumDays from &inclaims. as clm, _Enrollment as enrol Where clm.PatId = enrol.PatId and enrol.Enr_Start <= clm.ADate <= enrol.Enr_End Order By PatId, FirstIncid, FirstPrev, &claimvar., Adate; quit; /*Identify prior and post incident claims*/ data _CleanClaimsTop; set _ClaimsTop; by PatId FirstIncid FirstPrev &claimvar. ADate; format lAdate mmddyy10.; TopIncid=0; TopPrev=1; Patient=1; lAdate = lag(Adate); if first.&claimvar. then do; lAdate = .; end; else diff = Adate - lAdate; if WashTypBfAf in('MIN') and diff = . then TopIncid=1; if WashTypBfAf in('MULT') and (diff = . or diff >= WashPerBfAf) then TopIncid=1; if WNumDays < WashPerBfAf then TopIncid=0; /*Adjust incidence according to enrollment*/ if InDaysBf or IndaysAf then output; drop lAdate diff WashTypBfAf WashPerBfAf WNumDays; run; %MEND getOneQueryPriorPostClaims; /*******************************************************/ /* Get top value number for prior and post claims. */ /*******************************************************/ %MACRO getTopValue(topValue=,group=); proc sql noprint; select &topValue into :TOP_VALUE from _QueryTop_Unique where Group="&group."; quit; %PUT &TOP_VALUE; %MEND getTopValue; /*****************************************/ /* Get topX claims for each query. */ /*****************************************/ %MACRO getAllQueryTopClaims(); data _null_; set _GroupList end=fin; if fin then call symput('NQUERYGROUP',_n_); run; %PUT &NQUERYGROUP.; %DO CUR_QUERY_PROCESSED = 1 %TO &NQUERYGROUP.; data _null_; set _GroupList; if _n_ = &CUR_QUERY_PROCESSED. then call symput('ITGROUP',strip(Group)); run; %PUT &ITGROUP; /*Check if running the envelope on this query is needed*/ proc sql noprint; select EnvelNeeded into :ENVEL_TOP_NEEDED from _CSList_Top where Group="&ITGROUP."; quit; %PUT &ENVEL_TOP_NEEDED.; /*-------------------------------------------*/ /* 14 -- Data Extractions */ /*-------------------------------------------*/ /********************************************************************************************************/ /* Dispensings extraction for intervals before and after reference date. */ /* Limit to patients in master table (those with a reference date meeting all query criteria) */ /********************************************************************************************************/ /* Get top values for prior and post dispensings*/ %getTopValue(topValue=TopDrug,group=&ITGROUP.); %IF &TOP_VALUE. > 0 %THEN %DO; %IF %UPCASE("&NDCCODEMAP.") ne %STR("") %THEN %DO; /*Generic Name*/ proc sql noprint; create table _&DISTABLE._Top as select master.IndexDt, master.FirstIncid, master.FirstPrev, master.DaysBf, master.DaysAf, master.WashPerBfAf, master.WashTypBfAf, master.BlackOutAf, disp.PatId, disp.ADate, disp.genericname, /*MODIF WAVE3*/ substr(disp.NDC,1,master.NDCLengthBfAf) as NDC9 from _QueryTop_Master(where=(Group="&ITGROUP.")) as master, _QueryTop_&DISTABLE. as disp where master.PatId=disp.PatId; quit; %getOneQueryPriorPostClaims(inclaims=_&DISTABLE._Top,claimvar=genericname,/*MODIF WAVE3*/dedupvar=NDC9); %COMPILEALLCLAIMS(inclaims=_CleanClaimsTop,group=&ITGROUP.,claimvar=genericname,/*MODIF WAVE3*/dedupvar=NDC9,outclaims=MAP_RXGEN); proc datasets library=work nolist nowarn;delete _generic_:; quit; /*Drug class*/ proc sql noprint; create table _&DISTABLE._Top as select master.IndexDt, master.FirstIncid, master.FirstPrev, master.DaysBf, master.DaysAf, master.WashPerBfAf, master.WashTypBfAf, master.BlackOutAf, disp.PatId, disp.ADate, disp.drugclass, /*MODIF WAVE3*/ substr(disp.NDC,1,master.NDCLengthBfAf) as NDC9 from _QueryTop_Master (where=(Group="&ITGROUP.")) as master, _QueryTop_&DISTABLE. as disp where master.PatId=disp.PatId; quit; %getOneQueryPriorPostClaims(inclaims=_&DISTABLE._Top,claimvar=drugclass,/*MODIF WAVE3*/dedupvar=NDC9); %COMPILEALLCLAIMS(inclaims=_CleanClaimsTop,group=&ITGROUP.,claimvar=drugclass,/*MODIF WAVE3*/dedupvar=NDC9,outclaims=MAP_RXCLS); proc datasets library=work nolist nowarn;delete _dclass_:; quit; %END; %ELSE %DO; /*NDC*/ proc sql noprint; create table _&DISTABLE._Top as select master.IndexDt, master.FirstIncid, master.FirstPrev, master.DaysBf, master.DaysAf, master.WashPerBfAf, master.WashTypBfAf, master.BlackOutAf, disp.*, substr(disp.NDC,1,master.NDCLengthBfAf) as NDC9 from _QueryTop_Master (where=(Group="&ITGROUP.")) as master, _QueryTop_&DISTABLE. as disp where master.PatId=disp.PatId; quit; %getOneQueryPriorPostClaims(inclaims=_&DISTABLE._Top,claimvar=NDC9,/*MODIF WAVE3*/dedupvar=NDC9); %COMPILEALLCLAIMS(inclaims=_CleanClaimsTop,group=&ITGROUP.,claimvar=NDC9,/*MODIF WAVE3*/dedupvar=NDC9,outclaims=RX09); proc datasets library=work nolist nowarn;delete _ndc9_:;quit; %END; /*Some cleanup of no more useful datasets*/ proc datasets library=work nolist nowarn; delete _dispensing_top; quit; %END; /********************************************************************************************************/ /* Diagnosis extraction for intervals before and after reference date. */ /* Limit to patients in master table (those with a reference date meeting all query criteria) */ /********************************************************************************************************/ /* Get top values for prior and post ICD9 diagnosis*/ %getTopValue(topValue=TopDiag,group=&ITGROUP.); %IF &TOP_VALUE. > 0 %THEN %DO; proc sql noprint; create table _&DIATABLE._Top as select master.IndexDt, master.FirstIncid, master.FirstPrev, master.DaysBf, master.DaysAf, master.WashPerBfAf, master.WashTypBfAf, master.BlackOutAf, diag.*, /*Change Here*/ Case When substr(diag.DX,1,1)="E" then substr(diag.DX,1,master.DiagLengthBfAf+1) Else substr(diag.DX,1,master.DiagLengthBfAf) End as DX3 from _QueryTop_Master(where=(Group="&ITGROUP.")) as master, _QueryTop_&DIATABLE. as diag where master.PatId=diag.PatId; quit; /*Run the envelope if required*/ %IF &ENVEL_TOP_NEEDED. %THEN %DO; %ENVEL_TOP(indataset=_&DIATABLE._Top,caresettingField=DiagCareSettingBfAf,isProcDataset=0); %END; %IF %UPCASE("&DIAGCODEMAP.") ne %STR("") %THEN %DO; /*General Name*/ %getOneQueryPriorPostClaims(inclaims=_&DIATABLE._Top,claimvar=GeneralName,/*MODIF WAVE3*/dedupvar=DX3); %COMPILEALLCLAIMS(inclaims=_CleanClaimsTop,group=&ITGROUP.,claimvar=GeneralName,dedupvar=DX3,outclaims=MAP_DX); proc datasets library=work nolist nowarn; delete _gendx_:; quit; %END; %ELSE %DO; /*DX3*/ %getOneQueryPriorPostClaims(inclaims=_&DIATABLE._Top,claimvar=DX3,dedupvar=DX3); %COMPILEALLCLAIMS(inclaims=_CleanClaimsTop,group=&ITGROUP.,claimvar=DX3,dedupvar=DX3,outclaims=DX03); proc datasets library=work nolist nowarn; delete _dx3_:; quit; %END; %END; /*Some cleanup of no more useful datasets*/ proc datasets library=work nolist nowarn; delete _diagnosis_top; quit; /********************************************************************************************************/ /* Procedures extraction for intervals before and after reference date. */ /* Limit to patients in master table (those with a reference date meeting all query criteria) */ /********************************************************************************************************/ /* Get top values for prior and post ICD9, CPT4 and HCPCS procedures*/ %getTopValue(topValue=TopProc,group=&ITGROUP.); %IF &TOP_VALUE. > 0 %THEN %DO; %IF %UPCASE("&PROCCODEMAP.") ne %STR("") %THEN %DO; /*GeneralName*/ proc sql noprint; create table _&PROCTABLE._Top as select master.IndexDt, master.FirstIncid, master.FirstPrev, master.DaysBf, master.DaysAf, master.WashPerBfAf, master.WashTypBfAf, master.BlackOutAf, proc.* from _QueryTop_Master (where=(Group="&ITGROUP.")) as master, _QueryTop_&PROCTABLE. as proc where master.PatId=proc.PatId; quit; /*Run the envelope if required*/ %IF &ENVEL_TOP_NEEDED. %THEN %DO; %ENVEL_TOP(indataset=_&PROCTABLE._Top, caresettingField=ProcCareSettingBfAf,isProcDataset=1); %END; %getOneQueryPriorPostClaims(inclaims=_&PROCTABLE._Top,claimvar=GeneralName,dedupvar=PX_CodeType PX); %COMPILEALLCLAIMS(inclaims=_CleanClaimsTop,group=&ITGROUP.,claimvar=GeneralName,dedupvar=PX_CodeType PX,outclaims=MAP_PX); proc datasets library=work nolist nowarn; delete _genpx_:; quit; %END; %ELSE %DO; /*Proc codes*/ proc sql noprint; create table _&PROCTABLE._Top as select master.IndexDt, master.FirstIncid, master.FirstPrev, master.DaysBf, master.DaysAf, master.WashPerBfAf, master.WashTypBfAf, master.BlackOutAf, proc.* from _QueryTop_Master (where=(Group="&ITGROUP.")) as master, _QueryTop_&PROCTABLE. as proc where master.PatId=proc.PatId; quit; /*Run the envelope if required*/ %IF &ENVEL_TOP_NEEDED. %THEN %DO; %ENVEL_TOP(indataset=_&PROCTABLE._Top,caresettingField=ProcCareSettingBfAf,isProcDataset=1); %END; %getOneQueryPriorPostClaims(inclaims=_&PROCTABLE._Top,claimvar=PX,dedupvar=PX_CodeType PX); /*Separate ICD9, CPT and HCPCS*/ data _CleanClaimsTop_ICD9 _CleanClaimsTop_CPT _CleanClaimsTop_HCPCS; set _CleanClaimsTop; if Px_Codetype='09' then output _CleanClaimsTop_ICD9; if Px_Codetype='C4' then output _CleanClaimsTop_CPT; if Px_Codetype='HC' then output _CleanClaimsTop_HCPCS; run; %COMPILEALLCLAIMS(inclaims=_CleanClaimsTop_ICD9,group=&ITGROUP.,claimvar=PX,dedupvar=PX,outclaims=PX09); proc datasets library=work nolist nowarn; delete _icdproc_:; quit; %COMPILEALLCLAIMS(inclaims=_CleanClaimsTop_CPT,group=&ITGROUP.,claimvar=PX,dedupvar=PX,outclaims=PXC4); proc datasets library=work nolist nowarn; delete _cpt_:; quit; %COMPILEALLCLAIMS(inclaims=_CleanClaimsTop_HCPCS,group=&ITGROUP.,claimvar=PX,dedupvar=PX,outclaims=PXHC); proc datasets library=work nolist nowarn; delete _hcpcs_:; quit; %END; %END; /*Some cleanup of no more useful datasets*/ proc datasets library=work nolist nowarn; delete _cl: _procedures_top _tmp _tmp0 _tmp1; quit; %END; /*Loop through QueryGroup*/ %MEND getAllQueryTopClaims; %getAllQueryTopClaims(); /*Some cleanup of no more useful datasets*/ proc datasets library=work nolist nowarn; delete _cs: _IPDates_top _querytop:; quit; /****************************************************************************************************************/ /* Produce tables with summary counts and denominators. */ /****************************************************************************************************************/ /*General denominators table*/ proc sort data= DPLocal.&REQUESTID.&RUNID._MasterDenomTable; by Group segment sex count; run; data _denom; set DPLocal.&REQUESTID.&RUNID._MasterDenomTable; if upcase(segment)=:'INC' then do; segment=upcase(substr(segment,4,12)); Inc=1; end; else do; /*Prev*/ segment=upcase(substr(segment,5,11)); inc=0; end; time=0; if upcase(segment)=:'DAYS' then do; segment=upcase(substr(segment,5,11)); time=1; end; /*Assign Age Groupings for merging and create year month variables*/ if upcase(Segment)=:"AG" then do; i=compress(upcase(Segment),"AG"); format AgeGroup $20.; AgeGroup = scan("&AGESTRAT.",i,' '); end; if upcase(Segment)=:"PER" then do; StrataStart=intnx("Months",&QUERYFROM.,compress(upcase(Segment),"PER")-1,'sameday'); Year=year(StrataStart); Month=month(StrataStart); end; if upcase(Segment)=:"Y" then Year=compress(upcase(Segment),"Y"); keep segment inc Group count sex year month AgeGroup time; run; proc sort data=_denom; by Group segment sex inc time count; run; data _denom; format DpID SiteId $2.; merge _denom(where=(inc=0 and time=0) rename=count=PrevDenCount) _denom(where=(inc=1 and time=0) rename=count=IncDenCount) _denom(where=(inc=0 and time=1) rename=count=PrevDaysCount) _denom(where=(inc=1 and time=1) rename=count=IncDaysCount); by Group segment sex; DpID="&DPID."; SiteID="&SITEID."; drop inc; run; proc sql noprint; create table MSOC.&REQUESTID.&RUNID._DenTable0 as select DpId, SiteId, Group, Sex, AgeGroup, year, month, IncDenCount, IncDaysCount, PrevDenCount, PrevDaysCount from _denom; quit; /*General numerators table*/ proc sql noprint; create table _Table0 as Select PatId, Group, Sex, AgeGroup, Year, Month, FirstIncid, FirstPrev from DPLocal.&REQUESTID.&RUNID._CleanMasterTable; quit; proc means data=_Table0 noprint; var FirstIncid FirstPrev; class Group Sex AgeGroup Year Month; output out=MSOC.&REQUESTID.&RUNID._NumTable0(drop=_: where=(Group ne "")) sum=IncRefPatCount PrevRefPatCount; run; /*Put all prior-post claims summaries together*/ proc datasets library=DPLocal nolist nowarn; delete &REQUESTID.&RUNID._PPClaimsSummary; quit; %MACRO appendSummary(claimType); %IF %SYSFUNC(exist(MSOC.&REQUESTID.&RUNID._&claimType._summary))=1 %THEN %DO; data _tmp; format ClaimType $10.; set MSOC.&REQUESTID.&RUNID._&claimType._summary; ClaimType="&claimType"; Strata=upcase(Strata); run; proc append base = DPLocal.&REQUESTID.&RUNID._PPClaimsSummary data = _tmp force; run; proc datasets nolist nowarn library=MSOC; delete &REQUESTID.&RUNID._&claimType._summary; quit; %END; %MEND appendSummary; /*Default -- if no mapping provided*/ %appendSummary(claimType=RX09); %appendSummary(claimType=PXC4); %appendSummary(claimType=PXHC); %appendSummary(claimType=PX09); %appendSummary(claimType=DX03); /*If mapping provided*/ %appendSummary(claimType=MAP_RXGEN); %appendSummary(claimType=MAP_RXCLS); %appendSummary(claimType=MAP_DX); %appendSummary(claimType=MAP_PX); /*---------------------------------------------------------------------------*/ /* 15 -- Final tables */ /*---------------------------------------------------------------------------*/ /********************************************************************/ /* Build tables for both prevalent and incident reference dates */ /********************************************************************/ /***********************************************************************************************************/ /* Table 1: Overall Strata */ /* */ /* Table 2: Year Strata */ /* */ /* Table 3: Month Strata */ /* */ /* Table 4: Age Group and Sex Strata */ /***********************************************************************************************************/ %MACRO CreateTables(class=,num=,denomby=,denomwhere=,summarywhere=); /*Prevalent Table*/ proc means data=_Table0 nway noprint; var FirstPrev; class &class.; output out=PTable&num.(drop=_:) sum=PrevRefPatCount; run; /*Incident Table*/ proc means data=_Table0 nway noprint; var FirstIncid; class &class.; output out=ITable&num.(drop=_:) sum=IncRefPatCount; run; /*Add denominators*/ proc means data=_denom nway noprint; var prev: inc:; class Group &denomby.; where &denomwhere.; output out=_thisdenom(keep=Group &denomby. Prev: Inc:) sum=; run; /*Add prior-post claims infos*/ proc means data=DPLocal.&REQUESTID.&RUNID._PPClaimsSummary nway noprint; var prev: inc:; class Group &denomby. ClaimType; where &summarywhere. and not IncRefDate; output out=PSummary&num.(keep=Group ClaimType &denomby. Prev: Inc:) sum=; run; proc means data=DPLocal.&REQUESTID.&RUNID._PPClaimsSummary nway noprint; var prev: inc:; class Group &denomby. ClaimType; where &summarywhere. and IncRefDate; output out=ISummary&num.(keep=Group ClaimType &denomby. Prev: Inc:) sum=; run; /*Final Tables*/ data PTable&num.; format DPID $2. SiteID $4.; merge _thisdenom(drop=Inc:) PTable&num. PSummary&num.; by &class.; DpID="&DPID."; SiteID="&SITEID."; drop IncRefDate; rename PrevRefPatCount=Numerator PrevDenCount=Denominator PrevDaysCount=MemberDays; label PrevDenCount="Denominator" PrevDaysCount="MemberDays"; run; data ITable&num.; format DPID $2. SITEID $4.; merge _thisdenom(drop=Prev:) ITable&num. ISummary&num.; by &class.; DPID="&DPID."; SITEID="&SITEID."; drop IncRefDate; rename IncRefPatCount=Numerator IncDenCount=Denominator IncDaysCount=MemberDays; label IncDenCount="Denominator" IncDaysCount="MemberDays"; run; %MEND CreateTables; %CreateTables(class=Group,num=1,denomby=, denomwhere=Segment="ALL",summarywhere=Strata="ALL"); %CreateTables(class=Group year,num=2,denomby=year,denomwhere=Segment=:"Y",summarywhere=Strata=:"Y"); %CreateTables(class=Group year month,num=3,denomby=year month, denomwhere=Segment in:("PER"), summarywhere=Strata in:("PER")); %CreateTables(class=Group AgeGroup Sex,num=4,denomby=AgeGroup Sex,denomwhere=Segment in:("AG"), summarywhere=Strata in:("AG")); /*Macro to Export Results to MSOC Folder in .csv, .lst, and .sas7bdat format*/ %MACRO EXPORT(DATAPARTNER,REQUESTID,NAME,VARTO); %IF %INDEX(&name.,table4) %THEN %DO; data &NAME.; set &NAME.; AgeGroup=TRANWRD(AgeGroup,'-',' to '); run; %END; /*exporting raw tables into one .lst file*/ options nodate nonumber formdlim="-"; /* Select Table Name*/ data _Title; set titles; if table="&NAME." then do; &VARTO.=title; call symput('TITLE1',title);output; &VARTO.="Source: &REQUESTID.&RUNID."; call symput('TITLE2',&VARTO.); output; end; keep &VARTO.; run; TITLE1 "&TITLE1."; TITLE2 "&TITLE2."; /*Print results in .lst*/ proc print data=&name. noobs; run; data MSOC.&REQUESTID.&RUNID._&name.; set &NAME.; run; data _&NAME.; format &VARTO. $70.; set &NAME. _Title; run; PROC EXPORT DATA= _&NAME. OUTFILE= "&DPLOCAL.&REQUESTID.&RUNID._&NAME..csv" DBMS=CSV REPLACE; run; %MEND EXPORT; %MACRO WRAPPER; proc printto print="&MSOC.&REQUESTID.&RUNID._all_tables.lst" new; run; Data Titles; Format Table $9. Title $70.; Table="ptable1"; Title="Table 1a: Prevalent reference date - Overall - &STARTDATE."; output; Table="itable1"; Title="Table 1b: Incident reference date - Overall - &STARTDATE."; output; Table="ptable2"; Title="Table 2a: Prevalent reference date - By Year - &STARTDATE."; output; Table="itable2"; Title="Table 2b: Incident reference date - By Year - &STARTDATE."; output; Table="ptable3"; Title="Table 3a: Prevalent reference date - By Month - &STARTDATE."; output; Table="itable3"; Title="Table 3b: Incident reference date - By Month - &STARTDATE."; output; Table="ptable4"; Title="Table 4a: Prevalent reference date - By Age Group and Sex - &STARTDATE."; output; Table="itable4"; Title="Table 4b: Incident reference date - By Age Group and Sex - Incident - &STARTDATE."; output; Table="signature"; Title="Signature of Request - &STARTDATE."; output; run; %DO i=1 %TO 4; %EXPORT(&DPID.&SITEID., &REQUESTID., itable&i.,QueryGroup); %EXPORT(&DPID.&SITEID., &REQUESTID., ptable&i.,QueryGroup); %END; %MEND WRAPPER; %WRAPPER; data _NULL_; temp=DATETIME(); call symput('STOP',temp); seconds=temp-&start.; hours=int(seconds/3600); minutes=int((seconds-hours*3600)/60); seconds2=int((seconds-hours*3600-minutes*60)); call symput('totalseconds',put(seconds,best.)); call symput('hours',put(hours,4.0)); call symput('minutes',put(minutes,2.0)); call symput('seconds',put(seconds2,2.0)); run; %PUT TOTAL RUN TIME was &hours. h &minutes. m &seconds. s; /*********************************/ /* Create Request Signature File */ /*********************************/ data signature; MPNum="&MPNum."; MPVer="&MPVer."; DPID="&DpID."; SITEID="&SITEID."; RequestID="&REQUESTID."; RunID="&RUNID."; format StartTime StopTime datetime21.2; StartTime=trim(left(&START.)); StopTime=trim(left(&STOP.)); format RunTime Seconds $20.; RunTime="&hours. h &minutes. m &seconds. s"; Seconds="&totalseconds. s"; ScenarioCnt=strip("&NQUERYGROUP."); ENROLGAP="&ENROLGAP."; COVERAGE="&COVERAGE."; QUERYFROM="&QUERYFROMc."; QUERYTO="&QUERYTOc."; QUERYFILE="&QUERYFILE."; INCQUERYFILE="&INCQUERYFILE."; NDCCODEMAP="&NDCCODEMAP."; DIAGCODEMAP="&DIAGCODEMAP."; PROCCODEMAP="&PROCCODEMAP."; OUTTABLESFILE="&OUTTABLESFILE."; AGESTRAT="&AGESTRAT."; output; run; proc transpose data=signature out=signature(rename=_NAME_=Var rename=COL1=VALUE); var _ALL_; run; %EXPORT(&DPID.&SITEID., &RequestID.,signature,var); %MACRO REMOVETABLE(table=); %if %sysfunc(fileexist(&msoc.&REQUESTID.&RUNID._&table..sas7bdat))=1 %then %do; proc datasets library=msoc nolist nowarn; delete &REQUESTID.&RUNID._&table.; quit; %end; %MEND REMOVETABLE; %MACRO OUTPUTSELECTION; %IF %UPCASE("&OUTTABLESFILE.") ne %STR("") %THEN %DO; data _null_; set infolder.&OUTTABLESFILE.; strng=cats('%REMOVETABLE(table=', TabName,');'); if substr(upcase(TabRequired),1,1) ne "Y" then call execute(%nrstr(strng)); run; %END; %MEND OUTPUTSELECTION; %OUTPUTSELECTION; proc datasets library=work nolist nowarn; delete _:; quit; %PUT TOTAL RUN TIME was &hours. h &minutes. m &seconds. s; proc printto log=log print=print;run; %MEND MODULARPROGRAM7; /*---------------------------------------------------------------------------*/ /* 16 -- Example of invoking Modular Program Macro */ /*---------------------------------------------------------------------------*/ %MODULARPROGRAM7(REQUESTID=mp7, RUNID=r1, ENROLGAP=45, COVERAGE=MD, QUERYFROM=01/01/2006, QUERYTO=12/31/2008, QUERYFILE=Mp7_diabetes_query.sas7bdat, INCQUERYFILE=, NDCCODEMAP=ndc_map_table.sas7bdat, DIAGCODEMAP=dx_map_table.sas7bdat, PROCCODEMAP=px_map_table.sas7bdat, OUTTABLESFILE=mp7_output_tables.sas7bdat, AGESTRAT=45-64 65-74 75-84 85+ ); %MODULARPROGRAM7(REQUESTID=mp7, RUNID=r2, ENROLGAP=45, COVERAGE=MD, QUERYFROM=01/01/2006, QUERYTO=12/31/2008, QUERYFILE=Mp7_diabetes_query.sas7bdat, INCQUERYFILE=, NDCCODEMAP=, DIAGCODEMAP=, PROCCODEMAP=, OUTTABLESFILE=mp7_output_tables.sas7bdat, AGESTRAT=45-64 65-74 75-84 85+ );