/*---------------------------------------------------------------------------------------------------*\ | PROGRAM NAME: Modular Program 2 - Characterization of Use of Medical Product Exposures among | | Individuals with or without Condition(s) of Interest | | Group of Interest | | Date: 2/19/2013 | | Version: 3.1 | | | |-----------------------------------------------------------------------------------------------------| | | | The purpose of this program is to execute requests consistent with | | Modular Program 2 specifications | | | |-----------------------------------------------------------------------------------------------------| | | | Program inputs: | | As specified in Modular Program 2 documentation | | | | Program outputs: | | As specified in Modular Program 2 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 (7 sites) || || 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 (6 sites) || || Kaiser Permanente Colorado KP CO || || Kaiser Permanente Georgia KP G || || Kaiser Permanente Hawaii KP H || || 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:\'; /*NDC/ICD9 Codes File Location*/ %LET infolder=K:\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=K:\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=K:\dplocal\; /*SAS Output Files*/ libname DPLocal "&DPLocal."; /*---------------------------------------------------------------------------------------------------*/ /* End of User Inputs */ /*---------------------------------------------------------------------------------------------------*/ /*****************************************************************************************************/ /**************************** PLEASE DO NOT EDIT CODE BELOW THIS LINE ********************************/ /*****************************************************************************************************/ %MACRO MODULARPROGRAM2(REQUESTID=,ENROLGAP=,QUERYFROM=,QUERYTO=, QUERYFILE=,INCQUERYFILE=,CONDFILE=,AGESTRAT=); %LET DPID=%LOWCASE(&DPID.); %LET SITEID=%LOWCASE(&SITEID.); %LET REQUESTID=%LOWCASE(&REQUESTID.); %LET QUERYFILE=%LOWCASE(&QUERYFILE.); %LET INCQUERYFILE=%LOWCASE(&INCQUERYFILE.); %LET CONDFILE=%LOWCASE(&CONDFILE.); proc printto log="&MSOC.&DPID.&SITEID._&REQUESTID..log" new; run; %PUT "MODULARPROGRAM2_v3.1"; /*---------------------------------------------------------------------------------------------------*/ /* 0.0 Preprocess user inputs */ /*---------------------------------------------------------------------------------------------------*/ /*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; %MEND; %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(&QUERYFILE.,QUERYFILE,Code CodeType WashTyp Group Washper); %MACRO WRAPPER(); %IF %INDEX(%UPCASE("&INCQUERYFILE."),.) %THEN %DO; %IMPORTFILES(&INCQUERYFILE.,INCQUERYFILE,Group Code CodeType); %END; %ELSE %DO; data _INCQUERYFILE; if 0 then set _QUERYFILE; keep Group Code CodeType; 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.; keep Code CodeType Principal CondFrom CondTo CareSetting Group /*Washper*/ Inclusion; stop; run; %END; %MEND WRAPPER; %WRAPPER(); /*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*/ proc sort nodupkey data=_QUERYFILE; by Group codetype Code; 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_"; run; proc sort nodupkey data=_INCQUERYFILE; 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 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 from _QUERYFILE as quer right join _INCQUERYFILE as inqu on quer.Code = inqu.Code and quer.CodeType = inqu.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 quer.group, inqu.igroup, quer.Code, inqu.Code as icode, quer.CodeType, inqu.CodeType as iCodeType from _QUERYFILE as quer join _INCQUERYFILE as inqu on quer.Code = inqu.Code and quer.CodeType = inqu.CodeType 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 from _QUERYFILE where (CodeType||Code) not in (select (CodeType||Code) from _INCQUERYFILE where IGroup = "_ALLGROUPS_") except select igroup as group, "" as igroup, Code, "" as icode, CodeType, "" as iCodeType 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 from _INCQUERYFILE where IGroup ne "_ALLGROUPS_" except select "" as group, group as igroup, "" as Code, Code as icode, "" as CodeType, CodeType as iCodeType 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 Code = ICode; if CodeType = "" then CodeType = ICodeType; keep Group IGroup Code CodeType 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); by Group; run; data _querysettings; set _querysettings; WashTyp = upcase(WashTyp); 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.; 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); 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; 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 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 _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(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, proclist.*, 1 as RxSup, 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 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, diaglist.*, 0 as proc From indata.&diatable.(where=(compress(DX,'.') in:(&VECT_diag.))) as diagtb, _diag as diaglist Where diagtb.DX_codetype = diaglist.codetype and 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; 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; 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; delete _diagnosis; quit; %END; %MEND; %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=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 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; 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 principal='NO' then output; if principal='YES' and PDX='P' and EncType2 in('IP','ED') then output; end; end; end; run; %END; %MEND; %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; append base=_drugs data=_predrugs FORCE; delete _predrugs; quit; %END; %MEND; %WRAPPER; %MEND; %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; 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; delete _drugs; quit; %END; %MEND; %WRAPPER; /*Determine if MEDCOV is required*/ %GLOBAL MEDCOV; %MACRO WRAPPER; proc contents data=_proc noprint out=_tilt_ ; data _null_ ; set _tilt_ ; call symput('pnobs',trim(left(put(nobs,15.)))) ; run; proc contents data=_diag noprint out=_tilt_ ; data _null_ ; set _tilt_ ; call symput('dnobs',trim(left(put(nobs,15.)))) ; run; %IF %EVAL(&pnobs.>0 or &dnobs.>0) %THEN %DO; %LET MEDCOV=and upcase(MedCov)='Y'; %END; %ELSE %DO; %LET MEDCOV=; %END; %MEND; %WRAPPER; %PUT &MEDCOV.; /*---------------------------------------------------------------------------------------------------*/ /* 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 From indata.&ENRTABLE.(where=(upcase(drugcov)='Y' &MEDCOV.)) as Enrol, indata.&DEMTABLE.(where=(Birth_Date ne . and not missing(PatId))) as Demogs Where Demogs.PatId=Enrol.PatId and intnx(scan("&AGETYP.",1),Demogs.birth_date,&MINAGE.,'sameday') <= &QUERYTO. order by Patid, Enr_Start, Enr_end; quit; 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; if Enr_Start-lag(Enr_end)-1 > &ENROLGAP. then episode=episode+1; if first.Patid then episode=1; retain episode; run; /*---------------------------------------------------------------------------------------------------*/ /* 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; output out=_DenomInt(drop=_:) min(Enr_Start)= max(Enr_end)=; run; data _DenomInt; set _DenomInt; if enr_end < MinAgeDate then delete; LastAgeGroup =&NUMAGECAT.; do i=&NUMAGECAT. to 1 by -1; Threshdate=intnx(scan("&AGETYP.",i),birth_date,scan("&AGETHRESH.",i),'sameday'); if Threshdate <= min(&QUERYTO.,Enr_end) then do; LastAgeGroup =i; FirstAgeGroup=i; if Threshdate > max(&QUERYFROM.,Enr_Start) then do; do j=i-1 to 1 by -1; if intnx(scan("&AGETYP.",j),birth_date,scan("&AGETHRESH.",j),'sameday') <= max(&QUERYFROM.,Enr_Start) then do; FirstAgeGroup=j; leave; end; end; end; leave; end; end; rename episode=EligEpisode; drop i j Threshdate; run; proc datasets library=work; change _MasterQueryfile=_MasterQueryfile_; quit; proc sql noprint; create table _MasterQueryfile as Select mast.* from _MasterQueryfile_ as mast, _denomint as enrol where mast.PatId = enrol.PatId and enrol.Enr_Start <= mast.ADate <= enrol.Enr_End; quit; proc datasets library=work; 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; data _FDateTable; set _FDateTable; 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); 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=3; /*---------------------------------------------------------------------------------------------------*/ /* 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. */ /*---------------------------------------------------------------------------------------------------*/ %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); 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; if WashTyp in('MIN') then do; if InQuery and diff = . then do; indexdt=ADate; episode=episode+1; output; end; end; if WashTyp in('MULT') 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 WNumDays 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; 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.* 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*/ data _MasterTable_; merge _Quantity(in=a) _CleanAdates(in=b); by PatId ADate; if a; NoClaimsBef = 0; if b then NoClaimsBef = 1; Wash = 0; if WNumDays>=0 and WNumDays >= WashPer then Wash = 1; /*only for b*/ RxYear = Year(ADate); RxMonth = Month(ADate); run; /*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; quit; proc datasets library=work nolist nowarn; delete _MasterTable_; quit; /*Identify claims overlapping pre-existing condition period*/ data _MasterTable; set _MasterTable; /*If no inclusion are required for this group, we set the default HadCond=1*/ if &INCLREQD.=1 then HadCond = 0; else HadCond = 1; /*only exclusions are required or no pre-existing conditions at all*/ if cdate ne . then do; if Adate + CondFrom <= CDate <= Adate + CondTo then do; if inclusion=1 then HadCond = 1; else HadCond = 2; 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 and HadCond = 1 and ExclCritMeet = 1 then incident = 1; else incident = 0; run; /*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; %IF &I.=1 %THEN %DO; data DPLocal.&DPID.&SITEID._&REQUESTID._MasterTable; set _MasterTable; run; %END; %ELSE %DO; proc append base = DPLocal.&DPID.&SITEID._&REQUESTID._MasterTable data = _MasterTable force; run; %END; %END; %MEND; %LOOPTHROUGH(); %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)); end; run; %PUT &GROUPWASHOUT. &GROUPWASHTYP.; %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*/ Enr_Start=Max(&QUERYFROM.-&GROUPWASHOUT.,enr_start,PDate-&GROUPWASHOUT.); 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) _AllMember(in=b keep=Patid EligEpisode Pepisode Enr_End Sex birth_date MinAgeDate MaxAgeDate QueryStartDate FirstAgeGroup LastAgeGroup rename=Enr_End=Adate); by Patid EligEpisode Pepisode ADate; if a then Group="BEGINELIG"; 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 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 in the case of MIN Washtyp, ever*/ 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; 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. then do; if WashTyp in('MULT') then do; _IncAg(i)=1; IncDaysag(i)=sum(min(NumWashOutDays-&GROUPWASHOUT. +1,NumDays),IncDaysAg(i)); end; else if TermDt eq . or (TermDt ne . and StartDate<=TermDt) then do; /*WashTyp='Min'*/ _IncAg(i)=1; IncDaysag(i)=sum(min(NumWashOutDays-&GROUPWASHOUT. +1,NumDays),IncDaysAg(i)); end; end; end; end; do i=FirstPer to LastPer; format StartPerDate EndPerDate mmddyy10.; /*Here the use of the modulo operator has been chosen to avoid having to call the intnx() function to track year change as it was too much time consuming when this was tested in a synthetic real-life size problem*/ /*Year*/ if mod(i,12)=&YEARCHANGE. or i=FirstPer then do; year=int(&FROMY.+(i+&FORMOD.)/12); j=year-&FROMY.+1; /*Prevalence*/ _PrevY(j)=1; /*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; if NumWashOutDays >= &GROUPWASHOUT. then do; if WashTyp in('MULT') then do; _IncY(j)=1; IncDaysY(j)=sum(min(NumWashOutDays-&GROUPWASHOUT. +1,NumDays),IncDaysY(j)); end; else if TermDt eq . or (TermDt ne . and StartDate<=TermDt) then do; /*WashTyp='Min'*/ _IncY(j)=1; IncDaysY(j)=sum(min(NumWashOutDays-&GROUPWASHOUT. +1,NumDays),IncDaysY(j)); end; end; end; /*Year/Month*/ /*Compute Strata Start/End date*/ if i=FirstPer then StartPerdate=mdy(mod(i+&FORMOD.,12)+1,1,int(&FROMY.+(i+&FORMOD.)/12)); else StartPerdate=EndPerDate+1; EndPerdate=mdy(mod(i+&FORMOD.+1,12)+1,1,int(&FROMY.+(i+&FORMOD.+1)/12))-1; if EndDate= &GROUPWASHOUT. then do; if WashTyp in('MULT') then do; _IncPer(i)=1; IncDaysPer(i)=sum(min(NumWashOutDays-&GROUPWASHOUT. +1,NumDays),IncDaysPer(i)); end; else if TermDt eq . or (TermDt ne . and StartDate<=TermDt) then do; /*WashTyp='Min'*/ _IncPer(i)=1; IncDaysPer(i)=sum(min(NumWashOutDays-&GROUPWASHOUT. +1,NumDays),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(sex=M); %ACCUMDENOM(sex=F); %ACCUMDENOM(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'; run; %IF &I.=1 %THEN %DO; data DPLocal.&DPID.&SITEID._&REQUESTID._MasterDenomTable; set _MasterDenomTable; run; %END; %ELSE %DO; proc append base = DPLocal.&DPID.&SITEID._&REQUESTID._MasterDenomTable data = _MasterDenomTable force; run; %END; %END; %MEND DENOMLOOP; %DENOMLOOP(); /*---------------------------------------------------------------------------------------------------*/ /* 7.0 Denomloop post processing -- Modular Program specific */ /*---------------------------------------------------------------------------------------------------*/ proc sort data= DPLocal.&DPID.&SITEID._&REQUESTID._MasterDenomTable; by Group segment sex count; run; data _denom; set DPLocal.&DPID.&SITEID._&REQUESTID._MasterDenomTable; if segment=:'Inc' then do; segment=substr(segment,4,12); Inc=1; end; else do; /*Prev*/ segment=substr(segment,5,11); inc=0; end; time=0; if segment=:'Days' then do; segment=substr(segment,5,11); time=1; end; /*Assign Age Groupings for merging and create year month variables*/ if Segment=:"Ag" then do; i=compress(Segment,"Ag"); format AgeGroup $13.; AgeGroup = scan("&AGESTRAT.",i,' '); end; if Segment=:"Per" then do; StrataStart=intnx("Months",&QUERYFROM.,compress(Segment,"Per")-1,'sameday'); RxYear=year(StrataStart); RxMonth=month(StrataStart); end; if Segment=:"Y" then RxYear=compress(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.&DPID.&SITEID._&REQUESTID._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.&DPID.&SITEID._&REQUESTID._QueryGroup(keep=PatID Sex Adate AgeGroup Group RxSup NumDipensing RxYear RxMonth NewStart Incident Wash 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.&DPID.&SITEID._&REQUESTID._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.&DPID.&SITEID._&REQUESTID._QueryGroup; end; end; stop; run; proc sql noprint; create table _Table0 as Select PatId, Group, Adate, FIncDt, Sex, AgeGroup, RxYear, RxMonth, incident, NewStart, RxSup, NumDipensing from DPLocal.&DPID.&SITEID._&REQUESTID._QueryGroup order by PatId, Group; quit; /*For Prevalent*/ proc means data=_Table0 noprint; var RxSup 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; run; proc means data=_PTable0 noprint nway missing; var Dispensings DaySupp; 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 NumDipensing Incident; class Patid Group Sex AgeGroup RxYear RxMonth; where ADate >= FIncDt; output out=_ITable0(drop=_freq_ where=(PatId ne "") rename=_type_=seg) max(incident)=incident sum(NewStart)=NewStarts sum(NumDipensing)=Dispensings sum(RxSup)=DaySupp; run; proc means data=_ITable0 noprint nway missing; var NewStarts Dispensings DaySupp; 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.&DPID.&SITEID._&REQUESTID._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.&DPID.&SITEID._&REQUESTID._QueryGroup nway noprint; var Incident NewStart RxSup NumDipensing; class Patid &class.; where ADate >= FIncDt; output out=_ITable&num.(drop=_:) max(incident)=incident sum(NewStart)=NewStarts sum(NumDipensing)=Dispensings sum(RxSup)=DaySupp; run; proc means data=DPLocal.&DPID.&SITEID._&REQUESTID._QueryGroup nway noprint; var RxSup NumDipensing; class Patid &class.; output out=_PTable&num.(drop=_:) sum(NumDipensing)=Dispensings sum(RxSup)=DaySupp; run; /*Prevalent Table*/ proc means data=_PTable&num. nway noprint; var Dispensings DaySupp; 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; 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(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: &DPID.&SITEID. - &REQUESTID."; 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.&DATAPARTNER._&REQUESTID._&name.; set &NAME.; run; data &NAME.; format &VARTO. $70.; set &NAME. _Title; run; PROC EXPORT DATA= &NAME. OUTFILE= "&DPLOCAL.&DATAPARTNER._&REQUESTID._&NAME..csv" DBMS=CSV REPLACE; RUN; %MEND; %MACRO WRAPPER; proc printto print="&MSOC.&DPID.&SITEID._&REQUESTID._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; 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('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; DPID="&DPID."; SITEID="&SITEID."; RequestID="&RequestID."; format Start_Time Stop_Time datetime21.2; Start_Time=trim(left(&START.)); Stop_Time=trim(left(&STOP.)); format Execution_time $20.; Execution_time="&hours. h &minutes. m &seconds. s"; ENROLGAP="&ENROLGAP."; QUERYFROM="&QUERYFROMc."; QUERYTO="&QUERYTOc."; QUERYFILE="&QUERYFILE."; INCQUERYFILE="&INCQUERYFILE."; CONDFILE="&CONDFILE."; 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); proc datasets library=work nolist; delete _:; quit; proc printto log=log print=print; run; %MEND MODULARPROGRAM2; /*---------------------------------------------------------------------------*/ /* 11.0 -- Invoking Modular Program Macro */ /*---------------------------------------------------------------------------*/ %MODULARPROGRAM2(REQUESTID=mp2, ENROLGAP=45, QUERYFROM=01/01/2000, QUERYTO=12/31/2012, QUERYFILE=mpr18_query1.sas7bdat, INCQUERYFILE=, CONDFILE=, AGESTRAT=00-40 41-54 55-64 65-74 75-84 85+ );