/*---------------------------------------------------------------------------------------------------*\ | PROGRAM NAME: Modular Program 4 - Frequency of select events during concomitant exposure to | | drug/procedure groups of interest | | | | Date: 12/17/13 | | Version: 5.0 | | | |-----------------------------------------------------------------------------------------------------| | | | The purpose of this program is to execute requests consistent with | | Modular Program 4 specifications | | | |-----------------------------------------------------------------------------------------------------| | | | Program inputs: | | As specified in Modular Program 4 documentation | | | | Program outputs: | | As specified in Modular Program 4 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; /* 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\MP4\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\MP4\\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\MP4\DPLocal\; /*SAS Output Files*/ libname DPLocal "&DPLocal."; /*---------------------------------------------------------------------------------------------------*/ /* End of User Inputs */ /*---------------------------------------------------------------------------------------------------*/ /*****************************************************************************************************/ /**************************** PLEASE DO NOT EDIT CODE BELOW THIS LINE ********************************/ /*****************************************************************************************************/ /*MODIF WAVE3 HERE*/ %MACRO MODULARPROGRAM4(REQUESTID=,RUNID=,ENROLGAP=,COVERAGE=,QUERYFROM=,QUERYTO=,PRIMFILE=,INCPRIMFILE=,SECFILE=, INCSECFILE=,CONCOMFILE=,EVENTFILE=,INCEVENTFILE=,CONDFILE=,AGESTRAT=,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.); /*MODIF WAVE3 HERE*/ %LET COVERAGE=%UPCASE(&COVERAGE.); %LET PRIMFILE=%LOWCASE(&PRIMFILE.); %LET INCPRIMFILE=%LOWCASE(&INCPRIMFILE.); %LET SECFILE=%LOWCASE(&SECFILE.); %LET INCSECFILE=%LOWCASE(&INCSECFILE.); %LET CONCOMFILE=%LOWCASE(&CONCOMFILE.); %LET EVENTFILE=%LOWCASE(&EVENTFILE.); %LET INCEVENTFILE=%LOWCASE(&INCEVENTFILE.); %LET CONDFILE=%LOWCASE(&CONDFILE.); %LET OUTTABLESFILE=%LOWCASE(&OUTTABLESFILE.); /*MODIF WAVE3 HERE*/ %LET STOCKPILINGFILE=%LOWCASE(&STOCKPILINGFILE.); proc printto log="&MSOC.&REQUESTID.&RUNID..log" new; run; %PUT "MODULARPROGRAM4_v5.0"; %let MPVer=5.0; %let MPNum=4; /*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; /*MODIF WAVE3 HERE*/ %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; %MACRO IMPORTFILES2(var1,var2); %IF %INDEX(%UPCASE("&VAR1."),CPORT) %THEN %DO; proc cimport infile="&infolder.&VAR1." library=infolder memtype=data; run; %END; %LET &VAR2.=%SUBSTR(&VAR1.,1,%INDEX(%UPCASE(&VAR1.),.)-1 ); %LET VAR1=%SUBSTR(&VAR1.,1,%INDEX(%UPCASE(&VAR1.),.)-1); data _&var2.; set infolder.&var1.; run; %MEND IMPORTFILES2; %IMPORTFILES(&PRIMFILE.,PRIMFILE,_ALL_); %IMPORTFILES(&SECFILE.,SECFILE,_ALL_); %IMPORTFILES(&CONCOMFILE.,CONCOMFILE,_ALL_); %MACRO WRAPPER(); %IF %INDEX(%UPCASE("&INCPRIMFILE."),.) %THEN %DO; %IMPORTFILES(&INCPRIMFILE.,INCPRIMFILE,Group SubGroup CodeType Code); %END; %ELSE %DO; data _INCPRIMFILE; if 0 then set _PRIMFILE; keep Group SubGroup CodeType Code; stop; run; %END; %IF %INDEX(%UPCASE("&INCSECFILE."),.) %THEN %DO; %IMPORTFILES(&INCSECFILE.,INCSECFILE,Group SubGroup Codetype Code); %END; %ELSE %DO; data _INCSECFILE; if 0 then set _SECFILE; keep Group SubGroup Codetype Code; stop; run; %END; %IF %INDEX(%UPCASE("&EVENTFILE."),.) %THEN %DO; %IMPORTFILES(&EVENTFILE.,EVENTFILE,_ALL_); %END; %ELSE %DO; data _EVENTFILE; if 0 then set _PRIMFILE; format CareSetting $30. Principal $3. BlackoutPer best.; CareSetting=""; Principal="NO"; BlackoutPer=.; keep Group SubGroup Codetype Code WashTyp WashPer CareSetting Principal BlackoutPer; stop; run; %END; %IF %INDEX(%UPCASE("&INCEVENTFILE."),.) %THEN %DO; %IMPORTFILES(&INCEVENTFILE.,INCEVENTFILE,Group SubGroup Codetype Code CareSetting Principal); %END; %ELSE %DO; data _INCEVENTFILE; if 0 then set _PRIMFILE; format CareSetting $30. Principal $3.; CareSetting=""; Principal="NO"; keep Group SubGroup Codetype Code CareSetting Principal; 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 _PRIMFILE; format Principal $3. CareSetting $30. Condfrom Condto Inclusion best.; CareSetting=""; Principal="NO"; Condfrom=.; Condto=.; Inclusion=.; keep Group SubGroup Codetype Code Principal CareSetting Condfrom Condto Inclusion; stop; run; %END; %IF %INDEX(%UPCASE("&OUTTABLESFILE."),.) %THEN %DO; %IMPORTFILES2(&OUTTABLESFILE.,OUTTABLESFILE); %END; /*MODIF WAVE3 HERE*/ %IF %INDEX(%UPCASE("&STOCKPILINGFILE."),.) %THEN %DO; %IMPORTFILES(&STOCKPILINGFILE.,STOCKPILINGFILE,_ALL_); %END; %ELSE %DO; data _STOCKPILINGFILE; set _PRIMFILE(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(); /* 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(&PRIMFILE.,primfile); %EXPORTVALS(&SECFILE.,secfile); %EXPORTVALS(&CONCOMFILE.,concomfile); %MACRO WRAPPER; %IF &INCPRIMFILE.~= %STR() %THEN %DO; %EXPORTVALS(&INCPRIMFILE.,incprimfile); %END; %IF &INCSECFILE.~= %STR() %THEN %DO; %EXPORTVALS(&INCSECFILE.,incsecfile); %END; %IF &EVENTFILE.~= %STR() %THEN %DO; %EXPORTVALS(&EVENTFILE.,eventfile); %END; %IF &INCEVENTFILE.~= %STR() %THEN %DO; %EXPORTVALS(&INCEVENTFILE.,inceventfile); %END; %IF &CONDFILE.~= %STR() %THEN %DO; %EXPORTVALS(&CONDFILE.,condfile); %END; %MEND WRAPPER; %WRAPPER; /*---------------------------------------------------------------------------*/ /* 03 -- Pre-processing of input data */ /*---------------------------------------------------------------------------*/ /*****************************/ /* PRIM AND INCPRIM */ /*****************************/ data _PRIMFILE; format group $30.; set _PRIMFILE; code = compress(code,'. '); CodeType=upcase(CodeType); WashTyp = upcase(WashTyp); if WashTyp = '' then WashTyp = 'MULT'; if WashPer = . then WashPer = 0; /*MODIF WAVE3 HERE*/ if missing(EnrDays) then EnrDays=0; run; proc sort nodupkey data=_PRIMFILE; by Group CodeType Code; run; /*In some cases, we may wish that each query group in the primary 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 _INCPRIMFILE; format Igroup $30.; set _INCPRIMFILE(rename=(Group=IGroup)); if IGroup = "" then IGroup = "_ALLGROUPS_"; code = compress(code,'. '); CodeType=upcase(CodeType); run; proc sort nodupkey data=_INCPRIMFILE; by IGroup CodeType Code; 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 _primcodes as /*PART 1: if IGROUP is _ALLGROUPS_ then join the files using CodeType and Code and keeping all INCPRIM codes*/ select prim.group, inpr.igroup, prim.subgroup, inpr.subgroup as isubgroup, prim.Code, inpr.Code as icode, prim.CodeType, inpr.CodeType as iCodeType from _PRIMFILE as prim right join _INCPRIMFILE as inpr on prim.Code = inpr.Code and prim.CodeType = inpr.CodeType 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 prim.group, inpr.igroup, prim.subgroup, inpr.subgroup as isubgroup, prim.Code, inpr.Code as icode, prim.CodeType, inpr.CodeType as iCodeType from _PRIMFILE as prim join _INCPRIMFILE as inpr on prim.Code = inpr.Code and prim.CodeType = inpr.CodeType and prim.Group = inpr.IGroup where IGroup ne "_ALLGROUPS_" union /*PART 3: Add PRIM only codes*/ ( select group, "" as igroup, subgroup, "" as isubgroup, Code, "" as icode, CodeType, "" as iCodeType from _PRIMFILE where (CodeType||Code) not in (select (CodeType||Code) from _INCPRIMFILE where IGroup = "_ALLGROUPS_") except select igroup as group, "" as igroup, subgroup, "" as isubgroup, Code, "" as icode, CodeType, "" as iCodeType from _INCPRIMFILE ) union /*PART 4: Add INCPRIMFILE 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 from _INCPRIMFILE 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 from _PRIMFILE ) order by Group; quit; data _primcodes; set _primcodes; Prim = 0; IncPrim = 0; if Group ne "" then Prim = 1; if IGroup ne "" then Incprim = 1; if SubGroup = "" then SubGroup = ISubGroup; if CodeType = "" then CodeType = ICodeType; if Code = "" then Code = ICode; keep Group IGroup SubGroup Code CodeType Prim IncPrim; run; /*In the following steps, we retrieve the query group settings to be applied in creation of primary episodes loop*/ proc sort nodupkey data=_PRIMFILE out= _primsettings(keep=Group WashTyp WashPer EpisodeGap MinDaySupp MinEpisDur FollowPer ExpExtPer /*MODIF WAVE3 HERE*/ EnrDays); by Group; run; /**************************/ /* SEC AND INCSEC */ /**************************/ data _SECFILE; format Group $30.; set _SECFILE; code = compress(code,'. '); CodeType=upcase(CodeType); WashTyp = upcase(WashTyp); if WashTyp = '' then WashTyp = 'MULT'; if WashPer = . then WashPer = 0; /*MODIF WAVE3 HERE*/ if missing(EnrDays) then EnrDays=0; run; proc sort nodupkey data=_SECFILE; by Group CodeType Code; run; /*In some cases, we may wish that each concomitant group of drugs in the conc 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 _INCSECFILE; format IGroup $30.; set _INCSECFILE(rename=(Group=IGroup)); if IGroup = "" then IGroup = "_ALLGROUPS_"; code = compress(code,'. '); CodeType=upcase(CodeType); run; proc sort nodupkey data=_INCSECFILE; by IGroup CodeType Code; 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 _seccodes as /*PART 1: if IGROUP is _ALLGROUPS_ then join the files using CodeType and Code and keeping all INCSEC codes*/ select sec.group, inse.igroup, sec.subgroup, inse.subgroup as isubgroup, sec.Code, inse.Code as icode, sec.CodeType, inse.CodeType as iCodeType from _SECFILE as sec right join _INCSECFILE as inse on sec.Code = inse.Code and sec.CodeType = inse.CodeType 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 sec.group, inse.igroup, sec.subgroup, inse.subgroup as isubgroup, sec.Code, inse.Code as icode, sec.CodeType, inse.CodeType as iCodeType from _SECFILE as sec join _INCSECFILE as inse on sec.Code = inse.Code and sec.CodeType = inse.CodeType and sec.Group = inse.IGroup where IGroup ne "_ALLGROUPS_" union /*PART 3: Add sec only codes*/ ( select group, "" as igroup, subgroup, "" as isubgroup, Code, "" as icode, CodeType, "" as iCodeType from _SECFILE where (CodeType||Code) not in (select (CodeType||Code) from _INCSECFILE where IGroup = "_ALLGROUPS_") except select igroup as group, "" as igroup, subgroup, "" as isubgroup, Code, "" as icode, CodeType, "" as iCodeType from _INCSECFILE ) union /*PART 4: Add INCsecFILE 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 from _INCSECFILE 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 from _SECFILE ) order by Group; quit; data _seccodes; set _seccodes; Sec = 0; IncSec = 0; if Group ne "" then Sec = 1; if IGroup ne "" then IncSec = 1; if SubGroup = "" then SubGroup = ISubGroup; if CodeType = "" then CodeType = ICodeType; if Code = "" then Code = ICode; keep Group IGroup SubGroup Code CodeType Sec IncSec; run; /*In the following steps, we retrieve the secondary settings to be applied in incidence of secondary drugs calculation*/ proc sort nodupkey data=_SECFILE out=_secsettings(keep=Group WashTyp WashPer EpisodeGap MinDaySupp MinEpisDur FollowPer ExpExtPer /*MODIF WAVE3 HERE*/ EnrDays); by Group; run; /*Since a claim can either be primary incident or secondary we cannot apply at the retrieval a primary group setting, we shall apply the most severe washout setting*/ /*Defensive coding: consider the possibility to have the same drug as a primary and a secondary one*/ data _extractsettings(keep=WashTyp WashPer WashOrder); set _primsettings(keep=WashTyp WashPer) _secsettings(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 _primcodes; set _primcodes; if _N_ = 1 then set _extractsettings; run; data _seccodes; set _seccodes; if _N_ = 1 then set _extractsettings; run; /*****************************/ /* Events */ /*****************************/ data _EVENTFILE; set _EVENTFILE; code = compress(code,'. '); CodeType=upcase(CodeType); CareSetting=upcase(CareSetting); Principal=upcase(Principal); WashTyp = upcase(WashTyp); if WashTyp = '' then WashTyp = 'MULT'; if WashPer = . then WashPer = 0; if Principal="" then Principal="NO"; run; proc sort nodupkey data=_EVENTFILE; by Group CodeType Code CareSetting Principal; run; data _INCEVENTFILE; set _INCEVENTFILE; code = compress(code,'. '); CodeType=upcase(CodeType); CareSetting=upcase(CareSetting); Principal=upcase(Principal); if Principal="" then Principal="NO"; run; proc sort nodupkey data=_INCEVENTFILE; by Group CodeType Code CareSetting Principal; run; data _eventcodes; merge _EVENTFILE(in=a keep=Group SubGroup CodeType Code CareSetting Principal) _INCEVENTFILE(in=b keep=Group SubGroup CodeType Code CareSetting Principal); by Group CodeType Code CareSetting Principal; if a then event = 1; else event = 0; if b then incevent = 1; else incevent = 0; run; proc sort nodupkey data = _EVENTFILE out = _eventsettings(keep=Group WashTyp WashPer BlackoutPer); by Group; run; data _eventcodes; merge _eventcodes(in=a) _eventsettings; by group; if a; 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 _ndc(drop=Principal CareSetting); length Caresetting $ 30; length Principal $ 3; set _primcodes(in=a) _seccodes(in=b) _condfile(in=c) _eventcodes(in=d); if not a then do; Prim = 0; IncPrim = 0; end; if not b then do; Sec = 0; IncSec = 0; end; if c then Cond = 1;else Cond = 0; if not d then do; Event = 0; IncEvent = 0; end; Code=compress(Code,'. '); CodeType=upcase(CodeType); length=length(code); if WashTyp = '' then WashTyp = 'MULT'; if WashPer = . then WashPer = 0; if CondFrom = . then CondFrom = 0; if CondTo = . then CondTo = 0; 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; run; /*Storing all PrimGroup into a macro vector*/ proc sort nodupkey data = _primcodes(where=(Prim=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(keep=code); set _&file.; format code $8.; code = "'"||trim(left(code))||"'"; 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, 'S' as PDX, compress(PX,'.') as PX, PX_CodeType from indata.&proctable. (where=(compress(PX,'.') in:(&VECT_proc.))) 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 HERE */ 1 as RxAmt, compress(prctb.PX,'. ') as Code, proclist.GROUP, proclist.IGroup, proclist.SUBGROUP, proclist.CODETYPE, proclist.Prim, proclist.IncPrim, proclist.WASHTYP, proclist.WASHPER, proclist.Sec, proclist.IncSec, proclist.Principal, proclist.Caresetting, proclist.Event, proclist.IncEvent, 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.))) 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 HERE */ 1 as RxAmt, compress(diagtb.DX,'. ') as Code, diaglist.GROUP, diaglist.IGroup, diaglist.SUBGROUP, diaglist.CODETYPE, diaglist.prim, diaglist.IncPrim, diaglist.WASHTYP, diaglist.WASHPER, diaglist.Sec, diaglist.IncSec, diaglist.Principal, diaglist.Caresetting, diaglist.Event, diaglist.IncEvent, 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; %END; %IF %EVAL(&pnobs.> 0 and &dnobs.=0) %THEN %DO; data _MasterQueryfile; set _procedures; Clm = _N_; run; %END; %IF %EVAL(&pnobs.=0 and &dnobs.>0) %THEN %DO; data _MasterQueryfile; set _diagnosis; Clm = _N_; run; %END; %MEND GETMEDS; %GETMEDS(); /*---------------------------------------------------------------------------*/ /* 05 -- Envelope outpatient diagnoses during inpatient stays */ /*---------------------------------------------------------------------------*/ /*****************************************************************/ /* Reduce diagnosis table according to selected care setting */ /* after having reclassified as inpatient all selected claims */ /* within admission and discharge dates of an inpatient */ /* stay, when necessary */ /*****************************************************************/ %MACRO ENVEL(); %IF %SYSFUNC(exist(_MasterQueryfile))=1 %THEN %DO; proc contents data=_MasterQueryfile noprint out=_tilt_ ; data _null_ ; set _tilt_ ; call symput('nobs1',trim(left(put(nobs,15.)))) ; run; %END; %ELSE %DO; data _null_ ; call symput('nobs1',input('0',15.)) ; run; %END; %LET SOME=0; proc sort nodupkey data=_diag out=_diagCS(keep=caresetting); by caresetting; run; proc sort nodupkey data=_proc out=_procCS(keep=caresetting); by caresetting; run; data _null_; set _procCS _diagCS; caresetting=upcase(compress(caresetting,"'")); /*first determine whether the "all care settings" option was always chosen*/ if caresetting ne '' and not(indexw(caresetting,'OA') & indexw(caresetting,'IP') & indexw(caresetting,'ED') & indexw(caresetting,'AV') & indexw(caresetting,'IS') ) then call symput('SOME',input('1',1.)); /*this will trigger the envelope to run*/ else caresetting=''; run; %PUT &SOME. &nobs1.; %PUT %EVAL(&nobs1.>0 and &SOME.=1); %IF %EVAL(&nobs1.>0 and &SOME.=1) %THEN %DO; /*Reclassification as inpatient all selected claims within admission and discharge dates of an inpatient stay*/ /*Get Unique ADate-Ddate combinations*/ proc sort nodupkey data=indata.&ENCTABLE.(keep=PatId Adate Ddate EncType where=(EncType='IP')) out=_IPdates(keep=PatId Adate Ddate); by PatId Adate Ddate; run; proc sql noprint; create table _datematch as Select claimtb.Clm From _MasterQueryFile(where=(EncType not in('IP'))) as claimtb, _IPDates as datetb Where claimtb.PatId = datetb.PatId and datetb.ADate <= claimtb.ADate <= max(datetb.ADate,datetb.DDate); quit; proc sort nodupkey data = _datematch(keep=Clm); by Clm; run; /*For each record in Diagnosis matching an inpatient day date, recode encounter type as inpatient*/ data _MasterQueryFile(drop=EncType); merge _MasterQueryFile(in=a) _datematch(in=b); by Clm; EncType2=EncType; if b then do; if upcase(EncType) not in('IP') then pdx='S'; EncType2='IP'; end; run; proc datasets library = work nolist; modify _MasterQueryFile; rename EncType2=EncType; quit; proc datasets library = work nolist nowarn; delete _IPDates; quit; %END; %IF %SYSFUNC(exist(_MasterQueryfile))=1 %THEN %DO; /*Filter claims with matching EncType and Principal status*/ data _MasterQueryFile(drop=EncType2 clm principal caresetting numarg i EncType PDX code codetype); set _MasterQueryFile; format EncType2 $2.; /*case where all care settings are wanted and necessarily that principal=NO*/ if caresetting = '' then output; else do; Numarg=length(compress(caresetting," '"))/2; do i=1 to Numarg; EncType2=compress(scan(caresetting,i),"'"); if EncType2 = EncType then do; /*MODIF WAVE3 HERE*/ if upcase(principal)='NO' then output; if upcase(principal)='YES' and upcase(PDX)='P' and upcase(EncType2) in('IP','ED') then output; end; end; end; run; %END; %MEND ENVEL; %ENVEL(); /*---------------------------------------------------------------------------*/ /* 06 -- Extract drug claims */ /*---------------------------------------------------------------------------*/ %MACRO GETDRUGS(); %LET ISCode9=0; %LET ISCode11=0; data _null_; set _ndc; if length=9 then call symput("ISCode9",1); if length=11 then call symput("ISCode11",1); run; %PUT &ISCode9; %PUT &ISCode11; /*Extract 9 and/or 11 digit Codes claims*/ %MACRO WRAPPER; %IF %EVAL(&ISCode9.>0) %THEN %DO; proc sql noprint; create table _drugs as select CodeList.GROUP, CodeList.IGroup, CodeList.SUBGROUP, CodeList.CODE, CodeList.CODETYPE, Codelist.Prim, Codelist.IncPrim, CodeList.WASHTYP, CodeList.WASHPER, Codelist.Sec, Codelist.IncSec, CodeList.Event, CodeList.IncEvent, 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 /*MODIF WAVE3: removed to allow multiple claims per day group by GROUP, IGroup, SUBGROUP, CODE, CODETYPE, Prim, IncPrim, WASHTYP, WASHPER, Sec, IncSec, Event, IncEvent, CondLookTyp, Condfrom, Condto, Cond, Inclusion, Patid, RxDate, RxSup, RxAmt, proc having FREQ(RxDate) GE 1*/; 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.Prim, CodeList.IncPrim, CodeList.WASHTYP, CodeList.WASHPER, CodeList.Sec, CodeList.IncSec, CodeList.Event, CodeList.IncEvent, 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 /*MODIF WAVE3: removed to allow multiple claims per day group by GROUP, IGroup, SUBGROUP, CODE, CODETYPE, Prim, IncPrim, WASHTYP, WASHPER, Sec, IncSec, Event, IncEvent, CondLookTyp, Condfrom, Condto, Cond, Inclusion, Patid, RxDate, RxSup, RxAmt, proc having FREQ(RxDate) GE 1*/; quit; Title1 "Eleven digit NDC code frequency printout"; proc freq data = _predrugs; tables Code; run; Title1 ""; proc datasets library=work nolist; append base=_drugs data=_predrugs FORCE; 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(drop=code codetype); run; %END; %IF %SYSFUNC(exist(_MasterQueryFile))=0 and %SYSFUNC(exist(_Drugs))=1 %THEN %DO; data _MasterQueryFile; set _drugs(drop=code codetype); run; %END; %MEND WRAPPER; %WRAPPER; /*MODIF WAVE3 HERE*/ /*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; /*MODIF WAVE3 HERE*/ %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 */ /*---------------------------------------------------------------------------*/ /* To do: Add possibility to have ndc in events */ data _MasterPrimFile(keep=PatId ADate Group IGroup SubGroup Prim IncPrim RxSup RxAmt proc) _MasterSecFile(keep=PatId ADate Group IGroup SubGroup Sec IncSec RxSup RxAmt proc) _MasterEventFile(keep=PatId ADate Group IGroup SubGroup Event IncEvent RxSup RxAmt proc) _MasterCondFile(keep=PatId ADate Group IGroup SubGroup WashTyp WashPer CondLookTyp CondFrom CondTo Cond proc RxSup RxAmt Inclusion); set _MasterQueryFile; if Prim or IncPrim then output _MasterPrimFile; /*Contains primary related claims*/ else if Sec or IncSec then output _MasterSecFile; /*Contains secondary related claims*/ else if Event or IncEvent then output _MasterEventFile; /*Contains events 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=_MasterPrimFile,whereQ=Prim,whereI=not Prim and IncPrim,class=,id=Prim IncPrim); %STOCKPILE(configfile=_stockpilingfile,file=_MasterSecFile,whereQ=Sec,whereI=not Sec and IncSec,class=,id=Sec IncSec); %STOCKPILE(configfile=_stockpilingfile,file=_MasterEventFile,whereQ=Event,whereI=not Event and IncEvent,class=,id=Event IncEvent); %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=_MasterPrimFileQEx,claimtype=PRIMARY); %STOCKPILE_EXCLUDED(infile=_MasterSecFileQEx,claimtype=SECONDARY); %STOCKPILE_EXCLUDED(infile=_MasterEventFileQEx,claimtype=EVENT); %STOCKPILE_EXCLUDED(infile=_MasterCondFileQEx,claimtype=CONDITION); data msoc.&REQUESTID.&RUNID._stockpiling_excl; set _MasterPrimFileQEx _MasterSecFileQEx _MasterEventFileQEx _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 /*MODIF WAVE3 HERE*/ From indata.&ENRTABLE.(where=(&ENRCOV.)) as Enrol, indata.&DEMTABLE.(where=(Birth_Date ne . and not missing(PatId) and not missing(Sex))) 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 _MasterPrimFile as select mqfq.*, den.Enr_Start, den.Enr_End from _MasterPrimFileQ 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 _MasterPrimFileI as mqfi, _DenomInt as den where mqfi.PatId=den.PatId and den.Enr_Start <= mqfi.ExpireDt and mqfi.ADate <= den.Enr_End ; quit; data _MasterPrimFile; set _MasterPrimFile; 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 _MasterSecFile as select mcfq.*, den.Enr_Start, den.Enr_End from _MasterSecFileQ 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 _MasterSecFileI as mcfi, _DenomInt as den where mcfi.PatId=den.PatId and den.Enr_Start <= mcfi.ExpireDt and mcfi.ADate <= den.Enr_End; quit; data _MasterSecFile; set _MasterSecFile; 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 _MasterEventFile as select mcfq.*, den.Enr_Start, den.Enr_End from _MasterEventFileQ 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 _MasterEventFileI as mcfi, _DenomInt as den where mcfi.PatId=den.PatId and den.Enr_Start <= mcfi.ExpireDt and mcfi.ADate <= den.Enr_End; quit; 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 _MasterPrimFileQ _MasterPrimFileQex _MasterPrimFileQ_iter _MasterPrimFileQ_iterex _MasterPrimFileI _MasterPrimFileIex _MasterSecFileQ _MasterSecFileQex _MasterSecFileQ_iter _MasterSecFileQ_iterex _MasterSecFileI _MasterSecFileIex _MasterEventFileQ _MasterEventFileQex _MasterEventFileQ_iter _MasterEventFileQ_iterex _MasterEventFileI _MasterEventFileIex _MasterCondFileQ _MasterCondFileQex _MasterCondFileQ_iter _MasterCondFileQ_iterex _MasterCondFileI _MasterCondFileIex; 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 =_MasterPrimFile nway; var ADate; class PatId Group; where &QUERYFROM. <= ADate <= &QUERYTO. and Prim = 1; output out=_PrimFDateTable(drop=_:) min = MinDt / keeplen; run; /*Create QueryGroup Date Variables*/ proc transpose data =_PrimFDateTable out =_PrimFDateTable(drop=_NAME_); id Group; var MinDt; by PatId; run; proc transpose data=_grouplist out=_Groups(drop=_NAME_); id Group; run; data _PrimFDateTable; set _PrimFDateTable; if _N_=0 then set _Groups; format MinFdt MMDDYY10. MaxFdt MMDDYY10.; MinFdt = min(&GROUPVECT2.,.); MaxFdt = max(&GROUPVECT2.,.); run; proc means noprint data =_MasterSecFile nway; var ADate; class PatId Group; where &QUERYFROM. <= ADate <= &QUERYTO. and Sec = 1; output out=_SecFDateTable(drop=_:) min = MinDt / keeplen; run; /*Create QueryGroup Date Variables*/ proc transpose data = _SecFDateTable out = _SecFDateTable(drop=_NAME_); id Group; var MinDt; by PatId; run; data _SecFDateTable; set _SecFDateTable; if _N_=0 then set _Groups; format MinFdt MMDDYY10. MaxFdt MMDDYY10.; MinFdt = min(&GROUPVECT2.,.); MaxFdt = max(&GROUPVECT2.,.); run; data _PatList; set _PrimFDateTable(keep=PatId) _SecFDateTable(keep=PatId); run; proc sort nodupkey data = _PatList; by PatId; run; /************************************************/ /* Reduce Enrollment to Query Patients */ /************************************************/ data _Enrollment(keep=PatId Enr_Start Enr_End sex birth_date MinAgeDate MaxAgeDate); if 0 then set _PatList; declare hash ht (hashexp:16, dataset:'_PatList'); 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 */ /*---------------------------------------------------------------------------*/ /**************************************/ /* Looping through each query group */ /**************************************/ %global NQUERYGROUPS; %MACRO LOOPTHROUGH(); %let i = 1; 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.; %LET EVENTWASHOUT=0; %LET BLACKOUTPER=0; data _null_; set _eventsettings; if Group in("&ITGROUP.") then do; call symput('EVENTWASHOUT',WashPer); call symput('BLACKOUTPER',BlackOutPer); end; run; %PUT &EVENTWASHOUT.; %PUT &BLACKOUTPER.; data _null_; set _concomfile; if Group in("&ITGROUP.") then do; call symput('EXPORDER',upcase(ExpOrder)); end; run; %PUT &EXPORDER.; %let ps=Prim; %macro prim_or_sec(ps=); data _&ps.loopsettings; set _&ps.settings(where=(Group in("&ITGROUP."))); drop Group; run; data _&ps.SDFDateTable(keep=PatId); set _&ps.FDateTable; if &ITGROUP.; run; data _&ps.Quantity(drop=&ps. Inc&ps. Group SubGroup IGroup); if 0 then set _&ps.SDFDateTable; declare hash ht (hashexp:16, dataset:"_&ps.SDFDateTable"); ht.definekey('PatId'); ht.definedata(ALL: 'YES'); ht.definedone(); do until(eof1); set _Master&ps.file(where=((Group in("&ITGROUP.") or IGroup in("&ITGROUP.","_ALLGROUPS_")) and (&ps. or Inc&ps.))) end=eof1; if ht.find()=0 then do; InQuery = 0; if Group in("&ITGROUP.") and &QUERYFROM. <= ADate /*MODIF WAVE3 <= &QUERYTO.*/ then InQuery = 1; output _&ps.Quantity; end; end; stop; run; /*Defensive coding, enrollment episodes do not overlap*/ Proc SQL Noprint; Create Table _&ps.CleanQuantity as Select qty.*, enrol.Enr_Start, enrol.Enr_End, enrol.MinAgeDate, enrol.MaxAgeDate From _&ps.Quantity as qty, _Enrollment as enrol Where qty.PatId = enrol.PatId and enrol.Enr_Start <= qty.ADate <= enrol.Enr_End Order by PatId, ADate; quit; /*below is automatically enforced when creating episodes for study, must adjust the RxSup for study and conc to be consistent*/ data _&ps.CleanQuantity; set _&ps.CleanQuantity; ExpireDt = min(ExpireDt,Enr_End); RxSup = Expiredt - ADate + 1; run; data _&ps.Quantity; set _&ps.CleanQuantity; where InQuery=1; run; data _&ps.Quantity; set _&ps.Quantity; if _N_ = 1 then set _&ps.loopsettings; run; /*CREATING GAPLESS QUERY EPISODES FOR OVERLAPPING RXSUP - possibly more than on rank per episode*/ data _&ps.Quantity; set _&ps.Quantity; by Patid; if first.patid then overlap=.; else overlap=lexpiredt-ADate+1; if first.patid 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 data=_&ps.Quantity nway noprint; var ADate ExpireDt NumDispensing; class PatId rank; id Enr_Start Enr_End MinAgeDate MaxAgeDate WashTyp WashPer EpisodeGap MinEpisDur MinDaySupp FollowPer ExpExtPer /*MODIF WAVE3 HERE*/ EnrDays; output out=_&ps.Quantity(drop=_:) min(ADate)=&ps.ADate max(ExpireDt)= &ps.ExpireDt sum(NumDispensing)=&ps.Disp; run; /*Episode creation*/ data _&ps.Quantity(drop=LRunOutDate LEnrStart gap); set _&ps.Quantity; by PatId; /*Create Episode Indicator*/ LRunOutDate = lag(&ps.ExpireDt); LEnrStart = lag(Enr_Start); if first.PatId then do; LRunOutDate=.; LEnrStart=.; &ps.Episode=1; gap=.; end; else do; gap = &ps.ADate - LRunOutDate-1; if gap > EpisodeGap or lEnrStart ne Enr_Start then &ps.Episode=&ps.Episode+1; /*Change episode if exceed gap (above)*/ end; /*Identify Episode with necessary condition to qualify for incident episode*/ RxSup = &ps.ExpireDt - &ps.Adate + 1; retain &ps.Episode; run; /*Create episode summary for all episodes, including those InQuery=0 to further capture next episode start date*/ proc means data=_&ps.Quantity nway noprint; var &ps.Expiredt &ps.Adate RxSup Enr_Start Enr_End &ps.Disp; class PatId &ps.Episode; id WashTyp Washper MinAgeDate MaxAgeDate MinEpisDur MinDaySupp FollowPer ExpExtPer /*MODIF WAVE3 HERE*/ EnrDays; output out=_&ps.EPisodeSummary(drop=_:) min(&ps.Adate Enr_Start Enr_End)=&ps.EpisodeStartDt &ps.MinEnrStart &ps.MinEnrEnd max(&ps.Expiredt Enr_Start Enr_End)=&ps.EpisodeEndDt &ps.MaxEnrStart &ps.MaxEnrEnd sum(RxSup)=&ps.EpisodeRxSup sum(&ps.Disp)=&ps.DispEpi; run; Proc SQL Noprint; Create Table _&ps.NoClaimBf as Select epis.PatId, epis.&ps.Episode, epis.&ps.EpisodeStartDt, epis.WashTyp, epis.WashPer, qty.Expiredt From _&ps.CleanQuantity as qty right join _&ps.EPisodeSummary as epis on qty.PatId = epis.PatId where qty.ADate < epis.&ps.EpisodeStartDt Order by PatId, &ps.Episode; quit; data _&ps.NoClaimBf; set _&ps.NoClaimBf; &ps.NoClaimBf = 1; if WashTyp in('MIN') then &ps.NoClaimBf = 0; else if WashTyp in('MULT','SING') then do; if (&ps.EpisodeStartDt - ExpireDt - 1) < WashPer then &ps.NoClaimBf = 0; end; run; proc means noprint data = _&ps.NoClaimBf nway; var &ps.NoClaimBf; class PatId &ps.Episode; output out=_&ps.NoClaimBf(drop=_:) min = ; run; data _&ps.NextDate; set _&ps.EPisodeSummary; &ps.Episode=&ps.Episode-1; rename &ps.EpisodeStartDt=&ps.NextEpisodeStartDt; keep patid &ps.Episode &ps.EpisodeStartDt; run; data _&ps.ValidEpisodes; retain PatId Group &ps.Episode &ps.EpisodeStartDt &ps.EpisodeEndDt &ps.Incident; merge _&ps.EPisodeSummary(in=a) _&ps.NextDate _&ps.NoClaimBf(in=b); format Group $30. CondCheckDate mmddyy10.; by PatId &ps.Episode; if a; if not b then &ps.NoClaimBf = 1; Group = "&ITGROUP."; /*MODIF WAVE3 HERE*/ /*Enrollment*/ EnrNumDays= &ps.EpisodeStartDt - &ps.MinEnrStart; if EnrNumDays>=0 and EnrNumDays >= EnrDays then &ps.Enr = 1; else &ps.Enr = 0; if EnrNumDays>=0 and EnrNumDays >= max(WashPer,&EVENTWASHOUT.) then &ps.Wash = 1; else &ps.Wash = 0; /*Identify if episode is incident or not*/ if &ps.NoclaimBf = 1 and &ps.Enr = 1 and &ps.Wash=1 then &ps.Incident = 1; else &ps.Incident = 0; /*Episode duration and minimum drug use*/ &ps.EpisodeEndDt=min(&ps.EpisodeEndDt+ExpExtPer,&ps.NextEpisodeStartDt-1,&ps.MaxEnrEnd); if (&ps.EpisodeRxSup >= MinDaySupp) and (&ps.EpisodeEndDt - &ps.EpisodeStartDt + 1) >= MinEpisDur then Duration=1; else Duration=0; if (&ps.MaxEnrEnd - &ps.EpisodeStartDt + 1) >= FollowPer then FollowPerOK=1; else FollowPerOK=0; if (&ps.EpisodeStartDt + &BLACKOUTPER.) <= &ps.EpisodeEndDt then Blackout = 1; else Blackout = 0; CondCheckDate = &ps.EpisodeStartDt; EWashExt = 0; if MinAgeDate <= &ps.EpisodeStartDt <= MaxAgeDate and Duration and FollowPerOK and Blackout; /*MODIF WAVE3 HERE*/ if &QUERYFROM. <= &ps.EpisodeStartDt <= &QUERYTO.; keep Patid Group &ps.Episode &ps.EpisodeStartDt &ps.EpisodeEndDt &ps.MinEnrStart &ps.MaxEnrEnd &ps.DispEpi &ps.EpisodeRxSup &ps.Incident MinAgeDate MaxAgeDate WashTyp CondCheckDate EWashExt; run; /*MODIF WAVE3 BLOCK START*/ *Adjust episodes claims information; proc sql noprint; create table _&ps.cleanquantityep as select epis.PatId, epis.&ps.Episode, CASE WHEN qty.ExpireDt <= epis.&ps.EpisodeEndDt THEN Rxamt ELSE RxAmt*(RxSup-(ExpireDt-epis.&ps.EpisodeEndDt))/RxSup END as &ps.AmtSupp, qty.NumDispensing as &ps.DispEpi from _&ps.validepisodes as epis, _&ps.CleanQuantity as qty where epis.PatId = qty.PatId and qty.ADate <= epis.&ps.EpisodeEndDt and qty.ExpireDt >= epis.&ps.EpisodeStartDt; quit; proc means data=_&ps.cleanquantityep nway noprint; var &ps.AmtSupp &ps.DispEpi ; class PatId &ps.Episode; output out=_&ps.cleanquantityep(drop=_:) sum = ; run; data _&ps.ValidEpisodes; merge _&ps.ValidEpisodes _&ps.cleanquantityep; by PatId &ps.Episode;; run; /*MODIF WAVE3 BLOCK END*/ %mend prim_or_sec; %prim_or_sec(ps=Prim); %prim_or_sec(ps=Sec); /*Merge Primary and Secondary episodes information*/ proc sql noprint; create table _ConcEpisodes as select primep.PatId, primep.PrimEpisode, primep.PrimEpisodeStartDt, primep.PrimEpisodeEndDt, secep.SecEpisode, secep.SecEpisodeStartDt, secep.SecEpisodeEndDt, primep.PrimIncident, secep.SecIncident, primep.PrimMaxEnrEnd as MaxEnrEnd, /*for overlapping episodes must be the same*/ primep.PrimMinEnrStart as MinEnrStart, primep.MinAgeDate, primep.MaxAgeDate from _primvalidepisodes as primep, _secvalidepisodes as secep where primep.PatId = secep.PatId and secep.SecEpisodeStartDt <= primep.PrimEpisodeEndDt and secep.SecEpisodeEndDt >= primep.PrimEpisodeStartDt; quit; data _ConcEpisodes; set _ConcEpisodes; format ConcEpisodeStartDt ConcEpisodeEndDt mmddyy10.; if _N_ = 1 then set _concomfile(where=(Group in("&ITGROUP."))); ConcEpisodeStartDt=max(SecEpisodeStartDt,PrimEpisodeStartDt); ConcEpisodeEndDt=min(SecEpisodeEndDt,PrimEpisodeEndDt); ConcIncident = PrimIncident*SecIncident; if upcase(ExpOrder) = "Y" then do; if SecEpisodeStartDt < PrimEpisodeStartDt then delete; end; /*MODIF WAVE3 HERE*/ if upcase(ConcSameDay) = "Y" then do; if SecEpisodeStartDt ne PrimEpisodeStartDt then delete; end; drop Group; run; proc sort data = _ConcEpisodes; by PatId ConcEpisodeStartDt; run; data _ConcEpisodes; set _ConcEpisodes; by PatId; if first.PatId then ConcEpisode = 0; ConcEpisode = ConcEpisode + 1; retain ConcEpisode; run; data _ConcNextDate; set _ConcEpisodes; ConcEpisode=ConcEpisode-1; rename ConcEpisodeStartDt=ConcNextEpisodeStartDt; keep PatId ConcEpisode ConcEpisodeStartDt; run; data _ConcValidEpisodes(drop=ConcNextEpisodeStartDt); retain PatId Group ConcEpisode ConcEpisodeStartDt ConcEpisodeEndDt ConcIncident PrimEpisode SecEpisode ConcDay /*ttc*/ FEpi; merge _ConcEpisodes(in=a) _ConcNextDate; format CondCheckDate mmddyy10. Group $30. FEpi $1.; by PatId ConcEpisode; if a; Group = "&ITGROUP."; /*Episode duration and minimum drug use*/ ConcEpisodeEndDt=min(ConcEpisodeEndDt+ExpExtPer,ConcNextEpisodeStartDt-1,MaxEnrEnd); if (ConcEpisodeEndDt - ConcEpisodeStartDt + 1) >= MinEpisDur then Duration=1; else Duration=0; if (MaxEnrEnd - ConcEpisodeStartDt + 1) >= FollowPer then FollowPerOK=1; else FollowPerOK=0; if (ConcEpisodeStartDt + &BLACKOUTPER.) <= ConcEpisodeEndDt then Blackout = 1; else Blackout = 0; ConcDay = ConcEpisodeEndDt - ConcEpisodeStartDt + 1; /*Application of the EVENTWASHEXT condition*/ EWashExt = 0; if upcase(EventWashExt) = "Y" then EWashExt = max(ConcEpisodeStartDt - min(PrimEpisodeStartDt,SecEpisodeStartDt) - &EVENTWASHOUT.,0); /*Application of the CONDEARLIEST condition*/ if upcase(CondEarliest) = "Y" then CondCheckDate = min(PrimEpisodeStartDt,SecEpisodeStartDt); else CondCheckDate = ConcEpisodeStartDt; if ConcEpisodeStartDt >= PrimEpisodeStartDt then FEpi = 'P'; else FEpi = 'S'; keep PatId Group Conc: NumCom PrimEpisode SecEpisode /*ttc*/ EWashExt CondCheckDate Duration FollowPerOk Blackout PrimEpisodeStartDt PrimEpisodeEndDt SecEpisodeStartDt SecEpisodeEndDt FEpi MinEnrStart MaxEnrEnd MinAgeDate MaxAgeDate; run; /*Calculate secondary exposure dispensings overlapping primary exposure dispensings*/ proc sql noprint; create table _BothPillDays as select prim.PatId, prim.PrimEpisode, prim.PrimADate, prim.PrimExpireDt, sec.SecADate, sec.SecExpireDt from _PrimQuantity as prim, _SecQuantity as sec where prim.PatId = sec.PatId and prim.PrimAdate <= sec.SecExpireDt and prim.PrimExpireDt >= sec.SecAdate; quit; /*calculate overlapping days*/ data _BothPillDays; set _BothPillDays; format BothDayStartDt BothDayEndDt mmddyy10.; BothDayStartDt = max(PrimADate,SecADate); BothDayEndDt = min(PrimExpireDt,SecExpireDt); BothDay=BothDayEndDt-BothDayStartDt + 1; BothUser=1; run; proc means noprint data = _BothPillDays nway; var BothDay; class PatId PrimEpisode; output out=_BothPillDays(drop=_:) sum = ; run; /*have to get secondary dispensings overlapping with primary episodes*/ proc sql noprint; create table _SecDispOverPrimEp as select prim.PatId, prim.PrimEpisode, prim.PrimEpisodeStartDt, prim.PrimEpisodeEndDt, sec.SecADate, sec.SecExpireDt from _PrimValidEpisodes as prim, _SecQuantity as sec where prim.PatId = sec.PatId and prim.PrimEpisodeStartDt <= sec.SecExpireDt and prim.PrimEpisodeEndDt >= sec.SecADate; quit; data _SecDispOverPrimEp; set _SecDispOverPrimEp; SecAdate = max(PrimEpisodeStartDt,SecADate); SecExpireDt = min(PrimEpisodeEndDt,SecExpireDt); OConcDisp = 1; OConcDaySupp = SecExpireDt - SecADate + 1; run; proc means noprint data = _SecDispOverPrimEp nway; var OConcDisp OConcDaySupp; class PatId PrimEpisode; output out=_SecDispOverPrimEp(drop=_:) sum = ; run; /*have to get secondary episodes overlapping with primary episodes*/ proc sql noprint; create table _PrimSecEpOver as select prim.PatId, prim.PrimEpisode, prim.PrimEpisodeStartDt, prim.PrimEpisodeEndDt, sec.SecEpisodeStartDt, sec.SecEpisodeEndDt from _PrimValidEpisodes as prim, _SecValidEpisodes as sec where prim.PatId = sec.PatId and prim.PrimEpisodeStartDt <= sec.SecEpisodeEndDt and prim.PrimEpisodeEndDt >= sec.SecEpisodeStartDt; quit; data _PrimSecEpOver; set _PrimSecEpOver; SecEpisodeStartDt = max(SecEpisodeStartDt,PrimEpisodeStartDt); SecEpisodeEndDt = min(SecEpisodeEndDt,PrimEpisodeEndDt); OConcDay = SecEpisodeEndDt - SecEpisodeStartDt + 1; ttc = SecEpisodeStartDt - PrimEpisodeStartDt; run; /*ttc for episodes*/ proc means noprint data = _PrimSecEpOver nway; var OConcDay ttc; class PatId PrimEpisode; output out=_ttcdays(drop=_:) sum(OConcDay) = min(ttc) = ; run; data _overlapstats; merge _BothPillDays(in=a) _SecDispOverPrimEp(in=b) _ttcdays(in=c); by PatId PrimEpisode; if a & b & c; BothUser = 1; run; data _primvalidepisodes; merge _primvalidepisodes(in=a) _overlapstats; by PatId PrimEpisode; if a; if "&EXPORDER" ne "Y" then do; BothUser = .; BothDay = .; OConcDisp = .; OConcDaySupp = .; OConcDay = .; ttc = .; end; run; data _ConcValidEpisodes; set _ConcValidEpisodes; /*EG: Must talk about this*/ ConcDisp = .; ConcRxSup = .; ConcAmtSupp=.; if MinAgeDate <= ConcEpisodeStartDt <= MaxAgeDate and Duration and FollowPerOK and Blackout; keep PatId Group ConcEpisode ConcEpisodeStartDt ConcEpisodeEndDt ConcIncident ConcDay FEpi NumCom CondCheckDate EWashExt ConcRxSup ConcDisp /*MODIF WAVE3*/ ConcAmtSupp MinAgeDate MaxAgeDate MinEnrStart MaxEnrEnd; run; data _ValidEpisodes; set _PrimValidEpisodes(in=a rename=(PrimEpisode=Episode PrimEpisodeStartDt = EpisodeStartDt PrimEpisodeEndDt = EpisodeEndDt PrimIncident = Incident PrimMinEnrStart = MinEnrStart PrimMaxEnrEnd = MaxEnrEnd PrimEpisodeRxSup = EpisodeRxSup PrimDispEpi = NumDispensing /*MODIF WAVE3*/ PrimAmtSupp = EpisodeAmtSupp)) _SecValidEpisodes(in=b rename=(SecEpisode=Episode SecEpisodeStartDt = EpisodeStartDt SecEpisodeEndDt = EpisodeEndDt SecIncident = Incident SecMinEnrStart = MinEnrStart SecMaxEnrEnd = MaxEnrEnd SecEpisodeRxSup = EpisodeRxSup SecDispEpi = NumDispensing /*MODIF WAVE3*/ SecAmtSupp = EpisodeAmtSupp)) _ConcValidEpisodes(in=c rename=(ConcEpisode=Episode ConcEpisodeStartDt = EpisodeStartDt ConcEpisodeEndDt = EpisodeEndDt ConcIncident = Incident ConcRxSup = EpisodeRxSup ConcDisp = NumDispensing /*MODIF WAVE3*/ ConcAmtSupp = EpisodeAmtSupp)); format EpType $1.; Prim = 0; Sec = 0; Conc = 0; if a then Prim = 1; if b then Sec = 1; if c then Conc = 1; if Prim = 1 then EpType = "P"; if Sec = 1 then EpType = "S"; if Conc = 1 then EpType = "C"; 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 concomitant drugs incidence and pre-existing conditions */ /*---------------------------------------------------------------------------*/ data _MasterNonQueryFile; set _MasterEventFile _MasterCondFile; run; proc sql noprint; create table _events_and_conds as select episodes.PatId, episodes.Group, episodes.Episode, episodes.EpisodeStartDt, episodes.EpisodeEndDt, episodes.CondCheckDate, episodes.EWashExt, episodes.Prim, episodes.Sec, episodes.Conc, episodes.EpType, claims.Adate, claims.event, claims.incevent, claims.cond, claims.CondFrom, claims.CondTo, claims.inclusion from _masternonqueryfile(where=(event or incevent or cond)) as claims right join DPLocal.&REQUESTID.&RUNID._ValidEpisodes as episodes on claims.PatId=episodes.Patid and claims.Group=episodes.Group; quit; data _events_and_conds; if 0 then set _eventsettings; declare hash ht (hashexp:16,dataset:"_eventsettings"); ht.definekey('Group'); ht.definedata(ALL:'YES'); ht.definedone(); do until(eof1); set _events_and_conds end=eof1; if ht.find()=0 then output; end; stop; run; data _events_and_conds; set _events_and_conds; format EventDt mmddyy10.; HadEvent = 0; HadIncEvent = 0; if (event or incevent) then do; if (EpisodeStartDt-(Washper+EWashExt)-(washtyp='MIN')*999999) <= Adate < min(EpisodeStartDt+BlackoutPer,EpisodeEndDt) then HadIncEvent = 1; end; if event and min(EpisodeStartDt+BlackoutPer,EpisodeEndDt) <= Adate <= EpisodeEndDt then do; HadEvent = 1; EventDt = ADate; 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=_events_and_conds; by Group; run; data _events_and_conds; merge _events_and_conds _condReqD; by Group; run; /*Identify claims overlapping pre-existing condition period*/ data _events_and_conds; set _events_and_conds; /*Must not apply pre-existing condition claims occurring too far from index dates*/ if cond=1 and (ADate-CondFrom < CondCheckDate or ADate-CondTo > CondCheckDate) then cond=0; /*If no inclusion are required for this group, we set the default HadCond=1*/ if InclReqd=1 then HadCond = 0; else do; HadCond = 1; /*only exclusions are required or no pre-existing conditions at all*/ end; if cond and Adate ne . then do; if CondCheckDate + CondFrom <= ADate <= CondCheckDate + CondTo then do; if inclusion=1 then do; HadCond = 1; end; else do; HadCond = 2; end; 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.CondCheckDate, 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 CondCheckDate) 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 <= CondCheckDate + CondFrom and Enr_end >= CondCheckDate + CondTo then output; keep Group Patid CondCheckDate ExclNum TotExcl Meet; run; proc means data=_ExclEligFlags nway noprint; var Meet; class Group PatId CondCheckDate ExclNum; id TotExcl; output out=_ExclEligFlags(drop=_:) max=; run; proc means data=_ExclEligFlags nway noprint; var Meet; class Group PatId CondCheckDate; 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 CondCheckDate; run; proc sort data = _events_and_conds; by Group PatId CondCheckDate descending HadCond; run; proc sort nodupkey data = _ExclEligFlags; by Group PatId CondCheckDate; run; data _events_and_conds; merge _events_and_conds(in=a) _ExclEligFlags(in=b); by Group PatId CondCheckDate; if a; if ExclReqD=1 then ExclCritMeet=0; else ExclCritMeet=1; if b then ExclCritMeet=1; run; /*Note: At this step (_incconc_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 = _events_and_conds nway; var HadEvent HadIncEvent HadCond EventDt ExclCritMeet; class PatId Group EpType Episode; id BlackoutPer; output out= _events_and_conds(drop=_:) min(EventDt ExclCritMeet) = max(HadEvent HadIncEvent HadCond)=; run; proc sort data = DPLocal.&REQUESTID.&RUNID._ValidEpisodes; by PatId Group EpType Episode; run; data _premaster1 _premaster2; merge DPLocal.&REQUESTID.&RUNID._ValidEpisodes(in=a) _events_and_conds(in=b); by PatId Group EpType Episode; if a; HadEvent=HadEvent>0; HadIncEvent=HadIncEvent>0; if HadEvent and HadIncEvent=0 then do; EpisodeEndDt=EventDt; end; if HadCond = 1 and ExclCritMeet = 1; if Incident = 1 and HadIncEvent = 0 then Incident = 1; else Incident = 0; if Incident = 1 and (upcase(WashTyp) = "SING" or upcase(NumCom) = "ONE") then output _premaster1; else output _premaster2; run; proc sort data = _premaster1; by Group EpType PatId Episode; run; data _premaster1; set _premaster1; by Group EpType 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; quit; /*---------------------------------------------------------------------------*/ /* 11 -- Determine denominators */ /*---------------------------------------------------------------------------*/ /*Code was removed will be replaced in phase 2*/ /*---------------------------------------------------------------------------*/ /* 12 -- Post process denominator information */ /*---------------------------------------------------------------------------*/ /*Code was removed will be replaced in phase 2*/ /*---------------------------------------------------------------------------*/ /* 13 -- Build final tables */ /*---------------------------------------------------------------------------*/ proc sort nodupkey data = _Enrollment(keep=PatId Birth_Date Sex); by PatId; run; data DPLocal.&REQUESTID.&RUNID._QueryGroup(keep=Group PatId Sex AgeGroup Episode Year Month NumDispensing EpisodeRxSup /*MODIF WAVE3*/ EpisodeAmtSupp ttc ConcDay EPType DaysAtRisk EpDur ConcDay BothDay ConcIntensity Incident NewStart Patient HadEvent BothUser OConcDisp OConcDaySupp OConcDay); 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 $9.; if ht.find()=0 then do; if Incident = 1 then NewStart = 1; else NewStart = 0; Patient = 1; Year = year(EpisodeStartDt); Month = month(EpisodeStartDt); EpDur = EpisodeEndDt - EpisodeStartDt + 1; DaysAtRisk=min(EventDt,EpisodeEndDt)-(EpisodeStartDt+BlackoutPer)+1; ConcIntensity=.; if (EpisodeRxSup > 0 and BothDay >=0) then ConcIntensity=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._QueryGroup; end; end; stop; run; /*Squaring: list of all possibilities for uniform table output*/ proc sql; create table _Square as select Group from _primsettings; quit; data _Square; set _Square; Y=intck('year',&QUERYFROM.,&QUERYTO.)+1; do i=1 to Y; Year = year(INTNX('year', &QUERYFROM., i-1,'beginning')); do Month=1 to 12; do j=1 to &NUMAGECAT.; do incident=0 to 1; format AgeGroup $5.; AgeGroup = scan("&AGESTRAT.",j,' '); PatId = '_NA_'; Sex='F';NumDispensing=0;EpisodeRxSup=0;/*MODIF WAVE3*/ EpisodeAmtSupp=0;ttc=.;ConcDay=0;EPDur=0;BothDay=0; ConcIntensity=.;NewStart=0;Patient=0;DaysAtRisk=0;EPType = 'P';HadEvent=0; output; Sex='M';output; Sex='U';output; Sex='F';EPType = 'S';output; Sex='M';EPType = 'S';output; Sex='U';EPType = 'S';output; Sex='F';EPType = 'C';output; Sex='M';EPType = 'C';output; Sex='U';EPType = 'C';output; end; end; end; end; keep PatId Group Sex AgeGroup Year Month NumDispensing EpisodeRxSup /*MODIF WAVE3*/ EpisodeAmtSupp ttc ConcDay EPType EpDur ConcDay BothDay ConcIntensity Incident NewStart Patient DaysAtRisk HadEvent; run; data DPLocal.&REQUESTID.&RUNID._QueryGroup; set DPLocal.&REQUESTID.&RUNID._QueryGroup _Square; run; proc means noprint data = DPLocal.&REQUESTID.&RUNID._QueryGroup noprint; var Patient NewStart NumDispensing EpDur EpisodeRxSup /*MODIF WAVE3*/ EpisodeAmtSupp HadEvent DaysAtRisk; class PatId Group EPType Sex AgeGroup Year Month; types PatId*Group*EPType*(Sex AgeGroup Year Month) PatId*Group*EPType*Year*Month PatId*EPType*(Group); where incident = 1; output out=_ITable00(drop=_:) max(Patient) = UniquePatients sum(NewStart) = NewStarts sum(NumDispensing) = Dispensings sum(EpDur) = EpiDur sum(EpisodeRxSup) = DaysSupp sum(EpisodeAmtSupp) = AmtSupp sum(HadEvent) = Events sum(DaysAtRisk) = DaysAtRisk; run; proc means noprint data=_ITable00 noprint nway missing; var UniquePatients NewStarts Dispensings EpiDur DaysSupp /*MODIF WAVE3*/ AmtSupp Events DaysAtRisk; class Group EPType Sex AgeGroup Year Month; output out=_ITable00(drop=_:) sum = ; run; proc means noprint data = DPLocal.&REQUESTID.&RUNID._QueryGroup noprint; var Patient NewStart NumDispensing EpDur EpisodeRxSup /*MODIF WAVE3*/ EpisodeAmtSupp HadEvent DaysAtRisk; class PatId Group EPType Sex AgeGroup Year Month; types PatId*Group*EPType*(Sex AgeGroup Year Month) PatId*Group*EPType*Year*Month PatId*EPType*(Group); output out=_PTable00(drop=_:) max(Patient) = UniquePatients sum(NewStart) = NewStarts sum(NumDispensing) = Dispensings sum(EpDur)=EpiDur sum(EpisodeRxSup)=DaysSupp sum(EpisodeAmtSupp)=AmtSupp sum(HadEvent)=Events sum(DaysAtRisk)=DaysAtRisk; run; proc means noprint data=_PTable00 noprint nway missing; var UniquePatients NewStarts Dispensings EpiDur DaysSupp /*MODIF WAVE3*/ AmtSupp Events DaysAtRisk; class Group EPType Sex AgeGroup Year Month; output out=_PTable00(drop=_:) sum = ; run; data NumQueryTable0; set _ITable00(in=a) _PTable00(in=b); if a then Incident = 1; else Incident = 0; run; /*keeping one record per patient*/ %MACRO CLASSICTABLES(class=,num=,denomby=,denomwhere=); /*Member counts in segment*/ proc means data=DPLocal.&REQUESTID.&RUNID._QueryGroup nway noprint; var Patient NewStart NumDispensing EpDur EpisodeRxSup /*MODIF WAVE3*/ EpisodeAmtSupp HadEvent DaysAtRisk; class Patid &class.; where incident = 1; output out=_ITable&num.(drop=_:) max(Patient) = UniquePatients sum(NewStart) = NewStarts sum(NumDispensing) = Dispensings sum(EpDur)= EpiDur sum(EpisodeRxSup)= DaysSupp sum(EpisodeAmtSupp)= AmtSupp sum(HadEvent)= Events sum(DaysAtRisk)= DaysAtRisk; run; proc means data=_ITable&num. nway noprint; var UniquePatients NewStarts Dispensings EpiDur DaysSupp /*MODIF WAVE3*/ AmtSupp Events DaysAtRisk; class &class.; output out=ITable&num.(drop=_:) sum=; run; proc means data=DPLocal.&REQUESTID.&RUNID._QueryGroup nway noprint; var Patient NewStart NumDispensing EpDur EpisodeRxSup /*MODIF WAVE3*/ EpisodeAmtSupp HadEvent DaysAtRisk; class Patid &class.; output out=_PTable&num.(drop=_:) max(Patient) = UniquePatients sum(NewStart) = NewStarts sum(NumDispensing) = Dispensings sum(EpDur)=EpiDur sum(EpisodeRxSup)=DaysSupp sum(EpisodeAmtSupp)=AmtSupp sum(HadEvent)=Events sum(DaysAtRisk)=DaysAtRisk; run; proc means data=_PTable&num. nway noprint; var UniquePatients NewStarts Dispensings EpiDur DaysSupp /*MODIF WAVE3*/ AmtSupp Events DaysAtRisk; class &class.; output out=PTable&num.(drop=_:) sum=; run; /*Add denominators*/ /* proc means data=_denom nway noprint; var inc:; class Group &denomby.; where &denomwhere.; output out=_denom&num.(keep=Group &denomby. Inc:) sum=; run; */ data ITable&num.; format DPID SITEID $2. &class. UniquePatients NewStarts Dispensings EpiDur DaysSupp /*MODIF WAVE3*/ AmtSupp Events DaysAtRisk; /*merge _denom&num.*/ set ITable&num.; /*by &class.;*/ DPID="&DPID."; SITEID="&SITEID."; /* rename IncDenCount=Denominator IncDaysCount=MemberDays; label IncDenCount="Denominator" IncDaysCount="MemberDays"; */ run; data ITable&num.; merge ITable&num.(where=(EpType='P') rename=(UniquePatients=UniquePrimPatients NewStarts=PrimNewStarts Dispensings=PrimDispensings EpiDur=PrimEpiDur DaysSupp=PrimDaysSupp /*MODIF WAVE3*/ AmtSupp=PrimAmtSupp Events=PrimEvents DaysAtRisk=PrimDaysAtRisk)) ITable&num.(where=(EpType='S') rename=(UniquePatients=UniqueSecPatients NewStarts=SecNewStarts Dispensings=SecDispensings EpiDur=SecEpiDur DaysSupp=SecDaysSupp /*MODIF WAVE3*/ AmtSupp=SecAmtSupp Events=SecEvents DaysAtRisk=SecDaysAtRisk)) ITable&num.(where=(EpType='C') rename=(UniquePatients=UniqueConcomPatients NewStarts=ConcomNewStarts Dispensings=ConcomDispensings EpiDur=ConcomEpiDur DaysSupp=ConcomDaysSupp /*MODIF WAVE3*/ AmtSupp=ConcAmtSupp Events=ConcomEvents DaysAtRisk=ConcomDaysAtRisk)); by Group &denomby.; drop EpType; run; data PTable&num.; format DPID SITEID $2. &class. UniquePatients NewStarts Dispensings EpiDur DaysSupp /*MODIF WAVE3*/ AmtSupp Events DaysAtRisk; /*merge _denom&num.*/ set PTable&num.; /*by &class.;*/ DPID="&DPID."; SITEID="&SITEID."; /* rename IncDenCount=Denominator IncDaysCount=MemberDays; label IncDenCount="Denominator" IncDaysCount="MemberDays"; */ run; data PTable&num.; merge PTable&num.(where=(EpType='P') rename=(UniquePatients=UniquePrimPatients NewStarts=PrimNewStarts Dispensings=PrimDispensings EpiDur=PrimEpiDur DaysSupp=PrimDaysSupp /*MODIF WAVE3*/ AmtSupp=PrimAmtSupp Events=PrimEvents DaysAtRisk=PrimDaysAtRisk)) PTable&num.(where=(EpType='S') rename=(UniquePatients=UniqueSecPatients NewStarts=SecNewStarts Dispensings=SecDispensings EpiDur=SecEpiDur DaysSupp=SecDaysSupp /*MODIF WAVE3*/ AmtSupp=SecAmtSupp Events=SecEvents DaysAtRisk=SecDaysAtRisk)) PTable&num.(where=(EpType='C') rename=(UniquePatients=UniqueConcomPatients NewStarts=ConcomNewStarts Dispensings=ConcomDispensings EpiDur=ConcomEpiDur DaysSupp=ConcomDaysSupp /*MODIF WAVE3*/ AmtSupp=ConcAmtSupp Events=ConcomEvents DaysAtRisk=ConcomDaysAtRisk)); by Group &denomby.; drop EpType; run; %MEND CLASSICTABLES; %CLASSICTABLES(class=Group EPType,num=1,denomby=,denomwhere=Segment="ALL"); %CLASSICTABLES(class=Group EPType AgeGroup,num=2,denomby=AgeGroup,denomwhere=Segment in:("AG")); %CLASSICTABLES(class=Group EPType sex,num=3,denomby=sex,denomwhere=Segment="ALL"); %CLASSICTABLES(class=Group EPType Year,num=4,denomby=year,denomwhere=Segment=:"Y"); %CLASSICTABLES(class=Group EPType Year Month,num=5,denomby=year month,denomwhere=Segment in:("PER")); /****************************************/ /* Creating Concomitant Drug Usage File */ /****************************************/ data DPLocal.&REQUESTID.&RUNID._ConcQueryGroup; set DPLocal.&REQUESTID.&RUNID._QueryGroup(where=(BothUser=1) keep=PatId Group Sex AgeGroup Year Month OConcDisp OConcDay OConcDaySupp BothDay BothUser incident ttc ConcIntensity); rename OConcDisp = ConcDisp OConcDay = ConcDay OConcDaySupp = ConcDaySupp; run; proc sql; create table _Square as select Group from _primsettings; quit; data _Square; set _Square; Y=intck('year',&QUERYFROM.,&QUERYTO.)+1; do i=1 to Y; Year = year(INTNX('year', &QUERYFROM., i-1,'beginning')); do Month=1 to 12; do j=1 to &NUMAGECAT.; do incident=0 to 1; format AgeGroup $5.; AgeGroup = scan("&AGESTRAT.",j,' '); PatId = '_NA_'; Sex='F';ConcDisp=0;ConcDay=0;ConcDaySupp=0;BothDay=0;BothUser=0;ttc=.;ConcIntensity=.; output; Sex='M';output; Sex='U';output; Sex='F';EPType = 'S';output; Sex='M';EPType = 'S';output; Sex='U';EPType = 'S';output; Sex='F';EPType = 'C';output; Sex='M';EPType = 'C';output; Sex='U';EPType = 'C';output; end; end; end; end; keep PatId Group Sex AgeGroup Year Month ConcDisp ConcDay ttc ConcDaySupp BothDay BothUser Incident ConcIntensity; run; data DPLocal.&REQUESTID.&RUNID._ConcQueryGroup; set DPLocal.&REQUESTID.&RUNID._ConcQueryGroup _square; run; proc means data=DPLocal.&REQUESTID.&RUNID._ConcQueryGroup noprint; var ConcDisp ConcDay ConcDaySupp BothDay BothUser; class Patid Group Sex AgeGroup Year Month; where incident = 1; types PatId*Group*(Sex AgeGroup Year Month) PatId*Group*Year*Month PatId*(Group); output out=_INumCTab0(drop=_:) sum(ConcDisp)=ConcDispensings sum(ConcDay)=ConcTotalEpiDur sum(ConcDaySupp)=ConcTotalDaysSupp sum(BothDay)=DaySuppOverlap max(BothUser)=UniqueOverlapUsers sum(BothUser)=OverlapEpisodes; run; proc means data=_INumCTab0 noprint nway missing; var ConcDispensings ConcTotalEpiDur ConcTotalDaysSupp DaySuppOverlap UniqueOverlapUsers OverlapEpisodes; class Group Sex AgeGroup Year Month; output out=_INumCTab0(drop=_:) sum =; run; proc means data=DPLocal.&REQUESTID.&RUNID._ConcQueryGroup noprint; var ConcDisp ConcDay ConcDaySupp BothDay BothUser; class Patid Group Sex AgeGroup Year Month; types PatId*Group*(Sex AgeGroup Year Month) PatId*Group*Year*Month PatId*(Group); output out=_PNumCTab0(drop=_:) sum(ConcDisp)=ConcDispensings sum(ConcDay)=ConcTotalEpiDur sum(ConcDaySupp)=ConcTotalDaysSupp sum(BothDay)=DaySuppOverlap max(BothUser)=UniqueOverlapUsers sum(BothUser)=OverlapEpisodes; run; proc means data=_PNumCTab0 noprint nway missing; var ConcDispensings ConcTotalEpiDur ConcTotalDaysSupp DaySuppOverlap UniqueOverlapUsers OverlapEpisodes; class Group Sex AgeGroup Year Month; output out=_PNumCTab0(drop=_:) sum =; run; data _NumCTab0; set _INumCTab0(in=a) _PNumCTab0(in=b); if a then Incident = 1; else Incident = 0; run; proc datasets library=work nolist nowarn; delete _INumCTab0 _PNumCTab0; quit; /*---------------------------------------------------------------------------*/ /* 13.2 -- Output Incident and Prevalent tables to DPLOCAL */ /*---------------------------------------------------------------------------*/ proc means data=DPLocal.&REQUESTID.&RUNID._ConcQueryGroup noprint missing; var ttc ConcIntensity; class Group Sex AgeGroup Year Month; where incident = 1; types Group*(Sex AgeGroup Year Month) Group*Year*Month Group; output out=_INumCTab1(drop=_:) Mean(ttc ConcIntensity)=Meanttc MeanConcIntensity Std(ttc ConcIntensity)=SDttc SDConcIntensity Min(ttc ConcIntensity)=Minttc MinConcIntensity Median(ttc ConcIntensity)=Medianttc MedianConcIntensity Max(ttc ConcIntensity)=Maxttc MaxConcIntensity; run; proc means data=DPLocal.&REQUESTID.&RUNID._ConcQueryGroup noprint missing; var ttc ConcIntensity; class Group Sex AgeGroup Year Month; types Group*(Sex AgeGroup Year Month) Group*Year*Month Group; output out=_PNumCTab1(drop=_:) Mean(ttc ConcIntensity)=Meanttc MeanConcIntensity Std(ttc ConcIntensity)=SDttc SDConcIntensity Min(ttc ConcIntensity)=Minttc MinConcIntensity Median(ttc ConcIntensity)=Medianttc MedianConcIntensity Max(ttc ConcIntensity)=Maxttc MaxConcIntensity; run; data _NumCTab1; set _INumCTab1(in=a) _PNumCTab1(in=b); if a then Incident = 1; else Incident = 0; run; proc datasets library=work nolist nowarn; delete _INumCTab1 _PNumCTab1; quit; proc sort data=_NumCTab1; by Group Sex AgeGroup Year Month Incident; run; proc sort data=_NumCTab0; by Group Sex AgeGroup Year Month Incident; run; data NumConcTable0; merge _NumCTab0 _NumCTab1; by Group Sex AgeGroup Year Month Incident; run; data _CntTable; set Numquerytable0; if EpType = "P"; rename UniquePatients = UniquePrimPatients NewStarts = PrimNewStarts; keep UniquePatients NewStarts Group Sex AgeGroup Year Month Incident; run; proc sort data = _CntTable; by Group Sex AgeGroup Year Month Incident; run; data NumConcTable0; merge _CntTable NumConcTable0; by Group Sex AgeGroup Year Month Incident; run; %MACRO CREATESTRATATABLES(Prefix=,suffix=,strata=,where=,incident=); proc sort data=NumConcTable0; by Group &strata.; run; /***********/ /* Table 6 */ /***********/ data &Prefix.Table6&suffix.; retain Group &strata. UniquePrimPatients PrimNewStarts UniqueOverlapUsers OverlapEpisodes MeanConcIntensity SDConcIntensity MinConcIntensity MedianConcIntensity MaxConcIntensity; set NumConcTable0; by Group; where Group ne "" and &where. and incident=&incident.; keep Group &strata. UniquePrimPatients PrimNewStarts UniqueOverlapUsers OverlapEpisodes MeanConcIntensity SDConcIntensity MinConcIntensity MedianConcIntensity MaxConcIntensity; run; /***********/ /* Table 7 */ /***********/ data &Prefix.Table7&suffix.; retain Group &strata. UniquePrimPatients PrimNewStarts UniqueOverlapUsers OverlapEpisodes Meanttc SDttc Minttc Medianttc Maxttc; set NumConcTable0; by Group; where Group ne "" and &where. and incident=&incident.; keep Group &strata. UniquePrimPatients PrimNewStarts UniqueOverlapUsers OverlapEpisodes Meanttc SDttc Minttc Medianttc Maxttc; run; %MEND CREATESTRATATABLES; %CREATESTRATATABLES(Prefix=I,suffix=,strata=, where=Sex = "" and AgeGroup = "" and missing(Year)=1 and missing(Month)=1,incident=1); %CREATESTRATATABLES(Prefix=I,suffix=g,strata=Sex, where=Sex ne "" and AgeGroup = "" and missing(Year)=1 and missing(Month)=1,incident=1); %CREATESTRATATABLES(Prefix=I,suffix=ag,strata=AgeGroup, where=Sex = "" and AgeGroup ne "" and missing(Year)=1 and missing(Month)=1,incident=1); %CREATESTRATATABLES(Prefix=I,suffix=y,strata=Year, where=Sex = "" and AgeGroup = "" and missing(Year)=0 and missing(Month)=1,incident=1); %CREATESTRATATABLES(Prefix=I,suffix=ym,strata=Year Month, where=Sex = "" and AgeGroup = "" and missing(Year)=0 and missing(Month)=0,incident=1); %CREATESTRATATABLES(Prefix=P,suffix=,strata=, where=Sex = "" and AgeGroup = "" and missing(Year)=1 and missing(Month)=1,incident=0); %CREATESTRATATABLES(Prefix=P,suffix=g,strata=Sex, where=Sex ne "" and AgeGroup = "" and missing(Year)=1 and missing(Month)=1,incident=0); %CREATESTRATATABLES(Prefix=P,suffix=ag,strata=AgeGroup, where=Sex = "" and AgeGroup ne "" and missing(Year)=1 and missing(Month)=1,incident=0); %CREATESTRATATABLES(Prefix=P,suffix=y,strata=Year, where=Sex = "" and AgeGroup = "" and missing(Year)=0 and missing(Month)=1,incident=0); %CREATESTRATATABLES(Prefix=P,suffix=ym,strata=Year Month, where=Sex = "" and AgeGroup = "" and missing(Year)=0 and missing(Month)=0,incident=0); /*---------------------------------------------------------------------------*/ /* 13.4 -- Export 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; 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: TBD - Prevalent - &STARTDATE."; output; Table="ptable2"; Title="Table 2a: TBD - Prevalent by AgeGroup - &STARTDATE."; output; Table="ptable3"; Title="Table 3a: TBD - Prevalent by Sex - &STARTDATE."; output; Table="ptable4"; Title="Table 4a: TBD - Prevalent by Year - &STARTDATE."; output; Table="ptable5"; Title="Table 5a: TBD - Prevalent by Year and Month - &STARTDATE."; output; Table="ptable6"; Title="Table 6: TBD - Prevalent - &STARTDATE."; output; Table="ptable6ag"; Title="Table 6a: TBD - Prevalent by AgeGroup - &STARTDATE."; output; Table="ptable6g"; Title="Table 6a: TBD - Prevalent by Sex - &STARTDATE."; output; Table="ptable6y"; Title="Table 6a: TBD - Prevalent by Year - &STARTDATE."; output; Table="ptable6ym"; Title="Table 6a: TBD - Prevalent by Year and Month - &STARTDATE."; output; Table="ptable7"; Title="Table 7a: TBD - Prevalent - &STARTDATE."; output; Table="ptable7ag"; Title="Table 7a: TBD - Prevalent by AgeGroup - &STARTDATE."; output; Table="ptable7g"; Title="Table 7a: TBD - Prevalent by Sex - &STARTDATE."; output; Table="ptable7y"; Title="Table 7a: TBD - Prevalent by Year - &STARTDATE."; output; Table="ptable7ym"; Title="Table 7a: TBD - Prevalent by Year and Month - &STARTDATE."; output; Table="itable1"; Title="Table 1b: TBD - Incident - &STARTDATE."; output; Table="itable2"; Title="Table 2b: TBD - Incident by AgeGroup - &STARTDATE."; output; Table="itable3"; Title="Table 3b: TBD - Incident by Sex - &STARTDATE."; output; Table="itable4"; Title="Table 4b: TBD - Incident by Year - &STARTDATE."; output; Table="itable5"; Title="Table 5b: TBD - Incident by Year and Month - &STARTDATE."; output; Table="itable6"; Title="Table 6b: TBD - Incident - &STARTDATE."; output; Table="itable6ag"; Title="Table 6b: TBD - Incident by AgeGroup - &STARTDATE."; output; Table="itable6g"; Title="Table 6b: TBD - Incident by Sex - &STARTDATE."; output; Table="itable6y"; Title="Table 6b: TBD - Incident by Year - &STARTDATE."; output; Table="itable6ym"; Title="Table 6b: TBD - Incident by Year and Month - &STARTDATE."; output; Table="itable7"; Title="Table 7b: TBD - Incident - &STARTDATE."; output; Table="itable7ag"; Title="Table 7b: TBD - Incident by AgeGroup - &STARTDATE."; output; Table="itable7g"; Title="Table 7b: TBD - Incident by Sex - &STARTDATE."; output; Table="itable7y"; Title="Table 7b: TBD - Incident by Year - &STARTDATE."; output; Table="itable7ym"; Title="Table 7b: TBD - Incident by Year and Month - &STARTDATE."; output; Table="Numquerytable0"; Title="Table NumQueryTable0: Query Numerators"; output; Table="Numconctable0"; Title="Table NumConcTable0: Concomitant Numerators"; output; Table="signature"; Title="Signature of Request - &STARTDATE."; output; run; %MACRO WRAPPER; %DO i=1 %TO 5; %EXPORT(&DPID.&SITEID.,&REQUESTID.,ptable&i,Group); %EXPORT(&DPID.&SITEID.,&REQUESTID.,itable&i.,Group); %END; %DO i=6 %TO 7; %EXPORT(&DPID.&SITEID.,&REQUESTID.,ptable&i.,Group); %EXPORT(&DPID.&SITEID.,&REQUESTID.,ptable&i.g,Group); %EXPORT(&DPID.&SITEID.,&REQUESTID.,ptable&i.ag,Group); %EXPORT(&DPID.&SITEID.,&REQUESTID.,ptable&i.y,Group); %EXPORT(&DPID.&SITEID.,&REQUESTID.,ptable&i.ym,Group); %EXPORT(&DPID.&SITEID.,&REQUESTID.,itable&i.,Group); %EXPORT(&DPID.&SITEID.,&REQUESTID.,itable&i.g,Group); %EXPORT(&DPID.&SITEID.,&REQUESTID.,itable&i.ag,Group); %EXPORT(&DPID.&SITEID.,&REQUESTID.,itable&i.y,Group); %EXPORT(&DPID.&SITEID.,&REQUESTID.,itable&i.ym,Group); %END; %MEND WRAPPER; %WRAPPER; %EXPORT(&DPID.&SITEID.,&REQUESTID.,Numquerytable0,Group); %EXPORT(&DPID.&SITEID.,&REQUESTID.,Numconctable0,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."; /*MODIF WAVE3 HERE*/ COVERAGE="&COVERAGE."; QUERYFROM="&QUERYFROMc"; QUERYTO="&QUERYTOc"; PRIMFILE="&PRIMFILE"; INCPRIMFILE="&INCPRIMFILE"; SECFILE="&SECFILE"; INCSECFILE="&INCSECFILE"; CONCOMFILE="&CONCOMFILE"; EVENTFILE="&EVENTFILE"; INCEVENTFILE="&INCEVENTFILE"; CONDFILE="&CONDFILE"; AGESTRAT="&AGESTRAT"; 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 MODULARPROGRAM4; /*---------------------------------------------------------------------------*/ /* 14 -- Example of invoking Modular Program Macro */ /*---------------------------------------------------------------------------*/ %MODULARPROGRAM4(RequestID=mp4, RUNID=r1, ENROLGAP=30, COVERAGE=MD, QUERYFROM=01/01/2008, QUERYTO=12/31/2010, PRIMFILE=primary.sas7bdat, INCPRIMFILE=, SECFILE=secondary.sas7bdat, INCSECFILE=, CONCOMFILE=concomfile.sas7bdat, EVENTFILE=event.sas7bdat, INCEVENTFILE=, CONDFILE=, AGESTRAT=45-64 65-74 75+, OUTTABLESFILE=Mp4_output_tables.sas7bdat, STOCKPILINGFILE= );