/*---------------------------------------------------------------------------------------------------*\ | PROGRAM NAME: Modular Program 9 - Background Rates and Characterization of Health Outcomes of | | Interest Among Individuals with or without Condition(s) of | | Interest | | | | Date: 12/17/2013 | | Version: 3.0 | | | |-----------------------------------------------------------------------------------------------------| | | | The purpose of this program is to execute requests consistent with | | Modular Program 9 specifications | | | |-----------------------------------------------------------------------------------------------------| | | | Program inputs: | | As specified in Modular Program 9 documentation | | | | Program outputs: | | As specified in Modular Program 9 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\MP9\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\MP9\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\MP9\dplocal\; /*SAS Output Files*/ libname DPLocal "&DPLocal."; /*---------------------------------------------------------------------------------------------------*/ /* End of User Inputs */ /*---------------------------------------------------------------------------------------------------*/ /*****************************************************************************************************/ /**************************** PLEASE DO NOT EDIT CODE BELOW THIS LINE ********************************/ /*****************************************************************************************************/ %MACRO MODULARPROGRAM9(REQUESTID=,RUNID=,ENROLGAP=,COVERAGE=,QUERYFROM=,QUERYTO=, QUERYFILE=,INCQUERYFILE=,CONDFILE=,OUTTABLESFILE=,AGESTRAT=); %LET DPID=%LOWCASE(&DPID.); %LET SITEID=%LOWCASE(&SITEID.); %LET REQUESTID=%LOWCASE(&REQUESTID.); %LET RUNID=%LOWCASE(&RUNID.); %LET COVERAGE=%UPCASE(&COVERAGE.); %LET QUERYFILE=%LOWCASE(&QUERYFILE.); %LET INCQUERYFILE=%LOWCASE(&INCQUERYFILE.); %LET CONDFILE=%LOWCASE(&CONDFILE.); %LET OUTTABLESFILE=%LOWCASE(&OUTTABLESFILE.); proc printto log="&MSOC.&REQUESTID.&RUNID..log" new; run; %PUT "MODULARPROGRAM9_v3.0"; %let MPVer=3.0; %let MPNum=9; /*---------------------------------------------------------------------------------------------------*/ /* 0.0 Preprocess user inputs */ /*---------------------------------------------------------------------------------------------------*/ /*Empty work*/ proc datasets NOLIST NOWARN library=WORK; delete _:; quit; /*---------------------------------------------------------------------------------------------------*\ | Program name: Attrition table Tool. | | | | Date: 04/02/2013 | | Version: 1.0 | | | |-----------------------------------------------------------------------------------------------------| | | | For a MP or a task order, a cohort is created by applying a set of inclusion/exclusion criteria | | to a group of members. It is often useful to see how a cohort evolves after successively | | applying these filters. The attrition table tool will enable the requester to summarize this | | information in a single output table. | | | |-----------------------------------------------------------------------------------------------------| | | | Program inputs: | | -SAS data file with inclusion/exclusion criteria dummy variables | | | | Program outputs: | | -SAS data file (.SAS7BDAT format) containing the attrition table | | | | Macro variable definitions: | | INFILE = Source data set with inclusion/exclusion criteria dummy variables | | CRITFLAG = Variable used to identify inclusions/exclusions | | CLASSVARS = Variable used to identify count unit | | WHERE = Variable to identify extra conditions | | EXCLUDEDVAR = Name of the variable used to flag observations as excluded in the INFILE | | OUTFILE = Name of the attrition table | | CRITDESC = Title for an inclusion/exclusion in the OUTFILE | | CRITNUM = Inclusion/exclusion number in the OUTFILE | | CRITCNTNAME = Name of the count variable | | ADDITIONALVARS = Additional variables to add to the OUTFILE | | | \*---------------------------------------------------------------------------------------------------*/ %macro MS_ATTRITIONTABLE(INFILE=,CRITFLAG=,CLASSVARS=,WHERE=,EXCLUDEDVAR=,OUTFILE=, CRITDESC=,CRITNUM=,CRITCNTNAME=,ADDITIONALVARS=); %macro commonblock(BLOKIN=,BLOCKWHERE=); proc sql noprint; /*Count the number of observations where the criterion applies*/ select sum(&CRITFLAG.) into :SELECT_COUNTS from &BLOKIN. where &BLOCKWHERE.; /*Add an empty row into the outfile*/ proc sql noprint; insert into &OUTFILE. (&CRITCNTNAME.) values(0); quit; quit; /*Update the outfile with the criterion counts*/ data &OUTFILE.; set &OUTFILE. end=eof; if eof then do; &ADDITIONALVARS.; CritNum=&CRITNUM.; CritDesc=&CRITDESC.; &CRITCNTNAME.=max(&SELECT_COUNTS.,0); end; run; %mend commonblock; %if %upcase(%str("&EXCLUDEDVAR.")) ne %str("") %then %do; %if %upcase(%str("&CLASSVARS.")) ne %str("") %then %do; /*Count the number of dimensions in the class variable*/ %let DIMCNT = %SYSFUNC(COUNTW(&CLASSVARS.)); %put &DIMCNT; data _dimcnt; format classvars $30.; do i = 1 to &DIMCNT.; classvars = compress("'"||compress(scan("&CLASSVARS.",&DIMCNT.))||"'"); output; end; drop i; run; proc sql noprint; select classvars into :HKVAR separated by ',' from _dimcnt; quit; %put &HKVAR.; /*To collapse according to class variable*/ proc means noprint data = &INFILE. nway; var &CRITFLAG.; class &CLASSVARS.; where &where. and not &EXCLUDEDVAR.; output out=_tempattrition(drop=_:) max = ; run; /*Generate counts*/ %commonblock(BLOKIN=_tempattrition,BLOCKWHERE=&CRITFLAG.); /*Update of exclusion flag in the infile*/ data _exculdedkey; set _tempattrition(where=(&CRITFLAG.) keep = &CLASSVARS. &CRITFLAG.); rename &CRITFLAG. = _&EXCLUDEDVAR.; run; data &INFILE.(drop=_&EXCLUDEDVAR.); if 0 then set _exculdedkey; declare hash ht (hashexp:16, dataset:'_exculdedkey'); ht.definekey(&HKVAR.); ht.definedata(ALL: 'YES'); ht.definedone(); do until(eof1); set &INFILE. end=eof1; if ht.find()=0 then do; &EXCLUDEDVAR. = _&EXCLUDEDVAR.; end; output; end; stop; run; %end; %else %do; /*Generate counts*/ %commonblock(BLOKIN=&INFILE.,BLOCKWHERE=&where. and &CRITFLAG. and not &EXCLUDEDVAR.); /*Update of exclusion flag in the infile*/ data &infile.; set &infile.; if &where. and &CRITFLAG. then &EXCLUDEDVAR.=1; run; %end; %end; %else %do; %if %upcase(%str("&CLASSVARS.")) ne %str("") %then %do; /*Count the number of dimensions in the class variable*/ %let DIMCNT = %SYSFUNC(COUNTW(&CLASSVARS.)); %put &DIMCNT; data _dimcnt; format classvars $30.; do i = 1 to &DIMCNT.; classvars = compress("'"||compress(scan("&CLASSVARS.",&DIMCNT.))||"'"); output; end; drop i; run; proc sql noprint; select classvars into :HKVAR separated by ',' from _dimcnt; quit; %put &HKVAR.; /*To collapse according to class variable*/ proc means noprint data = &INFILE. nway; var &CRITFLAG.; class &CLASSVARS.; where &where.; output out=_tempattrition(drop=_:) max = ; run; /*Generate counts*/ %commonblock(BLOKIN=_tempattrition,BLOCKWHERE=&CRITFLAG.); %end; %else %do; /*Generate counts*/ %commonblock(BLOKIN=&INFILE.,BLOCKWHERE=&where. and &CRITFLAG.); %end; %end; %mend MS_ATTRITIONTABLE; /*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; /*MODIF WAVE2 HERE*/ %IF %STR("&ENROLGAP.")=%STR("") %THEN %DO; %LET ENROLGAP=0; %END; %IF %STR("&COVERAGE.")=%STR("") OR (%STR("&COVERAGE.") ne %STR("MD") AND %STR("&COVERAGE.") ne %STR("M") AND %STR("&COVERAGE.") ne %STR("D")) %THEN %DO; %LET COVERAGE=MD; %END; %MEND WRAPPER; %WRAPPER; %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 macro variables for study 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 &FORMOD.; %PUT &YEARCHANGE.; /*---------------------------------------------------------------------------------------------------*/ /* 0.1 Import query and incidence files */ /*---------------------------------------------------------------------------------------------------*/ %MACRO IMPORTFILES(var1,var2,var3); %IF %INDEX(%UPCASE("&VAR1."),CPORT) %THEN %DO; proc cimport infile="&infolder.&VAR1." library=infolder memtype=data; run; %END; %LET &VAR2.=%SUBSTR(&VAR1.,1,%INDEX(%UPCASE(&VAR1.),.)-1); %LET VAR1=%SUBSTR(&VAR1.,1,%INDEX(%UPCASE(&VAR1.),.)-1); data _&var2.; set infolder.&var1.; group = compress(trim(left(group))); group = translate(group,'_','-', '_','.', '_',',', '_','%', '_','$', '_','!', '_','*', '_','&', '_','#', '_','@'); keep &var3.; run; %MEND IMPORTFILES; %IMPORTFILES(&QUERYFILE.,QUERYFILE,Code CodeType WashTyp Group Washper CareSetting Principal /*MODIF WAVE2 HERE*/ EnrDays); %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; %MACRO WRAPPER(); %IF %INDEX(%UPCASE("&INCQUERYFILE."),.) %THEN %DO; %IMPORTFILES(&INCQUERYFILE.,INCQUERYFILE,Group Code CodeType CareSetting Principal); %END; %ELSE %DO; data _INCQUERYFILE; if 0 then set _QUERYFILE; keep Group Code CodeType CareSetting Principal; stop; run; %END; %IF %INDEX(%UPCASE("&CONDFILE."),.) %THEN %DO; %IMPORTFILES(&CONDFILE.,CONDFILE,Code CodeType Principal CondFrom CondTo Caresetting Group /*WashPer*/ Inclusion); %END; %ELSE %DO; data _CONDFILE; if 0 then set _QUERYFILE; format Principal $3. CareSetting $40. condfrom condto Inclusion best.; condfrom=.; condto=.; Inclusion=.; keep Code CodeType Principal CondFrom CondTo CareSetting Group Inclusion; stop; run; %END; %IF %INDEX(%UPCASE("&OUTTABLESFILE."),.) %THEN %DO; %IMPORTFILES2(&OUTTABLESFILE.,OUTTABLESFILE); %END; %MEND WRAPPER; %WRAPPER(); %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; /*Printing input files into a .lst file*/ %MACRO WRAPPER(); options nodate nonumber; Title1 "Input Query File printout"; proc print data=infolder.&QUERYFILE. noobs; run; %IF %UPCASE("&INCQUERYFILE.") ne %STR("") %THEN %DO; Title1 "Input IncQuery File printout"; proc print data=infolder.&INCQUERYFILE. noobs; run; %END; %IF %UPCASE("&CONDFILE.") ne %STR("") %THEN %DO; Title1 "Input pre-existing conditon File printout"; proc print data=infolder.&CONDFILE. noobs; run; %END; Title1 ""; %MEND WRAPPER; %WRAPPER(); /*Query*/ data _QUERYFILE; set _QUERYFILE; code = compress(code,'. '); CodeType=upcase(CodeType); WashTyp = upcase(WashTyp); if WashTyp = '' then WashTyp = 'MULT'; if WashPer = . then WashPer = 0; if missing(EnrDays) then EnrDays=0; run; proc sort nodupkey data=_QUERYFILE; by Group codetype Code CareSetting Principal; run; /*In some cases, we may wish that each query group in the query file be incident to a common list of drugs, in this case, the list should only be entered once while leaving the group variable empty*/ data _INCQUERYFILE; set _INCQUERYFILE(rename=(Group=IGroup)); if IGroup = "" then IGroup = "_ALLGROUPS_"; code = compress(code,'. '); CodeType=upcase(CodeType); run; proc sort nodupkey data=_INCQUERYFILE; by IGroup codetype Code CareSetting Principal; run; /*If codes from the common incident list (_ALLGROUPS_) are the union of all query group codes, the following steps avoid duplicating claims*/ proc sql noprint; create table _querycodes as /*PART 1: if IGROUP is _ALLGROUPS_ then join the files using CodeType and Code and keeping all INCQUERY codes*/ select quer.group, inqu.igroup, quer.Code, inqu.Code as icode, quer.CodeType, inqu.CodeType as iCodeType, quer.CareSetting, inqu.CareSetting as iCareSetting, quer.Principal, inqu.Principal as iPrincipal from _QUERYFILE as quer right join _INCQUERYFILE as inqu on quer.Code = inqu.Code and quer.CodeType = inqu.CodeType and quer.CareSetting = inqu.CareSetting and quer.Principal = inqu.Principal where IGroup = "_ALLGROUPS_" union /*PART 2: if IGROUP is not _ALLGROUPS_ then join the files using CodeType, Code and Group to get all common codes*/ select quer.group, inqu.igroup, quer.Code, inqu.Code as icode, quer.CodeType, inqu.CodeType as iCodeType, quer.CareSetting, inqu.CareSetting as iCareSetting, quer.Principal, inqu.Principal as iPrincipal from _QUERYFILE as quer join _INCQUERYFILE as inqu on quer.Code = inqu.Code and quer.CodeType = inqu.CodeType and quer.CareSetting = inqu.CareSetting and quer.Principal = inqu.Principal and quer.Group = inqu.IGroup where IGroup ne "_ALLGROUPS_" union /*PART 3: Add QUERY only codes*/ ( select group, "" as igroup, Code, "" as icode, CodeType, "" as iCodeType, CareSetting, "" as iCareSetting, Principal, "" as iPrincipal from _QUERYFILE where (CodeType||Code||Principal||CareSetting) not in (select (CodeType||Code||Principal||CareSetting) from _INCQUERYFILE where IGroup = "_ALLGROUPS_") except select igroup as group, "" as igroup, Code, "" as icode, CodeType, "" as iCodeType, CareSetting, "" as iCareSetting, Principal, "" as iPrincipal from _INCQUERYFILE ) union /*PART 4: Add INCQUERY only codes that doesnt apply to _ALLGROUPS_*/ ( select "" as group, igroup, "" as Code, Code as icode, "" as CodeType, CodeType as iCodeType, "" as CareSetting, CareSetting as iCareSetting, "" as Principal, Principal as iPrincipal from _INCQUERYFILE where IGroup ne "_ALLGROUPS_" except select "" as group, group as igroup, "" as Code, Code as icode, "" as CodeType, CodeType as iCodeType, "" as CareSetting, CareSetting as iCareSetting, "" as Principal, Principal as iPrincipal from _QUERYFILE ) order by Group; quit; data _querycodes; set _querycodes; Query = 0; Incid = 0; if Group ne "" then Query = 1; if IGroup ne "" then Incid = 1; if Code = "" then do; Code = iCode; CodeType = iCodeType; CareSetting = iCareSetting; Principal = iPrincipal; end; keep Group IGroup Code CodeType CareSetting Principal Query Incid; run; /*In the following steps, we retrieve the query group settings to be applied in creation of episodes loop*/ proc sort nodupkey data=_QUERYFILE out=_querysettings(keep=Group WashTyp WashPer EnrDays); by Group; run; /*Since a claim can either be query or incident we cannot apply at the retrieval a query group setting, we shall apply the most severe washout setting*/ proc sort data = _querysettings(keep=WashTyp WashPer) out = _extractsettings; by WashTyp descending WashPer; run; data _extractsettings; set _extractsettings; if _N_ = 1; run; data _querycodes; set _querycodes; if _N_ = 1 then set _extractsettings; run; /*If no INCQUERYFILE or no _ALLGROUPS_, keep original query washout*/ %MACRO WRAPPER(); %LET NB_ALL_GROUPS=0; %IF %UPCASE("&INCQUERYFILE.") ne %STR("") %THEN %DO; proc sql noprint; select count(IGROUP) into :NB_ALL_GROUPS from _INCQUERYFILE where IGROUP="_ALLGROUPS_"; quit; %END; %PUT &NB_ALL_GROUPS.; %IF &NB_ALL_GROUPS.=0 %THEN %DO; proc sql noprint; update _querycodes as qc set WashPer = ( select settings.WashPer from _querysettings as settings where qc.Group=settings.Group ) where exists ( select settings.WashPer from _querysettings as settings where qc.Group=settings.Group ); update _querycodes as qc set WashTyp = ( select settings.WashTyp from _querysettings as settings where qc.Group=settings.Group ) where exists ( select settings.WashTyp from _querysettings as settings where qc.Group=settings.Group ); quit; %END; %MEND WRAPPER; %WRAPPER; 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 codetype code Principal CondLookTyp condfrom condto CareSetting WashTyp Inclusion); by _ALL_; /*the retain included in the previous step makes sure Group codetype Code are in this order*/ run; data _diag _proc _ndc(drop=Principal CareSetting); length Caresetting $ 30; length Principal $ 3; set _querycodes(in=a) _condfile(in=b); if not a then do; Query = 0; Incid = 0; end; if b then cond = 1;else cond = 0; code = compress(code,'. '); length=length(code); if WashTyp = '' then WashTyp = 'MULT'; if WashPer = . then WashPer = 0; if CondFrom = . then CondFrom = 0; if CondTo = . then CondTo = 0; CodeType=upcase(CodeType); if CodeType in:('DX') then do; codetype=compress(codetype,'DX'); output _diag; end; if CodeType in:('RX') then do; codetype=compress(codetype,'RX'); output _ndc; end; if CodeType in:('PX') then do; codetype=compress(codetype,'PX'); output _proc; end; run; /*Storing all QueryGroup into a macro vector*/ proc sort nodupkey data = _querycodes(where=(Query=1)) out = _GroupList(keep=Group); by Group; run; proc sql noprint; select Group into :GROUPVECT1 separated by ' ' from _GroupList; proc sql noprint; select Group into :GROUPVECT2 separated by ',' from _GroupList; quit; %PUT &GROUPVECT1; %PUT &GROUPVECT2; /*Reading proc and diag codes into vectors for pre-filtering*/ %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); /*---------------------------------------------------------------------------------------------------*/ /* 1.0 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 _procedures as Select prctb.PatId, prctb.Adate, prctb.EncType, compress(prctb.PX,'. ') as Code, proclist.Group, proclist.IGroup, proclist.CodeType, proclist.CareSetting, proclist.Principal, proclist.Query, proclist.Incid, proclist.WashTyp, proclist.WashPer, proclist.Inclusion, proclist.CondFrom, proclist.CondTo, proclist.CondLookTyp, proclist.Cond, 1 as RxSup, /*MODIF WAVE3*/ 1 as RxAmt, 1 as proc, 'S' as PDX From indata.&proctable.(where=(compress(PX,'.') in:(&VECT_proc.))) as prctb, _proc as proclist Where prctb.PX_codetype = proclist.codetype and ( (proclist.exact=-1 and substr(compress(prctb.PX,'.'),1,proclist.length) = proclist.code and substr(compress(prctb.PX,'.'),proclist.WildcardIndex+1,proclist.LengthCodeEnd) = proclist.CodeEnd) or (proclist.exact=0 and substr(compress(prctb.PX,'.'),1,proclist.length) = proclist.Code) or (proclist.exact=1 and compress(prctb.PX,'.')=proclist.code) ) /*substr(compress(prctb.PX,'.'),1,proclist.length) = 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 _Diagnosis as Select diagtb.PatId, diagtb.Adate, diagtb.EncType, diagtb.pdx, compress(diagtb.DX,'. ') as Code, diaglist.Group, diaglist.IGroup, diaglist.CodeType, diaglist.CareSetting, diaglist.Principal, diaglist.Query, diaglist.Incid, diaglist.WashTyp, diaglist.WashPer, diaglist.Inclusion, diaglist.CondFrom, diaglist.CondTo, diaglist.CondLookTyp, diaglist.Cond, 1 as RxSup, /*MODIF WAVE3*/ 1 as RxAmt, -1 as proc From indata.&diatable.(where=(compress(DX,'.') in:(&VECT_diag.))) as diagtb, _diag as diaglist Where diagtb.DX_codetype = diaglist.codetype and ( (diaglist.exact=-1 and substr(compress(diagtb.DX,'.'),1,diaglist.length) = diaglist.code and substr(compress(diagtb.DX,'.'),diaglist.WildcardIndex+1,diaglist.LengthCodeEnd) = diaglist.CodeEnd) or (diaglist.exact=0 and substr(compress(diagtb.DX,'.'),1,diaglist.length) = diaglist.Code) or (diaglist.exact=1 and compress(diagtb.DX,'.')=diaglist.code) ) /*substr(compress(diagtb.DX,'.'),1,diaglist.length) = 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; proc sort nodupkey data = _Diagnosis; by _all_; run; Title1 "Diagnosis code frequency printout"; proc freq data = _diagnosis; tables Code; run; Title1 ""; %END; %IF %EVAL(&pnobs.> 0 and &dnobs.>0) %THEN %DO; data _MasterQueryfile; set _procedures _diagnosis; Clm = _N_; run; proc datasets library = work nolist nowarn; delete _procedures _diagnosis; quit; %END; %IF %EVAL(&pnobs.> 0 and &dnobs.=0) %THEN %DO; data _MasterQueryfile; set _procedures; Clm = _N_; run; proc datasets library = work nolist nowarn; delete _procedures; quit; %END; %IF %EVAL(&pnobs.=0 and &dnobs.>0) %THEN %DO; data _MasterQueryfile; set _diagnosis; Clm = _N_; run; proc datasets library = work nolist nowarn; delete _diagnosis; quit; %END; %MEND GETMEDS; %GETMEDS(); /*---------------------------------------------------------------------------------------------------*/ /* 2.0 ENVELOPE */ /*---------------------------------------------------------------------------------------------------*/ /*****************************************************************/ /* Reduce diagnosis table according to selected care setting */ /* after having reclassified as inpatient all selected claims*/ /* within admission and discharge dates of an inpatient */ /* stay, when necessary */ /*****************************************************************/ %MACRO ENVEL(); %IF %SYSFUNC(exist(_MasterQueryfile))=1 %THEN %DO; proc contents data=_MasterQueryfile noprint out=_tilt_ ; data _null_ ; set _tilt_ ; call symput('nobs1',trim(left(put(nobs,15.)))) ; run; %END; %ELSE %DO; data _null_ ; call symput('nobs1',input('0',15.)) ; run; %END; %LET SOME=0; proc sort nodupkey data=_diag out=_diagCS(keep=caresetting); by caresetting; run; proc sort nodupkey data=_proc out=_procCS(keep=caresetting); by caresetting; run; data _null_; set _procCS _diagCS; caresetting=upcase(compress(caresetting,"'")); /*first determine whether the "all care settings" option was always chosen*/ if caresetting ne '' and not(indexw(caresetting,'OA') & indexw(caresetting,'IP') & indexw(caresetting,'ED') & indexw(caresetting,'AV') & indexw(caresetting,'IS') ) then call symput('SOME',input('1',1.)); /*this will trigger the envelope to run*/ else caresetting=''; run; %PUT &SOME.; %PUT %EVAL(&SOME.=1); %IF %EVAL(&nobs1.>0 and &SOME.=1) %THEN %DO; /*Reclassification as inpatient all selected claims within admission and discharge dates of an inpatient stay*/ /*Get Unique ADate-Ddate combinations*/ proc sort nodupkey data=indata.&ENCTABLE.(keep=PatId Adate Ddate EncType where=(EncType='IP')) out=_IPdates(keep=PatId Adate Ddate); by PatId Adate Ddate; run; proc sql noprint; create table _datematch as Select claimtb.Clm From _MasterQueryFile(where=(EncType not in('IP'))) as claimtb, _IPDates as datetb Where claimtb.PatId = datetb.PatId and datetb.ADate <= claimtb.ADate <= max(datetb.ADate,datetb.DDate); quit; proc sort nodupkey data = _datematch(keep=Clm); by Clm; run; /*For each record in Diagnosis matching an inpatient day date, recode encounter type as inpatient*/ data _MasterQueryFile(drop=EncType); merge _MasterQueryFile(in=a) _datematch(in=b); by Clm; EncType2=EncType; if b then do; if upcase(EncType) not in('IP') then pdx='S'; EncType2='IP'; end; run; proc datasets library = work nolist; modify _MasterQueryFile; rename EncType2=EncType; quit; 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 setting are wanted and necessarily that principal=NO*/ if caresetting = '' then output; else do; Numarg=length(compress(caresetting," '"))/2; do i=1 to Numarg; EncType2=compress(scan(caresetting,i),"'"); if EncType2 = EncType then do; if upcase(principal)='NO' then output; if upcase(principal)='YES' and upcase(PDX)='P' and upcase(EncType2) in('IP','ED') then output; end; end; end; run; %END; %MEND ENVEL; %ENVEL(); /*---------------------------------------------------------------------------------------------------*/ /* 3.0 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.*, Dispensing.Patid, Dispensing.RxDate as ADate, Dispensing.RxSup, Dispensing.RxAmt, 0 as Proc from _ndc as CodeList, indata.&DISTABLE. as Dispensing where substr(Dispensing.ndc,1,9) = CodeList.code and Dispensing.Rxdate >= &QUERYFROM. - CodeList.WashPer + CodeList.CondFrom - (upcase(CodeList.washtyp)='MIN')*999999 and Dispensing.RxSup>0; 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.*, Dispensing.Patid, Dispensing.RxDate as ADate, Dispensing.RxSup, Dispensing.RxAmt, 0 as Proc from _ndc as CodeList, indata.&DISTABLE. as Dispensing where substr(Dispensing.ndc,1,11) = CodeList.code and Dispensing.Rxdate >= &QUERYFROM. - CodeList.WashPer + CodeList.CondFrom - (upcase(CodeList.washtyp)='MIN')*999999 and Dispensing.RXSup>0; quit; Title1 "Eleven digit NDC code frequency printout"; proc freq data = _predrugs; tables Code; run; Title1 ""; proc datasets library=work nolist nowarn; append base=_drugs data=_predrugs FORCE; delete _predrugs; quit; %END; %MEND WRAPPER; %WRAPPER; %MEND GETDRUGS; %GETDRUGS(); %MACRO WRAPPER; %IF %SYSFUNC(exist(_MasterQueryFile))=1 and %SYSFUNC(exist(_Drugs))=1 %THEN %DO; data _MasterQueryFile; set _MasterQueryFile _drugs(drop=code codetype); run; proc datasets library = work nolist nowarn; delete _drugs; quit; %END; %IF %SYSFUNC(exist(_MasterQueryFile))=0 and %SYSFUNC(exist(_Drugs))=1 %THEN %DO; data _MasterQueryFile; set _drugs(drop=code codetype); run; proc datasets library = work nolist nowarn; delete _drugs; quit; %END; %MEND WRAPPER; %WRAPPER; /*Verify if at least one claim was extracted from the CDM databases*/ %macro wrapper(); %if %sysfunc(exist(_MasterQueryFile))=0 %then %do; %put ERROR: No members meet cohort inclusion/exclusion criteria for all groups; %abort cancel; %end; %mend wrapper; %wrapper; /*---------------------------------------------------------------------------------------------------*/ /* 3.1 Enrollment and demographic data */ /*---------------------------------------------------------------------------------------------------*/ Proc SQL Noprint; Create Table _DenomInt as Select Demogs.PatId, Demogs.Birth_Date, Demogs.sex, Enrol.Enr_Start, Enrol.Enr_End, CASE WHEN upcase(MEDCOV) eq 'Y' THEN 1 ELSE 0 END as MedCov, CASE WHEN upcase(DRUGCOV) eq 'Y' THEN 1 ELSE 0 END as DrugCov From indata.&ENRTABLE.(where=(not missing(DRUGCOV) and not missing(MEDCOV))) as Enrol, indata.&DEMTABLE.(where=(Birth_Date ne . and not missing(PatId) and not missing(Sex))) as Demogs Where Demogs.PatId=Enrol.PatId order by Patid, Enr_Start, Enr_end; quit; %macro wrapper(); data _DenomInt; set _DenomInt(where=(Enr_End >= Enr_Start)); by PatId; format MinAgeDate MaxAgeDate mmddyy10.; MinAgeDate = intnx(scan("&AGETYP.",1),birth_date,&MINAGE.,'sameday'); if &MAXAGE.=99999 then MaxAgeDate=intnx('Years',birth_date,110,'sameday'); else MaxAgeDate=intnx(scan("&AGETYP.",&NUMAGECAT.),birth_date,&MAXAGE.+1,'sameday')-1; *Change episode if maximum allowable gap is reached or if changes occur in benefit coverage; %IF %UPCASE("&COVERAGE.") eq %STR("MD") %THEN %DO; if Enr_Start-lag(Enr_end)-1 > &ENROLGAP. or MedCov ne lag(MedCov) or DrugCov ne lag(DrugCov) then episode=episode+1; %END; %IF %UPCASE("&COVERAGE.") eq %STR("M") %THEN %DO; if Enr_Start-lag(Enr_end)-1 > &ENROLGAP. or MedCov ne lag(MedCov) then episode=episode+1; %END; %IF %UPCASE("&COVERAGE.") eq %STR("D") %THEN %DO; if Enr_Start-lag(Enr_end)-1 > &ENROLGAP. or DrugCov ne lag(DrugCov) then episode=episode+1; %END; if first.Patid then episode=1; retain episode; run; %mend wrapper; %wrapper(); /*---------------------------------------------------------------------------------------------------*/ /* 4.0 Process eligibility data */ /*---------------------------------------------------------------------------------------------------*/ /*Reconciliation of elig episodes*/ proc means data=_DenomInt nway noprint; var Enr_Start Enr_end; class PatId episode; id Birth_Date sex MinAgeDate MaxAgeDate MedCov DrugCov; output out=_DenomInt(drop=_:) min(Enr_Start)= max(Enr_end)=; run; data _DenomInt; set _DenomInt; TopCount = 0; if Enr_End >= &QUERYFROM. and Enr_Start <= &QUERYTO. then TopCount = 1; AgeCount = 0; if TopCount and (MaxAgeDate >= Enr_Start and MinAgeDate <= Enr_End) then AgeCount = 1; run; proc sql noprint; create table _Coverage as select PatId, MedCov, DrugCov, max(MedCov+DrugCov) as MaxNumCov from _DenomInt where TopCount=1 group by PatId; quit; proc means data=_Coverage nway noprint; var MedCov DrugCov; class PatId; where MaxNumCov < 2; output out=_Coverage(drop=_:) min=; run; data _Coverage; set _Coverage; *Flag that indicates if a patient has at least one episode with both Drug and Medical coverage or if he always has the same coverage pattern for all episodes; PatHasBothCovOROneCovPatternDum=1; *This case means that we have at leat one episode without MedCov and at least one without DrugCov; if MedCov=0 and DrugCov=0 then PatHasBothCovOROneCovPatternDum=0; run; data _DenomInt; merge _DenomInt _Coverage(in=b keep=PatId PatHasBothCovOROneCovPatternDum); by PatId; if not b then PatHasBothCovOROneCovPatternDum=1; MedCovDum=MedCov; DrugCovDum=DrugCov; run; %MACRO WRAPPER; %IF %UPCASE("&COVERAGE.") eq %STR("MD") or %UPCASE("&COVERAGE.") eq %STR("DM") %THEN %DO; data _DenomInt; set _DenomInt; if PatHasBothCovOROneCovPatternDum=0 then do; MedCovDum=1; DrugCovDum=1; end; run; %END; %IF %UPCASE("&COVERAGE.") eq %STR("M") %THEN %DO; data _DenomInt; set _DenomInt; PatHasBothCovOROneCovPatternDum=1; DrugCovDum=1; run; %END; %IF %UPCASE("&COVERAGE.") eq %STR("D") %THEN %DO; data _DenomInt; set _DenomInt; PatHasBothCovOROneCovPatternDum=1; MedCovDum=1; run; %END; %MEND WRAPPER; %WRAPPER; proc sql noprint; create table _waterfall_ ( CritNum num format=best., CritDesc char(150) format=$150., CritCnt num format=best.); quit; %MS_ATTRITIONTABLE(INFILE=_DenomInt,CRITFLAG=TopCount,CLASSVARS=PatId,WHERE=1,EXCLUDEDVAR=, OUTFILE=_waterfall_, CRITDESC="Initial Member Count - Members with a non-missing birth date/sex at any enrollment episode overlapping the query period", CRITNUM=1, CRITCNTNAME=CritCnt, ADDITIONALVARS= ); %MS_ATTRITIONTABLE(INFILE=_DenomInt,CRITFLAG=DrugCovDum,CLASSVARS=PatId,WHERE=TopCount=1,EXCLUDEDVAR=, OUTFILE=_waterfall_, CRITDESC="Exclusion – Members must be excluded if they only have episodes with DrugCov=N and MedCov=Y during the query period", CRITNUM=2, CRITCNTNAME=CritCnt, ADDITIONALVARS= ); %MS_ATTRITIONTABLE(INFILE=_DenomInt,CRITFLAG=MedCovDum,CLASSVARS=PatId,WHERE=TopCount=1 and DrugCovDum=1,EXCLUDEDVAR=, OUTFILE=_waterfall_, CRITDESC="Exclusion – Members must be excluded if they only have episodes with DrugCov=Y and MedCov=N during the query period", CRITNUM=3, CRITCNTNAME=CritCnt, ADDITIONALVARS= ); %MS_ATTRITIONTABLE(INFILE=_DenomInt,CRITFLAG=PatHasBothCovOROneCovPatternDum,CLASSVARS=PatId,WHERE=TopCount=1 and DrugCovDum=1 and MedCovDum=1,EXCLUDEDVAR=, OUTFILE=_waterfall_, CRITDESC="Exclusion – Members must be excluded if they only have episodes with DrugCov=Y and MedCov=N and DrugCov=N and MedCov=Y during the query period", CRITNUM=4, CRITCNTNAME=CritCnt, ADDITIONALVARS= ); %MS_ATTRITIONTABLE(INFILE=_DenomInt,CRITFLAG=AgeCount,CLASSVARS=PatId,WHERE=TopCount=1 and DrugCovDum=1 and MedCovDum=1 and PatHasBothCovOROneCovPatternDum=1,EXCLUDEDVAR=, OUTFILE=_waterfall_, CRITDESC="Exclusion - Members must satisfy the age range condition within the query period", CRITNUM=5, CRITCNTNAME=CritCnt, ADDITIONALVARS= ); proc sql noprint; create table _waterfall as select grp.Group, wat.CritNum, wat.CritDesc, wat.CritCnt from _waterfall_ as wat, _grouplist as grp; quit; data _DenomInt; set _DenomInt(where=(DrugCovDum=1 and MedCovDum=1 and PatHasBothCovOROneCovPatternDum=1)); 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 TopCount AgeCount DrugCov MedCov DrugCovDum MedCovDum PatHasBothCovOROneCovPatternDum; run; proc datasets library=work; change _MasterQueryfile=_MasterQueryfile_; quit; proc sql noprint; create table _MasterQueryfile as Select mast.* from _MasterQueryfile_ as mast, _denomint as enrol where mast.PatId = enrol.PatId and enrol.Enr_Start <= mast.ADate <= enrol.Enr_End; quit; proc datasets library=work nowarn; delete _MasterQueryfile_; quit; /*---------------------------------------------------------------------------------------------------*/ /* 5.0 Create FDATE table */ /*---------------------------------------------------------------------------------------------------*/ /*Find Min date per QueryGroup Date*/ proc means noprint data =_MasterQueryfile nway; var ADate; Class PatId Group; where &QUERYFROM. <= ADate <= &QUERYTO. and Query=1; /*keeping only Adates that are index dates candidates*/ output out=_FDateTable(drop=_:) min = MinDt; run; proc sql noprint; create table _condcoverage0 as Select fdats.*, claims.ADate, claims.WashPer, claims.CondLookTyp, claims.CondFrom, claims.CondTo, claims.inclusion from _MasterQueryfile(where=(cond)) as claims, _FDateTable as fdats where fdats.PatId = claims.PatID and fdats.Group=claims.Group and claims.ADate >= fdats.MinDt + claims.CondFrom order by PatId, group, ADate; quit; proc sort nodupkey data = _condcoverage0(rename=Adate=Cdate); by PatId Group inclusion CondFrom CondTo Cdate; run; /*Create QueryGroup Date Variables*/ proc transpose data = _FDateTable out = _FDateTable(drop=_NAME_); id Group; var MinDt; by PatId; run; proc transpose data=_querysettings out=_Groups(drop=_NAME_); id Group; run; data _Groups; set _Groups; if _N_ = 0; run; data _FDateTable; set _FDateTable; if _N_=0 then set _Groups; format MinFdt MMDDYY10. MaxFdt MMDDYY10.; MinFdt = min(&GROUPVECT2.,.); MaxFdt = max(&GROUPVECT2.,.); run; /*Reduce enrollment to study patients*/ data _Enrollment(keep=PatId Enr_Start Enr_End sex birth_date MinAgeDate MaxAgeDate); if 0 then set _FDateTable; declare hash ht (hashexp:16, dataset:'_FDateTable'); ht.definekey('PatId'); ht.definedata(ALL: 'YES'); ht.definedone(); do until(eof1); set _DenomInt end=eof1; if ht.find()=0 then do; output _Enrollment; end; end; stop; run; %LET I=1; /*---------------------------------------------------------------------------------------------------*/ /* 6.0 For each query drug and patient, assess prevalent vs. incident, retrieve */ /* dispensings during query period to compute usage by patient and query drug. */ /*---------------------------------------------------------------------------------------------------*/ %global NQUERYGROUP; %MACRO LOOPTHROUGH(); /*Loop for each query drug*/ data _null_; set _GroupList end=fin; if fin then call symput('NQUERYGROUP',_n_); run; %PUT &NQUERYGROUP.; %DO i = 1 %TO &NQUERYGROUP.; data _null_; set _GroupList; if _n_ = &i. then call symput('ITGROUP',strip(Group)); run; %PUT &ITGROUP; data _loopsettings; set _querysettings(where=(Group in("&ITGROUP."))); drop Group; run; %LET INCLREQD=0; /*No INCLUSION for this QUERYGROUP*/ %LET EXCLREQD=0; /*No EXCLUSION for this QUERYGROUP*/ data _LoopCondFile; set _condfile; if Group = "&ITGROUP."; if Inclusion=1 then call symput('INCLREQD',1); if Inclusion=0 then call symput('EXCLREQD',1); run; %PUT &INCLREQD. &EXCLREQD.; /*For each query drug get index date for each Pat Id*/ data _SDFDateTable(keep=PatId WashTyp WashPer EnrDays); set _FDateTable; if _N_ = 1 then set _loopsettings; if &ITGROUP.; run; /*Check for index date minus washout days for dispensings of query drugs and identify dispensings of query drug between index date and end of query period*/ data _Quantity; if 0 then set _SDFDateTable; declare hash ht (hashexp:16, dataset:'_SDFDateTable'); ht.definekey('PatId'); ht.definedata(ALL: 'YES'); ht.definedone(); do until(eof1); set _MasterQueryfile(where=((Group in("&ITGROUP.") or IGroup in("&ITGROUP.","_ALLGROUPS_")) and (query or incid))) end=eof1; if ht.find()=0 then do; InQuery = 0; if Group in("&ITGROUP.") and &QUERYFROM. <= ADate <= &QUERYTO. then InQuery = 1; output _Quantity; end; end; stop; run; proc sort data = _Quantity out=_IEpisode; by PatId Adate descending InQuery; proc sort nodupkey data = _IEpisode; by PatId Adate; run; /*create independent treatment episodes*/ data _IEpisode; set _IEpisode(keep=PatId WashPer WashTyp InQuery Adate); by PatId; format lAdate mmddyy10. indexdt mmddyy10.; lAdate = lag(Adate); if first.PatId then do; lAdate = .; episode=0; indexdt=.; end; else diff = Adate - lAdate - 1; if WashTyp in('MIN') then do; if InQuery and diff = . then do; indexdt=ADate; episode=episode+1; output; end; end; if WashTyp in('MULT','SING') then do; if InQuery and (diff = . or diff >= WashPer) then do; indexdt=ADate; episode=episode+1; output; end; end; retain episode; drop InQuery lAdate diff; run; /*Defensive coding*/ Proc SQL Noprint; Create Table _CleanAdates as Select IDates.PatId, IDates.WashPer, IDates.WashTyp, IDates.ADate, IDates.Indexdt, (IDates.ADate - enrol.Enr_Start) as EnrNumDays From _IEpisode as IDates, _Enrollment as enrol Where IDates.PatId = enrol.PatId and enrol.Enr_Start <= IDates.ADate <= enrol.Enr_End Order by PatId, ADate; quit; /*---------------------------------------------------------------------------------------------------*/ /* 6.1 If two or more records for a dispensing date of the same drug, take the max RxSup and RxAmt*/ /*---------------------------------------------------------------------------------------------------*/ proc means noprint data = _Quantity nway; var RxAmt RxSup; class PatId ADate; id Group WashTyp EnrDays; where InQuery; output out=_Quantity_(drop=_:) sum(RxAmt RxSup) = RxAmt RxSup N(RxAmt) = NumDipensing; run; /*claims must be within eligibility*/ proc sql noprint; create table _Quantity as Select qnt.*, enrol.MinAgeDate, enrol.MaxAgeDate, (qnt.ADate - enrol.Enr_Start) as EnrNumDays from _Quantity_ as qnt, _Enrollment as enrol Where qnt.PatId = enrol.PatId and enrol.Enr_Start <= qnt.ADate <= enrol.Enr_End Order by PatId, ADate; Quit; /*Assessing NoClaimsBef status at start of episode only*/ /*The WNumDays variable is in a and b, however, for common will have the same value*/ data _MasterTable_; merge _Quantity(in=a) _CleanAdates(in=b); by PatId ADate; if a; /*MODIF WAVE3*/ RxAmt = floor(RxAmt); label RxAmt="RxAmt"; label RxSup="RxSup"; NoClaimsBef = 0; if b then NoClaimsBef = 1; Enr = 0; if EnrNumDays>=0 and EnrNumDays >= EnrDays then Enr = 1; Wash = 0; if EnrNumDays>=0 and EnrNumDays >= WashPer then Wash = 1; * In case Washper is greater than EnrDays; RxYear = Year(ADate); RxMonth = Month(ADate); GClaim = 1; /*MODIF WAVE2 HERE*/ QueryEnrDum = 0; if Enr then QueryEnrDum = 1; QueryWashDum = 0; if QueryEnrDum and Wash then QueryWashDum = 1; IncQueryDum = 0; if QueryEnrDum and QueryWashDum and NoClaimsBef then IncQueryDum = 1; if MinAgeDate <= ADate <= MaxAgeDate; run; %MS_ATTRITIONTABLE(INFILE=_MasterTable_,CRITFLAG=GClaim,CLASSVARS=PatId,WHERE=1,EXCLUDEDVAR=, OUTFILE=_waterfall, CRITDESC="Exclusion - Members must have at least one QUERYGROUP claim within the query period", CRITNUM=6, CRITCNTNAME=CritCnt, ADDITIONALVARS=Group="&ITGROUP." ); %MS_ATTRITIONTABLE(INFILE=_MasterTable_,CRITFLAG=QueryEnrDum,CLASSVARS=PatId,WHERE=1,EXCLUDEDVAR=, OUTFILE=_waterfall, /*MODIF WAVE3*/ CRITDESC="Exclusion - Members must have at least one QUERYGROUP claim satisfying the enrollment criterion specified by the 'EnrDays' parameter", CRITNUM=7, CRITCNTNAME=CritCnt, ADDITIONALVARS=Group="&ITGROUP." ); %MS_ATTRITIONTABLE(INFILE=_MasterTable_,CRITFLAG=QueryWashDum,CLASSVARS=PatId,WHERE=1,EXCLUDEDVAR=, OUTFILE=_waterfall, /*MODIF WAVE3*/ CRITDESC="Exclusion - Members must have at least one QUERYGROUP claim satisfying the enrollment criterion specified by the Query 'WashPer' parameter", CRITNUM=8, CRITCNTNAME=CritCnt, ADDITIONALVARS=Group="&ITGROUP." ); %MS_ATTRITIONTABLE(INFILE=_MasterTable_,CRITFLAG=IncQueryDum,CLASSVARS=PatId,WHERE=1,EXCLUDEDVAR=, OUTFILE=_waterfall, /*MODIF WAVE3*/ CRITDESC="Exclusion - Members must have at least one QUERYGROUP claim that meets the incidence criteria [no QUERYGROUP claim in the prior 'WashPer' days]", CRITNUM=9, CRITCNTNAME=CritCnt, ADDITIONALVARS=Group="&ITGROUP." ); /*Merge claims with valid pre-existing condition periods*/ proc sql noprint; create table _MasterTable as Select claims.*, pdates.CondFrom, pdates.CondTo, pdates.CDate, pdates.CondLookTyp, pdates.Inclusion from _MasterTable_ as claims left join _condcoverage0(where=(Group in("&ITGROUP."))) as pdates on claims.PatId = pdates.PatId order PatId, ADate, CDate; quit; proc datasets library=work nolist nowarn; delete _MasterTable_; quit; /*Identify claims overlapping pre-existing condition period*/ data _MasterTable; set _MasterTable; PIncCondDum = 0; PExcCondDum = 1; /*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*/ PIncCondDum = 1; end; if cdate ne . then do; if Adate + CondFrom <= CDate <= Adate + CondTo then do; if inclusion=1 then do; HadCond = 1; PIncCondDum = 1; end; else do; HadCond = 2; PExcCondDum = 0; end; end; end; run; %MACRO WRAPPER; %IF &EXCLREQD. = 1 %THEN %DO; proc sort nodupkey data=_LoopCondFile; by CondFrom CondTo; where inclusion=0; run; data _LoopCondFile; set _LoopCondFile end=fin; where inclusion=0; ExclNum=_N_; if fin then call symput("NumExcl",ExclNum); run; %PUT &NumExcl.; proc sql noprint; create table _ExclEligFlags as Select claims.Patid, claims.Adate, edates.Enr_Start, edates.Enr_End, lookup.CondFrom, lookup.CondTo, lookup.ExclNum from _LoopCondFile(keep=CondFrom CondTo ExclNum) as lookup, _MasterTable as claims, _Enrollment as edates where claims.PatId = edates.PatId; quit; data _ExclEligFlags; set _ExclEligFlags; meet=1; /*Enrollment episode completely overlaps this exclusion period*/ if Enr_Start <= Adate + CondFrom and Enr_end >= Adate + CondTo then output; keep Patid Adate ExclNum meet; run; proc means data=_ExclEligFlags nway noprint; var meet; class PatId Adate ExclNum; output out=_ExclEligFlags(drop=_:) max=; run; proc means data=_ExclEligFlags nway noprint; var meet; class PatId Adate; 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=&NumExcl.; keep PatId Adate; run; %END; %ELSE %DO; data _ExclEligFlags; set _MasterTable(obs=1 keep=Patid Adate); if _N_<0; run; %END; %MEND WRAPPER; %WRAPPER; proc sort data = _MasterTable; by PatId ADate descending HadCond; run; proc sort nodupkey data = _MasterTable; by PatId Adate; run; data _MasterTable; merge _MasterTable(in=a) _ExclEligFlags(in=b); by PatId Adate; if a; if &EXCLREQD. = 1 then ExclCritmeet=0; else ExclCritmeet=1; if b then ExclCritmeet=1; run; /* Note: At this step (_MasterTable) and for each unique Adate, we know if the patient meets or doesn`t meet the INCL/ESCL 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 */ data _MasterTable; set _MasterTable; /*HadCond = 2 if any exclusion, HadCond = 1 Only inclusions, HadCond = 0 if no inclusion nor exclusions*/ if NoClaimsBef = 1 and Wash=1 /*MODIF WAVE2 HERE*/ and Enr = 1 and HadCond = 1 and ExclCritmeet = 1 then incident = 1; else incident = 0; /*MODIF WAVE2 HERE*/ ExclEligDum = 0; if (QueryEnrDum and QueryWashDum and IncQueryDum) and ExclCritmeet then ExclEligDum = 1; ExcCondDum = 0; if (QueryEnrDum and QueryWashDum and IncQueryDum and ExclEligDum) and PExcCondDum then ExcCondDum = 1; IncCondDum = 0; if (QueryEnrDum and QueryWashDum and IncQueryDum and ExclEligDum and ExcCondDum) and PIncCondDum then IncCondDum = 1; run; %MS_ATTRITIONTABLE(INFILE=_MasterTable,CRITFLAG=ExclEligDum,CLASSVARS=PatId,WHERE=Group="&ITGROUP.",EXCLUDEDVAR=, OUTFILE=_waterfall, /*MODIF WAVE3*/ CRITDESC="Exclusion - Members must have at least one QUERYGROUP claim satisfying the Exclusion enrollment requirement", CRITNUM=10, CRITCNTNAME=CritCnt, ADDITIONALVARS=Group="&ITGROUP." ); %MS_ATTRITIONTABLE(INFILE=_MasterTable,CRITFLAG=ExcCondDum,CLASSVARS=PatId,WHERE=Group="&ITGROUP.",EXCLUDEDVAR=, OUTFILE=_waterfall, /*MODIF WAVE3*/ CRITDESC="Exclusion - Members must have at least one QUERYGROUP claim satisfying the Exclusion conditions", CRITNUM=11, CRITCNTNAME=CritCnt, ADDITIONALVARS=Group="&ITGROUP." ); %MS_ATTRITIONTABLE(INFILE=_MasterTable,CRITFLAG=IncCondDum,CLASSVARS=PatId,WHERE=Group="&ITGROUP.",EXCLUDEDVAR=, OUTFILE=_waterfall, /*MODIF WAVE3*/ CRITDESC="Exclusion - Members must have at least one QUERYGROUP claim satisfying the Inclusion conditions", CRITNUM=12, CRITCNTNAME=CritCnt, ADDITIONALVARS=Group="&ITGROUP." ); /*FHadCondDt defined as the first Query claim to respect the pre-existing condition criterion*/ proc means noprint data = _MasterTable nway; var ADate; class PatId; where HadCond = 1 and ExclCritmeet = 1; output out=_FHadCondDt(drop=_:) min=FHadCondDt; run; /*keeping only claims thereafter*/ data _MasterTable; if 0 then set _FHadCondDt; declare hash ht (hashexp:16,dataset:"_FHadCondDt"); ht.definekey('PatId'); ht.definedata(ALL:'YES'); ht.definedone(); do until(eof1); set _MasterTable end=eof1; if ht.find()=0 then do; if ADate >= FHadCondDt then output; end; end; stop; run; /*Get first incident index date*/ proc means noprint data = _MasterTable nway; var ADate; Class PatId; where incident = 1; output out=_FIncDt(drop=_:) min=FIncDt; run; data _MasterTable; if 0 then set _FIncDt; declare hash ht (hashexp:16,dataset:"_FIncDt"); ht.definekey('PatId'); ht.definedata(ALL:'YES'); ht.definedone(); do until(eof1); set _MasterTable end=eof1; if ht.find() ne 0 then do; call missing(FIncDt); end; output; end; stop; run; /*MODIF WAVE2 HERE BLOC START*/ data _premaster1 _premaster2; set _MasterTable; if WashTyp = "SING" and Incident=1 then output _premaster1; else output _premaster2; run; proc sort data = _premaster1;; by Group PatId ADate; run; data _premaster1; set _premaster1; by Group PatId; if not first.PatID then Incident=0; run; data _MasterTable; set _premaster1 _premaster2; run; /*MODIF WAVE2 HERE BLOC END*/ %IF &I.=1 %THEN %DO; data DPLocal.&REQUESTID.&RUNID._MasterTable; set _MasterTable; run; %END; %ELSE %DO; proc append base = DPLocal.&REQUESTID.&RUNID._MasterTable data = _MasterTable force; run; %END; %END; %MEND LOOPTHROUGH; %LOOPTHROUGH(); proc sort data = _waterfall; by Group CritNum; run; data MSOC.&REQUESTID.&RUNID._AttritionTable; set _Waterfall; by group; Excluded = lag(CritCnt) - CritCnt; if first.group then Excluded = .; rename CritCnt = Remaining; run; %LET I=1; %MACRO DENOMLOOP(); data _null_; set _GroupList end=fin; if fin then call symput('NQUERYGROUP',_n_); run; %PUT &NQUERYGROUP.; %DO i = 1 %TO &NQUERYGROUP.; data _null_; set _GroupList; if _n_ = &I. then do; call symput('ITGROUP',strip(Group)); end; run; %PUT &ITGROUP; data _null_; set _querysettings; if Group = "&ITGROUP." then do; call symput('GROUPWASHOUT',WashPer); call symput('GROUPWASHTYP',strip(WashTyp)); /*MODIF WAVE2 HERE*/ call symput('GROUPENRDAYS',EnrDays); end; run; %PUT &GROUPWASHOUT. &GROUPWASHTYP. &GROUPENRDAYS.; %LET CONDLOOKTYP=; data _null_; set _condfile; if Group = "&ITGROUP." then do; call symput('CONDLOOKTYP',strip(CondLookTyp)); end; run; %PUT &CONDLOOKTYP; %LET INCLREQD=0; /*No INCLUSION for this QUERYGROUP*/ %LET EXCLREQD=0; /*No EXCLUSION for this QUERYGROUP*/ data _LoopCondFile; set _condfile; if Group = "&ITGROUP."; if Inclusion=1 then call symput('INCLREQD',1); if Inclusion=0 then call symput('EXCLREQD',1); run; %PUT &INCLREQD. &EXCLREQD.; %MACRO WRAPPER(); /*If pre-existing conditions are required need to select members with pre-existing conditions*/ %IF %EVAL(&INCLREQD.>0 or &EXCLREQD.>0) %THEN %DO; %IF &INCLREQD. = 1 %THEN %DO; proc sort nodupkey data = _masterqueryfile out = _cond(keep=PatId ADate CondFrom CondTo); by PatId ADate CondFrom CondTo; where cond and Group in("&ITGROUP.") and Inclusion = 1; run; /*Must re-define enrollment episodes to scan only when members meeting the pre-existing condition requirement*/ proc sql noprint; Create table _denomint2 as Select enrol.*, claims.ADate, claims.CondFrom, claims.CondTo, max(claims.Adate - claims.CondTo,enrol.Enr_Start) as PDate format mmddyy10., min(claims.Adate - claims.CondFrom,enrol.Enr_End) as EDate format mmddyy10. from _Denomint as enrol, _cond as claims where enrol.PatId = claims.PatId and enrol.Enr_Start <= claims.Adate - claims.CondFrom and enrol.Enr_End >= claims.Adate - claims.CondTo order PatId, EligEPisode, PDate; quit; /*for a same elig record, identify overlapping PDate - EDate intervals*/ data _MDenomint; set _denomint2; by PatId EligEpisode; format lEDate mmddyy10.; lEDate = lag(EDate); diff=PDate-lEDate-1; if first.EligEpisode then do; lPDate=.; diff=.; PEpisode=1; end; else do; if diff > 0 then do; PEpisode = PEpisode + 1; end; end; retain Pepisode; run; proc means data=_MDenomint nway noprint; var PDate EDate; class Patid EligEpisode PEpisode; id Enr_Start Enr_end Birth_Date MinAgeDate MaxAgeDate sex; output out=_MDenomint(drop=_:) min(PDate)= max(EDate)=; run; %END; %ELSE %DO; data _MDenomInt; set _DenomInt(where=(enr_end >= &QUERYFROM. and enr_start <= &QUERYTO.)); format PDate EDate mmddyy10.; PDate = max(Enr_Start,&QUERYFROM.); EDate = min(Enr_End,&QUERYTO.); PEpisode = EligEpisode; keep PatId PDate EDate Enr_Start Enr_end Birth_Date MinAgeDate MaxAgeDate sex EligEpisode PEpisode; run; %END; %IF &EXCLREQD. = 1 %THEN %DO; proc sort nodupkey data=_LoopCondFile; by CondFrom CondTo; where inclusion=0; run; data _LoopCondFile; set _LoopCondFile end=fin; where inclusion=0; ExclNum=_N_; if fin then call symput("NumExcl",ExclNum); run; %PUT &NumExcl.; proc sql noprint; create table _MDenomint_ as Select pdates.*, lookup.CondFrom, lookup.CondTo, lookup.ExclNum from _LoopCondFile(keep=CondFrom CondTo ExclNum) as lookup, _MDenomint as pdates order by PatId, EligEpisode, PEpisode, ExclNum; quit; data _MDenomint; set _MDenomint_; format MaxPDate MinEDate mmddyy10.; if CondFrom < 0 then MaxPdate = Enr_Start - CondFrom; else MaxPDate = PDate; if CondTo > 0 then MinEDate = Enr_End - CondTo; else MinEDate = EDate; /*check if above remain consistent*/ PDate = max(PDate,MaxPdate); EDate = min(EDate,MinEDate); run; proc means data=_MDenomint nway noprint; var PDate EDate; class Patid EligEpisode PEpisode; id Enr_Start Enr_end Birth_Date MinAgeDate MaxAgeDate sex; output out=_MDenomint(drop=_: where=(PDate<=EDate)) min(PDate)= max(EDate)=; run; /*go get exclusion claims*/ proc sort nodupkey data = _masterqueryfile out = _cond(keep=PatId ADate CondFrom CondTo); by PatId ADate CondFrom CondTo; where cond and Group in("&ITGROUP.") and Inclusion = 0; run; data _cond; set _cond; format ExcPdate ExcEdate mmddyy10.; ExcPdate = Adate - CondTo; ExcEdate = Adate - CondFrom; run; proc sort data = _cond; by PatId ExcPdate; run; data _cond; set _cond; by Patid; lExcEdate = lag(ExcEdate); diff = ExcPDate - lExcEdate - 1; if first.PatId then do; lExcEdate = .; diff = .; ExcEpisode = 1; end; else do; if diff > 0 then ExcEpisode = ExcEpisode + 1; end; retain ExcEpisode; run; proc means noprint data = _cond nway; var ExcPdate ExcEdate; class PatId ExcEpisode; output out=_cond(drop=_: ExcEpisode) min(ExcPdate)= max(ExcEdate)=; run; /*left join here with _MDenomint (watch out for multiple merge)*/ proc sql noprint; create table _MDenomint_ as Select pdates.*, exdates.ExcPdate, exdates.ExcEdate from _MDenomint as pdates left join _cond as exdates on pdates.PatId = exdates.PatId; quit; data _MDenomint(where=(PDate<=EDate)); set _MDenomint_; if (ExcPDate <= PDate and PDate <= ExcEDate <= EDate) then do; PDate = ExcEDate + 1; EDate = EDate; output; end; else if ExcPDate > PDate and ExcEDate < EDate then do; PDate = PDate; EDate = ExcPDate - 1; output; PDate = ExcEDate + 1; EDate = EDate; output; end; else if ExcPDate <= EDate and ExcEDate >= EDate then do; PDate = PDate; EDate = ExcPDate-1; output; end; else output; run; proc sort data = _MDenomint; by PatId PDate; run; data _MDenomint; set _MDenomint; by PatId; lEDate = lag(EDate); diff = PDate - lEDate- 1; if first.PatID then do; lEDate = .; diff = .; PEpisode = 1; end; else do; if diff > 0 then PEpisode = PEpisode + 1; end; retain PEpisode; run; proc means noprint data = _MDenomint nway; var PDate EDate; class PatId EligEpisode PEpisode; id Enr_Start Enr_end Birth_Date MinAgeDate MaxAgeDate sex; output out=_MDenomint(drop=_:) min(PDate)= max(EDate)=; run; %END; data _MDenomInt; set _MDenomInt(where=(enr_end >= &QUERYFROM. and enr_start <= &QUERYTO.)); Enr_End = min(Enr_End,EDate); if enr_end < MinAgeDate then delete; LastAgeGroup=&NUMAGECAT.; do i=&NUMAGECAT. to 1 by -1; Threshdate=intnx(scan("&AGETYP.",i),birth_date,scan("&AGETHRESH.",i),'sameday'); if Threshdate<= min(&QUERYTO.,Enr_end) then do; LastAgeGroup =i; FirstAgeGroup=i; if Threshdate > max(&QUERYFROM.,Enr_Start) then do; do j=i-1 to 1 by -1; if intnx(scan("&AGETYP.",j),birth_date,scan("&AGETHRESH.",j),'sameday') <= max(&QUERYFROM.,Enr_Start) then do; FirstAgeGroup=j; leave; end; end; end; leave; end; end; drop i j threshdate; run; %END; /*end for either inclusion or exclusion*/ %ELSE %DO; data _MDenomInt; set _DenomInt(where=(enr_end >= &QUERYFROM. and enr_start <= &QUERYTO.)); format PDate mmddyy10.; PDate = .; PEpisode=1; run; %END; %MEND WRAPPER; %WRAPPER(); /*Creating Claims Dates Listing - One record per date per QueryGroup*/ proc sort nodupkey data= _MasterQueryfile out=_Claims(keep=PatId ADate Group); by PatId ADate; where (Group in("&ITGROUP.") or IGroup in("&ITGROUP.","_ALLGROUPS_")) and (Query or Incid); run; /*list for pre-cleaning*/ proc sort nodupkey data=_Claims out=_ClaimList(keep=patid); by PatId; run; /*Creating Dates*/ data _AllMember; Merge _MDenomInt(in=a) _ClaimList(in=b); /*Sorted*/ by PatId; if a; withclaims = 0; if a and b then withclaims=1; format QueryStartDate mmddyy10.; /*Minimum date the patient can have an HOI*/ QueryStartDate=Max(&QUERYFROM.,MinAgeDate,enr_start,PDate); /*Enr_Start must be independent of MinAgeDate*/ /*MODIF WAVE2 HERE*/ Enr_Start=Max(&QUERYFROM.-Max(&GROUPWASHOUT.,&GROUPENRDAYS.),enr_start,PDate-Max(&GROUPWASHOUT.,&GROUPENRDAYS.)); enr_end=min(&QUERYTO.,enr_end,MaxAgeDate); if enr_end >= enr_start; keep PatId EligEpisode PEpisode birth_date sex MinAgeDate MaxAgeDate Enr_Start Enr_End LastAgeGroup FirstAgeGroup PDate withclaims QueryStartDate; run; /*RECAP: *Elig episodes now have been resized not to exceed this querygroup washout period prior to the patients *Starting to be at risk of having and event; *adding birth_dates and sex to claims and keeping claims within eligibility*/ Proc SQL Noprint; Create Table _Claims2 as Select Enrol.*, claims.ADate, claims.Group From _AllMember(where=(withclaims=1)) as Enrol, _Claims as claims Where Enrol.PatId=claims.PatId and Enrol.enr_Start <= claims.ADate <= Enrol.enr_end order Patid, EligEpisode, PEpisode, Adate; /*remove claims outside of period BEFORE AND AFTER*/ quit; /*Depile to create begin and end intervals (what if more than 1 enroll episode, will have duplicate claim when incident respective to own, should be ok)*/ data _DenomToLoop; set _AllMember(in=a keep=Patid EligEpisode Pepisode Enr_Start Sex birth_date MinAgeDate MaxAgeDate QueryStartDate FirstAgeGroup LastAgeGroup rename=Enr_Start=Adate) _Claims2(keep=Patid EligEpisode Pepisode ADate Sex birth_date MinAgeDate MaxAgeDate QueryStartDate FirstAgeGroup LastAgeGroup Group /*MODIF WAVE2 HERE*/Enr_Start) _AllMember(in=b keep=Patid EligEpisode Pepisode Enr_End Sex birth_date MinAgeDate MaxAgeDate QueryStartDate FirstAgeGroup LastAgeGroup /*MODIF WAVE2 HERE*/Enr_Start rename=Enr_End=Adate); by Patid EligEpisode Pepisode ADate; /*MODIF WAVE2 HERE*/ if a then do; Group="BEGINELIG"; Enr_Start=ADate; end; if b then Group="ENDELIG"; run; /*Creating HOI Free episodes from gapless Enrolment sequences*/ data _FindEpisodes; set _DenomToLoop; by PatId EligEpisode PEpisode; /*compute length of HOI free period*/ lADate=lag(Adate); lGroup=lag(Group); if first.PEpisode then do; lADate=.; lGroup=.; end; if lADate ne . then diff=ADate-lADate+1; /*Compute StartDate and EndDate of HOI free period*/ format lADate StartDate EndDate mmddyy10.; StartDate=lADate+1-(lGroup eq "BEGINELIG"); EndDate=ADate; /*Adate can be either a QueryGroup Incid/HOI claim or the end of elig sequence*/ /*Minimum date the patient can have an HOI in this new broken down period*/ QueryStartDate=Max(QueryStartDate,StartDate); /*Patient must meet age criteria*/ if MinAgeDate <= EndDate and MaxAgeDate >= StartDate; /*removes first record of episode*/ if diff ne .; /*Keeping episodes overlapping the Study Period - left truncated in the event of a INICD claim*/ if EndDate >= &QUERYFROM. and StartDate <= &QUERYTO.; if QueryStartDate <= EndDate; /*Time increments*/ FirstPer=intck("Months",&QUERYFROM.,QueryStartDate)+1; LastPer=intck("Months",&QUERYFROM.,EndDate)+1; keep Patid birth_date sex StartDate QueryStartDate EndDate /*MODIF WAVE2 HERE*/Enr_Start first: last:; /*RECALL: *StartDate=Start Date of the HOI Free continuous elig period (can includes washout time that may fall outside Study Period); *QueryStartDate= Start Date of the HOI Free episode overlapping the Query Period (for patient days calcs); *EndDate=End Date of the HOI Free episode*/ run; /*Determine the date at which the patient can no longer be eligible*/ /*MODIF WAVE2 HERE*/ %MACRO WRAPPER; %IF %STR("&GROUPWASHTYP.")=%STR("SING") %THEN %DO; proc means noprint data = Dplocal.&REQUESTID.&RUNID._MasterTable nway; var ADate; class PatId; where (Group in("&ITGROUP.")) and (Incident); output out=_TermDtTable(drop=_:) min=TermDt; run; %END; %ELSE %DO; proc means noprint data = _MasterQueryfile nway; var ADate; class PatId; where (Group in("&ITGROUP.") or IGroup in("&ITGROUP.","_ALLGROUPS_")) and (Query or Incid); output out=_TermDtTable(drop=_:) min=TermDt; run; %END; %MEND WRAPPER; %WRAPPER; data _Patient_days_M _Patient_days_F _Patient_days_U; merge _FindEpisodes(in=a) _TermDtTable; by Patid; if a; washtyp="&GROUPWASHTYP."; if sex='M' then output _Patient_days_M; else if sex='F' then output _Patient_days_F; else if sex='U' then output _Patient_days_U; run; %MACRO ACCUMDENOM(sex=); /*Here we will accumulate patients and patient-days to obtain up to 3 records at the end which will constitute the denominators for the modular program*/ data _Patient_days_&sex.; set _Patient_days_&sex. end=eof; by Patid; /*---------------------------------------------------------------------------------------------------*/ /* - - - NAMING CONVENTION OF ARRAYS: */ /* prefix _ means temporary vector */ /* prefix Inc = incident */ /* prefix Prev = prevalence */ /* */ /* Suffix AG = Age group stratification */ /* Suffix Per = period stratification (e.g., weekly, monthly, quarterly) */ /* Suffix Y = year stratification */ /* */ /* When "Days" is not included in name = we`re counting "members" */ /* When "Days" is included in name = we`re counting "members-days"; */ /*---------------------------------------------------------------------------------------------------*/ /*Age Groups*/ array _IncAg(*) _IncAg1-_IncAg&NUMAGECAT. ; array _PrevAg(*) _PrevAg1-_PrevAg&NUMAGECAT.; array PrevAg(*) PrevAg1-PrevAg&NUMAGECAT.; array PrevDaysAg(*) PrevDaysAg1-PrevDaysAg&NUMAGECAT.; array IncAg(*) IncAg1-IncAg&NUMAGECAT.; array IncDaysAg(*) IncDaysAg1-IncDaysAg&NUMAGECAT.; /*Periodicity*/ array _PrevPer(*) _PrevPer1-_PrevPer&NUMPER.; array _IncPer(*) _IncPer1-_IncPer&NUMPER. ; array PrevPer(*) PrevPer1-PrevPer&NUMPER.; array PrevDaysPer(*) PrevDaysPer1-PrevDaysPer&NUMPER.; array IncPer(*) IncPer1-IncPer&NUMPER.; array IncDaysPer(*) IncDaysPer1-IncDaysPer&NUMPER.; /*Year*/ array _PrevY(*) _PrevY&FROMY.-_PrevY&TOY. ; array _IncY(*) _IncY&FROMY.-_IncY&TOY. ; array PrevY(*) PrevY&FROMY.-PrevY&TOY.; array PrevDaysY(*) PrevDaysY&FROMY.-PrevDaysY&TOY.; array IncY(*) IncY&FROMY.-IncY&TOY.; array IncDaysY(*) IncDaysY&FROMY.-IncDaysY&TOY.; if first.patid then do; call missing(of _Inc:); call missing(of _Prev:); end; do i=FirstAgeGroup to LastAgeGroup; format StartAgeDate EndAgeDate mmddyy10. ; /*Compute Strata Start/End date*/ StartAgeDate=intnx(scan("&AGETYP.",i),birth_date,scan("&AGETHRESH.",i),'sameday'); if i<&NUMAGECAT. then EndAgeDate=intnx(scan("&AGETYP.",i+1),birth_date,scan("&AGETHRESH.",i+1),'sameday') - 1; else if &MAXAGE.=99999 then EndAgeDate=intnx("years",birth_date,110,'sameday'); else EndAgeDate=intnx(scan("&AGETYP.",&NUMAGECAT.),birth_date,&MAXAGE.+1,'sameday')-1; if EndDate= &GROUPWASHOUT. and NumEnrDays >= &GROUPENRDAYS. then do; if WashTyp in('MULT') then do; _IncAg(i)=1; IncDaysag(i)=sum(min(NumWashOutDays-&GROUPWASHOUT. +1,NumDays,/*MODIF WAVE2 HERE*/NumEnrDays-&GROUPENRDAYS. +1),IncDaysAg(i)); end; else if TermDt eq . or (TermDt ne . and StartDate<=TermDt) then do; /*WashTyp='Min' or 'Sing'*/ _IncAg(i)=1; IncDaysag(i)=sum(min(NumWashOutDays-&GROUPWASHOUT. +1,NumDays,/*MODIF WAVE2 HERE*/NumEnrDays-&GROUPENRDAYS. +1),IncDaysAg(i)); end; end; end; end; do i=FirstPer to LastPer; format StartPerDate EndPerDate mmddyy10.; /*Here the use of the modulo operator has been chosen to avoid having to call the intnx() function to track year change as it was too much time consuming when this was tested in a synthetic real-life size problem*/ /*Year*/ if mod(i,12)=&YEARCHANGE. or i=FirstPer then do; year=int(&FROMY.+(i+&FORMOD.)/12); j=year-&FROMY.+1; /*Prevalence*/ _PrevY(j)=1; /*number of query days overlapping this year*/ NumDays= min(mdy(12,31,year),EndDate)-max(QueryStartDate,mdy(1,1,year))+1; PrevDaysY(j) = sum(NumDays,PrevDaysY(j)); /*Incidence*/ NumWashOutDays=min(mdy(12,31,year),EndDate)-StartDate; /*MODIF WAVE2 HERE*/ NumEnrDays=min(mdy(12,31,year),EndDate)-Enr_Start; if NumWashOutDays >= &GROUPWASHOUT. and NumEnrDays >= &GROUPENRDAYS. then do; if WashTyp in('MULT') then do; _IncY(j)=1; IncDaysY(j)=sum(min(NumWashOutDays-&GROUPWASHOUT. +1,NumDays,/*MODIF WAVE2 HERE*/NumEnrDays-&GROUPENRDAYS. +1),IncDaysY(j)); end; else if TermDt eq . or (TermDt ne . and StartDate<=TermDt) then do; /*WashTyp='Min' or 'Sing'*/ _IncY(j)=1; IncDaysY(j)=sum(min(NumWashOutDays-&GROUPWASHOUT. +1,NumDays,/*MODIF WAVE2 HERE*/NumEnrDays-&GROUPENRDAYS. +1),IncDaysY(j)); end; end; end; /*Year/Month*/ /*Compute Strata Start/End date*/ if i=FirstPer then StartPerdate=mdy(mod(i+&FORMOD.,12)+1,1,int(&FROMY.+(i+&FORMOD.)/12)); else StartPerdate=EndPerDate+1; EndPerdate=mdy(mod(i+&FORMOD.+1,12)+1,1,int(&FROMY.+(i+&FORMOD.+1)/12))-1; if EndDate= &GROUPWASHOUT. and NumEnrDays >= &GROUPENRDAYS. then do; if WashTyp in('MULT') then do; _IncPer(i)=1; IncDaysPer(i)=sum(min(NumWashOutDays-&GROUPWASHOUT. +1,NumDays,/*MODIF WAVE2 HERE*/NumEnrDays-&GROUPENRDAYS. +1),IncDaysPer(i)); end; else if TermDt eq . or (TermDt ne . and StartDate<=TermDt) then do; /*WashTyp='Min' or 'Sing'*/ _IncPer(i)=1; IncDaysPer(i)=sum(min(NumWashOutDays-&GROUPWASHOUT. +1,NumDays,/*MODIF WAVE2 HERE*/NumEnrDays-&GROUPENRDAYS. +1),IncDaysPer(i)); end; end; end; end; if last.patid then do; PrevALL=sum(PrevALL,max(of _PrevY:,.)); IncALL=sum(IncALL,max(of _IncY:,.)); do i=FirstAgeGroup to LastAgeGroup; PrevAg(i)=sum(PrevAg(i),_PrevAg(i)); IncAg(i)=sum(IncAg(i),_IncAg(i)); end; do i = 1 to &NUMYEARS.; PrevY(i)=sum(PrevY(i),_PrevY(i)); IncY(i)=sum(IncY(i),_IncY(i)); end; do i = 1 to &NUMPER.; PrevPer(i)=sum(PrevPer(i),_PrevPer(i)); IncPer(i)=sum(IncPer(i),_IncPer(i)); end; end; if eof then output; retain _Inc: _Prev: Inc: Prev:; keep Patid birth_date Sex StartDate EndDate QueryStartDate Prev: Inc:; run; %MEND ACCUMDENOM; %ACCUMDENOM(sex=M); %ACCUMDENOM(sex=F); %ACCUMDENOM(sex=U); /*MODIF WAVE2 HERE*/ %MACRO SQUARE(file=,Sex=); proc contents data=&file. noprint out=_tilt_ ; data _null_ ; set _tilt_ ; call symput('nobs',trim(left(put(nobs,15.)))) ; run; %PUT &pnobs.; %IF %EVAL(&nobs.=0) %THEN %DO; proc sql noprint; insert into &file (Sex) values("&sex."); quit; %END; %MEND SQUARE; %SQUARE(file=_Patient_days_F,sex=F); %SQUARE(file=_Patient_days_M,sex=M); %SQUARE(file=_Patient_days_U,sex=U); data _MasterDenomTable; set _Patient_days_F(in=a) _Patient_days_M(in=b) _Patient_days_U(in=c); PrevDaysALL=sum(of PrevDaysY:,.); IncDaysALL=sum(of IncDaysY:,.); keep sex prev: inc:; run; proc transpose data = _MasterDenomTable out = _MasterDenomTable(rename=(_NAME_ = Segment COL1=count)); by sex; run; proc sort data = _MasterDenomTable; by segment sex; run; data _MasterDenomTable; format Segment $16. Group $30. count best12.; set _MasterDenomTable; Group = "&ITGROUP."; label Segment='Segment'; /*MODIF WAVE2 HERE*/ if missing(count) then count=0; run; %IF &I.=1 %THEN %DO; data DPLocal.&REQUESTID.&RUNID._MasterDenomTable; set _MasterDenomTable; run; %END; %ELSE %DO; proc append base = DPLocal.&REQUESTID.&RUNID._MasterDenomTable data = _MasterDenomTable force; run; %END; %END; %MEND DENOMLOOP; %DENOMLOOP(); /*---------------------------------------------------------------------------------------------------*/ /* 7.0 Denomloop post processing -- Modular Program specific */ /*---------------------------------------------------------------------------------------------------*/ proc sort data= DPLocal.&REQUESTID.&RUNID._MasterDenomTable; by Group segment sex count; run; /*Change Here*/ data _denom; set DPLocal.&REQUESTID.&RUNID._MasterDenomTable; if upcase(segment)=:'INC' then do; segment=upcase(substr(segment,4,12)); Inc=1; end; else do; /*Prev*/ segment=upcase(substr(segment,5,11)); inc=0; end; time=0; if upcase(segment)=:'DAYS' then do; segment=upcase(substr(segment,5,11)); time=1; end; /*Assign Age Groupings for merging and create year month variables*/ if upcase(Segment)=:"AG" then do; i=compress(upcase(Segment),"AG"); format AgeGroup $13.; AgeGroup = scan("&AGESTRAT.",i,' '); end; if upcase(Segment)=:"PER" then do; StrataStart=intnx("Months",&QUERYFROM.,compress(upcase(Segment),"PER")-1,'sameday'); RxYear=year(StrataStart); RxMonth=month(StrataStart); end; if upcase(Segment)=:"Y" then RxYear=compress(upcase(Segment),"Y"); keep segment inc Group count sex RxYear Rxmonth AgeGroup time; run; proc sort data=_denom; by Group segment sex inc time count; run; data _denom; format DpID SiteId $2.; merge _denom(where=(inc=0 and time=0) rename=count=PrevDenCount) _denom(where=(inc=1 and time=0) rename=count=IncDenCount) _denom(where=(inc=0 and time=1) rename=count=PrevDaysCount) _denom(where=(inc=1 and time=1) rename=count=IncDaysCount); by Group segment sex; DPID="&DPID."; SITEID="&SITEID."; drop inc; run; proc sql noprint; create table MSOC.&REQUESTID.&RUNID._DenTable0 as Select DPId, SITEID, Group, Sex, AgeGroup, RxYear, RxMonth, IncDenCount, IncDaysCount, PrevDenCount, PrevDaysCount from _denom; quit; /*---------------------------------------------------------------------------------------------------*/ /* 8.0 Add demographics information to master dispensing table */ /*---------------------------------------------------------------------------------------------------*/ proc sort nodupkey data = _Enrollment(keep=PatId Birth_Date Sex); by PatId; run; data DPLocal.&REQUESTID.&RUNID._QueryGroup(keep=PatID Sex Adate AgeGroup Group RxSup /*MODIF WAVE3*/ RxAmt NumDipensing RxYear RxMonth NewStart Incident FIncDt); 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 do; NewStart = 1; end; else do; NewStart = 0; IndexDt = .; end; do i=&NUMAGECAT. to 1 by -1; if ADate >= 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 <= ADate <= MaxAgeDate then output DPLocal.&REQUESTID.&RUNID._QueryGroup; end; end; stop; run; proc sql noprint; create table _Table0 as Select PatId, Group, Adate, FIncDt, Sex, AgeGroup, RxYear, RxMonth, incident, NewStart, RxSup, /*MODIF WAVE3*/ RxAmt, NumDipensing from DPLocal.&REQUESTID.&RUNID._QueryGroup order by PatId, Group; quit; /*For Prevalent*/ proc means data=_Table0 noprint; var RxSup /*MODIF WAVE3*/ RxAmt NumDipensing; class Patid Group Sex AgeGroup RxYear RxMonth; output out=_PTable0(drop=_freq_ where=(PatId ne "") rename=_type_=seg) sum(NumDipensing)=Dispensings sum(RxSup)=DaySupp sum(RxAmt)=AmtSupp; run; proc means data=_PTable0 noprint nway missing; var Dispensings DaySupp /*MODIF WAVE3*/ AmtSupp; class Group Sex AgeGroup RxYear RxMonth seg; output out=_PTable0(drop=_type_ rename=_freq_=Npts where=(Group ne "")) sum = ; run; /*For Incident*/ proc means data=_Table0 noprint; var NewStart RxSup /*MODIF WAVE3*/ RxAmt NumDipensing Incident; class Patid Group Sex AgeGroup RxYear RxMonth; where /*MODIF WAVE3 ADate >= FIncDt*/ incident = 1; output out=_ITable0(drop=_freq_ where=(PatId ne "") rename=_type_=seg) max(incident)=incident sum(NewStart)=NewStarts sum(NumDipensing)=Dispensings sum(RxSup)=DaySupp sum(RxAmt)=AmtSupp; run; proc means data=_ITable0 noprint nway missing; var NewStarts Dispensings DaySupp /*MODIF WAVE3*/ AmtSupp; class Group Sex AgeGroup RxYear RxMonth seg; where incident = 1; output out=_ITable0(drop=_type_ rename=_freq_=Npts where=(Group ne "")) sum = ; run; data MSOC.&REQUESTID.&RUNID._NumTab0; set _ITable0(in=a) _PTable0; Incident = 0; if a then Incident = 1; run; /*---------------------------------------------------------------------------------------------------*/ /* 9.0 Tables */ /*---------------------------------------------------------------------------------------------------*/ /****************************************************************************/ /* Table 1: Drug Usage by Generic Name */ /* DPID SiteID QueryDrug Denominator Npts Dispensings DaysSupp */ /* */ /* Table 2: Monthly Dispensings, by Generic Name */ /* DPID SiteID QueryDrug RxYear RxMonth NewUsers Npts Dispensings DaysSupp */ /* */ /* Table 3: Drug Usage by Generic Name and Age Groups */ /* DPID SiteID QueryDrug AgeGroup Denominator Npts Dispensings DaysSupp */ /* */ /* Table 4: Drug usage by Generic Name and Sex */ /* DPID SiteID QueryDrug Sex Denominator Npts Dispensings DaysSupp */ /* */ /* Table 5: Drug Usage by Generic Name and Year */ /* DPID SiteID QueryDrug Denominator RxYear Npts Dispensings DaysSupp */ /****************************************************************************/ /*keeping one record per patient*/ %MACRO CREATETABLES(class=,num=,denomby=,denomwhere=); /*Member counts in segment*/ proc means data=DPLocal.&REQUESTID.&RUNID._QueryGroup nway noprint; var Incident NewStart RxSup /*MODIF WAVE3*/ RxAmt NumDipensing; class Patid &class.; where /*MODIF WAVE3 ADate >= FIncDt*/ incident = 1; output out=_ITable&num.(drop=_:) max(incident)=incident sum(NewStart)=NewStarts sum(NumDipensing)=Dispensings sum(RxSup)=DaySupp sum(RxAmt)=AmtSupp; run; proc means data=DPLocal.&REQUESTID.&RUNID._QueryGroup nway noprint; var RxSup /*MODIF WAVE3*/ RxAmt NumDipensing; class Patid &class.; output out=_PTable&num.(drop=_:) sum(NumDipensing)=Dispensings sum(RxSup)=DaySupp sum(RxAmt)=AmtSupp; run; /*Prevalent Table*/ proc means data=_PTable&num. nway noprint; var Dispensings DaySupp /*MODIF WAVE3*/ AmtSupp; class &class.; output out=PTable&num.(drop=_type_ rename=_freq_=Npts) sum=; run; /*Incident Table*/ proc means data=_ITable&num. nway noprint; var NewStarts Dispensings DaySupp /*MODIF WAVE3*/ AmtSupp; class &class.; where incident=1; output out=ITable&num.(drop=_type_ rename=_freq_=Npts) sum=; run; /*Add denominators*/ proc means data=_denom nway noprint; var prev: inc:; class Group &denomby.; where &denomwhere.; output out=_thisdenom(keep=Group &denomby. Prev: Inc:) sum=; run; data PTable&num.; format DpID SiteID $2. &class. NPts NewStarts PrevDenCount PrevDaysCount; merge _thisdenom PTable&num. ; by &class.; DPID="&DPID."; SITEID="&SITEID."; NewStarts = .; drop Inc:; rename PrevDenCount=Denominator PrevDaysCount=MemberDays; label PrevDenCount="Denominator" PrevDaysCount="MemberDays"; run; data ITable&num.; format DpID SiteID $2. &class. NPts NewStarts IncDenCount IncDaysCount; merge _thisdenom ITable&num. ; by &class.; DPID="&DPID."; SITEID="&SITEID."; drop Prev:; rename IncDenCount=Denominator IncDaysCount=MemberDays; label IncDenCount="Denominator" IncDaysCount="MemberDays"; run; %MEND CREATETABLES; %CREATETABLES(class=Group,num=1,denomby=,denomwhere=Segment="ALL"); %CREATETABLES(class=Group RxYear RxMonth,num=2,denomby=Rxyear Rxmonth,denomwhere=Segment in:("PER")); %CREATETABLES(class=Group AgeGroup,num=3,denomby=AgeGroup,denomwhere=Segment in:("AG")); %CREATETABLES(class=Group sex,num=4,denomby=sex,denomwhere=Segment="ALL"); %CREATETABLES(class=Group RxYear,num=5,denomby=Rxyear,denomwhere=Segment=:"Y"); /*Macro to Export Results to MSOC Folder in .csv, .lst, and .sas7bdat format*/ %MACRO export(DATAPARTNER,REQUESTID,NAME,VARTO); %IF %INDEX(&name.,table3) %THEN %DO; data &NAME.; set &NAME.; AgeGroup=TRANWRD(AgeGroup,'-',' to '); run; %END; /*exporting raw tables into one .lst file*/ options nodate nonumber formdlim="-"; /*Select Table Name*/ data _Title; set titles; if table="&NAME." then do; &VARTO.=title; call symput('TITLE1',title);output; &VARTO.="Source: &REQUESTID.&RUNID."; call symput('TITLE2',&VARTO.); output; end; keep &VARTO.; run; TITLE1 "&TITLE1."; TITLE2 "&TITLE2."; /*Print results in .lst*/ proc print data=&name. noobs; run; data MSOC.&REQUESTID.&RUNID._&name.; set &NAME.; run; data &NAME.; format &VARTO. $70.; set &NAME. _Title; run; PROC EXPORT DATA= &NAME. OUTFILE= "&DPLOCAL.&REQUESTID.&RUNID._&NAME..csv" DBMS=CSV REPLACE; RUN; %MEND export; %MACRO WRAPPER; proc printto print="&MSOC.&REQUESTID.&RUNID._all_tables.lst" new; run; Data Titles; Format Table $9. Title $70.; Table="ptable1"; Title="Table 1a: Drug Usage - Prevalent Users - &STARTDATE."; output; Table="itable1"; Title="Table 1b: Drug Usage - Incident Users - &STARTDATE."; output; Table="ptable2"; Title="Table 2a: Monthly Dispensings - Prevalent Users - &STARTDATE."; output; Table="itable2"; Title="Table 2b: Monthly Dispensings - Incident Users - &STARTDATE."; output; Table="ptable3"; Title="Table 3a: Drug Usage by Age Group - Prevalent Users - &STARTDATE."; output; Table="itable3"; Title="Table 3b: Drug Usage by Age Group - Incident Users - &STARTDATE."; output; Table="ptable4"; Title="Table 4a: Drug Usage by Sex - Prevalent Users - &STARTDATE."; output; Table="itable4"; Title="Table 4b: Drug Usage by Sex - Incident Users - &STARTDATE."; output; Table="ptable5"; Title="Table 5a: Drug Usage by Year - Prevalent Users - &STARTDATE."; output; Table="itable5"; Title="Table 5b: Drug usage by Year - Incident Users - &STARTDATE."; output; Table="signature"; Title="Signature of Request - &STARTDATE."; output; run; %DO i=1 %TO 5; %EXPORT(&DPID.&SITEID., &REQUESTID.,ptable&i.,QueryDrug); %EXPORT(&DPID.&SITEID., &REQUESTID.,itable&i.,QueryDrug); %END; %MEND WRAPPER; %WRAPPER; data _NULL_; temp=DATETIME(); call symput('STOP',temp); seconds=temp-&start.; hours=int(seconds/3600); minutes=int((seconds-hours*3600)/60); seconds2=int((seconds-hours*3600-minutes*60)); call symput('totalseconds',put(seconds,best.)); call symput('hours',put(hours,4.0)); call symput('minutes',put(minutes,2.0)); call symput('seconds',put(seconds2,2.0)); run; %PUT TOTAL RUN TIME was &hours. h &minutes. m &seconds. s; /*---------------------------------------------------------------------------------------------------*/ /* 10.0 Create signature file */ /*---------------------------------------------------------------------------------------------------*/ data signature; MPNum="&MPNum."; MPVer="&MPVer."; DPID="&DPID."; SITEID="&SITEID."; RequestID="&RequestID."; RunID="&RunID."; format StartTime StopTime datetime21.2; StartTime=trim(left(&START.)); StopTime=trim(left(&STOP.)); format RunTime Seconds $20.; RunTime="&hours. h &minutes. m &seconds. s"; Seconds="&totalseconds. s"; ScenarioCnt=strip("&NQUERYGROUP."); ENROLGAP="&ENROLGAP."; /*MODIF WAVE2 HERE*/ COVERAGE="&COVERAGE."; QUERYFROM="&QUERYFROMc."; QUERYTO="&QUERYTOc."; QUERYFILE="&QUERYFILE."; INCQUERYFILE="&INCQUERYFILE."; CONDFILE="&CONDFILE."; OUTTABLESFILE="&OUTTABLESFILE."; AGESTRAT="&AGESTRAT."; output; run; proc transpose data=signature out=signature(rename=_NAME_=Var rename=COL1=VALUE); var _ALL_; run; %EXPORT(&DPID.&SITEID., &RequestID.,signature,var); %MACRO REMOVETABLE(table=); %if %sysfunc(fileexist(&msoc.&REQUESTID.&RUNID._&table..sas7bdat))=1 %then %do; proc datasets library=msoc nolist nowarn; delete &REQUESTID.&RUNID._&table.; quit; %end; %MEND REMOVETABLE; %MACRO OUTPUTSELECTION; %IF %UPCASE("&OUTTABLESFILE.") ne %STR("") %THEN %DO; data _null_; set _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 MODULARPROGRAM9; /*---------------------------------------------------------------------------*/ /* 11.0 -- Invoking Modular Program Macro */ /*---------------------------------------------------------------------------*/ %MODULARPROGRAM9(REQUESTID=mp9, RUNID=r1, ENROLGAP=45, COVERAGE=MD, QUERYFROM=01/01/2006, QUERYTO=12/31/2008, QUERYFILE=mp9_query.sas7bdat, INCQUERYFILE=, CONDFILE=mp9_cond.sas7bdat, OUTTABLESFILE=mp9_output_tables.sas7bdat, AGESTRAT=00-40 41-54 55-64 65-74 75-84 85+ );