/*---------------------------------------------------------------------------------------------------*\ | PROGRAM NAME: | | MODULAR PROGRAM 6 - FREQUENCY AND DURATION OF TREATMENT FOLLOWING AN EVENT OF INTEREST | | | | | | Date: 12/17/2013 | | Version: 7.0 | |-----------------------------------------------------------------------------------------------------| | | | The purpose of this program is to execute requests consistent with | | Modular Program 6 specifications | | | |-----------------------------------------------------------------------------------------------------| | | | Program inputs: | | As specified in Modular Program 6 documentation | | | | Program outputs: | | As specified in Modular Program 6 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 PROCTABLE=Procedure; %let ENCTABLE=Encounter; %let LABTABLE=Laboratory; /* 3) Edit this section to reflect locations for the libraries/folders for Mini-Sentinel Data and Output folders */ /********** 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\MP6\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\MP6\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\MP6\DPLocal\; /*SAS Output Files*/ libname DPLocal "&DPLocal."; /*---------------------------------------------------------------------------------------------------*/ /* End of User Inputs */ /*---------------------------------------------------------------------------------------------------*/ /*****************************************************************************************************/ /**************************** PLEASE DO NOT EDIT CODE BELOW THIS LINE ********************************/ /*****************************************************************************************************/ /*MODIF WAVE3 HERE*/ %MACRO MODULARPROGRAM6(REQUESTID=,RUNID=,ENROLGAP=,COVERAGE=,QUERYFROM=,QUERYTO=,QUERYFILE=, INCQUERYFILE=,POSTDIAGFILE=,INCPOSTDIAGFILE=,CONDFILE=,AGESTRAT=, LABSCODEMAP=,PTLOCCODEMAP=,OUTTABLESFILE=,STOCKPILINGFILE=); /*---------------------------------------------------------------------------*/ /* 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 POSTDIAGFILE=%LOWCASE(&POSTDIAGFILE.); %LET INCPOSTDIAGFILE=%LOWCASE(&INCPOSTDIAGFILE.); %LET CONDFILE=%LOWCASE(&CONDFILE.); %let LABSCODEMAP=%LOWCASE(&LABSCODEMAP.); %let PTLOCCODEMAP=%LOWCASE(&PTLOCCODEMAP.); %LET OUTTABLESFILE=%LOWCASE(&OUTTABLESFILE.); /*MODIF WAVE3 HERE*/ %LET STOCKPILINGFILE=%LOWCASE(&STOCKPILINGFILE.); proc printto log="&MSOC.&REQUESTID.&RUNID..log" new; run; %PUT "MODULARPROGRAM6_v7.0"; %let MPVer=7.0; %let MPNum=6; /*Empty work*/ proc datasets NOLIST NOWARN library=WORK; delete _:; quit; /*Set default values*/ %MACRO WRAPPER; %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("&ENROLGAP.")=%STR("") %THEN %DO; %LET ENROLGAP=0; %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; /*************************/ /* Age Buckets Transform */ /*************************/ %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 $1000. AGETYP $1000.; do i=1 to &NUMAGECAT.; _agetyp=compress(scan("&AGESTRAT.",i*2-1),'DWMQY','klu'); _agetyp=TRANWRD(UPCASE(_agetyp), 'Y','Years'); _agetyp=TRANWRD(UPCASE(_agetyp), 'D','Days'); _agetyp=TRANWRD(UPCASE(_agetyp), 'W','Weeks'); _agetyp=TRANWRD(UPCASE(_agetyp), 'Q','Quarters'); _agetyp=TRANWRD(UPCASE(_agetyp), 'M','Months'); 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=compress(scan("&AGESTRAT.",i*2),'DWMQY','l')*1; if MAXAGE='' then MAXAGE=99999; end; output; end; call symput('AGETHRESH',strip(AGETHRESH)); call symput('AGETYP',strip(AGETYP)); call symput('MINAGE',strip(MINAGE)); call symput('MAXAGE',strip(MAXAGE)); run; %PUT &AGETHRESH.; %PUT &AGETYP.; %PUT &NUMAGECAT.; %PUT &MINAGE; %PUT &MAXAGE.; /*************/ /* Set Dates */ /*************/ /*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 Cports Input Files */ /********************************/ %MACRO IMPORTFILES(var1,var2,var3); /*var1=input file, var2= work file, var3= variables to keep*/ %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); %LET VAR1=%SUBSTR(&VAR1.,1,%INDEX(%UPCASE(&VAR1.),.)-1); data _&var2.; set infolder.&var1.; group = compress(trim(left(group))); group = translate(group,'_','-', '_','.', '_',',', '_','%', '_','$', '_','!', '_','*', '_','&', '_','#', '_','@'); keep &var3.; run; %MEND IMPORTFILES; %IMPORTFILES(&QUERYFILE.,QUERYFILE,_ALL_); %IMPORTFILES(&POSTDIAGFILE.,POSTDIAGFILE,_ALL_); %MACRO WRAPPER(); %IF %INDEX(%UPCASE("&INCQUERYFILE."),.) %THEN %DO; %IMPORTFILES(&INCQUERYFILE.,INCQUERYFILE,Group SubGroup CodeType Code Principal Caresetting); %END; %ELSE %DO; data _INCQUERYFILE; if 0 then set _QUERYFILE; keep Group SubGroup CodeType Code Principal Caresetting; stop; run; %END; %IF %INDEX(%UPCASE("&INCPOSTDIAGFILE."),.) %THEN %DO; %IMPORTFILES(&INCPOSTDIAGFILE.,INCPOSTDIAGFILE,Group PostDiagGroup SubGroup Codetype Code Principal Caresetting); %END; %ELSE %DO; data _INCPOSTDIAGFILE; if 0 then set _POSTDIAGFILE; keep Group PostDiagGroup SubGroup Codetype Code Principal Caresetting; stop; run; %END; %IF %INDEX(%UPCASE("&CONDFILE."),.) %THEN %DO; %IMPORTFILES(&CONDFILE.,CONDFILE,Code CodeType Principal CondFrom CondTo Caresetting Group SubGroup Inclusion); %END; %ELSE %DO; data _CONDFILE; if 0 then set _QUERYFILE; format Principal $3. CareSetting $40. Condfrom Condto Inclusion best.; condfrom=.; condto=.; Inclusion=.; keep Group SubGroup Codetype Code Principal CareSetting Condfrom Condto Inclusion; stop; run; %END; /*MODIF WAVE3 HERE*/ %IF %INDEX(%UPCASE("&STOCKPILINGFILE."),.) %THEN %DO; %IMPORTFILES(&STOCKPILINGFILE.,STOCKPILINGFILE,_ALL_); %END; %ELSE %DO; data _STOCKPILINGFILE; set _QUERYFILE(keep=Group); format SameDay $2. SupRange AmtRange $40. PercentDays best.; SameDay="aa"; SupRange="0<-HIGH"; AmtRange="0<-HIGH"; PercentDays=0; run; %END; /*MODIF WAVE3 HERE*/ proc sort nodupkey data=_STOCKPILINGFILE; by Group; run; %MEND WRAPPER; %WRAPPER(); /*Import mapping files*/ %MACRO IMPORTMAPFILES(var1,var2,var3); /*var1=input file, var2= work file, var3= variables to keep*/ %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); %LET VAR1=%SUBSTR(&VAR1.,1,%INDEX(%UPCASE(&VAR1.),.)-1); data _&var2.; set infolder.&var1.; keep &var3.; run; %MEND IMPORTMAPFILES; %MACRO WRAPPER(); %IF %INDEX(%UPCASE("&LABSCODEMAP."),.) %THEN %DO; %IMPORTMAPFILES(&LABSCODEMAP.,LABSCODEMAP,_ALL_); %END; %IF %INDEX(%UPCASE("&PTLOCCODEMAP."),.) %THEN %DO; %IMPORTMAPFILES(&PTLOCCODEMAP.,PTLOCCODEMAP,_ALL_); %END; %IF %INDEX(%UPCASE("&OUTTABLESFILE."),.) %THEN %DO; %IMPORTMAPFILES(&OUTTABLESFILE.,OUTTABLESFILE,_ALL_); %END; %MEND WRAPPER; %WRAPPER(); /* MODIF WAVE3 HERE */ /* Ensure Stockpiling module is available */ %macro ProcessInclude(filename=); %if %sysfunc(fileexist(&infolder.&filename.))=0 %then %do; %put ERROR: Include file &infolder.&filename. is required but does not exist; %abort cancel; %end; %include "&infolder.&filename."; %mend ProcessInclude; %ProcessInclude(filename=ms_stockpiling.sas); /**********************************/ /* EXPORTING INPUT VALUES TO MSOC */ /**********************************/ %MACRO EXPORTVALS(var1,var2); options nodate nonumber; proc printto print="&MSOC.&REQUESTID.&RUNID._&var2..lst" new; run; Title1 "&var2. File printout"; proc print data=infolder.&var1. noobs; run; Title1 ""; proc printto print=print; run; %MEND EXPORTVALS; %EXPORTVALS(&QUERYFILE.,queryfile); %EXPORTVALS(&POSTDIAGFILE.,postdiagfile); %MACRO WRAPPER; %IF &INCQUERYFILE.~= %STR() %THEN %DO; %EXPORTVALS(&INCQUERYFILE.,incqueryfile); %END; %IF &INCPOSTDIAGFILE.~= %STR() %THEN %DO; %EXPORTVALS(&INCPOSTDIAGFILE.,incpostdiagfile); %END; %IF &CONDFILE.~= %STR() %THEN %DO; %EXPORTVALS(&CONDFILE.,condfile); %END; %MEND WRAPPER; %WRAPPER; /*---------------------------------------------------------------------------*/ /* 03 -- Pre-processing of input data */ /*---------------------------------------------------------------------------*/ /*****************************/ /* QUERY AND INCQUERY */ /*****************************/ data _QUERYFILE; format group $30.; set _QUERYFILE; MinEpisDur=1; code = compress(code,'. '); CodeType=upcase(CodeType); WashTyp = upcase(WashTyp); if WashTyp = '' then WashTyp = 'MULT'; if WashPer = . then WashPer = 0; if missing(EnrDays) then EnrDays=0; run; proc sort nodupkey data=_QUERYFILE; by Group CodeType Code Caresetting Principal; run; /*In some cases, we may wish that each query group in the query file be incident to a common list of drugs, in this case, the list should only be entered once while leaving the group variable empty*/ data _INCQUERYFILE; format Igroup $30.; set _INCQUERYFILE(rename=(Group=IGroup)); if IGroup = "" then IGroup = "_ALLGROUPS_"; code = compress(code,'. '); CodeType=upcase(CodeType); run; proc sort nodupkey data=_INCQUERYFILE; by IGroup CodeType Code Caresetting Principal; run; /*If codes from the common incident list (_ALLGROUPS_) are the union of all query group codes, the following steps avoid repeating claims*/ proc sql noprint; create table _querycodes as /*PART 1: if IGROUP is _ALLGROUPS_ then join the files using CodeType and Code and keeping all INCQUERY codes*/ select quer.group, inqu.igroup, quer.subgroup, inqu.subgroup as isubgroup, quer.Code, inqu.Code as icode, quer.CodeType, inqu.CodeType as iCodeType, quer.CareSetting, inqu.CareSetting as iCareSetting, quer.Principal, inqu.Principal as iPrincipal from _QUERYFILE as quer right join _INCQUERYFILE as inqu on quer.Code = inqu.Code and quer.CodeType = inqu.CodeType and quer.CareSetting = inqu.CareSetting and quer.Principal = inqu.Principal where IGroup = "_ALLGROUPS_" union /*PART 2: if IGROUP is not _ALLGROUPS_ then join the files using CodeType, Code and Group to get all common codes*/ select quer.group, inqu.igroup, quer.subgroup, inqu.subgroup as isubgroup, quer.Code, inqu.Code as icode, quer.CodeType, inqu.CodeType as iCodeType, quer.CareSetting, inqu.CareSetting as iCareSetting, quer.Principal, inqu.Principal as iPrincipal from _QUERYFILE as quer join _INCQUERYFILE as inqu on quer.Code = inqu.Code and quer.CodeType = inqu.CodeType and quer.Group = inqu.IGroup and quer.CareSetting = inqu.CareSetting and quer.Principal = inqu.Principal where IGroup ne "_ALLGROUPS_" union /*PART 3: Add QUERY only codes*/ ( select group, "" as igroup, subgroup, "" as isubgroup, Code, "" as icode, CodeType, "" as iCodeType, CareSetting, "" as iCareSetting, Principal, "" as iPrincipal from _QUERYFILE where (CodeType||Code||Principal||CareSetting) not in ( select (CodeType||Code||Principal||CareSetting) from _INCQUERYFILE where IGroup = "_ALLGROUPS_" ) except select igroup as group, "" as igroup, subgroup, "" as isubgroup, Code, "" as icode, CodeType, "" as iCodeType, CareSetting, "" as iCareSetting, Principal, "" as iPrincipal from _INCQUERYFILE ) union /*PART 4: Add INCQUERY only codes that doesn't apply to _ALLGROUPS_*/ ( select "" as group, igroup, "" as subgroup, subgroup as isubgroup, "" as Code, Code as icode, "" as CodeType, CodeType as iCodeType, "" as CareSetting, CareSetting as iCareSetting, "" as Principal, Principal as iPrincipal from _INCQUERYFILE where IGroup ne "_ALLGROUPS_" except select "" as group, group as igroup, "" as subgroup, subgroup as isubgroup, "" as Code, Code as icode, "" as CodeType, CodeType as iCodeType, "" as CareSetting, CareSetting as iCareSetting, "" as Principal, Principal as iPrincipal from _QUERYFILE ) order by Group; quit; data _querycodes; set _querycodes; Query = 0; Incid = 0; if Group ne "" then Query = 1; if IGroup ne "" then Incid = 1; if SubGroup = "" then SubGroup = ISubGroup; if CodeType = "" then CodeType = ICodeType; if Code = "" then Code = ICode; keep Group IGroup SubGroup Code CodeType CareSetting Principal Query Incid; run; /*In the following steps, we retrieve the query group settings to be applied in creation of episodes loop*/ proc sort nodupkey data=_QUERYFILE out=_querysettings(keep=Group WashTyp WashPer MinEpisDur MinFollowPer LookupPer CareSetting Principal EnrDays); by Group; run; /*****************************/ /* POSTDIAG AND INCPOSTDIAG */ /*****************************/ data _POSTDIAGFILE; format group PostDiagGroup $30.; set _POSTDIAGFILE; WashTyp = upcase(WashTyp); if strip(Cats) = "" then Cats="0+"; run; proc sort nodupkey data=_POSTDIAGFILE; by Group PostDiagGroup CodeType Code /*MODIF WAVE3*/ Caresetting Principal; run; /*In some cases, we may wish that each post-diagnosis group of drugs in the PostDiag file be incident to a common list of diags, in this case, the list should only be entered once while leaving the group variable empty*/ data _INCPOSTDIAGFILE; format IGroup PostDiagGroup $30.; set _INCPOSTDIAGFILE(rename=(Group=IGroup)); if IGroup = "" then IGroup = "_ALLGROUPS_"; if PostDiagGroup = "" then PostDiagGroup = "_ALLPOSTDIAGGROUPS_"; run; proc sort nodupkey data=_INCPOSTDIAGFILE; by IGroup PostDiagGroup CodeType Code /*MODIF WAVE3*/ Caresetting Principal; run; /*If codes from the common post-diagnosis list (_ALLGROUPS_) are the union of all post-diagnosis group codes, the following steps avoid repeating claims*/ proc sql noprint; create table _postdiagcodes as /*PART 1: if IGROUP is _ALLGROUPS_ then join the files using CodeType and Code and keeping all INCPOSTDIAG codes*/ select postdiag.group, incpostdiag.igroup, postdiag.postdiaggroup, incpostdiag.postdiaggroup as ipostdiaggroup, postdiag.subgroup, incpostdiag.subgroup as isubgroup, postdiag.Code, incpostdiag.Code as icode, postdiag.CodeType, incpostdiag.CodeType as iCodeType, /*MODIF WAVE3*/ postdiag.CareSetting, incpostdiag.CareSetting as iCareSetting, postdiag.Principal, incpostdiag.Principal as iPrincipal from _POSTDIAGFILE as postdiag right join _INCPOSTDIAGFILE as incpostdiag on postdiag.Code = incpostdiag.Code and postdiag.CodeType = incpostdiag.CodeType and postdiag.CareSetting = incpostdiag.CareSetting and postdiag.Principal = incpostdiag.Principal where IGroup = "_ALLGROUPS_" union /*PART 2: if IGROUP is not _ALLGROUPS_ then join the files using CodeType, Code and Group to get all common codes*/ select postdiag.group, incpostdiag.igroup, postdiag.postdiaggroup, incpostdiag.postdiaggroup as ipostdiaggroup, postdiag.subgroup, incpostdiag.subgroup as isubgroup, postdiag.Code, incpostdiag.Code as icode, postdiag.CodeType, incpostdiag.CodeType as iCodeType, /*MODIF WAVE3*/ postdiag.CareSetting, incpostdiag.CareSetting as iCareSetting, postdiag.Principal, incpostdiag.Principal as iPrincipal from _POSTDIAGFILE as postdiag join _INCPOSTDIAGFILE as incpostdiag on postdiag.Code = incpostdiag.Code and postdiag.CodeType = incpostdiag.CodeType and postdiag.Group = incpostdiag.IGroup and postdiag.CareSetting = incpostdiag.CareSetting and postdiag.Principal = incpostdiag.Principal where IGroup ne "_ALLGROUPS_" union /*PART 3: Add POSTDIAG only codes*/ ( select group, "" as igroup, postdiaggroup, "" as ipostdiaggroup, subgroup, "" as isubgroup, Code, "" as icode, CodeType, "" as iCodeType, /*MODIF WAVE3*/ CareSetting, "" as iCareSetting, Principal, "" as iPrincipal from _POSTDIAGFILE where (CodeType||Code||Principal||CareSetting) not in ( select (CodeType||Code||Principal||CareSetting) from _INCPOSTDIAGFILE where IGroup = "_ALLGROUPS_" ) except select igroup as group, "" as igroup, postdiaggroup, "" as ipostdiaggroup, subgroup, "" as isubgroup, Code, "" as icode, CodeType, "" as iCodeType, /*MODIF WAVE3*/ CareSetting, "" as iCareSetting, Principal, "" as iPrincipal from _INCPOSTDIAGFILE ) union /*PART 4: Add INCPOSTDIAG only codes that doesn't apply to _ALLGROUPS_*/ ( select "" as group, igroup, "" as postdiaggroup, postdiaggroup as ipostdiaggroup, "" as subgroup, subgroup as isubgroup, "" as Code, Code as icode, "" as CodeType, CodeType as iCodeType, /*MODIF WAVE3*/ "" as CareSetting, CareSetting as iCareSetting, "" as Principal, Principal as iPrincipal from _INCPOSTDIAGFILE where IGroup ne "_ALLGROUPS_" except select "" as group, group as igroup, "" as postdiaggroup, postdiaggroup as ipostdiaggroup, "" as subgroup, subgroup as isubgroup, "" as Code, Code as icode, "" as CodeType, CodeType as iCodeType, /*MODIF WAVE3*/ "" as CareSetting, CareSetting as iCareSetting, "" as Principal, Principal as iPrincipal from _POSTDIAGFILE ) order by Group; quit; data _postdiagcodes; set _postdiagcodes; PostDiag = 0; IncPostDiag = 0; if Group ne "" then PostDiag = 1; if IGroup ne "" then IncPostDiag = 1; if PostDiagGroup = "" then PostDiagGroup = IPostDiagGroup; if SubGroup = "" then SubGroup = ISubGroup; if CodeType = "" then CodeType = ICodeType; if Code = "" then Code = ICode; keep Group IGroup PostDiagGroup SubGroup Code CodeType PostDiag IncPostDiag; run; /*In the following steps, we retrieve the PostDiagGroup settings to be applied in incidence of post-diagnosis drugs calculation*/ proc sort nodupkey data=_POSTDIAGFILE out=_postdiagsettings(keep=Group PostDiagGroup WashTyp WashPer EpisodeGap Cats CodeCount); by Group PostDiagGroup; run; *Create PostDiag Cats; data _postdiagcats(keep=Group PostDiagGroup LowerBd UpperBd Cat); set _postdiagsettings; format LowerBd UpperBd best.; NumCats=CountW(Cats,' '); do i=1 to NumCats; Cat=scan(Cats,i,' '); MinusPos=index(Cat,'-'); PlusPos=index(Cat,'+'); CatLength=length(Cat); if MinusPos=0 and PlusPos=0 then do; LowerBd=input(Cat,best.); UpperBd=input(Cat,best.); end; else if PlusPos=0 then do; LowerBd=input(substr(Cat,1,MinusPos-1),best.); UpperBd=input(substr(Cat,MinusPos+1,MinusPos-1),best.); end; else do; LowerBd=input(substr(Cat,1,PlusPos-1),best.); UpperBd=99999999; end; if i=1 then LowerBd=.; output; end; run; /*Since a claim can either be query incident or post-diagnosis we cannot apply at the retrieval a query group setting, we shall apply the most severe washout setting*/ data _extractsettings (keep=WashTyp WashPer WashOrder); set _querysettings(keep=WashTyp WashPer) _postdiagsettings(keep=WashTyp WashPer); WashOrder=1; if strip(upcase(WashTyp))="MIN" then WashOrder=0; run; proc sort data = _extractsettings; by WashOrder descending WashPer; run; data _extractsettings; set _extractsettings; if _N_ = 1; run; data _querycodes; set _querycodes; if _N_ = 1 then set _extractsettings; run; data _postdiagcodes; set _postdiagcodes; if _N_ = 1 then set _extractsettings; run; /*****************************/ /* Pre-existing conditions */ /*****************************/ data _CONDFILE; retain Group codetype Code; set _CONDFILE; length CondLookTyp $2.; format WashTyp $4.; WashTyp='MULT'; if CondFrom=. and CondTo=. then delete; /*Defensive coding - should have one and/or the other*/ CondLookTyp='F'; if missing(CondFrom) then CondFrom=0; /*Default value will ultimately include index date(=0)*/ if missing(CondTo) then CondTo=0; /*Default value will ultimately include index date(=0)*/ run; proc sort nodupkey data=_CONDFILE(keep=Group SubGroup Codetype Code Principal CareSetting CondLookTyp WashTyp Condfrom Condto Inclusion); by _ALL_; /*the retain included in the previous step makes sure Group codetype Code are in this order*/ run; /*****************************/ /* Claims classification */ /*****************************/ data _diag _proc _labs _ndc(drop=Principal CareSetting); length Caresetting $ 30; length Principal $ 3; set _querycodes(in=a) _postdiagcodes(in=b) _condfile(in=c); if not a then do; Query = 0; Incid = 0; end; if not b then do; PostDiag = 0; IncPostDiag = 0; end; if c then Cond = 1;else Cond = 0; code=compress(code,'. '); length=length(code); if WashTyp = '' then WashTyp = 'MULT'; if WashPer = . then WashPer = 0; if CondFrom = . then CondFrom = 0; if CondTo = . then CondTo = 0; CodeType=upcase(CodeType); if CodeType in:('DX') then do; codetype=compress(codetype,'DX'); output _diag; end; if CodeType in:('RX') then do; codetype=compress(codetype,'RX'); output _ndc; end; if CodeType in:('PX') then do; codetype=compress(codetype,'PX'); output _proc; end; if CodeType in:('LABS') then do; output _labs; end; run; /*Storing all QueryGroup into a macro vector*/ proc sort nodupkey data = _querycodes(where=(Query=1)) out = _GroupList(keep=Group); by Group; run; proc sql noprint; select Group into :GROUPVECT1 separated by ' ' from _GroupList; quit; proc sql noprint; select Group into :GROUPVECT2 separated by ',' from _GroupList; quit; %PUT &GROUPVECT1; %PUT &GROUPVECT2; /*Reading proc and diag codes into vectors*/ %GLOBAL VECT_proc; %GLOBAL VECT_diag; %MACRO CREATEVECT(file); %IF %SYSFUNC(exist(_&file.))=1 %THEN %DO; data _&file.; set _&file.; OrigCode=code; exact=index(code,'*')=0; if exact=1 then do; code=compress(code,'*'); end; else do; WildcardIndex=index(code,'*'); if WildcardIndex=length(code) then do; /*the star is at the end*/ code=compress(code,'*'); end; else do; /*the star is in a middle position*/ exact=-1; LengthCodeEnd=length(code)-WildcardIndex; CodeEnd=substr(code,WildcardIndex+1,LengthCodeEnd); code=substr(code,1,WildcardIndex-1); end; end; length=length(Code); run; data _null_; call symput("VECT_&file.",""); run; data _temp(rename=code2=code); set _&file.; format code2 $8.; code2 = "'"||trim(left(code))||"'"; keep code2; run; proc sql noprint; select unique code into :VECT_&file. separated by ' ' from _temp; quit; %PUT &&VECT_&file..; %END; %MEND CREATEVECT; %CREATEVECT(proc); %CREATEVECT(diag); /*---------------------------------------------------------------------------*/ /* 04 -- Extract medical (diagnosis and procedures) claims */ /*---------------------------------------------------------------------------*/ %MACRO GETMEDS(); proc contents data=_proc noprint out=_tilt_ ; data _null_ ; set _tilt_ ; call symput('pnobs',trim(left(put(nobs,15.)))) ; run; %PUT &pnobs.; %IF %EVAL(&pnobs.>0) %THEN %DO; proc sql noprint; Create Table _procextract as select PatID, ADate, EncType, 'X' as PDX, compress(PX,'.') as PX, PX_CodeType from indata.&proctable.(where=(compress(PX,'.') in:(&VECT_proc.))) /*MODIF WAVE3: removed to allow multiple claims per day group by PatID, ADate, EncType, PDX, PX, PX_CodeType having freq(patid) ge 1*/; quit; proc sql noprint; Create Table _procedures as Select prctb.PatId, prctb.Adate, prctb.EncType, prctb.PDX, 1 as RxSup, /*MODIF WAVE3*/ 1 as RxAmt, compress(prctb.PX,'. ') as Code, proclist.GROUP, proclist.IGroup, proclist.SUBGROUP, proclist.CODETYPE, proclist.Query, proclist.Incid, proclist.WASHTYP, proclist.WASHPER, proclist.PostDiagGroup, proclist.PostDiag, proclist.IncPostDiag, proclist.Principal, proclist.Caresetting, proclist.CondLookTyp, proclist.Condfrom, proclist.Condto, proclist.Cond, proclist.Inclusion, proclist.length, 1 as proc From _procextract 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 prctb.Adate >= &QUERYFROM. - proclist.WashPer + proclist.CondFrom -(proclist.washtyp='MIN')*999999; /*The 999999 is to extend the washout period long enough in the case where washtyp = 'MIN'*/ quit; 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 _diagextract as select PatID, ADate, EncType, PDX, compress(DX,'.') as DX, DX_CodeType from indata.&diatable.(where=(compress(DX,'.') in:(&VECT_diag.))) /*MODIF WAVE3: removed to allow multiple claims per day group by PatID, ADate, EncType, PDX, DX, DX_CodeType having freq(patid) ge 1*/; quit; proc sql noprint; Create Table _Diagnosis as Select diagtb.PatId, diagtb.Adate, diagtb.EncType, diagtb.pdx, 1 as RxSup, /*MODIF WAVE3*/ 1 as RxAmt, compress(diagtb.DX,'. ') as Code, diaglist.GROUP, diaglist.IGroup, diaglist.SUBGROUP, diaglist.CODETYPE, diaglist.Query, diaglist.Incid, diaglist.WASHTYP, diaglist.WASHPER, diaglist.PostDiagGroup, diaglist.PostDiag, diaglist.IncPostDiag, diaglist.Principal, diaglist.Caresetting, diaglist.CondLookTyp, diaglist.Condfrom, diaglist.Condto, diaglist.Cond, diaglist.Inclusion, diaglist.length, -1 as proc From _diagextract 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 diagtb.Adate >= &QUERYFROM. - diaglist.WashPer + diaglist.CondFrom -(diaglist.washtyp='MIN')*999999; /*The 999999 is to extend the washout period long enough in the case where washtyp = 'MIN'*/ quit; 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; Clm = _N_; run; proc datasets library = work nolist nowarn; delete _procedures _diagnosis _procextract _diagextract; quit; %END; %IF %EVAL(&pnobs.> 0 and &dnobs.=0) %THEN %DO; data _MasterQueryfile; set _procedures; Clm = _N_; run; proc datasets library = work nolist nowarn; delete _procedures _procextract; quit; %END; %IF %EVAL(&pnobs.=0 and &dnobs.>0) %THEN %DO; data _MasterQueryfile; set _diagnosis; Clm = _N_; run; proc datasets library = work nolist nowarn; delete _diagnosis _diagextract; quit; %END; %MEND GETMEDS; %GETMEDS(); %MACRO GETLABS(); proc contents data=_labs noprint out=_tilt_ ; data _null_ ; set _tilt_ ; call symput('lnobs',trim(left(put(nobs,15.)))) ; run; %PUT &lnobs.; %IF %EVAL(&lnobs.>0) %THEN %DO; proc sort nodupkey data=_labs out=_labscodes(keep=CodeType Code); by CodeType Code; run; proc sort nodupkey data=_labscodemap; by CodeType Code; run; *Map Lab codes; data _labscodes; merge _labscodes _labscodemap; by CodeType Code; keep MS_Test_Name MS_Test_Sub_Category Specimen_Source CodeType Code; run; *Extract Lab data and assign ADate; data _labsextract(drop=Order_Dt Lab_Dt Result_Dt); format aDate mmddyy10.; length aDate 4.; if 0 then set _labscodes; declare hash ht (hashexp:16, dataset:'_labscodes'); ht.definekey('MS_Test_Name','MS_Test_Sub_Category','Specimen_Source'); ht.definedata(ALL: 'YES'); ht.definedone(); do until(eof1); set indata.&LABTABLE. end=eof1; if ht.find()=0 then do; if not missing(Lab_Dt) then ADate=Lab_Dt; else if not missing(Result_Dt) then ADate=Result_Dt; else ADate=Order_Dt; PDX='X'; if not missing(ADate) then output _labsextract; end; end; stop; run; proc sql noprint; create table _laboratories as Select labstb.PatId, labstb.Adate, labstb.Pt_Loc, labstb.pdx, 1 as RxSup, /*MODIF WAVE3*/ 1 as RxAmt, labslist.GROUP, labslist.IGroup, labslist.SUBGROUP, labslist.CODE, labslist.CODETYPE, labslist.Query, labslist.Incid, labslist.WASHTYP, labslist.WASHPER, labslist.PostDiagGroup, labslist.PostDiag, labslist.IncPostDiag, labslist.Principal, labslist.Caresetting, labslist.CondLookTyp, labslist.Condfrom, labslist.Condto, labslist.Cond, labslist.Inclusion, labslist.length, -2 as proc From _labsextract as labstb, _labs as labslist Where labstb.codetype = labslist.codetype and labstb.code = labslist.Code and labstb.Adate >= &QUERYFROM. - labslist.WashPer + labslist.CondFrom - (labslist.washtyp='MIN')*999999 order by Pt_Loc; /*The 999999 is to extend the washout period long enough in the case where washtyp = 'MIN'*/ quit; * Assign EncType; proc sort nodupkey data=_ptloccodemap; by Pt_Loc; run; data _laboratories; merge _laboratories(in=a) _ptloccodemap(keep=Pt_Loc EncType); by Pt_Loc; if a; drop Pt_Loc; run; Title1 "Laboratory code frequency printout"; proc freq data = _laboratories; tables Code; run; Title1 ""; %END; %MEND GETLABS; %GETLABS(); *Append LABS to master query file; %MACRO WRAPPER; %IF %SYSFUNC(exist(_MasterQueryFile))=1 and %SYSFUNC(exist(_laboratories))=1 %THEN %DO; data _MasterQueryFile; format code $11.; set _MasterQueryFile _laboratories; Clm = _N_; run; proc datasets library = work nolist nowarn; delete _laboratories; quit; %END; %IF %SYSFUNC(exist(_MasterQueryFile))=0 and %SYSFUNC(exist(_laboratories))=1 %THEN %DO; data _MasterQueryFile; set _laboratories; Clm = _N_; run; proc datasets library = work nolist nowarn; delete _laboratories; quit; %END; %MEND WRAPPER; %WRAPPER; /*---------------------------------------------------------------------------*/ /* 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 */ /******************************************************************/ data _condfile_; set _condfile; run; %GLOBAL NEEDENVEL; %LET NEEDENVEL=0; %MACRO ENVELNEEDED(inputfile=); %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=&inputfile.(keep=caresetting) out=_CS; by caresetting; run; data _null_; set _CS; 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. &nobs1.; %PUT %EVAL(&nobs1.>0 and &SOME.=1); %IF %EVAL(&nobs1.>0 and &SOME.=1) %THEN %DO; %LET NEEDENVEL=1; %END; %MEND ENVELNEEDED; %MACRO WRAPPER(); %IF %EVAL(&NEEDENVEL.=0) %THEN %DO; %ENVELNEEDED(inputfile=_queryfile); %END; %IF %EVAL(&NEEDENVEL.=0) %THEN %DO; %ENVELNEEDED(inputfile=_incqueryfile); %END; %IF %EVAL(&NEEDENVEL.=0) %THEN %DO; %ENVELNEEDED(inputfile=_postdiagfile); %END; %IF %EVAL(&NEEDENVEL.=0) %THEN %DO; %ENVELNEEDED(inputfile=_incpostdiagfile); %END; %IF %EVAL(&NEEDENVEL.=0) %THEN %DO; %ENVELNEEDED(inputfile=_condfile_); %END; %PUT &NEEDENVEL.; %MEND WRAPPER; %WRAPPER(); %MACRO ENVEL(); /*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; proc datasets library = work nolist nowarn; delete _IPDates; quit; %MEND ENVEL; %MACRO WRAPPER(); %IF %EVAL(&NEEDENVEL.=1) %THEN %DO; %ENVEL(); %END; %MEND WRAPPER; %WRAPPER(); %LET infile=_MasterQueryfile; %LET settingsfile=_queryfile; %LET groupingkey=Group Subgroup Codetype Code Proc; %LET flag=Query; %MACRO APPLYCARESETTING(infile=,settingsfile=,groupingkey=,flag=); /*Get appropriate Principal and care setting*/ data &settingsfile.; set &settingsfile.; if CodeType in:('DX') then do; codetype=compress(codetype,'DX'); proc=-1; end; if CodeType in:('RX') then do; codetype=compress(codetype,'RX'); proc=0; end; if CodeType in:('PX') then do; codetype=compress(codetype,'PX'); proc=1; end; if CodeType in:('LABS') then do; proc=-2; end; rename Principal=&flag.Principal; rename Caresetting=&flag.Caresetting; keep &groupingkey. Principal Caresetting; run; proc sort nodupkey data=&settingsfile.; by &groupingkey.; run; /*Adding quotes and commas for hash key*/ data _groupingkeyvar; set &settingsfile.; keep &groupingkey.; run; proc contents data = _groupingkeyvar out=_groupingkeyvar(keep = VARNUM NAME) noprint; run; proc sql noprint; select "'"||strip(NAME)||"'" into :groupingkeyquot separated by ',' from _groupingkeyvar order by VARNUM; quit; %PUT &groupingkeyquot.; data &infile.(drop=&flag.principal &flag.caresetting KeepRec&flag.); if 0 then set &settingsfile.; declare hash ht (hashexp:16, dataset:"&settingsfile."); ht.definekey(&groupingkeyquot.); ht.definedata(ALL: 'YES'); ht.definedone(); do until(eof1); set &infile. end=eof1; if ht.find() ne 0 then do; call missing(&flag.Principal,&flag.Caresetting); end; format EncType2 $2.; KeepRec&flag.=0; /*Case where all care setting are wanted and necessarily that principal=NO*/ if &flag. and &flag.caresetting = '' then KeepRec&flag.=1; else if &flag. then do; Numarg=length(compress(&flag.caresetting," '"))/2; do i=1 to Numarg; EncType2=compress(scan(&flag.caresetting,i),"'"); if EncType2 = EncType then do; if upcase(&flag.principal)='NO' then KeepRec&flag.=1; if upcase(&flag.principal)='YES' and upcase(PDX)='P' and upcase(EncType2) in('IP','ED') then KeepRec&flag.=1; end; end; end; /*Adjust record flag according to principal and care setting*/ &flag.=KeepRec&flag.; output &infile.; end; stop; run; %MEND APPLYCARESETTING; %MACRO WRAPPER(); %IF %SYSFUNC(exist(_MasterQueryfile))=1 %THEN %DO; %APPLYCARESETTING(infile=_MasterQueryfile,settingsfile=_queryfile,groupingkey=Group Subgroup Codetype Code Proc,flag=Query); %APPLYCARESETTING(infile=_MasterQueryfile,settingsfile=_incqueryfile,groupingkey=iGroup Subgroup Codetype Code Proc,flag=Incid); %APPLYCARESETTING(infile=_MasterQueryfile,settingsfile=_postdiagfile,groupingkey=Group PostDiagGroup Subgroup Codetype Code Proc,flag=PostDiag); %APPLYCARESETTING(infile=_MasterQueryfile,settingsfile=_incpostdiagfile,groupingkey=iGroup PostDiagGroup Subgroup Codetype Code Proc,flag=IncPostDiag); %APPLYCARESETTING(infile=_MasterQueryfile,settingsfile=_condfile_,groupingkey=Group Subgroup Codetype Code Proc,flag=Cond); data _MasterQueryFile(drop=Enctype2 numarg i clm EncType PDX principal caresetting); set _MasterQueryFile; /*Delete record that doesn't meet care setting requirements*/ if Query or Incid or PostDiag or IncPostDiag or Cond; run; %END; %MEND WRAPPER; %WRAPPER(); /*---------------------------------------------------------------------------*/ /* 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.SUBGROUP, CodeList.CODE, CodeList.CODETYPE, CodeList.Query, CodeList.Incid, CodeList.WASHTYP, CodeList.WASHPER, CodeList.PostDiagGroup, CodeList.PostDiag, CodeList.IncPostDiag, CodeList.CondLookTyp, CodeList.Condfrom, CodeList.Condto, CodeList.Cond, CodeList.Inclusion, Dispensing.Patid, Dispensing.RxDate as ADate, Dispensing.RxSup, Dispensing.RxAmt, 0 as Proc from _ndc as CodeList, indata.&DISTABLE. as Dispensing where RxSup > 0 and substr(Dispensing.ndc,1,9) = CodeList.code and (Dispensing.Rxdate+Dispensing.RxSup-1) >= &QUERYFROM. - CodeList.WashPer + CodeList.CondFrom - (upcase(CodeList.washtyp)='MIN')*999999; quit; 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.SUBGROUP, CodeList.CODE, CodeList.CODETYPE, CodeList.Query, CodeList.Incid, CodeList.WASHTYP, CodeList.WASHPER, CodeList.PostDiagGroup, CodeList.PostDiag, CodeList.IncPostDiag, CodeList.CondLookTyp, CodeList.Condfrom, CodeList.Condto, CodeList.Cond, CodeList.Inclusion, Dispensing.Patid, Dispensing.RxDate as ADate, Dispensing.RxSup, Dispensing.RxAmt, 0 as Proc from _ndc as CodeList, indata.&DISTABLE. as Dispensing where RxSup > 0 and Dispensing.ndc = CodeList.code and (Dispensing.Rxdate+Dispensing.RxSup-1) >= &QUERYFROM. - CodeList.WashPer + CodeList.CondFrom - (upcase(CodeList.washtyp)='MIN')*999999; quit; 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(); %MACRO WRAPPER; %IF %SYSFUNC(exist(_MasterQueryFile))=1 and %SYSFUNC(exist(_Drugs))=1 %THEN %DO; data _MasterQueryFile; set _MasterQueryFile _drugs; run; proc datasets library = work nolist nowarn; delete _drugs; quit; %END; %IF %SYSFUNC(exist(_MasterQueryFile))=0 and %SYSFUNC(exist(_Drugs))=1 %THEN %DO; data _MasterQueryFile; set _drugs; run; proc datasets library = work nolist nowarn; delete _drugs; quit; %END; %MEND WRAPPER; %WRAPPER; /*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 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.; /*---------------------------------------------------------------------------*/ /* 07 -- Stockpile drugs */ /*---------------------------------------------------------------------------*/ /*Get PostDiagFile CodeCount setting and remove same day duplicates where necessary*/ proc sort data=_MasterQueryFile(where=(PostDiag ne 1)) out=_premaster1; by _ALL_; run; proc sort data=_MasterQueryFile(where=(PostDiag = 1)) out=_premaster2; by Group PostDiagGroup; run; data _premaster2; merge _premaster2 _postdiagsettings(keep=Group PostDiagGroup CodeCount); by Group PostDiagGroup; run; proc sort nodupkey data=_premaster2(where=(CodeCount = 1)) out=_premaster3; by _ALL_; run; data _MasterQueryFile; set _premaster1 _premaster2(where=(CodeCount ne 1)) _premaster3; drop CodeCount Code Codetype; run; data _MasterQueryFile(keep=PatId ADate Group IGroup SubGroup Query Incid PostDiag IncPostDiag RxSup RxAmt proc) _MasterPostDiagFile(keep=PatId ADate Group IGroup PostDiagGroup SubGroup PostDiag IncPostDiag RxSup RxAmt proc) _MasterCondFile(keep=PatId ADate Group IGroup SubGroup WashTyp WashPer CondLookTyp CondFrom CondTo Cond RxSup RxAmt proc Inclusion); set _MasterQueryFile; if query or incid then output _MasterQueryFile; /*Contains query related claims*/ /*Create 2 files here instead of _MasterNonQueryFile since stockpiling must be applied to post-diagnosis claims*/ else if postdiag or incpostdiag then output _MasterPostDiagFile; /*Contains post-diagnosis related claims*/ else output _MasterCondFile; /*Contains pre-existing condition related claims*/ run; /*MODIF WAVE3 HERE*/ %macro STOCKPILE(configfile=,file=,whereQ=,whereI=,class=,id=); data _null_; set _GroupList end=fin; if fin then call symput('NQUERYGROUPS',_n_); run; %put &NQUERYGROUPS.; %do i = 1 %to &NQUERYGROUPS.; data _null_; set _GroupList; if _n_ = &i. then call symput('ITGROUP',strip(Group)); run; %if %str("&configfile.") ne %str("") %then %do; data _null_; set &configfile.; if Group in("&ITGROUP.") then do; call symput('SAMEDAY',strip(SameDay)); call symput('SUPRANGE',strip(SupRange)); call symput('AMTRANGE',strip(AmtRange)); call symput('PERCENTDAYS',PercentDays); end; run; %end; %else %do; %let SAMEDAY=aa; %let SUPRANGE=0<-HIGH; %let AMTRANGE=0<-HIGH; %let PERCENTDAYS=; %end; %let PERCENTDAYS = %trim(&PERCENTDAYS.); %if %str("&PERCENTDAYS.")=%str("0") %then %do; %let PERCENTDAYS=; %end; %put &ITGROUP. &SAMEDAY. &SUPRANGE. &AMTRANGE. &PERCENTDAYS.; *QUERY records (Group non blank); data &file.Q_iter; set &file.; where &whereQ. and Group in("&ITGROUP."); run; %MS_STOCKPILING(INFILE=&file.Q_iter, CLMDATE=ADate, CLMSUP=RxSup, CLMAMT=RxAmt, PROCFLAG=proc, PERCENTDAYS=&PERCENTDAYS., GROUPING=group &class. subgroup, SAMEDAY=&SAMEDAY., SUPRANGE=&SUPRANGE., AMTRANGE=&AMTRANGE., ID=IGroup &id., OUTFILE=&file.Q_iter, OUTFILEEXCL=&file.Q_iterex ); data &file.Q_iterex; set &file.Q_iterex; format Group $30.; Group="&ITGROUP."; run; %if &I.=1 %then %do; data &file.Q; set &file.Q_iter; run; data &file.Qex; set &file.Q_iterex; run; %end; %else %do; proc append base = &file.Q data = &file.Q_iter force; run; proc append base = &file.Qex data = &file.Q_iterex force; run; %end; %end; *INCID only records (Group blank and IGroup non blank); data &file.I; set &file.; where &whereI.; run; *Stockpiling input file only applies to QUERYFILE codes; %MS_STOCKPILING(INFILE=&file.I, CLMDATE=ADate, CLMSUP=RxSup, CLMAMT=RxAmt, PROCFLAG=proc, PERCENTDAYS=, GROUPING=igroup &class. subgroup, SAMEDAY=aa, SUPRANGE=0<-HIGH, AMTRANGE=0<-HIGH, ID=Group &id., OUTFILE=&file.I, OUTFILEEXCL=&file.Iex ); %mend STOCKPILE; %STOCKPILE(configfile=_stockpilingfile,file=_MasterQueryFile,whereQ=Query,whereI=not Query and Incid,class=,id=Query Incid PostDiag IncPostDiag); %STOCKPILE(configfile=_stockpilingfile,file=_MasterPostDiagFile,whereQ=PostDiag,whereI=not PostDiag and IncPostDiag,class=PostDiagGroup,id=PostDiag IncPostDiag); %STOCKPILE(configfile=_stockpilingfile,file=_MasterCondFile,whereQ=Cond,whereI=not Cond,class=CondFrom CondTo Inclusion,id=Cond WashPer WashTyp CondLookTyp); %macro STOCKPILE_EXCLUDED(infile=,claimtype=); proc means data=&infile. nway noprint; var ExclSup ExclAmt ExclBoth; class Group PatId; id Patient; output out=&infile.(drop=_:) sum(ExclSup ExclAmt ExclBoth)= N(ExclSup)=ExclClaims; run; proc means data=&infile. nway noprint; var Patient ExclClaims ExclSup ExclAmt ExclBoth; class Group; output out=&infile.(drop=_:) N(Patient)=ExclMembers sum(ExclClaims ExclSup ExclAmt ExclBoth)=; run; data &infile.; retain Group ClaimType ExclMembers ExclClaims ExclSup ExclAmt ExclBoth; merge &infile.(in=a) _grouplist(in=b); by Group; format ClaimType $10.; ClaimType="&claimtype."; if not a then do; ExclMembers=0; ExclClaims=0; ExclSup=0; ExclAmt=0; ExclBoth=0; end; run; %mend STOCKPILE_EXCLUDED; %STOCKPILE_EXCLUDED(infile=_MasterQueryFileQEx,claimtype=QUERY); %STOCKPILE_EXCLUDED(infile=_MasterPostDiagFileQEx,claimtype=POSTDIAG); %STOCKPILE_EXCLUDED(infile=_MasterCondFileQEx,claimtype=CONDITION); data msoc.&REQUESTID.&RUNID._stockpiling_excl; set _MasterQueryFileQEx _MasterPostDiagFileQEx _MasterCondFileQEx; run; /*---------------------------------------------------------------------------*/ /* 08 -- Extract eligible patients */ /*---------------------------------------------------------------------------*/ /********************************************************/ /* Create _DenomInt (Extraction of eligible patients) */ /********************************************************/ 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; data _DenomInt; set _DenomInt(where=(Enr_End >= Enr_Start)); by PatId; format MinAgeDate MaxAgeDate Enr_End Enr_Start 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 eligible 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)= / keeplen; run; /*Ensure all claims kept are in patients enrollment period*/ proc sql noprint; create table _MasterQueryFile as select mqfq.*, den.Enr_Start, den.Enr_End from _MasterQueryFileQ as mqfq, _DenomInt as den where mqfq.PatId=den.PatId and den.Enr_Start <= mqfq.ExpireDt and mqfq.ADate <= den.Enr_End union all corr select mqfi.*, den.Enr_Start, den.Enr_End from _MasterQueryFileI as mqfi, _DenomInt as den where mqfi.PatId=den.PatId and den.Enr_Start <= mqfi.ExpireDt and mqfi.ADate <= den.Enr_End ; quit; data _MasterQueryFile; set _MasterQueryFile; ADate=max(Enr_Start,Adate); ExpireDt=min(Enr_end,ExpireDt); if Adate < &QUERYFROM. <= ExpireDt then do; _Adate=Adate; _ExpireDt=ExpireDt; ExpireDt=&QUERYFROM.-1; output; Adate=&QUERYFROM.; ExpireDt=_ExpireDt; output; end; else output; drop _Adate _ExpireDt Enr_Start; run; proc sql noprint; create table _MasterPostDiagFile as select mcfq.*, den.Enr_Start, den.Enr_End from _MasterPostDiagFileQ as mcfq, _DenomInt as den where mcfq.PatId=den.PatId and den.Enr_Start <= mcfq.ExpireDt and mcfq.ADate <= den.Enr_End union all corr select mcfi.*, den.Enr_Start, den.Enr_End from _MasterPostDiagFileI as mcfi, _DenomInt as den where mcfi.PatId=den.PatId and den.Enr_Start <= mcfi.ExpireDt and mcfi.ADate <= den.Enr_End; quit; data _MasterPostDiagFile; set _MasterPostDiagFile; ADate=max(Enr_Start,Adate); ExpireDt=min(Enr_end,ExpireDt); if Adate < &QUERYFROM. <= ExpireDt then do; _Adate=Adate; _ExpireDt=ExpireDt; ExpireDt=&QUERYFROM.-1; output; Adate=&QUERYFROM.; ExpireDt=_ExpireDt; output; end; else output; drop _Adate _ExpireDt Enr_Start Enr_End; run; proc sql noprint; create table _MasterCondFile as select mqfq.* from _MasterCondFileQ as mqfq, _DenomInt as den where mqfq.PatId=den.PatId and den.Enr_Start <= mqfq.ADate <= den.Enr_End; quit; proc datasets library = work nolist nowarn; /* MODIF WAVE3 HERE*/ delete _MasterQueryFileQ _MasterQueryFileI _MasterPostDiagFileQ _MasterPostDiagFileI _MasterCondFileQ _MasterCondFileI _masterqueryfileqex _masterqueryfileiex _masterpostdiagfileqex _masterpostdiagfileiex _masterqueryfileq_iter _masterqueryfileq_iterex _masterpostdiagfileq_iter _masterpostdiagfileq_iterex _mastercondfileqex _mastercondfileiex _mastercondfileq_iter _mastercondfileq_iterex; quit; /*Add age groups to DenomInt*/ 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; rename episode=EligEpisode; drop i j threshdate; run; /**********************/ /* Create _FdateTable */ /**********************/ proc means noprint data =_MasterQueryFile nway; var ADate; class PatId Group; where &QUERYFROM. <= ADate <= &QUERYTO. and Query=1; output out=_FDateTable(drop=_:) min = MinDt /keeplen; run; /*Create QueryGroup Date Variables*/ proc transpose data = _FDateTable out = _FDateTable(drop=_NAME_); id Group; var MinDt; by PatId; run; proc transpose data=_querysettings 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 MinAgeDate MaxAgeDate); 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; /*---------------------------------------------------------------------------*/ /* 09 -- Process data for each query group */ /*---------------------------------------------------------------------------*/ %let i = 1; /********************************************************/ /* Looping through each query group */ /********************************************************/ %global NQUERYGROUPS; %MACRO LOOPTHROUGH(); data _null_; set _GroupList end=fin; if fin then call symput('NQUERYGROUPS',_n_); run; %PUT &NQUERYGROUPS.; %DO i = 1 %TO &NQUERYGROUPS.; data _null_; set _GroupList; if _n_ = &i. then call symput('ITGROUP',strip(Group)); run; %PUT &ITGROUP.; data _loopsettings; set _querysettings(where=(Group in("&ITGROUP."))); drop Group; run; data _SDFDateTable(keep=PatId); set _FDateTable; if &ITGROUP.; run; /*Select all claims for this study drug (and post-diagnosis) and identify those that are within query dates*/ data _Quantity(drop=query incid incpostdiag Group SubGroup IGroup Enr_End); if 0 then set _SDFDateTable; declare hash ht (hashexp:16, dataset:'_SDFDateTable'); ht.definekey('PatId'); ht.definedata(ALL: 'YES'); ht.definedone(); do until(eof1); set _MasterQueryfile(where=((Group in("&ITGROUP.") or IGroup in("&ITGROUP.","_ALLGROUPS_")) and (query or incid))) _MasterPostDiagfile(where=((Group in("&ITGROUP.")) and PostDiag)) end=eof1; 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; /*Defensive coding, enrollment episodes do not overlap*/ Proc SQL Noprint; Create Table _CleanQuantity as Select qty.*, enrol.Enr_Start, enrol.Enr_End, enrol.MinAgeDate, enrol.MaxAgeDate From _Quantity as qty, _Enrollment as enrol Where qty.PatId = enrol.PatId and enrol.Enr_Start <= qty.ADate <= enrol.Enr_End Order by PatId, PostDiag, ADate; quit; /*below is automatically enforced when creating episodes for study, must adjust the RxSup for study and PostDiag to be consistent*/ data _CleanQuantity; set _CleanQuantity; ExpireDt = min(ExpireDt,Enr_End); /*MODIF WAVE3*/ RxAmtDaily=RxAmt/RxSup; RxSup = Expiredt - ADate + 1; run; /*---------------------------------------------------------------------------*/ /* 09.1 -- Post-diagnosis episodes */ /*---------------------------------------------------------------------------*/ /********************************************/ /* START Post-Diagnosis Episode Indicator */ /********************************************/ data _PostDiagQuantity(drop=MinAgeDate MaxAgeDate); set _CleanQuantity; where PostDiag=1 and InQuery=1; run; proc sort data=_PostDiagQuantity; by PostDiagGroup; run; /*MODIF WAVE3*/ data _PostDiagAmt; set _PostDiagQuantity; keep PatId PostDiagGroup ADate ExpireDt RxAmtDaily; run; data _PostDiagQuantity(drop=Group WashTyp WashPer); merge _PostDiagQuantity(in=a) _PostDiagSettings(where=(Group="&ITGROUP.") in=b); by PostDiagGroup; if a & b; run; /*CREATING GAPLESS POSTDIAG EPISODES FOR OVERLAPPING RXSUP - possibly more than on rank per PostDiag episode*/ proc sort data=_PostDiagQuantity; by Patid PostDiagGroup ADate; run; data _PostDiagQuantity; set _PostDiagQuantity; by Patid PostDiagGroup; if first.PostDiagGroup then overlap=.; else overlap=lexpiredt-ADate+1; if first.PostDiagGroup then do; rank=1; lexpiredt=expiredt; end; if overlap ne . then do; if overlap <= 0 then do; rank=rank+1; lexpiredt=expiredt; end; else do; if lexpiredt <= expiredt then lexpiredt=expiredt; /*claims not completely contained*/ end; end; retain rank lexpiredt; drop lexpiredt overlap; run; proc means noprint data = _PostDiagQuantity nway; var ADate ExpireDt NumDispensing; class PatId PostDiagGroup rank; id episodegap Enr_start Enr_end; output out=_PostDiagquantity(drop=_:) min(ADate)=PostDiagRankStart max(ExpireDt)=PostDiagRankEnd sum(NumDispensing)=PostDiagDisp; run; /*For post-diagnosis claims, episodes are computed only considering post-diagnosis episode gap since they are to be linked to query drugs episodes*/ data _PostDiagQuantity(drop=Gap EpisodeGap LRunOutDate LEnrStart Enr_Start Enr_End); set _PostDiagQuantity; by PatId PostDiagGroup; /*Create Episode Indicator*/ format LRunOutDate mmddyy10.; LRunOutDate = lag(PostDiagRankEnd); LEnrStart = lag(Enr_Start); if first.PostDiagGroup then do; LRunOutDate=.; LEnrStart=.; PostDiagEpisode=1; gap=.; end; else do; gap = PostDiagRankStart - LRunOutDate-1; if gap > EpisodeGap or lEnrStart ne Enr_Start then PostDiagEpisode=PostDiagEpisode+1; /*Change episode if exceed gap (above) of hole in enrollment*/ end; retain PostDiagEpisode; run; proc means data=_PostDiagQuantity nway noprint; var PostDiagRankEnd PostDiagRankStart; class PatId PostDiagGroup PostDiagEpisode; output out=_PostDiagEpisodeSummary(drop=_:) min(PostDiagRankStart)=PostDiagEpisodeStartDt max(PostDiagRankEnd)=PostDiagEpisodeEndDt / keeplen; run; proc sort data =_PostDiagQuantity; by PatId PostDiagGroup PostDiagEpisode; run; data _PostDiagQuantity; merge _PostDiagQuantity(in=a) _PostDiagEpisodeSummary(in=b); by PatId PostDiagGroup PostDiagEpisode; if a & b; run; /*---------------------------------------------------------------------------*/ /* 09.2 -- Query drugs episodes */ /*---------------------------------------------------------------------------*/ /***********************************/ /* START Query Episode Indicator */ /***********************************/ data _CleanQuantityEp; set _CleanQuantity(where=(PostDiag=0 and InQuery=1)); if _N_ = 1 then set _loopsettings; drop PostDiagGroup PostDiag RxAmt; run; /*data step to compute episode end according to LOOKPER*/ data _CleanQuantityEp; set _CleanQuantityEp; by Patid; format EpisExpireDt LookExpireDt mmddyy10.; if first.patid then do; LookExpireDt=ADate + LookupPer - 1; LookExpireDt = min(LookExpireDt,enr_end); EpisExpireDt = LookExpireDt; RxSup=0; /*Force to 0 to update non missing only in the next data step*/ end; else do; if ADate <= EpisExpireDt then do; LookExpireDt = EpisExpireDt; end; else do; /*New episode*/ LookExpireDt = ADate + LookupPer - 1; LookExpireDt = min(LookExpireDt,enr_end); EpisExpireDt = LookExpireDt; RxSup=0; /*Force to 0 to update non missing only in the next data step*/ end; end; retain EpisExpireDt; rename ADate=StudyADate; rename ExpireDt=StudyExpireDt; run; /*Assess the lookup period as a single study dispensing and put the lookup number of days in RxSup, ignoring multiple counts in a single episode*/ data _CleanQuantityEp; set _CleanQuantityEp; StudyDisp=1; if RxSup=0 then RxSup = LookExpiredt - StudyADate + 1; run; /*Episode creation*/ data _CleanQuantityEp(drop=LRunOutDate LEnrStart gap); set _CleanQuantityEp; by PatId; StudyADate = max(&QUERYFROM.,StudyADate); /*Create Episode Indicator*/ LRunOutDate = lag(LookExpireDt); LEnrStart = lag(Enr_Start); if first.PatId then do; LRunOutDate=.; LEnrStart=.; episode=1; gap=.; end; else do; gap = StudyADate - LRunOutDate-1; if gap >= 0 or lEnrStart ne Enr_Start then episode=episode+1; /*Change episode if exceed gap (above)*/ end; /*Identify Episode with necessary condition to qualify for incident episode*/ retain episode; run; /*Create episode summary for all episodes, including those InQuery=0 to further capture next episode start date*/ proc means data=_CleanQuantityEp nway noprint; var LookExpireDt StudyAdate RxSup Enr_Start Enr_End StudyDisp; class PatId episode; id WashTyp Washper MinFollowPer MinAgeDate MaxAgeDate EnrDays; output out=_EPisodeSummary(drop=_:) min(StudyAdate Enr_Start Enr_End)=EpisodeStartDt MinEnrStart MinEnrEnd max(LookExpireDt Enr_Start Enr_End)=EpisodeEndDt MaxEnrStart MaxEnrEnd sum(RxSup)=EpisodeRxSup sum(StudyDisp)=StudyDisp; run; Proc SQL Noprint; Create Table _NoClaimBf as Select epis.PatId, epis.Episode, epis.EpisodeStartDt, epis.WashTyp, epis.WashPer, qty.Expiredt From _CleanQuantity(where=(PostDiag=0)) as qty right join _EPisodeSummary as epis on qty.PatId = epis.PatId where qty.ADate < epis.EpisodeStartDt Order by PatId, episode; quit; data _NoClaimBf; set _NoClaimBf; NoClaimBf = 1; if WashTyp in('MIN') then NoClaimBf = 0; else if WashTyp in('MULT','SING') then do; if (EpisodeStartDt - ExpireDt - 1) < WashPer then NoClaimBf = 0; end; run; proc means noprint data = _NoClaimBf nway; var NoClaimBf; class PatId episode; output out=_NoClaimBf(drop=_:) min = ; run; data _NextDate; set _EPisodeSummary; episode=episode-1; rename EpisodeStartDt=NextEpisodeStartDt; keep patid episode EpisodeStartDt; run; data _ValidEpisodes; merge _EPisodeSummary(in=a) _NextDate _NoClaimBf(in=b); by patid episode; if a; if not b then NoClaimBf = 1; /*Enrollment*/ EnrNumDays= EpisodeStartDt - MinEnrStart; if EnrNumDays>=0 and EnrNumDays >= EnrDays then Enr = 1; else Enr = 0; if EnrNumDays>=0 and EnrNumDays >= WashPer then Wash = 1; else Wash = 0; /*Identify if episode is incident or not*/ if NoclaimBf = 1 and Enr = 1 and Wash=1 then Incident = 1; else Incident = 0; /*Episode duration and minimum drug use*/ EpisodeEndDt=min(EpisodeEndDt,NextEpisodeStartDt-1,MaxEnrEnd); Duration=1; /*Follow period after index date*/ if (MaxEnrEnd - EpisodeStartDt + 1) >= MinFollowPer then FollowPerOK=1; else FollowPerOK=0; /*Keeping only valid episodes*/ if Duration and FollowPerOK; if MinAgeDate <= EpisodeStartDt <= MaxAgeDate; keep Patid Episode EpisodeStartDt EpisodeEndDt MinEnrStart MaxEnrEnd StudyDisp EpisodeRxSup Incident WashTyp; run; /*---------------------------------------------------------------------------*/ /* 09.3 -- Output episode information */ /*---------------------------------------------------------------------------*/ /***********************/ /* START Output Method */ /***********************/ /*Merge Study and Post-Diagnosis episodes information*/ proc sql noprint; create table _StudyPostDiagEpisodes as select validep.PatId, validep.Episode, validep.EpisodeStartDt, validep.EpisodeEndDt, PostDiagep.PostDiagGroup, PostDiagep.PostDiagEpisode, PostDiagep.PostDiagEpisodeStartDt, PostDiagep.PostDiagEpisodeEndDt from _validepisodes as validep, _PostDiagepisodesummary as PostDiagep where validep.PatId = PostDiagep.PatId and PostDiagep.PostDiagEpisodeStartDt <= validep.EpisodeEndDt and PostDiagep.PostDiagEpisodeEndDt >= validep.EpisodeStartDt; /*overlap*/ quit; /*Calculate PostDiagDay considering episode overlapping*/ data _StudyPostDiagEpisodes; set _StudyPostDiagEpisodes; PostDiagEpisodeStartDt=max(PostDiagEpisodeStartDt,EpisodeStartDt); PostDiagEpisodeEndDt=min(PostDiagEpisodeEndDt,EpisodeEndDt); PostDiagDay=PostDiagEpisodeEndDt-PostDiagEpisodeStartDt+1; run; proc sql noprint; create table _PostDiagSummary1 as select PatId, Episode, PostDiagGroup, Sum(PostDiagDay) as PostDiagDay from _StudyPostDiagEpisodes group by PatId, Episode, PostDiagGroup order by PatId, Episode, PostDiagGroup; quit; proc sql noprint; create table _StudyPillDays as select epis.PatId, epis.PostDiagGroup, epis.episode, epis.PostDiagEpisode, epis.EpisodeStartDt, epis.EpisodeEndDt, qty.StudyADate, qty.StudyExpireDt, qty.StudyDisp, qty.LookExpireDt from _StudyPostDiagEpisodes as epis, _cleanquantityep as qty where epis.PatId = qty.PatId and epis.Episode = qty.Episode; quit; proc means data=_StudyPillDays nway noprint; var StudyADate StudyExpireDt StudyDisp; class PatId PostDiagGroup episode PostDiagEpisode; id EpisodeStartDt EpisodeEndDt; output out=_StudyPillDays(drop=_:) min(StudyADate)=RankStart max(LookExpireDt)=RankEnd sum(StudyDisp)=StudyDisp; run; proc means data=_PostDiagquantity nway noprint; var PostDiagRankStart PostDiagRankEnd PostDiagDisp; class PatId PostDiagGroup PostDiagEpisode rank; output out=_PostDiagquantity(drop=_:) min(PostDiagRankStart)=PostDiagRankStart max(PostDiagRankEnd)=PostDiagRankEnd sum(PostDiagDisp)=PostDiagDisp; run; proc sql noprint; create table _BothPillDays as select stypill.*, qty.PostDiagRankStart, qty.PostDiagRankEnd, qty.PostDiagDisp from _StudyPillDays as stypill, _PostDiagquantity as qty where stypill.PatId = qty.PatId and stypill.PostDiagEpisode = qty.PostDiagEpisode and stypill.PostDiagGroup = qty.PostDiagGroup and stypill.RankStart <= qty.PostDiagRankEnd and stypill.RankEnd >= qty.PostDiagRankStart; quit; /*calculate overlapping days*/ data _BothPillDays; set _BothPillDays; format BothDayStartDt BothDayEndDt mmddyy10.; BothDayStartDt = max(RankStart,PostDiagRankStart); BothDayEndDt = min(RankEnd,PostDiagRankEnd); PostDiagRankStart = max(EpisodeStartDt,PostDiagRankStart); BothDay=BothDayEndDt-BothDayStartDt + 1; BothUser=1; drop EpisodeStartDt; run; /*calculate PostDiagDaySupp overlapping Query episode*/ proc sql noprint; create table _PostDiagDaySupp as select stypill.PatId, stypill.Episode, stypill.EpisodeStartDt, stypill.EpisodeEndDt, qty.PostDiagGroup, qty.PostDiagDisp, qty.PostDiagRankStart, qty.PostDiagRankEnd from _validepisodes as stypill, _PostDiagquantity as qty where stypill.PatId = qty.PatId and qty.PostDiagRankStart <= stypill.EpisodeEndDt and qty.PostDiagRankEnd >= stypill.EpisodeStartDt; quit; data _PostDiagDaySupp; set _PostDiagDaySupp; PostDiagRankStart = max(PostDiagRankStart,EpisodeStartDt); PostDiagRankEnd = min(PostDiagRankEnd,EpisodeEndDt); PostDiagDaySupp = PostDiagRankEnd - PostDiagRankStart + 1; run; proc means noprint data = _PostDiagDaySupp nway; var PostDiagDaySupp PostDiagDisp; class PatId Episode PostDiagGroup; output out=_PostDiagDaySupp(drop=_:) sum = ; run; /*MODIF WAVE3*/ proc sql noprint; create table _PostDiagAmtSupp as select stypill.PatId, stypill.Episode, (min(stypill.EpisodeEndDt,amt.ExpireDt)-max(stypill.EpisodeStartDt,amt.ADate)+1) * amt.RxAmtDaily as PostDiagAmtSupp, amt.PostDiagGroup from _validepisodes as stypill, _PostDiagAmt as amt where stypill.PatId = amt.PatId and amt.ADate <= stypill.EpisodeEndDt and amt.ExpireDt >= stypill.EpisodeStartDt; quit; proc means data=_PostDiagAmtSupp nway noprint; var PostDiagAmtSupp; class PatId Episode PostDiagGroup; output out=_PostDiagAmtSupp(drop=_:) sum = ; run; /*Get other post-diagnosis summary info*/ proc sql noprint; create table _PostDiagSummary2 as select PatId, Episode, PostDiagGroup, sum(BothDay) as BothDay, min(PostDiagRankStart) as FPostDiagDt format mmddyy10., /*Can be before Fdate*/ max(BothUser) as BothUser from _BothPillDays group by PatId, Episode, PostDiagGroup order by PatId, Episode, PostDiagGroup; quit; data _PostDiagSummary; merge _PostDiagSummary1 _PostDiagSummary2 _PostDiagDaySupp /*MODIF WAVE3*/ _PostDiagAmtSupp; by PatId Episode PostDiagGroup; run; /*Merge _ValidEpisodes with post-diagnosis drugs summary information*/ proc sort data=_PostDiagSummary; by PatId episode PostDiagGroup; run; proc sql noprint; create table _validEpisodesSquared as select epis.*, postgrp.postdiaggroup, postgrp.washper as PostDiagWashper from _ValidEpisodes as epis, _postdiagsettings(where=(group="&ITGROUP.")) as postgrp order by PatID, Episode, PostDiagGroup; quit; data _ValidEpisodes; format Group $30.; merge _validEpisodesSquared(in=a) _PostDiagSummary(in=b); by PatID Episode PostDiagGroup; Group = "&ITGROUP."; EpDur = EpisodeEndDt - EpisodeStartDt + 1; if a & not b then BothUser = 0; if EpisodeStartDt - MinEnrStart < PostDiagWashper then Incident=0; /*MODIF WAVE3*/ PostDiagAmtSupp=Floor(PostDiagAmtSupp); run; %IF &I.=1 %THEN %DO; data DPLocal.&REQUESTID.&RUNID._ValidEpisodes; set _ValidEpisodes; run; %END; %ELSE %DO; proc append base = DPLocal.&REQUESTID.&RUNID._ValidEpisodes data = _ValidEpisodes force; run; %END; %END; /*NQUERYGROUPS loop end*/ %MEND LOOPTHROUGH; %LOOPTHROUGH(); /*---------------------------------------------------------------------------*/ /* 10 -- Process post-diagnosis drugs incidence and pre-existing conditions */ /*---------------------------------------------------------------------------*/ data _MasterNonQueryFile; set _MasterPostDiagFile _MasterCondFile; run; proc sql noprint; create table _incpostdiag_and_conds as /*PART1: Merge by Patid if IGroup=_ALLGROUPS_ and PostDiagGroup=_ALLPOSTDIAGGROUPS*/ select episodes.PatId, episodes.Group, episodes.PostDiagGroup, episodes.episode, episodes.EpisodeStartDt, episodes.EpisodeEndDt, episodes.washtyp as EpWashTyp, claims.Adate, claims.RxSup, claims.cond, claims.washper, claims.washtyp, claims.CondLookTyp, claims.CondFrom, claims.CondTo, claims.inclusion, claims.PostDiag, claims.incpostdiag from _MasterNonQueryFile as claims right join DPLocal.&REQUESTID.&RUNID._ValidEpisodes as episodes on claims.PatId=episodes.Patid where claims.IGroup = "_ALLGROUPS_" and claims.PostDiagGroup = "_ALLPOSTDIAGGROUPS" and claims.cond ne 1 UNION /*PART2: Merge by Patid and PostDiagGroup if IGroup=_ALLGROUPS_ and PostDiagGroup ne _ALLPOSTDIAGGROUPS_*/ select episodes.PatId, episodes.Group, episodes.PostDiagGroup, episodes.episode, episodes.EpisodeStartDt, episodes.EpisodeEndDt, episodes.washtyp as EpWashTyp, claims.Adate, claims.RxSup, claims.cond, claims.washper, claims.washtyp, claims.CondLookTyp, claims.CondFrom, claims.CondTo, claims.inclusion, claims.PostDiag, claims.incpostdiag from _MasterNonQueryFile as claims right join DPLocal.&REQUESTID.&RUNID._ValidEpisodes as episodes on claims.PatId=episodes.Patid and claims.PostDiagGroup=episodes.PostDiagGroup where claims.IGroup = "_ALLGROUPS_" and claims.PostDiagGroup ne "_ALLPOSTDIAGGROUPS_" and claims.cond ne 1 UNION /*PART3: Merge by Patid and Group for pre-existing conditions or if PostDiagGroup=_ALLPOSTDIAGGROUPS_*/ select episodes.PatId, episodes.Group, episodes.PostDiagGroup, episodes.episode, episodes.EpisodeStartDt, episodes.EpisodeEndDt, episodes.washtyp as EpWashTyp, claims.Adate, claims.RxSup, claims.cond, claims.washper, claims.washtyp, claims.CondLookTyp, claims.CondFrom, claims.CondTo, claims.inclusion, claims.PostDiag, claims.incpostdiag from _MasterNonQueryFile as claims right join DPLocal.&REQUESTID.&RUNID._ValidEpisodes as episodes on claims.PatId=episodes.Patid and (claims.Group=episodes.Group or claims.IGroup=episodes.Group) where (claims.IGroup ne "_ALLGROUPS_" and claims.PostDiagGroup = "_ALLPOSTDIAGGROUPS_") or claims.cond = 1 UNION /*PART4: Merge by Patid Group and PostDiagGroup otherwise*/ select episodes.PatId, episodes.Group, episodes.PostDiagGroup, episodes.episode, episodes.EpisodeStartDt, episodes.EpisodeEndDt, episodes.washtyp as EpWashTyp, claims.Adate, claims.RxSup, claims.cond, claims.washper, claims.washtyp, claims.CondLookTyp, claims.CondFrom, claims.CondTo, claims.inclusion, claims.PostDiag, claims.incpostdiag from _MasterNonQueryFile as claims right join DPLocal.&REQUESTID.&RUNID._ValidEpisodes as episodes on claims.PatId=episodes.Patid and (claims.Group=episodes.Group or claims.IGroup=episodes.Group) and claims.PostDiagGroup=episodes.PostDiagGroup where claims.IGroup ne "_ALLGROUPS_" and claims.PostDiagGroup ne "_ALLPOSTDIAGGROUPS_" and claims.cond ne 1; quit; /*Add real washout info to post-diagnosis drugs. INCPOSTDIAGFILE records washout should be the same as those in POSTDIAGFILE for a group*/ proc sql noprint; update _incpostdiag_and_conds as icc set WashPer = ( select settings.WashPer from _PostDiagsettings as settings where icc.Group=settings.Group and icc.PostDiagGroup=settings.PostDiagGroup and icc.Cond ne 1 ) where exists ( select settings.WashPer from _PostDiagsettings as settings where icc.Group=settings.Group and icc.PostDiagGroup=settings.PostDiagGroup and icc.Cond ne 1 ); update _incpostdiag_and_conds as icc set WashTyp = ( select settings.WashTyp from _PostDiagsettings as settings where icc.Group=settings.Group and icc.PostDiagGroup=settings.PostDiagGroup and icc.Cond ne 1 ) where exists ( select settings.WashTyp from _PostDiagsettings as settings where icc.Group=settings.Group and icc.PostDiagGroup=settings.PostDiagGroup and icc.Cond ne 1 ); quit; data _incpostdiag_and_conds; set _incpostdiag_and_conds; /*Determine incidence of post-diagnosis drugs in reference to study drugs index date*/ HadPostDiag = 0; HadIncPostDiag = 0; if (PostDiag or incpostdiag) and (Washper>0) and (ADate < EpisodeStartDt and (EpisodeStartDt-Washper-(WashTyp='MIN')*999999) <= Adate + RxSup - 1) then HadIncPostDiag = 1; if PostDiag and (EpisodeStartDt <= Adate <= EpisodeEndDt or EpisodeStartDt <= Adate + RxSup - 1 <= EpisodeEndDt) then do; HadPostDiag = 1; end; run; /* For each group, determine if pre-existing conditions inclusion and/or exclusion are required*/ data _condfile; set _condfile; InclReqd=0; ExclReqd=0; if inclusion=1 then InclReqD=1; if inclusion=0 then ExclReqD=1; run; proc means data=_condfile nway noprint; var InclReqD ExclReqD; class Group; output out= _condReqD(drop=_:) max=; run; proc sort data=_incpostdiag_and_conds; by Group; run; data _incpostdiag_and_conds; merge _incpostdiag_and_conds _condReqD; by Group; run; /*Identify claims overlapping pre-existing condition period*/ data _incpostdiag_and_conds; set _incpostdiag_and_conds; /*Must not apply pre-existing condition claims occurring too far from index dates*/ if cond=1 and (ADate-CondFromEpisodeStartDt) then cond=0; /*If no inclusion are required for this group, we set the default HadCond=1*/ if InclReqd=1 then HadCond = 0; else HadCond = 1; /*only exclusions are required or no pre-existing conditions at all*/ if cond and Adate ne . then do; if EpisodeStartDt + CondFrom <= ADate <= EpisodeStartDt + CondTo then do; if inclusion=1 then HadCond = 1; else HadCond = 2; end; end; run; /*Verify eligibility for exclusion pre-existing condition*/ proc sort nodupkey data=_condfile out=_CondExcl; by Group CondFrom CondTo; where inclusion=0; run; data _CondExcl; set _CondExcl; by Group; if first.Group then ExclNum=1; else ExclNum = ExclNum + 1; retain ExclNum; run; proc means data=_CondExcl nway noprint; var ExclNum; class Group; output out=_CondExclTot(drop=_:) max=TotExcl; run; data _CondExcl; merge _CondExcl _CondExclTot; by Group; run; proc sql noprint; create table _ExclEligFlags as Select claims.Patid, claims.EpisodeStartDt, edates.Enr_Start, edates.Enr_End, lookup.Group, lookup.CondFrom, lookup.CondTo, lookup.ExclNum, lookup.TotExcl from _CondExcl(keep=Group CondFrom CondTo ExclNum TotExcl) as lookup, DPLocal.&REQUESTID.&RUNID._ValidEpisodes(keep=Group Patid EpisodeStartDt) as claims, _Enrollment(keep=Enr_Start Enr_End PatId) as edates where lookup.Group = claims.Group and claims.PatId = edates.PatId; quit; data _ExclEligFlags; set _ExclEligFlags; Meet=1; /*Enrollment episode completely overlaps this exclusion period*/ if Enr_Start <= EpisodeStartDt + CondFrom and Enr_end >= EpisodeStartDt + CondTo then output; keep Group Patid EpisodeStartDt ExclNum TotExcl Meet; run; proc means data=_ExclEligFlags nway noprint; var Meet; class Group PatId EpisodeStartDt ExclNum; id TotExcl; output out=_ExclEligFlags(drop=_:) max=; run; proc means data=_ExclEligFlags nway noprint; var Meet; class Group PatId EpisodeStartDt; id TotExcl; output out=_ExclEligFlags(drop=_:) sum=NumExclCritmeet; run; /*Members who meet the eligibility criteria for all of the exclusion period*/ data _ExclEligFlags; set _ExclEligFlags; where NumExclCritmeet=TotExcl; keep Group PatId EpisodeStartDt; run; proc sort data = _incpostdiag_and_conds; by Group PatId EpisodeStartDt descending HadCond; run; proc sort nodupkey data = _ExclEligFlags; by Group PatId EpisodeStartDt; run; data _incpostdiag_and_conds; merge _incpostdiag_and_conds(in=a) _ExclEligFlags(in=b); by Group PatId EpisodeStartDt; if a; if ExclReqD=1 then ExclCritmeet=0; else ExclCritmeet=1; if b then ExclCritmeet=1; run; /*Note: At this step (_incpostdiag_and_conds) and for each unique Adate, we know if the patient meets or doesn't meet the INCL/EXCL criteria. Specifically, - HadCond = 2 means that FROM THIS ADATE, the member has one or more claim meeting the EXCLUSION criteria (from this Adate) - HadCond = 1 means that FROM THIS ADATE, the member has one or more claim meeting the INCLUSION criteria, but has no claim meeting the EXCLUSION criteria - HadCond = 0 means that FROM THIS ADATE, the member has no claim for neither INCLUSIONS or EXCLUSIONS */ proc means noprint data = _incpostdiag_and_conds nway missing; var HadPostDiag HadIncPostDiag HadCond ExclCritmeet; class Group PostDiagGroup PatId Episode; id EpWashTyp; output out= _incpostdiag_and_conds(drop=_:) max(HadPostDiag HadIncPostDiag HadCond)= min(ExclCritmeet)= /keeplen; run; proc sort data = DPLocal.&REQUESTID.&RUNID._ValidEpisodes; by Group PostDiagGroup PatId Episode; run; data _premaster1 _premaster2; merge DPLocal.&REQUESTID.&RUNID._ValidEpisodes(in=a) _incpostdiag_and_conds(in=b); by Group PostDiagGroup PatId Episode; if a; HadPostDiag=HadPostDiag>0; HadIncPostDiag=HadIncPostDiag>0; if HadCond = 1 and ExclCritmeet = 1 and ((HadIncPostDiag = 0 and Incident = 1) or Incident = 0); RxYear = Year(EpisodeStartDt); RxMonth = Month(EpisodeStartDt); if EpWashTyp = "SING" and Incident=1 then output _premaster1; else output _premaster2; run; proc sort data = _premaster1;; by Group PostDiagGroup PatId Episode; run; data _premaster1; set _premaster1; by Group PostDiagGroup PatId; if not first.PatId then Incident=0; run; data DPLocal.&REQUESTID.&RUNID._MasterTable; set _premaster1 _premaster2; run; proc datasets library=work nolist nowarn; delete _premaster1 _premaster2 _premaster3; quit; /*---------------------------------------------------------------------------*/ /* 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 call symput('ITGROUP',strip(Group)); run; %PUT &ITGROUP.; data _null_; set _PostDiagsettings(where=(Group in("&ITGROUP."))) end=fin; if fin then call symput('NPOSTDIAGGROUP',_n_); run; %PUT &NPOSTDIAGGROUP.; /*Select this Query class specific settings*/ data _null_; set _querysettings; if Group in("&ITGROUP.") then do; call symput('GROUPWASHOUT',WashPer); call symput('GROUPWASHTYP',strip(WashTyp)); call symput('GROUPMINEPISDUR',Max(MinEpisDur,1)); call symput('GROUPLOOKUPPER',LookupPer); call symput('GROUPENRDAYS',EnrDays); end; run; %PUT &GROUPWASHOUT. &GROUPWASHTYP. &GROUPMINEPISDUR. &GROUPLOOKUPPER. &GROUPENRDAYS.; %LET CONDLOOKTYP=; /*Filter according to pre-existing conditions*/ data _null_; set _condfile; if Group in("&ITGROUP.") then do; call symput('CONDLOOKTYP',strip(CondLookTyp)); end; run; %PUT &CONDLOOKTYP.; %LET INCLREQD=0; /*No INCLUSION for this QUERYGROUP*/ %LET EXCLREQD=0; /*No EXCLUSION for this QUERYGROUP*/ data _LoopCondFile; set _condfile; if Group = "&ITGROUP."; if Inclusion=1 then call symput('INCLREQD',1); if Inclusion=0 then call symput('EXCLREQD',1); run; %PUT &INCLREQD. &EXCLREQD.; %MACRO WRAPPER(); /*If pre-existing conditions are required need to select members with pre-existing conditions*/ %IF %EVAL(&INCLREQD.>0 or &EXCLREQD.>0) %THEN %DO; %IF &INCLREQD. = 1 %THEN %DO; proc sort nodupkey data = _masternonqueryfile out = _cond(keep=PatId ADate CondFrom CondTo); by PatId ADate CondFrom CondTo; where cond and Group in("&ITGROUP.") and Inclusion = 1; run; /*Must re-define enrollment episodes to scan only when members meeting the pre-existing condition requirement*/ proc sql noprint; Create table _denomint2 as Select enrol.*, claims.ADate, claims.CondFrom, claims.CondTo, max(claims.Adate - claims.CondTo,enrol.Enr_Start) as PDate format mmddyy10., min(claims.Adate - claims.CondFrom,enrol.Enr_End) as EDate format mmddyy10. from _Denomint as enrol, _cond as claims where enrol.PatId = claims.PatId and enrol.Enr_Start <= claims.Adate - claims.CondFrom and enrol.Enr_End >= claims.Adate - claims.CondTo order PatId, EligEPisode, PDate; quit; /*for a same eligible record, identify overlapping PDate - EDate intervals*/ data _MDenomint; set _denomint2; by PatId EligEpisode; format lEDate mmddyy10.; lEDate = lag(EDate); diff=PDate-lEDate-1; if first.EligEpisode then do; lPDate=.; diff=.; PEpisode=1; end; else do; if diff > 0 then do; PEpisode = PEpisode + 1; end; end; retain Pepisode; run; proc means data=_MDenomint nway noprint; var PDate EDate; class Patid EligEpisode PEpisode; id Enr_Start Enr_end Birth_Date MinAgeDate MaxAgeDate sex; output out=_MDenomint(drop=_:) min(PDate)= max(EDate)=; run; %END; %ELSE %DO; data _MDenomInt; set _DenomInt(where=(enr_end >= &QUERYFROM. and enr_start <= &QUERYTO.)); format PDate EDate mmddyy10.; PDate = max(Enr_Start,&QUERYFROM.); EDate = min(Enr_End,&QUERYTO.); PEpisode = EligEpisode; keep PatId PDate EDate Enr_Start Enr_end Birth_Date MinAgeDate MaxAgeDate sex EligEpisode PEpisode; run; %END; %IF &EXCLREQD. = 1 %THEN %DO; proc sort nodupkey data=_LoopCondFile; by CondFrom CondTo; where inclusion=0; run; data _LoopCondFile; set _LoopCondFile end=fin; where inclusion=0; ExclNum=_N_; if fin then call symput("NumExcl",ExclNum); run; %PUT &NumExcl.; proc sql noprint; create table _MDenomint_ as Select pdates.*, lookup.CondFrom, lookup.CondTo, lookup.ExclNum from _LoopCondFile(keep=CondFrom CondTo ExclNum) as lookup, _MDenomint as pdates order by PatId, EligEpisode, PEpisode, ExclNum; quit; data _MDenomint; set _MDenomint_; format MaxPDate MinEDate mmddyy10.; if CondFrom < 0 then MaxPdate = Enr_Start - CondFrom; else MaxPDate = PDate; if CondTo > 0 then MinEDate = Enr_End - CondTo; else MinEDate = EDate; /*check if above remain consistent*/ PDate = max(PDate,MaxPdate); EDate = min(EDate,MinEDate); run; proc means data=_MDenomint nway noprint; var PDate EDate; class Patid EligEpisode PEpisode; id Enr_Start Enr_end Birth_Date MinAgeDate MaxAgeDate sex; output out=_MDenomint(drop=_: where=(PDate<=EDate)) min(PDate)= max(EDate)=; run; /*go get exclusion claims*/ proc sort nodupkey data = _masternonqueryfile out = _cond(keep=PatId ADate CondFrom CondTo); by PatId ADate CondFrom CondTo; where cond and Group in("&ITGROUP.") and Inclusion = 0; run; data _cond; set _cond; format ExcPdate ExcEdate mmddyy10.; ExcPdate = Adate - CondTo; ExcEdate = Adate - CondFrom; run; proc sort data = _cond; by PatId ExcPdate; run; data _cond; set _cond; by Patid; lExcEdate = lag(ExcEdate); diff = ExcPDate - lExcEdate - 1; if first.PatId then do; lExcEdate = .; diff = .; ExcEpisode = 1; end; else do; if diff > 0 then ExcEpisode = ExcEpisode + 1; end; retain ExcEpisode; run; proc means noprint data = _cond nway; var ExcPdate ExcEdate; class PatId ExcEpisode; output out=_cond(drop=_: ExcEpisode) min(ExcPdate)= max(ExcEdate)=; run; /*left join here with _MDenomint (watch out for multiple merge)*/ proc sql noprint; create table _MDenomint_ as Select pdates.*, exdates.ExcPdate, exdates.ExcEdate from _MDenomint as pdates left join _cond as exdates on pdates.PatId = exdates.PatId; quit; data _MDenomint(where=(PDate<=EDate)); set _MDenomint_; if (ExcPDate <= PDate and PDate <= ExcEDate <= EDate) then do; PDate = ExcEDate + 1; EDate = EDate; output; end; else if ExcPDate > PDate and ExcEDate < EDate then do; PDate = PDate; EDate = ExcPDate - 1; output; PDate = ExcEDate + 1; EDate = EDate; output; end; else if ExcPDate <= EDate and ExcEDate >= EDate then do; PDate = PDate; EDate = ExcPDate-1; output; end; else output; run; proc sort data = _MDenomint; by PatId PDate; run; data _MDenomint; set _MDenomint; by PatId; lEDate = lag(EDate); diff = PDate - lEDate- 1; if first.PatID then do; lEDate = .; diff = .; PEpisode = 1; end; else do; if diff > 0 then PEpisode = PEpisode + 1; end; retain PEpisode; run; proc means noprint data = _MDenomint nway; var PDate EDate; class PatId EligEpisode PEpisode; id Enr_Start Enr_end Birth_Date MinAgeDate MaxAgeDate sex; output out=_MDenomint(drop=_:) min(PDate)= max(EDate)=; run; %END; data _MDenomInt; set _MDenomInt(where=(enr_end >= &QUERYFROM. and enr_start <= &QUERYTO.)); Enr_End = min(Enr_End,EDate); 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; %END; /*end for either inclusion or exclusion*/ %ELSE %DO; data _MDenomInt; set _DenomInt(where=(enr_end >= &QUERYFROM. and enr_start <= &QUERYTO.)); format PDate mmddyy10.; PDate = .; PEpisode=1; run; %END; %MEND WRAPPER; %WRAPPER(); /*Loop through post-diagnosis groups inside the study group*/ %DO j = 1 %TO &NPOSTDIAGGROUP.; %LET POSTDIAGWASHOUT = 0; %LET POSTDIAGWASHTYP = MULT; data _null_; set _PostDiagsettings (where=(Group in("&ITGROUP."))); if _n_ = &j. then do; call symput('ITPOSTDIAGGROUP',strip(PostDiagGroup)); call symput('POSTDIAGWASHOUT',WashPer); call symput('POSTDIAGWASHTYP',strip(WashTyp)); end; run; %PUT &ITPOSTDIAGGROUP. &POSTDIAGWASHOUT. &POSTDIAGWASHTYP.; /*Creating Claims Dates for post-diagnosis and query drugs*/ proc sort nodupkey data= _masternonqueryfile out= _PostDiagClaims(keep=Group PatId ADate Expiredt); by PatId ADate Expiredt; where (PostDiag or IncPostDiag) and (Group in("&ITGROUP.") or IGroup in("&ITGROUP.","_ALLGROUPS_")) and PostDiaggroup in("&ITPOSTDIAGGROUP."); run; proc sort data = _PostDiagClaims; by PatId ADate Expiredt; run; proc sort data= _masterqueryfile out= _QueryClaims(keep=Group PatId ADate Expiredt Enr_End); by PatId ADate Expiredt; where Group in("&ITGROUP.") or IGroup in("&ITGROUP.","_ALLGROUPS_"); run; data _QueryClaims; set _QueryClaims; by Patid; format LookExpireDt mmddyy10.; if first.patid then do; LookExpireDt=ADate + &GROUPLOOKUPPER. - 1; LookExpireDt=min(Enr_end,LookExpireDt); EpisExpireDt = LookExpireDt; end; else do; if ADate <= EpisExpireDt then do; LookExpireDt = EpisExpireDt; end; else do; LookExpireDt = ADate + &GROUPLOOKUPPER. - 1; LookExpireDt=min(Enr_end,LookExpireDt); end; end; retain EpisExpireDt; drop EpisExpireDt; run; /*Flag if query or post-diagnosis claim since washout regime can be different*/ data _Claims3; set _PostDiagClaims(in=a) _QueryClaims(in=b); by PatId ADate ExpireDt; QueryClaim = 0; PostDiagClaim = 0; if a then PostDiagClaim = 1; if b then QueryClaim = 1; run; proc sql noprint; create table _Claims as select PatId, ADate, Group, ExpireDt, LookExpireDt, max(QueryClaim) as QueryClaim, max(PostDiagClaim) as PostDiagClaim from _Claims3 group by PatId, ExpireDt; quit; /*List for pre-cleaning*/ proc sort nodupkey data=_Claims out=_ClaimList(keep=patid); by PatId; run; /*Creating Dates (query, enroll, pre-existing, etc.)*/ data _AllMember; Merge _MDenomInt(in=a) _ClaimList(in=b); by PatId; if a; withclaims = 0; if a and b then withclaims=1; format QueryStartDate mmddyy10.; QueryStartDate=Max(&QUERYFROM.,MinAgeDate,enr_start,PDate); /*Minimum date the patient can have an HOI*/ Enr_Start=Max(QueryStartDate-max(&GROUPWASHOUT.,&POSTDIAGWASHOUT.,&GROUPENRDAYS.),enr_start); enr_end=min(&QUERYTO.,enr_end,MaxAgeDate); if enr_end >= enr_start; keep PatId EligEpisode PEpisode Birth_Date Sex MinAgeDate MaxAgeDate Enr_Start Enr_End LastAgeGroup FirstAgeGroup PDate withclaims QueryStartDate; run; /*Remove claims outside of period BEFORE AND AFTER enrollment*/ proc sql noprint; Create Table _Claims2 as Select Enrol.*, claims.ADate, claims.Expiredt, claims.LookExpireDt, claims.Group, claims.QueryClaim, claims.PostDiagClaim 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, EligEpisode, Pepisode, Adate; quit; /*To treat overlapping claims and create effective washout*/ data _claims2 _claimmark(keep=PatId EligEpisode PEpisode QueryClaim PostDiagClaim ClmNum lEffWashDt rename=(lEffWashDt=EffWashDt)); set _claims2; by PatId EligEpisode PEpisode; format EffWashDt lEffWashDt mmddyy10.; EffWashDt = ExpireDt + max(&GROUPWASHOUT.*QueryClaim,&POSTDIAGWASHOUT.*PostDiagClaim); EffWashDt = max(EffWashDt,LookExpireDt); /* LookExpireDt is missing for postdiag so it will be OK*/ if first.PEpisode then do; ClmEpisode = 1; ClmNum = 0; lExpireDt = .; lEffWashDt = .; end; if lExpireDt ne . then do; diff = ADate - lExpiredt - 1; ClmEpisode = ClmEpisode + 1; end; ClmNum = ClmNum + 1; lexpiredt = max(expiredt,lexpiredt); lEffWashDt = max(EffWashDt,lEffWashDt); retain lexpiredt ClmEpisode ClmNum lEffWashDt; run; proc means noprint data = _claims2 nway; var ADate Expiredt ClmNum; class PatID EligEpisode PEpisode ClmEpisode; id Birth_Date MaxAgeDate MinAgeDate Sex LastAgeGroup FirstAgeGroup QueryStartDate Group Enr_Start; output out=_claims2(drop=_:) min(ADate)= max(ExpireDt LookExpireDt)= max(ClmNum)= /keeplen; run; proc sql noprint; Create Table _Claims3 as Select clmep.PatId, clmep.EligEpisode, clmep.PEpisode, clmep.ADate, clmep.ExpireDt, clmep.LookExpireDt, clmep.Sex, clmep.Birth_date, clmep.MinAgeDate, clmep.MaxAgeDate, clmep.QueryStartDate, clmep.FirstAgeGroup, clmep.LastAgeGroup, clmep.Group, clmep.Enr_Start, clmdu.EffWashDt, clmdu.PostDiagClaim, clmdu.QueryClaim From _claims2 as clmep, _claimmark as clmdu Where clmep.PatId = clmdu.PatId and clmep.EligEpisode = clmdu.EligEpisode and clmep.PEpisode = clmdu.PEpisode and clmep.ClmNum = clmdu.ClmNum order by PatId,EligEpisode,PEpisode,ADate; 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 EligEpisode Pepisode Enr_Start Sex birth_date MinAgeDate MaxAgeDate QueryStartDate FirstAgeGroup LastAgeGroup rename=Enr_Start=Adate) _Claims3(keep=Patid EligEpisode Pepisode ADate Expiredt LookExpiredt Sex birth_date MinAgeDate MaxAgeDate QueryStartDate FirstAgeGroup LastAgeGroup Group EffWashDt PostDiagClaim QueryClaim Enr_Start) _AllMember(in=b keep=Patid EligEpisode Pepisode Enr_End Sex birth_date MinAgeDate MaxAgeDate QueryStartDate FirstAgeGroup LastAgeGroup Enr_Start rename=Enr_End=Adate); by Patid EligEpisode Pepisode ADate; if a then do; Group="BEGINELIG"; Expiredt=Adate; PostDiagClaim = 1; QueryClaim = 1; EffWashDt = ExpireDt + max(&GROUPWASHOUT.*QueryClaim,&POSTDIAGWASHOUT.*PostDiagClaim); Enr_Start=ADate; end; if b then do; Group="ENDELIG"; Expiredt=Adate; PostDiagClaim = 0; QueryClaim = 0; EffWashDt = ExpireDt + max(&GROUPWASHOUT.*QueryClaim,&POSTDIAGWASHOUT.*PostDiagClaim); end; IsClaim=0; if not a and not b then IsClaim=1; run; data _DenomToLoop(rename=(lEffWashDt=EffWashDt)); set _DenomToLoop; by PatId EligEpisode PEpisode; format lEffWashDt mmddyy10.; if first.PEpisode then do; lEffWashDt = .; end; lEffWashDt = max(EffWashDt,lEffWashDt); retain lEffWashDt; drop EffWashDt; run; /*Creating HOI Free episodes from gapless Enrollment sequences*/ data _FindEpisodes(rename=(lPostDiagClaim=PostDiagClaim lQueryClaim=QueryClaim lEffWash=EffWash)); set _DenomToLoop; by PatId EligEpisode PEpisode; EffWash = EffWashDt - ExpireDt - 1*IsClaim; lGroup = lag(Group); lExpireDt = lag(ExpireDt); lEffWash = lag(EffWash); lPostDiagClaim = lag(PostDiagClaim); lQueryClaim = lag(QueryClaim); if first.Pepisode then do; lExpireDt = .; lGroup = ""; lEffWash = .; lPostDiagClaim = .; lQueryClaim =.; end; if lExpiredt ne . then diff=ADate-lExpireDt /*- 1*/; format lExpireDt StartDate EndDate mmddyy10.; StartDate = lExpireDt + 1 - (lGroup eq "BEGINELIG"); EndDate = ADate; QueryStartDate=Max(QueryStartDate,StartDate); if MinAgeDate <= EndDate and MaxAgeDate >= StartDate; if diff >= 0 and QueryStartDate <= EndDate; if EndDate >= &QUERYFROM. and StartDate <= &QUERYTO.; FirstPer=intck("Months",&QUERYFROM.,QueryStartDate)+1; LastPer=intck("Months",&QUERYFROM.,EndDate)+1; keep PatId Birth_Date sex StartDate QueryStartDate EndDate Enr_Start first: last: lPostDiagClaim lQueryClaim lEffWash; run; /*---------------------------------------------------------------------------*/ /* 11.1 -- Get index dates */ /*---------------------------------------------------------------------------*/ /* Six possibilities 1 - Mult washout for post-diagnosis and study drug, no need to worry about index dates or post-diagnosis dates (output index date table only for code execution) 2 - Mult washout for post-diagnosis and Min washout for drug, can only have one drug episode, need to retrieve index date and see if any claim before the index date 3 - Min washout for post-diagnosis and mult washout for drug, can have more than one drug episode until post-diagnosis, get first post-diagnosis or inc post-diagnosis date in period and see if we have also before study 4 - Min washout for post-diagnosis and min washout for drug, can have only one drug episode 5 - Min washout for post-diagnosis and sing washout for drug, eligible to have an episode until post-diagnosis claim, single episode 6 - Mult washout for post-diagnosis and sing washout for drug, single episode */ %MACRO WRAPPER(); %IF %STR("&POSTDIAGWASHTYP.")=%STR("MULT") and %STR("&GROUPWASHTYP.")=%STR("MULT") %THEN %DO; data _TermDtTable; set Dplocal.&REQUESTID.&RUNID._MasterTable(where=(Group in("&ITGROUP."))); format TermDt mmddyy10.; TermDt=EpisodeStartDt; keep PatId TermDt; run; /*keeping TermDt, only useful for minimum washout cases when each PatId will have a single index date*/ proc sort nodupkey data = _TermDtTable; by PatId; run; %END; %ELSE %IF %STR("&POSTDIAGWASHTYP.")=%STR("MULT") and %STR("&GROUPWASHTYP.")=%STR("MIN") %THEN %DO; proc means noprint data = _QueryClaims nway; var ADate; Class PatId; output out=_TermDtTable(drop=_:) min=TermDt /keeplen; run; %END; %ELSE %IF %STR("&POSTDIAGWASHTYP.")=%STR("MIN") and %STR("&GROUPWASHTYP.")=%STR("MULT") %THEN %DO; proc means noprint data = _PostDiagClaims nway; var ADate; Class PatId; output out=_TermDtTable(drop=_:) min=TermDt /keeplen; run; %END; %ELSE %IF %STR("&POSTDIAGWASHTYP.")=%STR("MIN") and %STR("&GROUPWASHTYP.")=%STR("MIN") %THEN %DO; proc means noprint data = _QueryClaims nway; var ADate; Class PatId; output out=_QueryTermDtTable(drop=_:) min=QTermDt /keeplen; run; proc means noprint data = _PostDiagClaims nway; var ADate; Class PatId; output out=_PostDiagTermDtTable(drop=_:) min=CTermDt /keeplen; run; data _TermDtTable; merge _QueryTermDtTable _PostDiagTermDtTable; by PatId; format TermDt mmddyy10.; TermDt = min(QTermDt,CTermDt); keep PatId TermDt; run; %END; %ELSE %IF %STR("&POSTDIAGWASHTYP.")=%STR("MIN") and %STR("&GROUPWASHTYP.")=%STR("SING") %THEN %DO; data _QueryTermDtTable; set Dplocal.&REQUESTID.&RUNID._MasterTable(where=(Group in("&ITGROUP.") and incident=1)); format QTermDt mmddyy10.; QTermDt=EpisodeStartDt; keep PatId QTermDt; run; proc sort data = _QueryTermDtTable; by PatId; run; proc means noprint data = _PostDiagClaims nway; var ADate; Class PatId; output out=_PostDiagTermDtTable(drop=_:) min=CTermDt; run; data _TermDtTable; merge _QueryTermDtTable _PostDiagTermDtTable; by PatId; format TermDt mmddyy10.; TermDt = min(QTermDt,CTermDt); keep PatId TermDt; run; %END; %ELSE %IF %STR("&POSTDIAGWASHTYP.")=%STR("MULT") and %STR("&GROUPWASHTYP.")=%STR("SING") %THEN %DO; data _TermDtTable; set Dplocal.&REQUESTID.&RUNID._MasterTable(where=(Group in("&ITGROUP.") and incident=1)); format TermDt mmddyy10.; TermDt=EpisodeStartDt; keep PatId TermDt; run; proc sort data = _TermDtTable; by PatId; run; %END; %MEND WRAPPER; %WRAPPER(); data _Patient_days_M _Patient_days_F _Patient_days_U; merge _FindEpisodes(in=a) _TermDtTable; by Patid; if a; 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; run; /*---------------------------------------------------------------------------*/ /* 11.2 -- 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; /*Can put 0 for minimum episode duration, must floor at 1 since 0 days episode duration is not possible*/ DaysInExcess = EndDate - Max(StartDate+EffWash,QueryStartDate) + 1; 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= &GROUPMINEPISDUR. and (NumWashOutDays-EffWash) >= 0 and NumEnrDays >= &GROUPENRDAYS. then do; if %STR("&POSTDIAGWASHTYP.")=%STR("MULT") and %STR("&GROUPWASHTYP.")=%STR("MULT") then do; _IncAg(i)=1; IncDaysag(i)=sum(NumDays,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(NumDays,IncDaysAg(i)); 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*/ _PrevY(j)=1; NumDays= min(mdy(12,31,year),EndDate)-max(QueryStartDate,mdy(1,1,year))+1; /*number of query days overlapping this year*/ PrevDaysY(j) = sum(NumDays,PrevDaysY(j)); /*Incidence*/ NumWashOutDays=min(mdy(12,31,year),EndDate)-StartDate; NumEnrDays=min(mdy(12,31,year),EndDate)-Enr_Start; /*number of query days overlapping this year*/ NumDays=min(min(mdy(12,31,year),EndDate)-max(QueryStartDate,mdy(1,1,year))+1, NumWashOutDays-EffWash+1,NumEnrDays-&GROUPENRDAYS.+1); if DaysInExcess >= &GROUPMINEPISDUR. and (NumWashOutDays-EffWash) >= 0 and NumEnrDays >= &GROUPENRDAYS. then do; if %STR("&POSTDIAGWASHTYP.")=%STR("MULT") and %STR("&GROUPWASHTYP.")=%STR("MULT") then do; _IncY(j)=1; IncDaysY(j)=sum(NumDays,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(NumDays,IncDaysY(j)); 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= &GROUPMINEPISDUR. and (NumWashOutDays-EffWash) >= 0 and NumEnrDays >= &GROUPENRDAYS. then do; if %STR("&POSTDIAGWASHTYP.")=%STR("MULT") and %STR("&GROUPWASHTYP.")=%STR("MULT") then do; _IncPer(i)=1; IncDaysPer(i)=sum(NumDays,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(NumDays,IncDaysPer(i)); end; end; end; end; if last.patid then do; /*With this stage, all Y are filled and accumulated*/ PrevALL=sum(PrevALL,max(of _PrevY:,.)); IncALL=sum(IncALL,max(of _IncY:,.)); /*only one presence is enough*/ 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:,.); 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. PostDiagGroup $30. Segment $16. count best12.; set _MasterDenomTable; Group = "&ITGROUP."; PostDiagGroup = "&ITPOSTDIAGGROUP."; label Segment='Segment'; if missing(count) then count=0; run; %IF &I.=1 and &J.=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; /*loop through NPOSTDIAGGROUPS*/ %END; /*loop through NQUERYGROUPS*/ %MEND DENOMLOOP; %DENOMLOOP(); proc datasets library=work nolist nowarn; delete _allmember _claimmark claims2 claims3 _denomint _denomtoloop _findepisodes _masterqueryfile _Mdenomint _patient_days: _termdttable; quit; proc sort nodupkey data=DPLocal.&REQUESTID.&RUNID._MasterDenomTable; by _ALL_; run; /*---------------------------------------------------------------------------*/ /* 12 -- Post process denominator information */ /*---------------------------------------------------------------------------*/ /***********************************/ /* START DENOMLOOP POST PROCESSING */ /* MODULAR PROGRAM SPECIFIC */ /***********************************/ 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 $13.; 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 PostDiaggroup count sex Year month AgeGroup time; run; proc sort data=_denom; by Group PostDiaggroup 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 PostDiaggroup segment sex; DPID="&DPID."; SITEID="&SITEID."; drop inc; run; proc sql noprint; create table DenTable0 as Select DPId, SITEID, Group, PostDiaggroup, Sex, AgeGroup, Year, Month, IncDenCount, IncDaysCount, PrevDenCount, PrevDaysCount from _denom; quit; proc sort nodupkey data = DenTable0; by _All_; run; /*---------------------------------------------------------------------------*/ /* 13 -- Build final tables */ /*---------------------------------------------------------------------------*/ proc sort nodupkey data = _Enrollment(keep=PatId Birth_Date Sex); by PatId; run; data DPLocal.&REQUESTID.&RUNID._PostDiagQueryGroup(keep=Group PostDiaggroup PatID Sex AgeGroup RxYear RxMonth NumDispensing EpisodeRxSup EpDur PostDiagDay BothDay PostDiagDisp PostDiagDaySupp /*MODIF WAVE3*/ PostDiagAmtSupp BothUser FPostDiagDt tte PostDiagIntensity Incident NewStart episode); if 0 then set _Enrollment; declare hash ht (hashexp:16, dataset:"_Enrollment"); ht.definekey('PatId'); ht.definedata(ALL: 'YES'); ht.definedone(); do until(eof1); set DPLocal.&REQUESTID.&RUNID._MasterTable end=eof1; format AgeGroup $13.; if ht.find()=0 then do; NumDispensing = StudyDisp; if Incident = 1 then NewStart = 1; else NewStart = 0; tte=FPostDiagDt-EpisodeStartDt; PostDiagIntensity=.; if (EpisodeRxSup > 0 and BothDay >=0) then PostDiagIntensity=BothDay/EpisodeRxSup; do i=&NUMAGECAT. to 1 by -1; if EpisodeStartDt >= intnx(scan("&AGETYP.",i),birth_date,scan("&AGETHRESH.",i),'sameday') then do; AgeGroup = scan("&AGESTRAT.",i,' '); leave; end; end; 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 MinAgeDate <= EpisodeStartDt <= MaxAgeDate then output DPLocal.&REQUESTID.&RUNID._PostDiagQueryGroup; end; end; stop; run; /*Squaring: list of all possibilities for uniform table output*/ proc sql; create table _Square as select Group, PostDiaggroup from _PostDiagsettings; quit; data _Square; set _Square; Year=intck('year',&QUERYFROM.,&QUERYTO.)+1; do i=1 to Year; RxYear = year(INTNX('year', &QUERYFROM., i-1, 'beginning')); do RxMonth=1 to 12; do j=1 to &NUMAGECAT.; do incident=0 to 1; format AgeGroup $13.; AgeGroup = scan("&AGESTRAT.",j,' '); Sex='F';NumDispensing=0;EpisodeRxSup=0;PostDiagDisp=0;PostDiagDay=0;PostDiagDaySupp=0;/*MODIF WAVE3*/PostDiagAmtSupp=0; EpDur=0;BothDay=0;BothUser=0;tte=.;PostDiagIntensity=.;NewStart=0;episode=1; output; Sex='M';output; Sex='U';output; end; end; end; end; keep Group PostDiaggroup Sex AgeGroup RxYear RxMonth NumDispensing EpisodeRxSup PostDiagDisp PostDiagDay PostDiagDaySupp /*MODIF WAVE3*/ PostDiagAmtSupp BothDay BothUser tte PostDiagIntensity Incident NewStart EpDur; run; data DPLocal.&REQUESTID.&RUNID._PostDiagQueryGroup; set DPLocal.&REQUESTID.&RUNID._PostDiagQueryGroup _Square; run; /*---------------------------------------------------------------------------*/ /* 13.1 -- Create query drug usage file */ /*---------------------------------------------------------------------------*/ /*nodupkey because not unique number of records due to patient with multiple post-diagnosis PostDiaggroup*/ proc sort nodupkey data=DPLocal.&REQUESTID.&RUNID._PostDiagQueryGroup (where=(strip(PatID) ne "")) out=DPLocal.&REQUESTID.&RUNID._QueryGroup(keep=Group PostDiagGroup Patid Sex AgeGroup RxYear RxMonth NewStart NumDispensing EpDur EpisodeRxSup Incident Episode); by Patid Group PostDiagGroup Episode; run; /*Incident Data*/ proc means data=DPLocal.&REQUESTID.&RUNID._QueryGroup missing noprint; var NewStart EpDur NumDispensing EpisodeRxSup; class Patid Group PostDiagGroup Sex AgeGroup RxYear RxMonth; where incident = 1; id Incident; output out=INumQueryTable0(drop=_freq_ where=(PatId ne "") rename=_type_=seg) sum(NewStart)=NewStarts sum(EpDur)=TotalLookDur sum(NumDispensing)=claims; run; proc means data=INumQueryTable0 noprint nway missing; var NewStarts TotalLookDur claims; class Group PostDiagGroup Sex AgeGroup RxYear RxMonth seg; id Incident; output out=INumQueryTable0(drop=_type_ rename=_freq_=UniquePatients where=(Group ne "")) sum =; run; /*Prevalent Data*/ proc means data=DPLocal.&REQUESTID.&RUNID._QueryGroup missing noprint; var NewStart EpDur NumDispensing EpisodeRxSup; class Patid Group PostDiagGroup Sex AgeGroup RxYear RxMonth; output out=PNumQueryTable0(drop=_freq_ where=(PatId ne "") rename=_type_=seg) N(NewStart)=NewStarts sum(EpDur)=TotalLookDur sum(NumDispensing)=claims; run; proc means data=PNumQueryTable0 noprint nway missing; var NewStarts TotalLookDur claims; class Group PostDiagGroup Sex AgeGroup RxYear RxMonth seg; output out=PNumQueryTable0(drop=_type_ rename=_freq_=UniquePatients where=(Group ne "")) sum =; run; data Numquerytable0; set INumQueryTable0 PNumQueryTable0; run; proc datasets library=work nolist nowarn; delete INumQueryTable0 PNumQueryTable0; quit; /*---------------------------------------------------------------------------*/ /* 13.2 -- Create post-diagnosis drug usage file */ /*---------------------------------------------------------------------------*/ data DPLocal.&REQUESTID.&RUNID._PostDiagQueryGroup; set DPLocal.&REQUESTID.&RUNID._PostDiagQueryGroup(where=(PostDiaggroup ne '')); run; /*Incident Data*/ proc means data=DPLocal.&REQUESTID.&RUNID._PostDiagQueryGroup missing noprint; var PostDiagDisp PostDiagDay PostDiagDaySupp /*MODIF WAVE3*/ PostDiagAmtSupp BothDay BothUser; class Patid Group PostDiaggroup Sex AgeGroup RxYear RxMonth; where incident = 1; id incident; output out=_INumCTab0(drop=_freq_ where=(PatId ne "") rename=_type_=seg) sum(PostDiagDisp)=PostDiagDispensings sum(PostDiagDay)=PostDiagTotalEpiDur sum(PostDiagDaySupp)=PostDiagTotalDaysSupp sum(PostDiagAmtSupp)=PostDiagTotalAmtSupp sum(BothDay)=TreatmentOverlap max(BothUser)=UniqueOverlapUsers; run; *Add PostDiag Cats; proc sql noprint; create table _INumCTabCat0 as select INum.*, Cats.Cat, 1 as UniqueCatPatients from _INumCTab0 as INum left join _PostDiagCats as Cats on INum.Group = Cats.Group and INum.PostDiagGroup = Cats.PostDiagGroup and INum.PostDiagDispensings >= Cats.LowerBd and INum.PostDiagDispensings <= Cats.UpperBd; quit; proc means data=_INumCTab0 noprint nway missing; var PostDiagDispensings PostDiagTotalEpiDur PostDiagTotalDaysSupp /*MODIF WAVE3*/ PostDiagTotalAmtSupp TreatmentOverlap UniqueOverlapUsers; class Group PostDiaggroup Sex AgeGroup RxYear RxMonth seg; id incident; output out=_INumCTab0(drop=_: where=(Group ne "")) sum =; run; proc means data=_INumCTabCat0 noprint nway missing; var UniqueCatPatients; class Group PostDiaggroup Sex AgeGroup RxYear RxMonth Cat seg; id incident; output out=_INumCTabCat0(drop=_: where=(Group ne "")) sum =; run; /*Prevalent Data*/ proc means data=DPLocal.&REQUESTID.&RUNID._PostDiagQueryGroup missing noprint; var PostDiagDisp PostDiagDay PostDiagDaySupp /*MODIF WAVE3*/ BothDay BothUser; class Patid Group PostDiaggroup Sex AgeGroup RxYear RxMonth; output out=_PNumCTab0(drop=_freq_ where=(PatId ne "") rename=_type_=seg) sum(PostDiagDisp)=PostDiagDispensings sum(PostDiagDay)=PostDiagTotalEpiDur sum(PostDiagDaySupp)=PostDiagTotalDaysSupp sum(PostDiagAmtSupp)=PostDiagTotalAmtSupp sum(BothDay)=TreatmentOverlap max(BothUser)=UniqueOverlapUsers; run; *Add PostDiag Cats; proc sql noprint; create table _PNumCTabCat0 as select PNum.*, Cats.Cat, 1 as UniqueCatPatients from _PNumCTab0 as PNum left join _PostDiagCats as Cats on PNum.Group = Cats.Group and PNum.PostDiagGroup = Cats.PostDiagGroup and PNum.PostDiagDispensings >= Cats.LowerBd and PNum.PostDiagDispensings <= Cats.UpperBd; quit; proc means data=_PNumCTab0 noprint nway missing; var PostDiagDispensings PostDiagTotalEpiDur PostDiagTotalDaysSupp /*MODIF WAVE3*/ PostDiagTotalAmtSupp TreatmentOverlap UniqueOverlapUsers; class Group PostDiaggroup Sex AgeGroup RxYear RxMonth seg; output out=_PNumCTab0(drop=_: where=(Group ne "")) sum =; run; proc means data=_PNumCTabCat0 noprint nway missing; var UniqueCatPatients; class Group PostDiaggroup Sex AgeGroup RxYear RxMonth Cat seg; output out=_PNumCTabCat0(drop=_: where=(Group ne "")) sum =; run; data _NumCTab0; set _INumCTab0 _PNumCTab0; PostDiagUniquePatients=UniqueOverlapUsers; run; data _NumCTabCat0; set _INumCTabCat0 _PNumCTabCat0; run; proc datasets library=work nolist nowarn; delete _INumCTab0 _PNumCTab0 _INumCTabCat0 _PNumCTabCat0; quit; /*---------------------------------------------------------------------------*/ /* 13.3 -- Output Incident and Prevalent tables to DPLOCAL */ /*---------------------------------------------------------------------------*/ proc means data=DPLocal.&REQUESTID.&RUNID._PostDiagQueryGroup noprint missing; var tte PostDiagIntensity; class Group PostDiaggroup Sex AgeGroup RxYear RxMonth; where incident = 1; id incident; output out=_INumCTab1(drop=_: where=(Group ne "")) Mean(tte PostDiagIntensity)=Meantte MeanPostDiagIntensity Std(tte PostDiagIntensity)=SDtte SDPostDiagIntensity Min(tte PostDiagIntensity)=Mintte MinPostDiagIntensity Median(tte PostDiagIntensity)=Mediantte MedianPostDiagIntensity Max(tte PostDiagIntensity)=Maxtte MaxPostDiagIntensity; run; proc means data=DPLocal.&REQUESTID.&RUNID._PostDiagQueryGroup noprint missing; var tte PostDiagIntensity; class Group PostDiaggroup Sex AgeGroup RxYear RxMonth; output out=_PNumCTab1(drop=_: where=(Group ne "")) Mean(tte PostDiagIntensity)=Meantte MeanPostDiagIntensity Std(tte PostDiagIntensity)=SDtte SDPostDiagIntensity Min(tte PostDiagIntensity)=Mintte MinPostDiagIntensity Median(tte PostDiagIntensity)=Mediantte MedianPostDiagIntensity Max(tte PostDiagIntensity)=Maxtte MaxPostDiagIntensity; run; data _NumCTab1; set _INumCTab1 _PNumCTab1; run; proc sort data=_NumCTab1; by Group PostDiaggroup Sex AgeGroup RxYear RxMonth incident; run; proc sort data=_NumCTab0; by Group PostDiaggroup Sex AgeGroup RxYear RxMonth incident; run; data NumPostDiagTable0; merge _NumCTab0 _NumCTab1; by Group PostDiaggroup Sex AgeGroup RxYear RxMonth incident; run; data NumPostDiagTableCat0; set _NumCTabCat0; run; proc datasets library=work nolist nowarn; delete _INumCTab1 _PNumCTab1 _NumCTab0 _NumCTab1 _NumCTabCat0; quit; proc sort data=NumPostDiagTableCat0; by Group PostDiaggroup Sex AgeGroup RxYear RxMonth incident; run; proc sort data=NumPostDiagTable0; by Group PostDiaggroup Sex AgeGroup RxYear RxMonth incident; run; proc sort data=NumQueryTable0; by Group PostDiaggroup Sex AgeGroup RxYear RxMonth incident; run; /*---------------------------------------------------------------------------*/ /* 13.4 -- Final tables */ /*---------------------------------------------------------------------------*/ /*General numerators table*/ data NumTable0(rename=RxYear=Year rename=RxMonth=Month); retain DPID SITEID Group Sex AgeGroup RxYear RxMonth incident UniquePatients NewStarts claims TotalLookDur PostDiaggroup; merge NumQueryTable0 NumPostDiagTable0; by Group PostDiaggroup Sex AgeGroup RxYear RxMonth incident; DPID="&DPID."; SITEID="&SITEID."; drop seg; run; /*Numerators table stratified by Cats*/ data NumTableCat0(rename=RxYear=Year rename=RxMonth=Month); retain DPID SITEID Group Sex AgeGroup RxYear RxMonth incident UniquePatients NewStarts claims TotalLookDur PostDiaggroup; merge NumQueryTable0 NumPostDiagTableCat0; by Group PostDiaggroup Sex AgeGroup RxYear RxMonth incident; DPID="&DPID."; SITEID="&SITEID."; drop seg; run; data _square2; set _square(rename=RxYear=Year rename=RxMonth=Month); UniqueCatPatients=.; keep Group PostDiagGroup Sex AgeGroup Year Month Incident UniqueCatPatients; run; proc sql noprint; create table _squareCats as select sq.*, cats.Cat from _square2 as sq, _postdiagcats as cats where sq.Group=_postdiagcats.Group and sq.PostDiagGroup=_postdiagcats.PostDiagGroup; quit; proc means data=_squareCats missing noprint; var UniqueCatPatients; class Group PostDiagGroup Sex AgeGroup Year Month Incident Cat; output out=_squareCats(drop=_:) sum=; run; /*MODIF WAVE3 Block Start*/ proc means data=NumQueryTable0(rename=RxYear=Year rename=RxMonth=Month) noprint missing; var UniquePatients; class Group PostDiagGroup Sex AgeGroup Year Month seg Incident; output out=_square3(drop=_:) max=; run; proc sort data=_squareCats; by Group PostDiagGroup Sex AgeGroup Year Month Incident; run; proc sort nodupkey data=_square3; by Group PostDiagGroup Sex AgeGroup Year Month Incident; run; data _squareCats; merge _squareCats _square3; by Group PostDiagGroup Sex AgeGroup Year Month Incident; run; /*MODIF WAVE3 Block End*/ proc sort data=_squareCats; by Group PostDiagGroup Sex AgeGroup Year Month Incident Cat; run; proc sort data=NumTableCat0; by Group PostDiagGroup Sex AgeGroup Year Month Incident Cat; run; data NumTableCat0; merge _squareCats(where=(cat ne "")) NumTableCat0(where=(cat ne "") drop=UniquePatients in=b); by Group PostDiagGroup Sex AgeGroup Year Month Incident Cat; if not b and not missing(UniquePatients) then UniqueCatPatients=0; run; /**************************/ /* Creating Output Tables */ /**************************/ %MACRO CREATETABLES(incident=,prefix=,denomvar=); %MACRO CREATESTRATATABLES(suffix=,strata=,where=,denomwhere=); proc sort data=NumTable0; by Group PostDiaggroup &strata.; run; /***********/ /* Table 1 */ /***********/ data &Prefix.Table1&suffix.; retain Group PostDiaggroup &strata. UniquePatients NewStarts claims TotalLookDur PostDiagUniquePatients PostDiagDispensings PostDiagTotalEpiDur PostDiagTotalDaysSupp /*MODIF WAVE3*/ PostDiagTotalAmtSupp; set NumTable0; by Group PostDiaggroup; where Group ne "" and PostDiaggroup ne "" and &where. and incident=&incident.; keep Group PostDiaggroup &strata. UniquePatients NewStarts claims TotalLookDur PostDiagUniquePatients PostDiagDispensings PostDiagTotalEpiDur PostDiagTotalDaysSupp /*MODIF WAVE3*/ PostDiagTotalAmtSupp; run; /*Add denominators*/ proc means data=_denom nway noprint; var &denomvar.:; class Group PostDiaggroup &strata.; where &denomwhere.; output out=&Prefix.denom&suffix.(keep=Group PostDiaggroup &strata. &denomvar.:) sum=; run; data &Prefix.Table1&suffix.; format DPID SITEID $2.; merge &Prefix.denom&suffix. &Prefix.Table1&suffix.; by Group PostDiaggroup &strata.; DPID="&DPID."; SITEID="&SITEID."; rename &denomvar.DenCount=Denominator &denomvar.DaysCount=MemberDays; label &denomvar.DenCount="Denominator" &denomvar.DaysCount="MemberDays"; run; /***********/ /* Table 2 */ /***********/ data &Prefix.Table2&suffix.; format DPID SITEID $2.; retain DPID SITEID Group PostDiaggroup &strata. UniqueOverlapUsers PercentPostDiagUsers TreatmentOverlap PostDiagIntensity; set NumTable0; by Group PostDiaggroup; where Group ne "" and PostDiaggroup ne "" and &where. and incident=&incident.; PercentPostDiagUsers=.; PostDiagIntensity=.; if UniqueOverlapUsers ne . and UniquePatients > 0 then PercentPostDiagUsers=UniqueOverlapUsers/UniquePatients; if TreatmentOverlap ne . and TotalLookDur > 0 then PostDiagIntensity=TreatmentOverlap/TotalLookDur; DPID="&DPID."; SITEID="&SITEID."; keep DPID SITEID Group PostDiaggroup &strata. UniqueOverlapUsers PercentPostDiagUsers TreatmentOverlap PostDiagIntensity; run; /***********/ /* Table 3 */ /***********/ data &Prefix.Table3&suffix.; format DPID SITEID $2.; retain DPID SITEID Group PostDiaggroup &strata. UniqueOverlapUsers MeanPostDiagIntensity SDPostDiagIntensity MinPostDiagIntensity MedianPostDiagIntensity MaxPostDiagIntensity; set NumTable0; by Group PostDiaggroup; where Group ne "" and PostDiaggroup ne "" and &where. and incident=&incident.; DPID="&DPID."; SITEID="&SITEID."; keep DPID SITEID Group PostDiaggroup &strata. UniqueOverlapUsers MeanPostDiagIntensity SDPostDiagIntensity MinPostDiagIntensity MedianPostDiagIntensity MaxPostDiagIntensity; run; /***********/ /* Table 4 */ /***********/ data &Prefix.Table4&suffix.; format DPID SITEID $2.; retain DPID SITEID Group PostDiaggroup &strata. UniqueOverlapUsers Meantte SDtte Mintte Mediantte Maxtte; set NumTable0; by Group PostDiaggroup; where Group ne "" and PostDiaggroup ne "" and &where. and incident=&incident.; DPID="&DPID."; SITEID="&SITEID."; keep DPID SITEID Group PostDiaggroup &strata. UniqueOverlapUsers Meantte SDtte Mintte Mediantte Maxtte; run; %MEND CREATESTRATATABLES; %CREATESTRATATABLES(suffix=,strata=, where=Sex = "" and AgeGroup = "" and missing(Year)=1 and missing(Month)=1,denomwhere=Segment="ALL"); %CREATESTRATATABLES(suffix=g,strata=Sex, where=Sex ne "" and AgeGroup = "" and missing(Year)=1 and missing(Month)=1,denomwhere=Segment="ALL"); %CREATESTRATATABLES(suffix=ag,strata=AgeGroup, where=Sex = "" and AgeGroup ne "" and missing(Year)=1 and missing(Month)=1,denomwhere=Segment in:("AG")); %CREATESTRATATABLES(suffix=y,strata=Year, where=Sex = "" and AgeGroup = "" and missing(Year)=0 and missing(Month)=1,denomwhere=Segment=:"Y"); %CREATESTRATATABLES(suffix=ym,strata=Year Month, where=Sex = "" and AgeGroup = "" and missing(Year)=0 and missing(Month)=0,denomwhere=Segment=:"PER"); %MACRO CREATECATSTRATATABLES(suffix=,strata=,where=); proc sort data=NumTableCat0; by Group PostDiaggroup &strata.; run; /***********/ /* Table 5 */ /***********/ data &Prefix.Table5&suffix.; retain Group PostDiaggroup &strata. UniquePatients UniqueCatPatients; set NumTableCat0; by Group PostDiaggroup; where Group ne "" and PostDiaggroup ne "" and &where. and incident=&incident.; keep Group PostDiaggroup &strata. UniquePatients UniqueCatPatients; run; %MEND CREATECATSTRATATABLES; %CREATECATSTRATATABLES(suffix=,strata=Cat, where=Sex = "" and AgeGroup = "" and missing(Year)=1 and missing(Month)=1); %CREATECATSTRATATABLES(suffix=g,strata=Sex Cat, where=Sex ne "" and AgeGroup = "" and missing(Year)=1 and missing(Month)=1); %CREATECATSTRATATABLES(suffix=ag,strata=AgeGroup Cat, where=Sex = "" and AgeGroup ne "" and missing(Year)=1 and missing(Month)=1); %CREATECATSTRATATABLES(suffix=y,strata=Year Cat, where=Sex = "" and AgeGroup = "" and missing(Year)=0 and missing(Month)=1); %CREATECATSTRATATABLES(suffix=ym,strata=Year Month Cat, where=Sex = "" and AgeGroup = "" and missing(Year)=0 and missing(Month)=0); /*****************************/ /* Exporting Tables and Data */ /*****************************/ %MACRO EXPORT(DATAPARTNER,REQUESTID,NAME,VARTO); /*To export in .csv and .sas7bdat format*/ %IF %INDEX(&name.,ag) %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. $115.; set &NAME. _Title; run; PROC EXPORT DATA= &NAME. OUTFILE= "&DPLOCAL.&REQUESTID.&RUNID._&NAME..csv" DBMS=CSV REPLACE; RUN; %MEND EXPORT; %MACRO WRAPPER; %IF %STR("&prefix.")=%STR("p") %THEN %DO; proc printto print="&MSOC.&REQUESTID.&RUNID._all_tables.lst" new; run; /*Not tabulated to accommodate partners who cannot read more than 120 long lines*/ Data Titles; Format Table $15. Title $115.; Table="ptable1"; Title="Table 1a: Counts of Members, Episodes, Dispensing and Days of Supply - Prevalent - &STARTDATE."; output; Table="ptable1g"; Title="Table 1a: Counts of Members, Episodes, Dispensing and Days of Supply - Prevalent by Sex - &STARTDATE."; output; Table="ptable1ag"; Title="Table 1a: Counts of Members, Episodes, Dispensing and Days of Supply - Prevalent by AgeGroup - &STARTDATE."; output; Table="ptable1y"; Title="Table 1a: Counts of Members, Episodes, Dispensing and Days of Supply - Prevalent by Year - &STARTDATE."; output; Table="ptable1ym"; Title="Table 1a: Counts of Members, Episodes, Dispensing and Days of Supply - Prevalent by Year and Month - &STARTDATE."; output; Table="itable1"; Title="Table 1b: Counts of Members, Episodes, Dispensing and Days of Supply - Incident - &STARTDATE."; output; Table="itable1g"; Title="Table 1b: Counts of Members, Episodes, Dispensing and Days of Supply - Incident by Sex - &STARTDATE."; output; Table="itable1ag"; Title="Table 1b: Counts of Members, Episodes, Dispensing and Days of Supply - Incident by AgeGroup - &STARTDATE."; output; Table="itable1y"; Title="Table 1b: Counts of Members, Episodes, Dispensing and Days of Supply - Incident by Year - &STARTDATE."; output; Table="itable1ym"; Title="Table 1b: Counts of Members, Episodes, Dispensing and Days of Supply - Incident by Year and Month - &STARTDATE."; output; Table="ptable2"; Title="Table 2a: PostDiag Intensity - Prevalent - &STARTDATE."; output; Table="ptable2g"; Title="Table 2a: PostDiag Intensity - Prevalent by Sex - &STARTDATE."; output; Table="ptable2ag"; Title="Table 2a: PostDiag Intensity - Prevalent by AgeGroup - &STARTDATE."; output; Table="ptable2y"; Title="Table 2a: PostDiag Intensity - Prevalent by Year - &STARTDATE."; output; Table="ptable2ym"; Title="Table 2a: PostDiag Intensity - Prevalent by Year and Month - &STARTDATE."; output; Table="itable2"; Title="Table 2b: PostDiag Intensity - Incident - &STARTDATE."; output; Table="itable2g"; Title="Table 2b: PostDiag Intensity - Incident by Sex - &STARTDATE."; output; Table="itable2ag"; Title="Table 2b: PostDiag Intensity - Incident by AgeGroup - &STARTDATE."; output; Table="itable2y"; Title="Table 2b: PostDiag Intensity - Incident by Year - &STARTDATE."; output; Table="itable2ym"; Title="Table 2b: PostDiag Intensity - Incident by Year and Month - &STARTDATE."; output; Table="ptable3"; Title="Table 3a: Percent Days Overlap Distribution (per Patient) - Prevalent - &STARTDATE."; output; Table="ptable3g"; Title="Table 3a: Percent Days Overlap Distribution (per Patient) - Prevalent by Sex - &STARTDATE."; output; Table="ptable3ag"; Title="Table 3a: Percent Days Overlap Distribution (per Patient) - Prevalent by AgeGroup - &STARTDATE."; output; Table="ptable3y"; Title="Table 3a: Percent Days Overlap Distribution (per Patient) - Prevalent by Year - &STARTDATE."; output; Table="ptable3ym"; Title="Table 3a: Percent Days Overlap Distribution (per Patient) - Prevalent by Year and Month - &STARTDATE."; output; Table="itable3"; Title="Table 3b: Percent Days Overlap Distribution (per Patient) - Incident - &STARTDATE."; output; Table="itable3g"; Title="Table 3b: Percent Days Overlap Distribution (per Patient) - Incident by Sex - &STARTDATE."; output; Table="itable3ag"; Title="Table 3b: Percent Days Overlap Distribution (per Patient) - Incident by AgeGroup - &STARTDATE."; output; Table="itable3y"; Title="Table 3b: Percent Days Overlap Distribution (per Patient) - Incident by Year - &STARTDATE."; output; Table="itable3ym"; Title="Table 3b: Percent Days Overlap Distribution (per Patient) - Incident by Year and Month - &STARTDATE."; output; Table="ptable4"; Title="Table 4a: Days between Study/PostDiag claims - Prevalent - &STARTDATE."; output; Table="ptable4g"; Title="Table 4a: Days between Study/PostDiag claims - Prevalent by Sex - &STARTDATE."; output; Table="ptable4ag"; Title="Table 4a: Days between Study/PostDiag claims - Prevalent by AgeGroup - &STARTDATE."; output; Table="ptable4y"; Title="Table 4a: Days between Study/PostDiag claims - Prevalent by Year - &STARTDATE."; output; Table="ptable4ym"; Title="Table 4a: Days between Study/PostDiag claims - Prevalent by Year and Month - &STARTDATE."; output; Table="itable4"; Title="Table 4b: Days between Study/PostDiag claims - Incident - &STARTDATE."; output; Table="itable4g"; Title="Table 4b: Days between Study/PostDiag claims - Incident by Sex - &STARTDATE."; output; Table="itable4ag"; Title="Table 4b: Days between Study/PostDiag claims - Incident by AgeGroup - &STARTDATE."; output; Table="itable4y"; Title="Table 4b: Days between Study/PostDiag claims - Incident by Year - &STARTDATE."; output; Table="itable4ym"; Title="Table 4b: Days between Study/PostDiag claims - Incident by Year and Month - &STARTDATE."; output; Table="ptable5"; Title="Table 5a: Counts of Members in PostDiag categories - Prevalent - &STARTDATE."; output; Table="ptable5g"; Title="Table 5a: Counts of Members in PostDiag categories - Prevalent by Sex - &STARTDATE."; output; Table="ptable5ag"; Title="Table 5a: Counts of Members in PostDiag categories - Prevalent by AgeGroup - &STARTDATE."; output; Table="ptable5y"; Title="Table 5a: Counts of Members in PostDiag categories - Prevalent by Year - &STARTDATE."; output; Table="ptable5ym"; Title="Table 5a: Counts of Members in PostDiag categories - Prevalent by Year and Month - &STARTDATE."; output; Table="itable5"; Title="Table 5b: Counts of Members in PostDiag categories - Incident - &STARTDATE."; output; Table="itable5g"; Title="Table 5b: Counts of Members in PostDiag categories - Incident by Sex - &STARTDATE."; output; Table="itable5ag"; Title="Table 5b: Counts of Members in PostDiag categories - Incident by AgeGroup - &STARTDATE."; output; Table="itable5y"; Title="Table 5b: Counts of Members in PostDiag categories - Incident by Year - &STARTDATE."; output; Table="itable5ym"; Title="Table 5b: Counts of Members in PostDiag categories - Incident by Year and Month - &STARTDATE."; output; Table="DenTable0"; Title="Table DenTable0: Global Denominators"; output; Table="NumTable0"; Title="Table NumTable0: Global Numerators"; output; Table="signature"; Title="Signature of Request - &STARTDATE."; output; run; %END; %ELSE %DO; proc printto print="&MSOC.&REQUESTID.&RUNID._all_tables.lst"; run; %END; %DO i=1 %TO 5; %EXPORT(&DPID.&SITEID.,&REQUESTID.,&prefix.table&i.,Group); %EXPORT(&DPID.&SITEID.,&REQUESTID.,&prefix.table&i.g,Group); %EXPORT(&DPID.&SITEID.,&REQUESTID.,&prefix.table&i.ag,Group); %EXPORT(&DPID.&SITEID.,&REQUESTID.,&prefix.table&i.y,Group); %EXPORT(&DPID.&SITEID.,&REQUESTID.,&prefix.table&i.ym,Group); %END; %MEND WRAPPER; %WRAPPER; %MEND CREATETABLES; %CREATETABLES(incident=.,prefix=p,denomvar=Prev); %CREATETABLES(incident=1,prefix=i,denomvar=Inc); %EXPORT(&DPID.&SITEID.,&REQUESTID.,DenTable0,Group); %EXPORT(&DPID.&SITEID.,&REQUESTID.,NumTable0,Group); data _NULL_; temp=DATETIME(); seconds=temp-&start.; hours=int(seconds/3600); minutes=int((seconds-hours*3600)/60); seconds2=int((seconds-hours*3600-minutes*60)); call symput('STOP',temp); 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; 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("&NQUERYGROUPS."); ENROLGAP="&ENROLGAP."; COVERAGE="&COVERAGE."; QUERYFROM="&QUERYFROMc"; QUERYTO="&QUERYTOc"; QUERYFILE="&QUERYFILE"; INCQUERYFILE="&INCQUERYFILE"; POSTDIAGFILE="&POSTDIAGFILE"; INCPOSTDIAGFILE="&INCPOSTDIAGFILE"; CONDFILE="&CONDFILE"; AGESTRAT="&AGESTRAT"; LABSCODEMAP="&LABSCODEMAP."; PTLOCCODEMAP="&PTLOCCODEMAP."; OUTTABLESFILE="&OUTTABLESFILE."; /*MODIF WAVE3 HERE*/ STOCKPILINGFILE="&STOCKPILINGFILE."; 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 _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; proc printto log=log print=print; run; %MEND MODULARPROGRAM6; /*---------------------------------------------------------------------------*/ /* 14 -- Example of invoking Modular Program Macro */ /*---------------------------------------------------------------------------*/ %MODULARPROGRAM6(RequestID=mp6, RUNID=r1, ENROLGAP=45, COVERAGE=MD, QUERYFROM=01/01/2006, QUERYTO=12/31/2008, QUERYFILE=mp6_query.sas7bdat, INCQUERYFILE=, POSTDIAGFILE=mp6_postdiag.sas7bdat, INCPOSTDIAGFILE=, CONDFILE=, AGESTRAT=0-19 20-34 35-49 50-64 65-79 80+, LABSCODEMAP=, PTLOCCODEMAP=, OUTTABLESFILE=mp6_output_tables.sas7bdat, STOCKPILINGFILE=mp6_stockpiling.sas7bdat );