/*---------------------------------------------------------------------------------------------------*\ | PROGRAM NAME: Modular Program 1: Drug and\or Procedure Use and Exposure | | | | | | Date: 10/17/2012 | | Version: 2.0 | | | |-----------------------------------------------------------------------------------------------------| | | | The purpose of this program is to execute requests consistent with | | Modular Program 1 specifications | | | |-----------------------------------------------------------------------------------------------------| | | | Program inputs: | | As specified in Modular Program 1 documentation | | | | Program outputs: | | As specified in Modular Program 1 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; /***********************/ /* User Inputs */ /***********************/ /*1) Edit DPID and Site ID according to the table below*/ %LET DPID=MS; %LET SITEID=OC; /*2) Edit this section to reflect your name for each Table/File (or View)*/ %LET ENRTABLE=Enrollment; %LET DEMTABLE=Demographic; %LET DISTABLE=Dispensing; %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:\MP1\InputFiles\; /*Folder containing summary files to be exported*/ /*and shared with Mini-Sentinel Operations Center (MSOC)*/ %LET MSOC=C:\MP1\MSOC\; /*Folder containing final datasets to be kept local at the partner site (DPLocal)*/ %LET DPLOCAL=C:\MP1\DPLocal\; /*---------------------------------------------------------------------------------------------------*\ | End of User Inputs | \*---------------------------------------------------------------------------------------------------*/ /*****************************************************************************************************\ |**************************** PLEASE DO NOT EDIT CODE BELOW THIS LINE ********************************| \*****************************************************************************************************/ libname infolder "&infolder."; libname MSOC "&MSOC."; libname DPLocal "&DPLocal."; %MACRO MODULARPROGRAM1(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.); proc printto log="&MSOC.&DPID.&SITEID._&REQUESTID..log" new; run; %PUT "MINI_SENTINEL_MODULAR_PROGRAM1_v2.0"; /*---------------------------------------------------------------------------------------------------*\ | 0.1 Preprocessing 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.2 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 QueryGroup*/ data _&QUERYFILE.; set infolder.&QUERYFILE.; Group = compress(trim(left(Group))); Group = translate(Group,'_','-', '_','.', '_',',', '_','%', '_','$', '_','!', '_','*', '_','&', '_','#', '_','@'); WashTyp = Upcase(WashTyp); 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; /*Create empty dataset to avoid runtime errors*/ data _INCQUERYFILE; if 0 then set _&QUERYFILE.; keep code codetype group; stop; run; %END; %MEND; %WRAPPER; %MACRO wrapper(); /*Print 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; /*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; /*---------------------------------------------------------------------------------------------------*\ | 0.3 START 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 _proc(keep=NGroup Code Codetype length 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:('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 _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 _proc(in=a) _ndc(in=b) end=eof1; if ht.find()=0 then do; if a then output _proc; if b then output _ndc; end; end; stop; run; %GLOBAL VECT_PROC; %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); /*---------------------------------------------------------------------------------------------------*\ | 1.0 Extract claims | \*---------------------------------------------------------------------------------------------------*/ /*---------------------------------------------------------------------------------------------------*\ | 1.1 Extract procedure claims | \*---------------------------------------------------------------------------------------------------*/ %MACRO GETMEDS(); proc contents data=_proc noprint out=_tilt_ ; data _null_ ; set _tilt_ ; call symput('pnobs',trim(left(put(nobs,15.)))) ; run; %IF %EVAL(&pnobs.>0) %THEN %DO; PROC SQL Noprint; Create Table _procedures as Select prctb.PatId, prctb.Adate, proclist.NGroup, proclist.code, 1 as proc, proclist.Query, proclist.Incid, proclist.washper, proclist.washTyp, 1 as RxSup, 1 as RxAmt 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; /* Note: The 999999 is to extend the washout period in the case where washtyp = 'MIN'*/ quit; Title1 "Procedure code frequency printout"; proc freq data = _procedures; tables Code; run; Title1 ""; data _MasterQueryfile; set _procedures; run; proc datasets library = work nolist; delete _procedures; quit; %END; %MEND; %GETMEDS(); /*---------------------------------------------------------------------------------------------------*\ | 1.2 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, 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 - (upcase(CodeList.washtyp)='MIN')*999999; quit; Title1 "Nine digit NDC code frequency printout"; proc freq data = _drugs; tables Code; run; Title1 ""; %END; %IF %EVAL(&ISCode11.>0) %THEN %DO; proc sql noprint; create table _predrugs as select CodeList.NGroup, CodeList.WashPer, CodeList.WashTyp, CodeList.Query, CodeList.Incid, CodeList.Code, 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 - (upcase(CodeList.washtyp)='MIN')*999999; quit; Title1 "Eleven digit NDC code frequency printout"; proc freq data = _predrugs; tables Code; run; Title1 ""; proc datasets library=work nolist; 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 MEDCOV is required*/ %GLOBAL MEDCOV; %MACRO WRAPPER; proc contents data=_proc noprint out=_tilt_ ; data _null_ ; set _tilt_ ; call symput('nobs',trim(left(put(nobs,15.)))) ; run; %IF %EVAL(&nobs.>0) %THEN %DO; %LET MEDCOV=and upcase(MedCov)='Y'; %END; %ELSE %DO; %LET MEDCOV=; %END; %MEND; %WRAPPER; %PUT &MEDCOV.; /*---------------------------------------------------------------------------------------------------*\ | 1.3 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. and not missing(Enr_Start) and not missing(Enr_End))) as Enrol, indata.&DEMTABLE.(where=(Birth_Date ne . and not missing(PatId))) as Demogs Where Demogs.PatId=Enrol.PatId and intnx(scan("&AGETYP.",1),Demogs.birth_date,&MINAGE.,'sameday') <= &QUERYTO. order Patid, Enr_Start, Enr_end; Quit; data _DenomInt; set _DenomInt(where=(Enr_End >= Enr_Start)); by PatId; format MinAgeDate MaxAgeDate 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; /*---------------------------------------------------------------------------------------------------*\ | 1.4 Create FDATE table | \*---------------------------------------------------------------------------------------------------*/ /*Find Min date per QueryGroup Date*/ 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 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=1; /*---------------------------------------------------------------------------------------------------*\ | 2.0 Prevalent v Incident | \*---------------------------------------------------------------------------------------------------*/ /*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 do; call symput('ITGROUP',Group); call symput('ITNGROUP',NGroup); end; run; %PUT &ITGROUP; %PUT &ITNGROUP; /*For each query drug get index date for each Pat Id*/ data _SDFDateTable(keep=PatId); set _FDateTable; 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=(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 out=_IEpisode; by PatId Adate descending InQuery; run; proc sort nodupkey data = _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; 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; proc means noprint data = _Quantity nway; var RxAmt RxSup; class PatId ADate; id NGroup; where query and &QUERYFROM. <= ADate <= &QUERYTO.; output out=_Quantity_(drop=_:) sum(RxAmt RxSup) = RxAmt RxSup N(RxAmt) = NumDipensing; 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 indexdt*/ /*Will also accommodate implementation of multiple index dates*/ if NoClaimsBef = 1 and Wash = 1 then incident = 1; else incident = 0; RxYear = Year(ADate); RxMonth = 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; /*Create 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 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.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 enrollment 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 overlaping the study 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 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; /*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; %LET SEX=M; %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; NumDays= min(mdy(12,31,year),EndDate)-max(QueryStartDate,mdy(1,1,year))+1; /* Number of query days overlaping this year*/ 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; 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(); /*---------------------------------------------------------------------------------------------------*\ | 3.0 Denomloop post processing -- Modular 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 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 NGroup count sex RxYear Rxmonth 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, RxYear, RxMonth, IncDenCount, IncDaysCount, PrevDenCount, PrevDaysCount from _denom; quit; /*---------------------------------------------------------------------------------------------------*\ | 4.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 RxSup RxYear RxMonth NewStart NumDipensing 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; data DPLocal.&DPID.&SITEID._&REQUESTID._QueryGroup(keep=PatID Sex Adate AgeGroup Group RxYear RxMonth RxSup NumDipensing 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, RxYear, RxMonth, incident, NewStart, NumDipensing, RxSup 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; /*---------------------------------------------------------------------------------------------------*\ | 5.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; /*---------------------------------------------------------------------------------------------------*\ | 6.0 Signature | \*---------------------------------------------------------------------------------------------------*/ 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; proc printto log=log print=print; run; %MEND MODULARPROGRAM1; /*---------------------------------------------------------------------------------------------------*\ | 7.0 Sample call to MP1 macro | \*---------------------------------------------------------------------------------------------------*/ %MODULARPROGRAM1(REQUESTID=testnew1, ENROLGAP=0, QUERYFROM=01/01/2007, QUERYTO=01/31/2007, QUERYFILE=Queryhoismoke.sas7bdat, INCQUERYFILE=,0; AGESTRAT=00-64 );