/*---------------------------------------------------------------------------------------------------*\ | PROGRAM NAME: Modular Program 5: Background Rates for Health Outcomes of Interest | | | | | | Date: 02/06/2013 | | Version: 2.1 | | | |-----------------------------------------------------------------------------------------------------| | | | The purpose of this program is to execute requests consistent with | | Modular Program 5 specifications | | | |-----------------------------------------------------------------------------------------------------| | | | Program inputs: | | As specified in Modular Program 5 documentation | | | | Program outputs: | | As specified in Modular Program 5 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 */ /***********************/ /*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 (6 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 (7 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 || \*-------------------------------------------------------------------*/ /*Edit this section to reflect your name for each Table/File (or View)*/ %LET ENRTABLE=enrollment; %LET DEMTABLE=demographic; %LET DISTABLE=dispensing; %LET DIATABLE=diagnosis; %LET ENCTABLE=encounter; %LET PROCTABLE=procedure; /*3) Edit this section to reflect locations for the libraries/folders for Mini-Sentinel Data*/ /*and Output folders*/ /*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*/ libname indata 'C:\MSCDM\'; /*Folder containing input files*/ %LET INFOLDER=C:\MP5\InputFiles\; /*Folder containing summary files to be exported*/ /*and shared with Mini-Sentinel Operations Center (MSOC)*/ %LET MSOC=C:\MP5\MSOC\; /*Folder containing final datasets to be kept local at the partner site (DPLocal)*/ %LET DPLOCAL=C:\MP5\DPLocal\; /*---------------------------------------------------------------------------------------------------*\ | End of User Inputs | \*---------------------------------------------------------------------------------------------------*/ /*****************************************************************************************************\ |**************************** PLEASE DO NOT EDIT CODE BELOW THIS LINE ********************************| \*****************************************************************************************************/ libname infolder "&infolder."; libname MSOC "&MSOC."; libname DPLocal "&DPLocal."; %MACRO MODULARPROGRAM5(REQUESTID=,ENROLGAP=, QUERYFROM=,QUERYTO=,QUERYFILE=,INCQUERYFILE=,AGESTRAT=); %LET DPID=%LOWCASE(&DPID.); %LET SITEID=%LOWCASE(&SITEID.); %LET REQUESTID=%LOWCASE(&REQUESTID.); %LET QUERYFILE=%LOWCASE(&QUERYFILE.); %LET INCQUERYFILE=%LOWCASE(&INCQUERYFILE.); %PUT "MODULARPROGRAM5v2.1"; proc printto log="&MSOC.&DPID.&SITEID._&REQUESTID..log" new; run; /*---------------------------------------------------------------------------------------------------*\ | 0.1 Preprocessing user inputs | \*---------------------------------------------------------------------------------------------------*/ /*Empty working files*/ 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; /*Set macro variables for age groupings*/ %MACRO WORDNUMARG(VECTOR,OUTNAME); /*Macro to count words in a macro VECTOR*/ %GLOBAL &OUTNAME.; %LET NUMARG=0; %DO %WHILE(%QSCAN(&VECTOR,&NUMARG+1,%STR( )) ne %STR()); %LET NUMARG = %EVAL(&NUMARG+1); %END; %LET &OUTNAME.=&NUMARG.; %MEND WORDNUMARG; %WORDNUMARG(&AGESTRAT.,NUMAGECAT); /*Age Stratification*/ %PUT &NUMAGECAT.; data _null_; format AGETHRESH $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 query dates*/ data _NULL_; temp=DATETIME(); call symput('START',temp); call symput('STARTDATE',put(datepart(temp),date9.)); call symput('STARTTIME',put(timepart(temp),time4.)); run; Data _null_ ; Call Symput('QUERYFROMc',"&QUERYFROM."); Call Symput('QUERYTOc',"&QUERYTO."); Call Symput('QUERYFROM',put(input("&QUERYFROM",mmddyy10.),best12.)); Call Symput('QUERYTO',put(input("&QUERYTO",mmddyy10.),best12.)); run; %PUT &QUERYFROM; %PUT &QUERYTO; data _null_; call symput("NUMPER",trim(left(intck('Months',&QUERYFROM.,&QUERYTO.)+1))); call symput("NUMYEARS",trim(left(intck('Years',&QUERYFROM.,&QUERYTO.)+1))); call symput("NUMMONTHS",trim(left(intck('Months',&QUERYFROM.,&QUERYTO.)+1))); call symput("FROMY",trim(left(year(&QUERYFROM.)))); call symput("TOY",trim(left(year(&QUERYTO.)))); call symput("FROMMO",trim(left(month(&QUERYFROM.)))); run; %PUT &NUMPER.; %PUT &NUMYEARS.; %PUT &NUMMONTHS; %PUT &FROMY.; %PUT &TOY.; data _null_; call symput("FORMOD",trim(left(&FROMMO.-2))); if month(&QUERYFROM.) = 1 then call symput("YEARCHANGE",1); else if month(&QUERYFROM.) = 2 then call symput("YEARCHANGE",0); else call symput("YEARCHANGE",14-month(&QUERYFROM.)); run; %PUT &YEARCHANGE.; %PUT &FORMOD.; /*---------------------------------------------------------------------------------------------------*\ | 1 Import data files | \*---------------------------------------------------------------------------------------------------*/ /*---------------------------------------------------------------------------------------------------*\ | 1.1 Import query and incidence files | \*---------------------------------------------------------------------------------------------------*/ /*Query file*/ %MACRO IMPORTFILES(VAR1,VAR2); %IF %INDEX(%UPCASE("&VAR1."),CPORT) %THEN %DO; proc cimport infile="&infolder.&VAR1." library=infolder memtype=data; run; %END; %LET &VAR2.=%SUBSTR(&VAR1.,1,%INDEX(%UPCASE(&VAR1.),.)-1 ); %MEND; %IMPORTFILES(&QUERYFILE.,QUERYFILE); /*Cleaning of QUERYFILE*/ data _&QUERYFILE.; set infolder.&QUERYFILE.; Group = compress(trim(left(Group))); Group = translate(Group,'_','-', '_','.', '_',',', '_','%', '_','$', '_','!', '_','*', '_','&', '_','#', '_','@'); WashTyp = Upcase(Washtyp); /*Setting initial values*/ if TWOCLAIM="" then TWOCLAIM='NO'; if PRINCIPAL="" then PRINCIPAL='NO'; run; /*QueryGroup incident definitions file*/ %MACRO WRAPPER; %IF %INDEX(%UPCASE("&INCQUERYFILE."),.) %THEN %DO; %IMPORTFILES(&INCQUERYFILE.,INCQUERYFILE); %END; /*Cleaning of INCQueryFILE queryGroup*/ %IF %UPCASE("&INCQUERYFILE.") ne %STR("") %THEN %DO; data _INCQUERYFILE; set infolder.&INCQUERYFILE.; Group = compress(trim(left(Group))); Group = translate(Group,'_','-', '_','.', '_',',', '_','%', '_','$', '_','!', '_','*', '_','&', '_','#', '_','@'); keep code codetype Group; run; %END; %ELSE %DO; data _INCQUERYFILE; if 0 then set _&QUERYFILE.; keep code codetype Group; stop; run; %END; %MEND; %WRAPPER; %MACRO WRAPPER(); /*Printing input files into a .lst file*/ options nodate nonumber; proc printto print="&MSOC.&DPID.&SITEID._&REQUESTID._diag.out" new; run; Title1 "Input Query File printout"; proc print data=infolder.&QUERYFILE. noobs; run; %IF %UPCASE("&INCQUERYFILE.") ne %STR("") %THEN %DO; proc print data=infolder.&INCQUERYFILE. noobs; run; %END; %MEND WRAPPER; %WRAPPER(); Title1 ""; data _querydescrkey; set _&QUERYFILE.(keep=Group); run; proc sort nodupkey data = _querydescrkey; by group; run; %MACRO MISSGROUP(INFILE=); %LET QTRANS = 0; data _null_; set &INFILE.; if Group = "" then call symput('QTRANS',input('1',1.)); run; %PUT &QTRANS.; %IF %EVAL(&QTRANS.=1) %THEN %DO; data _nogroup(drop=Group) _withgroup; set &INFILE.; if Group = "" then output _nogroup; else output _withgroup; run; proc sql noprint; create table _temp as Select inpfile.*, grps.Group from _nogroup as inpfile, _querydescrkey as grps; quit; data &INFILE.; set _temp _withgroup; run; proc datasets library=work nolist; delete _temp _withgroup _nogroup; quit; %END; %MEND MISSGROUP; %MISSGROUP(INFILE=_INCQUERYFILE); /*Creating index mapping to group string*/ data _querydescrkey; set _querydescrkey; by group; if _n_ = 1 then NGroup = 0; if first.Group then NGroup = NGroup + 1; retain NGroup; run; /*Substitute numeric group id in query and incident files*/ data _&QUERYFILE.(drop=Group) _INCQUERYFILE(keep=NGroup Code CodeType); if 0 then set _querydescrkey; declare hash ht (hashexp:16, dataset:"_querydescrkey"); ht.definekey('Group'); ht.definedata(ALL: 'YES'); ht.definedone(); do until(eof1); set _&QUERYFILE.(in=a) _INCQUERYFILE end=eof1; if ht.find()=0 then do; if a then output _&QUERYFILE.; else output _INCQUERYFILE; end; end; stop; run; /*---------------------------------------------------------------------------------------------------*\ | 1.2 Create formats | \*---------------------------------------------------------------------------------------------------*/ data _groupfmt; set _querydescrkey; format fmtname $10.; fmtname = "groupfmt"; Start = NGroup; Label = Group; keep fmtname Start Label; run; /*Insert formats necessary for denominator algorithm*/ proc sql noprint; insert into _groupfmt values("groupfmt",-1,"BEGINELIG") values("groupfmt",0,"ENDELIG"); quit; proc format cntlin=_groupfmt library=work; quit; /*---------------------------------------------------------------------------------------------------*\ | 2.0 Pre-processing of input data | \*---------------------------------------------------------------------------------------------------*/ /*Storing all QueryGroup into a macro vector*/ proc sort nodupkey data = _querydescrkey out = _GroupList(keep=Group NGroup); by Group; run; proc sort nodupkey data = _&QUERYFILE. out = _GroupListWashout(keep=NGroup WashPer WashTyp); by NGroup; run; proc sql noprint; select Group into :GROUPVECT1 separated by ' ' from _GroupList; quit; proc sql noprint; select Group into :GROUPVECT2 separated by ',' from _GroupList; quit; %PUT &GROUPVECT1; %PUT &GROUPVECT2; /*Create extraction lists for file pre-extraction -- To remove true duplicates*/ proc sort nodupkey data=_&QUERYFILE.; by NGroup codetype Code; run; proc sort nodupkey data=_INCQUERYFILE; by NGroup codetype Code; run; data _diag(keep=NGroup Code Codetype length CareSetting Principal Query Incid) _proc(keep=NGroup Code Codetype length CareSetting Principal Query Incid) _ndc(keep=NGroup Code Codetype length Query Incid); merge _&QUERYFILE.(in=a) _INCQUERYFILE(in=b keep=NGroup codetype Code); by NGroup codetype Code; format NGroup groupfmt.; code=compress(code,'. '); length=length(code); if a then Query=1;else Query=0; if b then incid=1;else incid=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; /*Must add washout and washout type to above files*/ data _diag _proc _ndc; if 0 then set _GroupListWashout; declare hash ht (hashexp:16, dataset:"_GroupListWashout"); ht.definekey('NGroup'); ht.definedata(ALL: 'YES'); ht.definedone(); do until(eof1); set _diag(in=a) _proc(in=b) _ndc(in=c) end=eof1; if ht.find()=0 then do; if a then output _diag; if b then output _proc; if c then output _ndc; end; end; stop; run; /*Reading proc and diag codes into vectors*/ %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); /*---------------------------------------------------------------------------------------------------*\ | 3.0 Extract medical (diagnosis and procedure 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.NGroup, proclist.Code, 1 as proc, 'S' as PDX, proclist.Query, proclist.Incid, proclist.washper, proclist.washTyp, proclist.CareSetting, proclist.Principal From indata.&proctable.(where=(compress(PX,'.') in:(&VECT_proc.))) as prctb, _proc as proclist Where prctb.PX_codetype = proclist.codetype and substr(compress(prctb.PX,'.'),1,proclist.length) = proclist.Code and prctb.Adate >= &QUERYFROM. - proclist.WashPer - (upcase(proclist.washtyp)='MIN')*999999; /*The 999999 is to extend the washout period long enough in the case where washtyp = 'MIN'*/ QUIT; proc sort nodupkey data = _procedures; by _all_; run; Title1 "Procedure code frequency printout"; proc freq data = _procedures; tables Code; run; Title1 ""; %END; proc contents data=_diag noprint out=_tilt_ ; data _null_ ; set _tilt_ ; call symput('dnobs',trim(left(put(nobs,15.)))) ; run; %PUT &dnobs.; %IF %EVAL(&dnobs.>0) %THEN %DO; PROC SQL Noprint; Create Table _Diagnosis as Select diagtb.PatId, diagtb.Adate, diagtb.EncType, diagtb.pdx, diaglist.NGroup, diaglist.Code, 0 as proc, diaglist.Query, diaglist.Incid, diaglist.washper, diaglist.washTyp, diaglist.CareSetting, diaglist.Principal From indata.&diatable.(where=(compress(DX,'.') in:(&VECT_diag.))) as diagtb, _diag as diaglist Where diagtb.DX_codetype = diaglist.codetype and substr(compress(diagtb.DX,'.'),1,diaglist.length) = diaglist.Code and diagtb.Adate >= &QUERYFROM. - diaglist.WashPer - (upcase(diaglist.washtyp)='MIN')*999999; /*The 999999 is to extend the washout period long enough in the case where washtyp = 'MIN'*/ QUIT; proc sort nodupkey data = _Diagnosis; by _all_; run; Title1 "Diagnosis code frequency printout"; proc freq data = _diagnosis; tables Code; run; Title1 ""; %END; %IF %EVAL(&pnobs.> 0 and &dnobs.>0) %THEN %DO; data _MasterQueryfile; set _procedures _diagnosis; by PatId; Clm = _N_; run; proc datasets library = work nolist; delete _procedures _diagnosis; quit; %END; %IF %EVAL(&pnobs.> 0 and &dnobs.=0) %THEN %DO; data _MasterQueryfile; set _procedures; by PatId; Clm = _N_; run; proc datasets library = work nolist; delete _procedures; quit; %END; %IF %EVAL(&pnobs.=0 and &dnobs.>0) %THEN %DO; data _MasterQueryfile; set _diagnosis; by PatId; Clm = _N_; run; proc datasets library = work nolist; delete _diagnosis; quit; %END; %MEND; %GETMEDS(); /*---------------------------------------------------------------------------------------------------*\ | 4.0 Start envelope | \*---------------------------------------------------------------------------------------------------*/ /****************************************************************\ | Reduce diagnosis table according to selected caresetting | | after having reclassified as inpatient all selected claims | | within admission and discharge dates of an inpatient | | stay, when necessary | \****************************************************************/ %MACRO ENVEL(); %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 caresettings" 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; %END; /*Filter claims with matching EncType and Principal status*/ %IF %SYSFUNC(exist(_MasterQueryfile))=1 %THEN %DO; data _MasterQueryFile(drop=EncType2 clm principal caresetting numarg i EncType PDX code /*codetype*/); set _MasterQueryFile; format EncType2 $2.; /*Case where all caresetting 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(); /*---------------------------------------------------------------------------------------------------*\ | 5.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.NGroup, CodeList.WashPer, CodeList.WashTyp, CodeList.Query, CodeList.Incid, CodeList.Code, Dispensing.Patid, Dispensing.RxDate as ADate, 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 - (upcase(CodeList.washtyp)='MIN')*999999; quit; proc sort nodupkey data = _drugs; by _ALL_; run; Title1 "Nine digit NDC code frequency printout"; proc freq data = _drugs; tables Code; run; Title1 ""; %END; %IF %EVAL(&ISCode11.>0) %THEN %DO; proc sql noprint; create table _predrugs as select CodeList.NGroup, CodeList.WashPer, CodeList.WashTyp, CodeList.Query, CodeList.Incid, CodeList.Code, Dispensing.Patid, Dispensing.RxDate as ADate, 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 - (upcase(CodeList.washtyp)='MIN')*999999; quit; proc sort nodupkey data = _predrugs; by _ALL_; run; Title1 "Eleven digit NDC code frequency printout"; proc freq data = _predrugs; tables Code; run; Title1 ""; proc datasets library=work nolist; 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; run; proc datasets library = work nolist; delete _drugs; quit; %END; %IF %SYSFUNC(exist(_MasterQueryFile))=0 and %SYSFUNC(exist(_Drugs))=1 %THEN %DO; proc datasets library = work nolist; change _drugs=_MasterQueryFile; quit; %END; %MEND; %WRAPPER; /*Determine if required coverage*/ %GLOBAL MEDCOV; %GLOBAL DRUGCOV; %MACRO WRAPPER; proc contents data=_proc noprint out=_tilt_ ; data _null_ ; set _tilt_ ; call symput('nobs1',trim(left(put(nobs,15.)))) ; run; proc contents data=_diag noprint out=_tilt_ ; data _null_ ; set _tilt_ ; call symput('nobs2',trim(left(put(nobs,15.)))) ; run; %IF %EVAL(&nobs1.+&nobs2.>0) %THEN %DO; %LET MEDCOV=Upcase(MedCov)='Y'; %END; %ELSE %DO; %LET MEDCOV=1; %END; proc contents data=_ndc noprint out=_tilt_ ; data _null_ ; set _tilt_ ; call symput('nobs',trim(left(put(nobs,15.)))) ; run; %IF %EVAL(&nobs.>0) %THEN %DO; %LET DRUGCOV=Upcase(DrugCov)='Y'; %END; %ELSE %DO; %LET DRUGCOV=1; %END; %MEND; %WRAPPER; %PUT &MEDCOV.; %PUT &DRUGCOV.; /*Restrict elig to respect data floor and cap*/ 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=(&DRUGCOV. and &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 Patid, Enr_Start, Enr_end; Quit; /*Creating continuous elig episodes*/ 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; /*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; 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; /*---------------------------------------------------------------------------------------------------*\ | 6.0 Create Fdate table | \*---------------------------------------------------------------------------------------------------*/ /*Find Min date per QueryGroup Date (now possible to have diags, */ /*drug, and procs in a same QueryGroup)*/ proc means noprint data =_MasterQueryFile nway; var ADate; Class PatId NGroup; where &QUERYFROM. <= ADate <= &QUERYTO. and Query=1; output out=_FDateTable(drop=_:) min = MinDt; run; /*Create QueryGroup date variables*/ proc transpose data = _FDateTable out = _FDateTable(drop=_NAME_); id NGroup; var MinDt; by PatId; run; data _FDateTable; set _FDateTable; format MinFdt MMDDYY10. MaxFdt MMDDYY10.; MinFdt = min(&GROUPVECT2.,.); MaxFdt = max(&GROUPVECT2.,.); run; /*Reduce enrollment to query patients*/ data _Enrollment(keep=PatId Enr_Start Enr_End sex birth_date); if 0 then set _FDateTable; declare hash ht (hashexp:16, dataset:'_FDateTable'); ht.definekey('PatId'); ht.definedata(ALL: 'YES'); ht.definedone(); do until(eof1); set _DenomInt end=eof1; if ht.find()=0 then do; output _Enrollment; end; end; stop; run; %LET I=1; %MACRO LOOPTHROUGH(); data _null_; set _GroupList end=fin; if fin then call symput('NQUERYGROUP',_n_); run; %PUT &NQUERYGROUP.; %DO i = 1 %TO &NQUERYGROUP.; data _null_; set _GroupList; if _n_ = &i. then do; call symput('ITGROUP',Group); call symput('ITNGROUP',NGroup); end; run; %PUT &ITGROUP; %PUT &ITNGROUP; data _SDFDateTable(keep=PatId); set _FDateTable; if &ITGROUP.; run; /*---------------------------------------------------------------------------------------------------*\ | 7.0 Query drug processing | \*---------------------------------------------------------------------------------------------------*/ /*Check for index date minus washout days for dispensings of query drug*/ /*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=(NGroup in(&ITNGROUP.))) end=eof1; InQuery = 0; if ht.find()=0 then do; if query and &QUERYFROM. <= ADate <= &QUERYTO. then InQuery = 1; output _Quantity; end; end; stop; run; proc sort data = _Quantity; by PatId Adate descending InQuery; run; proc sort nodupkey data =_Quantity out= _IEpisode; by PatId Adate; run; 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 InQuery; run; /*Defensive coding*/ Proc SQL Noprint; Create Table _CleanAdates as Select IDates.PatId, 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; proc sort nodupkey data = _Quantity out = _Quantity_; by PatId ADate; where Query and &QUERYFROM. <= ADate <= &QUERYTO.; run; proc sql noprint; create table _Quantity as Select qnt.* from _Quantity_ as qnt, _Enrollment as enrol Where qnt.PatId = enrol.PatId and enrol.Enr_Start <= qnt.ADate <= enrol.Enr_End Order by PatId, ADate; Quit; data _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; /*The patient can only be incident at the date of the indexd*/ /*Will also accomodate implementation of multiple indexdates*/ if NoClaimsBef = 1 and Wash = 1 then incident = 1; else incident = 0; Year = Year(ADate); Month = Month(ADate); 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(); %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',Group); call symput('ITNGROUP',NGroup); end; run; %PUT &ITGROUP; %PUT &ITNGROUP; data _null_; set _GroupListWashout; if NGroup = &ITNGROUP. then do; call symput('GROUPWASHOUT',WashPer); call symput('GROUPWASHTYP',WashTyp); end; run; %PUT &GROUPWASHOUT. &GROUPWASHTYP.; /*Creating claims dates listing - One record per date per QueryGroup*/ proc sort nodupkey data= _MasterQueryfile out=_Claims(keep=PatId ADate NGroup); by PatId ADate; where NGroup in(&ITNGROUP.); run; /*List for pre-cleaning*/ proc sort nodupkey data=_Claims out=_ClaimList(keep=patid); by PatId; run; /*Creating dates*/ data _AllMember; Merge _DenomInt(in=a) _ClaimList(in=b); /*Sorted*/ by PatId; if a; if a and b then withclaims=1; format QueryStartDate mmddyy10.; QueryStartDate=Max(&QUERYFROM.,MinAgeDate,enr_start); /*Minimum date the patient can have an HOI*/ Enr_Start=Max(QueryStartDate-&GROUPWASHOUT.,enr_start); enr_end=min(&QUERYTO.,enr_end,MaxAgeDate); if enr_end >= enr_start; drop i j Threshdate; run; /*Note: RECAP: Elig episodes now have been resized not to exceed this QueryGroup washout period prior to the patients starting to be at risk of having an event adding birth_dates and sex to claims and keeping claims within eligibility */ Proc SQL Noprint; Create Table _Claims2 as Select Enrol.*, claims.ADate, claims.NGroup From _AllMember(where=(withclaims=1)) as Enrol, _Claims as claims Where Enrol.PatId=claims.PatId and Enrol.enr_Start <= claims.ADate <= Enrol.enr_end order Patid, episode, Adate; *remove claims outside of period BEFORE AND AFTER; Quit; /*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 episode Enr_Start Sex birth_date MinAgeDate MaxAgeDate QueryStartDate FirstAgeGroup LastAgeGroup rename=Enr_Start=Adate) _Claims2(keep=Patid episode ADate Sex birth_date MinAgeDate MaxAgeDate QueryStartDate FirstAgeGroup LastAgeGroup NGroup) _AllMember(in=b keep=Patid episode Enr_End Sex birth_date MinAgeDate MaxAgeDate QueryStartDate FirstAgeGroup LastAgeGroup rename=Enr_End=Adate); by Patid episode ADate; if a then NGroup=-1; if b then NGroup=0; run; /*Creating HOI free episodes from gapless enrolment sequences*/ data _FindEpisodes; set _DenomToLoop; by PatId episode; /*Compute length of HOI-free period*/ lADate=lag(Adate); lNGroup=lag(NGroup); if first.episode then do; lADate=.; lNGroup=.; 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-(lNGroup eq -1); EndDate=ADate; /*Adate can be either a QueryGroup Incid/HOI claim or the end of elig sequence*/ QueryStartDate=Max(QueryStartDate,StartDate); /*Minimum date the patient can have an HOI in*/ /*this new broken down period*/ /*Patient must meet age criteria*/ if MinAgeDate <= EndDate and MaxAgeDate >= StartDate; /*Removes first record of episode*/ if diff ne .; /*Keeping episodes overlapping the Query Period - left trucated in the event of a INICD claim*/ if EndDate >= &QUERYFROM. and StartDate <= &QUERYTO.; if QueryStartDate <= EndDate; /*Time increments*/ FirstPer=intck("Months",&QUERYFROM.,QueryStartDate)+1; LastPer=intck("Months",&QUERYFROM.,EndDate)+1; keep Patid birth_date sex StartDate QueryStartDate EndDate first: last:; /*Note: RECALL StartDate=Start Date of the HOI Free continuous elig period (can includes washout time that may fall outside Query Period) QueryStartDate= Start Date of the HOI Free episode overlapping the Query Period (for patient days calcs) EndDate=End Date of the HOI Free episode */ run; /*Get index dates*/ data _IndexDtTable; set DPLocal.&DPID.&SITEID._&REQUESTID._MasterTable(where=(NGroup in(&ITNGROUP.) and ADate=IndexDt)); keep PatId IndexDt; run; /*Keeping indexdt, only useful for minimum washout cases when each PatId will have a*/ /*single index date*/ proc sort nodupkey data = _IndexDtTable; by PatId; run; data _Patient_days_M _Patient_days_F _Patient_days_U; merge _FindEpisodes(in=a) _IndexDtTable; 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 indexdt eq . or (indexdt ne . and StartDate<=indexdt) 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 modulor 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 indexdt eq . or (indexdt ne . and StartDate<=indexdt) 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 indexdt eq . or (indexdt ne . and StartDate<=indexdt) 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. count best12.; set _MasterDenomTable; NGroup = &ITNGroup.; 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(); /*---------------------------------------------------------------------------------------------------*\ | 8.0 Denom loop post processing -- Modulate program specific | \*---------------------------------------------------------------------------------------------------*/ proc sort data= DPLocal.&DPID.&SITEID._&REQUESTID._MasterDenomTable; by NGroup segment sex count; run; data _denom; set DPLocal.&DPID.&SITEID._&REQUESTID._MasterDenomTable; if upcase(segment)=:'INC' then do; segment=upcase(substr(segment,4,12)); Inc=1; end; else do; /*Prev*/ segment=upcase(substr(segment,5,11)); inc=0; end; time=0; if upcase(segment)=:'DAYS' then do; segment=upcase(substr(segment,5,11)); time=1; end; /*Assign age groupings for merging and create year month variables*/ if upcase(Segment)=:"AG" then do; i=compress(upcase(Segment),"AG"); format AgeGroup $13.; AgeGroup = scan("&AGESTRAT.",i,' '); end; if upcase(Segment)=:"PER" then do; StrataStart=intnx("Months",&QUERYFROM.,compress(upcase(Segment),"PER")-1,'sameday'); Year=year(StrataStart); Month=month(StrataStart); end; if upcase(Segment)=:"Y" then Year=compress(upcase(Segment),"Y"); keep segment inc NGroup count sex year month AgeGroup time; run; proc sort data=_denom; by NGroup 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 NGroup segment sex; DpID="&DpID."; SiteID="&SiteID."; drop inc; run; data _denom(drop=NGroup); if 0 then set _querydescrkey; declare hash ht (hashexp:16, dataset:"_querydescrkey"); ht.definekey('NGroup'); ht.definedata(ALL: 'YES'); ht.definedone(); do until(eof1); set _denom end=eof1; if ht.find()=0 then output; end; stop; run; proc sql noprint; create table MSOC.&DPID.&SITEID._&REQUESTID._DenTable0 as Select DpId, SiteId, Group, Sex, AgeGroup, year, month, IncDenCount, IncDaysCount, PrevDenCount, PrevDaysCount from _denom; quit; /*---------------------------------------------------------------------------------------------------*\ | 9.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 NGroup Year Month Incident Wash NewStart 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; data DPLocal.&DPID.&SITEID._&REQUESTID._QueryGroup(keep=PatID Sex Adate AgeGroup Group Year Month Incident Wash NewStart FIncDt); if 0 then set _querydescrkey; declare hash ht (hashexp:16, dataset:"_querydescrkey"); ht.definekey('NGroup'); ht.definedata(ALL: 'YES'); ht.definedone(); do until(eof1); set DPLocal.&DPID.&SITEID._&REQUESTID._QueryGroup end=eof1; if ht.find()=0 then output; end; stop; run; proc sql noprint; create table _Table0 as Select PatId, Group, ADate, FIncDt, Sex, AgeGroup, Year, Month, incident, NewStart from DPLocal.&DPID.&SITEID._&REQUESTID._QueryGroup order by PatId, Group; quit; /*For Prevalent*/ proc means data=_Table0 noprint; var NewStart; class Patid Group Sex AgeGroup Year Month; output out=_PTable0(drop=_freq_ where=(PatId ne "") rename=_type_=seg) sum(NewStart)=NewStarts N(NewStart)=NumClaims; run; proc means data=_PTable0 noprint nway missing; var NewStarts NumClaims; class Group Sex AgeGroup Year Month seg; output out=_PTable0(drop=_type_ rename=_freq_=Npts where=(Group ne "")) sum = ; run; /*For Incident*/ proc means data=_Table0 noprint; var NewStart Incident; class Patid Group Sex AgeGroup Year Month; where ADate >= FIncDt; output out=_ITable0(drop=_freq_ where=(PatId ne "") rename=_type_=seg) max(incident)=incident sum(NewStart)=NewStarts N(NewStart)=NumClaims; run; proc means data=_ITable0 noprint nway missing; var NewStarts NumClaims; class Group Sex AgeGroup Year Month 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; /*---------------------------------------------------------------------------------------------------*\ | 10.0 Build tables for both prevalent and incident users (5 tables in all) | \*---------------------------------------------------------------------------------------------------*/ /*********************************************************************************************************\ | Table 1: HOI Rates - Overall | | DPID SiteID QueryGroup PrevDenCount PrevNumCount PrevRate IncDenCount IncNumCount IncRate | | | | Table 2: HOI Yearly Rates | | DPID SiteID QueryGroup Year PrevDenCount PrevNumCount PrevRate IncDenCount IncNumCount IncRate | | | | Table 3: HOI Monthly Rates | | DPID SiteID QueryGroup Year Month PrevDenCount PrevNumCount PrevRate IncDenCount IncNumCount IncRate | | | | Table 4: HOI Rates - By Age Group and Sex | | DPID SiteID QueryGroup AgeGroup Sex PrevDenCount PrevNumCount PrevRate IncDenCount IncNumCount IncRate | \*********************************************************************************************************/ /*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; class Patid &class.; where ADate >= FIncDt; output out=_ITable&num.(drop=_:) max(incident)=incident sum(NewStart)=NewStarts N(NewStart)=NumClaims; run; proc means data=DPLocal.&DPID.&SITEID._&REQUESTID._QueryGroup nway noprint; var NewStart; class Patid &class.; output out=_PTable&num.(drop=_:) sum(NewStart)=NewStarts N(NewStart)=NumClaims; run; /*Prevalent table*/ proc means data=_PTable&num. nway noprint; var NewStarts NumClaims; class &class.; output out=PTable&num.(drop=_type_ rename=_freq_=Npts) sum=; run; /*Incident table*/ proc means data=_ITable&num. nway noprint; var NewStarts NumClaims; 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 /*PrevRate*/; 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 year,num=2,denomby=year, denomwhere=Segment=:"Y"); %CreateTables(class=Group year month,num=3,denomby=year month, denomwhere=Segment in:("PER")); %CreateTables(class=Group AgeGroup Sex,num=4,denomby=AgeGroup Sex, denomwhere=Segment in:("AG")); /*---------------------------------------------------------------------------------------------------*\ | 11.0 Export results to MSOC folder | \*---------------------------------------------------------------------------------------------------*/ /*(Export Results in .csv, .lst, and .sas7bdat format)*/ %MACRO export(DATAPARTNER,REQUESTID,NAME,VARTO); %IF %INDEX(&name.,table4) %THEN %DO; data &NAME.; set &NAME.; AgeGroup=TRANWRD(AgeGroup,'-',' to '); run; %END; /*Exporting raw tables into one .lst file*/ options nodate nonumber formdlim="-"; /*Select table name*/ data _Title; set titles; if table="&NAME." then do; &VARTO.=title; call symput('TITLE1',title);output; &VARTO.="Source: &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: HOI Rates - Prevalent - Overall - &STARTDATE."; output; Table="itable1"; Title="Table 1b: HOI Rates - Incident - Overall - &STARTDATE."; output; Table="ptable2"; Title="Table 2a: HOI Yearly Rates - Prevalent - &STARTDATE."; output; Table="itable2"; Title="Table 2b: HOI Yearly Rates - Incident - &STARTDATE."; output; Table="ptable3"; Title="Table 3a: HOI Monthly Rates - Prevalent - &STARTDATE."; output; Table="itable3"; Title="Table 3b: HOI Monthly Rates - Incident - &STARTDATE."; output; Table="ptable4"; Title="Table 4a: HOI Rates - By Age Group and Sex - Prevalent - &STARTDATE."; output; Table="itable4"; Title="Table 4b: HOI Rates - By Age Group and Sex - Incident - &STARTDATE."; output; Table="signature"; Title="Signature of Request - &STARTDATE."; output; run; %DO I=1 %TO 4; %EXPORT(&DPID.&SITEID., &REQUESTID., itable&i.,QueryGroup); %EXPORT(&DPID.&SITEID., &REQUESTID., ptable&i.,QueryGroup); %END; %MEND WRAPPER; %WRAPPER; data _NULL_; temp=DATETIME(); call symput('STOP',temp); seconds=temp-&start.; hours=int(seconds/3600); minutes=int((seconds-hours*3600)/60); seconds2=int((seconds-hours*3600-minutes*60)); call symput('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; /*---------------------------------------------------------------------------------------------------*\ | 12.0 Request 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."; 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; %PUT TOTAL RUN TIME was &hours. h &minutes. m &seconds. s; proc printto log=log print=print; run; %MEND; /*---------------------------------------------------------------------------------------------------*\ | 13.0 Sample call to MP5 macro | \*---------------------------------------------------------------------------------------------------*/ %MODULARPROGRAM5 ( REQUESTID=testnew5, ENROLGAP=0, QUERYFROM=01/01/2001, QUERYTO=12/31/2011, QUERYFILE=mpr39_query_mp5.sas7bdat, INCQUERYFILE=Mpr39_incquery_mp5.sas7bdat, AGESTRAT=0M-1M 1M-2M 2M-3M 3M-4M 4M-5M 5M-6M 6M-7M 7M-8M 8M-9M 9M-10M 10M-11M 11M-12M 12M-13M 13M-14M 14M-15M 15M-16M 16M-17M 17M-18M 18M-19M 19M-20M 20M-21M 21M-22M 22M-23M 23M-24M 24M-25M 25M-26M 26M-27M 27M-28M 28M-29M 29M-30M 30M-31M 31M-32M 32M-33M 33M-34M 34M-35M 35M-36M 36M-37M 37M-38M 38M-39M 39M-40M 40M-41M 41M-42M 42M-43M 43M-44M 44M-45M 45M-46M 46M-47M 47M-48M 48M-49M 49M-50M 50M-51M 51M-52M 52M-53M 53M-54M 54M-55M 55M-56M 56M-57M 57M-58M 58M-59M 59M-60M 60M-61M 61M-62M 62M-63M 63M-64M 64M-65M 65M-66M 66M-67M 67M-68M 68M-69M 69M-70M 70M-71M 71M-72M 72M-72M );