/*---------------------------------------------------------------------------------------------------*\ | PROGRAM NAME: | | MODULAR PROGRAM 8 Drug Use Studies - Comparison to Nationally Projected Databases | | | | Date: 04/14/14 | | Version: 1.0 | | | |-----------------------------------------------------------------------------------------------------| | | | The purpose of this program is to describe the uptake, persistence, and patterns of NMEs | | from their approval date. The program can also be used for drugs on the market. | | | |-----------------------------------------------------------------------------------------------------| | | | Program inputs: | | As specified in the user guide | | | | Program outputs: | | As specified in the user guide | | | |-----------------------------------------------------------------------------------------------------| | | | Contact: | | Mini-Sentinel Coordinating Center | | info@mini-sentinel.org | | | \*---------------------------------------------------------------------------------------------------*/ *System Options; options mprint linesize=150 pagesize=50 compress=yes reuse=no symbolgen validvarname=V7 ERRORS=0; /***********************/ /* User Inputs */ /***********************/ *1) Edit DPID and Site ID according to the table below; %let DPID=MS; %let SITEID=OCXX; /*----------------------------------------------------------------------*\ || || || DATA PARTNERS DPID SITEID || || ------------------------------------------------------------------- || || Aetna (one site) AE OS || || Healthcore (one site) HC OS || || Humana (one site) HU OS || || Vanderbilt University (one site) VB OS || || Optum (one site) OP OS || || HMORN (7 sites) || || Group Health Cooperative HM GHC || || Fallon Community Health Plan HM MPCI || || Henry Ford Health System HM HFHS || || Lovelace Health System HM LCF || || Marshfield Clinic HM MCRF || || HealthPartners HM HPRF || || Harvard Pilgrim Health Care HM HPHC || || Kaiser Permanente (6 sites) || || Kaiser Permanente Colorado KP CO || || Kaiser Permanente Georgia KP GA || || Kaiser Permanente Hawaii KP HA || || Kaiser Permanente Northern California KP NC || || Kaiser Permanente Northwest KP NW || || Kaiser Permanente Mid Atlantic KP MA || \*----------------------------------------------------------------------*/ *2) Edit this section to reflect your name for each Table/File (or View); %let ENRTABLE=enrollment; %let DEMTABLE=demographic; %let DISTABLE=dispensing; *3) Edit this section to reflect locations for the Libraries and Folders for Mini-Sentinel; * IMPORTANT NOTE: end of path separators are needed; * Windows-based platforms: "\", e.g. "C:\user\sas\" and not "C:\user\sas"; * Unix-based platforms: "/", e.g."/home/user/sas/" and not "/home/user/sas"; /********** FOLDER CONTAINING INPUT DATA FILES AND MSCDM DATA ***************************************/; /*Data in MSCDM Format*/ libname indata 'K:\mscdm\'; /*NDC/ICD9 Codes File Location*/ %let infolder=K:\inputfiles\; /*SAS Output Files*/ libname infolder "&infolder."; /********** FOLDER CONTAINING SUMMARY FILES TO BE EXPORTED TO Mini Sentinel Operations Center (MSOC)*/; /*CSV Output Files*/ %let MSOC=K:\msoc\; /*SAS Output Files*/ libname MSOC "&MSOC."; /*********** FOLDER CONTAINING FINAL DATASETS TO BE KEPT LOCAL AT THE PARTNER SITE (DPLocal)**********/; /*CSV Output Files*/ %let DPLocal=K:\dplocal\; /*SAS Output Files*/ libname DPLocal "&DPLocal."; /***************************** END OF USER EDITING AREA *********************************/ *MODIF HERE; %macro MODULARPROGRAM8(MSPROJID=,MSWPTYPE=,MSWPID=,MSVERID=,RUNID=,ENROLGAP=,QUERYFILE=,FLOORCAPFILE=, OUTTABLESFILE=,AGESTRAT=,DISPDAYSSUPSTRAT=,TOTALDAYSSUPTHRESH=); %let DPID=%LOWCASE(&DPID.); %let SITEID=%LOWCASE(&SITEID.); *MODIF HERE; %let MSPROJID=%LOWCASE(&MSPROJID.); %let MSWPTYPE=%LOWCASE(&MSWPTYPE.); %let MSWPID=%LOWCASE(&MSWPID.); %let MSVERID=%LOWCASE(&MSVERID.); %let UNDERSCORE=_; %let MSREQID=&MSPROJID.&UNDERSCORE.&MSWPTYPE.&UNDERSCORE.&MSWPID.&UNDERSCORE.&MSVERID.; %let RUNID=%LOWCASE(&RUNID.); %let QUERYFILE=%LOWCASE(&QUERYFILE.); %let FLOORCAPFILE=%LOWCASE(&FLOORCAPFILE.); %let OUTTABLESFILE=%LOWCASE(&OUTTABLESFILE.); proc printto log="&MSOC.&RUNID..log" new; run; %PUT "MODULARPROGRAM8_v1.0"; %let MPVer=1.0; %let MPNum=8; %global NQUERYGROUP; /***********************************************************************************************************/ /* 0. Preprossessing of 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-19 20-49 50-64 65-74 75+; %end; %if %str("&DISPDAYSSUPSTRAT.")=%str("") %then %do; %let DISPDAYSSUPSTRAT=0-14 15-29 30-59 60-89 90+; %end; %if %str("&TOTALDAYSSUPTHRESH.")=%str("") %then %do; %let TOTALDAYSSUPTHRESH=030 090 180 270 360; %end; %mend wrapper; %wrapper; %macro WORDNUMARG(VECTOR,OUTNAME); *Macro to count words in a macro VECTOR; %global &OUTNAME.; %let NUMARG=0; %do %while(%qscan(&VECTOR,&NUMARG+1,%str( )) ne %str()); %let NUMARG = %eval(&NUMARG+1); %end; %let &OUTNAME.=&NUMARG.; %mend WORDNUMARG; *Age Stratification; %WORDNUMARG(&AGESTRAT.,NUMAGECAT); %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.; *Individual Dispensings Stratification; %WORDNUMARG(&DISPDAYSSUPSTRAT.,NUMDISPDAYSSUP); %put &NUMDISPDAYSSUP.; data _null_; format DISPDAYSSUPTHRESH $1000.; do i=1 to &NUMDISPDAYSSUP.; DISPDAYSSUPTHRESH=strip(DISPDAYSSUPTHRESH)||" "||scan("&DISPDAYSSUPSTRAT.",i*2-1); if i=1 then MINDISPDAYSSUP=DISPDAYSSUPTHRESH; if i=&NUMDISPDAYSSUP. then do; MAXDISPDAYSSUP=compress(scan("&DISPDAYSSUPSTRAT.",i*2))*1; if MAXDISPDAYSSUP='' then MAXDISPDAYSSUP=99999; end; output; end; call symput('DISPDAYSSUPTHRESH',strip(DISPDAYSSUPTHRESH)); call symput('MINDISPDAYSSUP',strip(MINDISPDAYSSUP)); call symput('MAXDISPDAYSSUP',strip(MAXDISPDAYSSUP)); run; %put &DISPDAYSSUPTHRESH.; %put &NUMDISPDAYSSUP.; %put &MINDISPDAYSSUP; %put &MAXDISPDAYSSUP.; *Importing input files; %macro importfiles(var1,var2); %if %index(%UPCASE("&VAR1."),CPORT) %then %do; proc cimport infile="&infolder.&VAR1." library=infolder memtype=data; run; %end; %let VAR1=%substr(&VAR1.,1,%index(%UPCASE(&VAR1.),.)-1); data _&var2.; set infolder.&var1.; run; %mend; %importfiles(&QUERYFILE.,QUERYFILE); *File with Datafloor and Datacap; %let DATAFLOORDATE=.; %let DATACAPDATE=.; %macro wrapper(); %if %str("&FLOORCAPFILE.") eq %str("") %then %do; %let FLOORCAPFILE=FloorAndCap; proc sql noprint; create table _&FLOORCAPFILE. ( DataFloorDate num format=mmddyy10., DataCapDate num format=mmddyy10.); quit; %end; %else %do; %importfiles(&FLOORCAPFILE.,FLOORCAPFILE); %end; %mend wrapper; %wrapper(); proc sql noprint; select DataFloorDate into :DATAFLOORDATE from _FLOORCAPFILE; select DataCapDate into :DATACAPDATE from _FLOORCAPFILE; quit; *File with list of output tables; %macro wrapper(); %if %index(%upcase("&OUTTABLESFILE."),.) %then %do; %importfiles(&OUTTABLESFILE.,OUTTABLESFILE); %end; %mend wrapper; %wrapper(); *Set macro variables for dates; data _NULL_; temp=DATETIME(); call symput('START',temp); call symput('STARTDATE',put(datepart(temp),date9.)); call symput('STARTTIME',put(timepart(temp),time4.)); Call Symput('DATAFLOORDATEc',"&DATAFLOORDATE."); Call Symput('DATACAPDATEc',"&DATACAPDATE."); Call Symput('DATAFLOORDATE',put(input("&DATAFLOORDATE.",mmddyy10.),best12.)); Call Symput('DATACAPDATE',put(input("&DATACAPDATE.",mmddyy10.),best12.)); if missing(&DATAFLOORDATE.) then Call Symput('DATAFLOORDATE',put(input("01/01/1900",mmddyy10.),best12.)); if missing(&DATACAPDATE.) then Call Symput('DATACAPDATE',put(input("01/01/2900",mmddyy10.),best12.)); run; %put &DATAFLOORDATE; %put &DATACAPDATE; *Cleaning of QueryFile; data _QUERYFILE; set _QUERYFILE; format StudyStartDate mmddyy10.; Group = compress(trim(left(Group))); Group = translate(Group,'_','-', '_','.', '_',',', '_','%', '_','$', '_','!', '_','*', '_','&', '_','#', '_','@'); if StartDateType = 'A' then StudyStartDate=AppDate; else StudyStartDate=LaunchDate; if missing(EpisodeGap) then EpisodeGap=0; if strip(EpisodeGapType) = '' then do; EpisodeGapType='P'; EpisodeGap=30; end; if missing(MinDaySupp) then MinDaySupp=1; if missing(MaxDaySupp) then MaxDaySupp=90; drop StartDateType AppDate LaunchDate; run; *Copy to MSOC for summary program; data msoc.&RUNID._table1; set _QUERYFILE; run; proc sql noprint; select min(StudyStartDate) into :MINSTARTDATE from _QUERYFILE; quit; %put &MINSTARTDATE; *Storing all Groups into a macro vector; proc sort nodupkey data=_QUERYFILE out=_settings(keep=Group StudyStartDate StudyEndDate WashPer EnrDays MinDaySupp MaxDaySupp MinEpisDur EpisodeGapType EpisodeGap ExpExtPer StockPilType StockPilPercent NME); by Group; run; proc sql noprint; select Group into :GROUPVECT separated by ',' from _settings; quit; %put &GROUPVECT; *Create extraction lists for file pre-extraction and remove true duplicates; proc sort nodupkey data=_QUERYFILE; by Group codetype Code; run; *MODIF HERE; %macro ProcessInclude(filename=); %if %sysfunc(fileexist(&infolder.&filename.))=0 %then %do; %put ERROR: Include file &infolder.&filename. is required but does not exist; %abort cancel; %end; %include "&infolder.&filename."; %mend ProcessInclude; /* Ensure Stockpiling module is available */ %ProcessInclude(filename=ms_stockpiling.sas); /***********************************************************************************************************/ /* 1. Extract all dispensings for all study drugs identified between specified Start Dates and End Dates */ /***********************************************************************************************************/ *The GETDRUGS macro is exactly the same as the one in the toolkit. Could be replaced by an include eventually; %macro MS_GETDRUGS(INFILE=,NDCINFILE=,REMOVEDUP=,OUTFILE=); * Check if duplicates should be removed; %let distinct=; %if %upcase(%str("&REMOVEDUP.")) eq %str("YES") %then %do; %let distinct=distinct; %end; *Check if column proc is present and split codetype if not; %let procexist=0; data _null_; dsname="&NDCINFILE."; varname='proc'; dsid=open(dsname); if dsid > 0 then do; call symput('procexist',put(varnum(dsid,varname),best.)); end; rc=close(dsid); run; %put &procexist.; %if %eval(&procexist.=0) %then %do; data _codelist(keep=CodeType Code length); set &NDCINFILE.; length=length(Code); if CodeType in:('RX') then do; codetype=compress(codetype,'RX'); output; end; run; %end; %else %do; *Reduce NDCINFILE to minimum rows and columns for performance gain; data _codelist (keep=CodeType Code length); set &NDCINFILE. (where=(proc=0)); length=length(code); run; %end; proc sort nodupkey data=_codelist; by CodeType Code; run; * Check for NDC9 and NDC11 extraction; %let ISCode9=0; %let ISCode11=0; data _null_; set _codelist; if length=9 then call symput("ISCode9",1); if length=11 then call symput("ISCode11",1); run; %put &ISCode9; %put &ISCode11; * Extract 9 digit Codes claims; %if %eval(&ISCode9.>0) %then %do; proc sql noprint; create table _drugs9 as select &distinct. Dispensing.*, 0 as Proc, 9 as length from _codelist as CodeList, &INFILE. as Dispensing where substr(Dispensing.Ndc,1,9) = CodeList.code; quit; %end; * Extract 11 digit Codes claims; %if %eval(&ISCode11.>0) %then %do; proc sql noprint; create table _drugs11 as select &distinct. Dispensing.*, 0 as Proc, 11 as length from _codelist as CodeList, &INFILE. as Dispensing where substr(Dispensing.Ndc,1,11) = CodeList.code; quit; %end; * Generate OUTFILE and clean temporary datasets; %if %eval(&ISCode9.>0 and &ISCode11.>0) %then %do; data &OUTFILE.; set _drugs9 _drugs11; run; proc datasets library = work nolist; delete _drugs9 _drugs11 _codelist; quit; %end; %if %eval(&ISCode9.>0 and &ISCode11.=0) %then %do; data &OUTFILE.; set _drugs9; run; proc datasets library = work nolist; delete _drugs9 _codelist; quit; %end; %if %eval(&ISCode9.=0 and &ISCode11.>0) %then %do; data &OUTFILE.; set _drugs11; run; proc datasets library = work nolist; delete _drugs11 _codelist; quit; %end; %mend MS_GETDRUGS; %MS_GETDRUGS(INFILE=indata.&distable.,NDCINFILE=_QUERYFILE,REMOVEDUP=NO,OUTFILE=_PreDrugs); proc sql noprint; create table _Drugs as select CodeList.*, Dispensing.Patid, Dispensing.RxDate, Dispensing.RxSup, Dispensing.RxAmt, /*Dichotomic variables for exclusions below*/ max(Dispensing.RxSup < CodeList.MinDaySupp or Dispensing.RxSup > CodeList.MaxDaySupp) as NotInSuppRange from _QUERYFILE as CodeList, _PreDrugs(where=(&DATAFLOORDATE.<= RxDate <=&DATACAPDATE.)) as Dispensing where substr(Dispensing.ndc,1,Dispensing.length) = CodeList.code and CodeList.StudyStartDate - CodeList.WashPer <= RxDate <= CodeList.StudyEndDate group by Group, PatId; quit; proc datasets library=work nolist; delete _PreDrugs; quit; %macro wrapper(); data _GoodClaims _BadClaims; set _Drugs(rename=(RxDate=ADate)); SubGroup = Group; if NotInSuppRange = 0 then output _GoodClaims; else output _BadClaims; run; data _null_; set _settings end=fin; if fin then call symput('NQUERYGROUP',_n_); run; %put &NQUERYGROUP.; %do i=1 %to &NQUERYGROUP.; data _null_; set _settings; if _n_ = &i. then call symput('ITQUERYGROUP',strip(Group)); run; %put &ITQUERYGROUP; data _loopsettings; set _settings; if Group in("&ITQUERYGROUP.") then do; call symput('GROUPSTOCKPILTYPE',strip(StockPilType)); call symput('GROUPSTOCKPILPERCENT',StockPilPercent/100); output; end; drop Group; run; %put &GROUPSTOCKPILTYPE.; %put &GROUPSTOCKPILPERCENT.; *MODIF HERE; data _SPGoodClaims; set _GoodClaims; where Group="&ITQUERYGROUP."; run; %let SAMEDAY=aa; %let SUPRANGE=0<-HIGH; %let AMTRANGE=-99999999<-HIGH; %if %str("&GROUPSTOCKPILTYPE.")=%str("S") %then %do; %MS_STOCKPILING(INFILE=_SPGoodClaims, CLMDATE=ADate, CLMSUP=RxSup, CLMAMT=RxAmt, PROCFLAG=, PERCENTDAYS=, GROUPING=SubGroup, SAMEDAY=&SAMEDAY., SUPRANGE=&SUPRANGE., AMTRANGE=&AMTRANGE., ID=Group StudyStartDate StudyEndDate WashPer EnrDays MinDaySupp MaxDaySupp MinEpisDur EpisodeGapType EpisodeGap ExpExtPer NME Descr NotInSuppRange, OUTFILE=_SPGoodClaims, OUTFILEEXCL=_SPGoodClaimsEx); %end; %else %do; %MS_STOCKPILING(INFILE=_SPGoodClaims, CLMDATE=ADate, CLMSUP=RxSup, CLMAMT=RxAmt, PROCFLAG=, PERCENTDAYS=&GROUPSTOCKPILPERCENT., GROUPING=SubGroup, SAMEDAY=&SAMEDAY., SUPRANGE=&SUPRANGE., AMTRANGE=&AMTRANGE., ID=Group StudyStartDate StudyEndDate WashPer EnrDays MinDaySupp MaxDaySupp MinEpisDur EpisodeGapType EpisodeGap ExpExtPer NME Descr NotInSuppRange, OUTFILE=_SPGoodClaims, OUTFILEEXCL=_SPGoodClaimsEx); %end; %if &i.=1 %then %do; data _Drugs; set _SPGoodClaims; run; %end; %else %do; proc append base = _Drugs data = _SPGoodClaims force; run; %end; %end; data _Drugs; retain PatId Group ExpireDt RxSup RxAmt; set _Drugs _BadClaims; rename ADate=RxDate; drop SubGroup NumDispensing CodeType Code StockPilType StockPilPercent; run; %mend wrapper; %wrapper(); /***********************************************************************************************************/ /* 2. Identify index date for each selected member */ /***********************************************************************************************************/ *Find minimum date per drug of interest per patient in the study period; proc means noprint data =_Drugs nway; var RxDate; class PatId Group; where StudyStartDate <= RxDate <= StudyEndDate; output out=_FDateTable(drop=_:) min(RxDate) = MinDt; run; *Create Group Date Variables; proc transpose data = _FDateTable out = _FDateTable(drop=_NAME_); id Group; var MinDt; by PatId; run; proc transpose data=_settings out=_Groups(drop=_NAME_); id Group; run; data _Groups; set _Groups; if _N_ = 0; run; data _FDateTable; set _FDateTable; if _N_=0 then set _Groups; run; /***********************************************************************************************************/ /* 3. Extract enrollment and demographic information for study cohort members. */ /***********************************************************************************************************/ Proc SQL Noprint; Create Table _DenomInt as Select Demogs.PatId, Demogs.Birth_Date, Demogs.Sex, /*Dichotomic variables for exclusions below*/ CASE WHEN missing(Birth_Date)=1 THEN 1 ELSE 0 END as NoBD, CASE WHEN Sex not in ('M','F') THEN 1 ELSE 0 END as NoSex, CASE WHEN upcase(MEDCOV) ne 'Y' THEN 1 ELSE 0 END as NotMedCov, CASE WHEN upcase(DRUGCOV) ne 'Y' THEN 1 ELSE 0 END as NotDrugCov, max(Enrol.Enr_Start,&DATAFLOORDATE.) as Enr_Start, min(Enrol.Enr_End,&DATACAPDATE.) as Enr_End From indata.&ENRTABLE.(where=(not missing(DRUGCOV) and not missing(MEDCOV))) as Enrol, indata.&DEMTABLE.(where=((not missing(PatId)))) as Demogs Where Demogs.PatId=Enrol.PatId order Patid, Enr_Start, Enr_end; Quit; data _DenomInt; set _DenomInt(where=(Enr_End>=Enr_Start)); format MinAgeDate MaxAgeDate Enr_Start Enr_end mmddyy10.; by PatId; *Change episode if maximum allowable gap is reached or if changes occur in benefit coverage; if Enr_Start-lag(Enr_end)-1 > &ENROLGAP. or NotMedCov ne lag(NotMedCov) or NotDrugCov ne lag(NotDrugCov) then episode=episode+1; if first.Patid then episode=1; 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.,'sameday') - 1; retain episode; run; *Reconciliation of elig episodes; *No overlap of periods will remain after this; proc means data=_DenomInt nway noprint; var Enr_Start Enr_end; class PatId episode; id Birth_Date Sex MinAgeDate MaxAgeDate NoBD NoSex NotMedCov NotDrugCov; output out=_DenomInt(drop=_:) min(Enr_Start)= max(Enr_end)=; run; *Reduce enrollment to exposed patients; data _Enrollment(keep=PatId CovType Enr_Start Enr_End sex birth_date MinAgeDate MaxAgeDate NoBD NoSex NotMedCov NotDrugCov); 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; if NotMedCov=0 then CovType=0; else CovType=1; output _Enrollment; end; end; stop; run; *Stack _Enrollment to add the both coverage strata; data _Enrollment; set _Enrollment _Enrollment(in=b); if b then CovType=2; run; *Table to report exclusion information on patients and dispensings; proc sql noprint; create table _exclusions ( Group char(30) format=$30., CovType num format=best., ExclNumber num format=best., Exclusions char(60) format=$60., Counts num format=best.); quit; %macro waterfall(infile=,covType=,exclTitle=,exclVar=,exclNum=,where=,removeExclLines=); data _NULL_; call symput('ITGROUP',"'&ITQUERYGROUP.'"); run; %put &ITGROUP.; proc sql noprint; select sum(&exclVar.) into :EXLC_COUNTS from &infile. where &where. and not Excluded and &exclVar.; insert into _exclusions values(&ITGROUP., &covType., &exclNum., &exclTitle., &EXLC_COUNTS.); quit; * Reduce infile to eligible patients by flagging; %if %upcase(%str("&removeExclLines.")) = %str("YES") %then %do; data &infile.; set &infile.; if &where. and &exclVar.=1 then Excluded=1; run; %end; %mend waterfall; %let i=1; /***********************************************************************************************************/ /* 4. For each member exposed to a given study drug, apply inclusion criteria */ /* 5. For each cohort member, execute stockpiling, create treatment episodes, assign censoring */ /***********************************************************************************************************/ %macro CreateEpisodes(); *Loop for each query drug; data _null_; set _settings end=fin; if fin then call symput('NQUERYGROUP',_n_); run; %put &NQUERYGROUP.; %do i = 1 %to &NQUERYGROUP.; data _null_; set _settings; if _n_ = &i. then call symput('ITQUERYGROUP',strip(Group)); run; %put &ITQUERYGROUP; *MODIF HERE (MANTIS #921); data _loopsettings; set _settings; if Group in("&ITQUERYGROUP."); drop Group; run; data _SDFDateTable_(keep=PatId &ITQUERYGROUP. StudyStartDate StudyEndDate WashPer EnrDays MinDaySupp MaxDaySupp MinEpisDur EpisodeGapType EpisodeGap ExpExtPer NME); set _FDateTable; if _N_ = 1 then set _loopsettings; if &ITQUERYGROUP.; run; /***********************************************************************************************************/ /* 4.ii. Verify member meets inclusion criteria for age range, coverage required, and completeness */ /* of information on age and sex */ /* 4.iii. Remove all members with dispensings that fall outside of range of valid days supplied specified. */ /* Report on number of dispensing and members with out-of-range values */ /***********************************************************************************************************/ Proc SQL Noprint; Create Table _SDFDateTable(rename=&ITQUERYGROUP.=IndexDt) as Select FDTab.*, enrol.Birth_Date, enrol.Sex, enrol.Enr_Start, enrol.Enr_End, enrol.MinAgeDate, enrol.MaxAgeDate, enrol.NoBD, enrol.NoSex, (MaxAgeDate < StudyStartDate or MinAgeDate > StudyEndDate) as NotInAgeRange, enrol.NotMedCov eq 0 as MedCov, enrol.NotMedCov, enrol.NotDrugCov eq 0 as DrugCov, enrol.NotDrugCov, 1 as Patient From _SDFDateTable_ as FDTab, /*Remove duplicates from enrollment by keeping only the both coverage rows*/ _Enrollment(where=(CovType=2)) as enrol Where FDTab.PatId = enrol.PatId and enrol.Enr_Start <= FDTab.&ITQUERYGROUP. <= enrol.Enr_End order by PatId; Quit; *Coverage required prior index date for at least the washout period; data _SDFDateTable; set _SDFDateTable; if NME=1 then EffWashout = min(WashPer,IndexDt - StudyStartDate); else EffWashout = WashPer; if missing(EnrDays) then EnrDays = EffWashout; NotCovPrior=0; if IndexDt - max(EnrDays, EffWashout) < Enr_Start then NotCovPrior=1; run; Proc SQL Noprint; Create table _NoClaimBf as Select distinct idates.PatId From _SDFDateTable as idates, _Drugs(where=(Group in("&ITQUERYGROUP.") and not missing(ExpireDt))) as qty /*MODIF HERE (MANTIS #914)*/ Where idates.PatId = qty.PatId and ((idates.IndexDt - EffWashout)*(idates.NME=0)) <= qty.ExpireDt < idates.IndexDt Order by PatId; quit; Proc SQL noprint; Create Table _OutOfSuppRange as Select distinct PatId, NotInSuppRange From _Drugs(where=(Group in("&ITQUERYGROUP.") and NotInSuppRange = 1)) Order by PatId; quit; data _SDFDateTable; merge _SDFDateTable(in=a) _NoClaimBf(in=b); by PatId; if a; ClaimBf = 0; if b then ClaimBf = 1; run; %macro wrapper(); %do j=0 %to 2; data _SDFDateTable; set _SDFDateTable; Excluded=0; *Must be done in the DO loop since reseting is required for each coverage; run; %waterfall(infile=_SDFDateTable,covType=&j.,exclTitle='Members Exposed To Drug Of Interest', exclVar=Patient,exclNum=1,where=1,removeExclLines=NO); %waterfall(infile=_SDFDateTable,covType=&j.,exclTitle='Members Without Birth Date', exclVar=NoBD,exclNum=2,where=1,removeExclLines=YES); %waterfall(infile=_SDFDateTable,covType=&j.,exclTitle='Members Without Known Sex', exclVar=NoSex,exclNum=3,where=1,removeExclLines=YES); %waterfall(infile=_SDFDateTable,covType=&j.,exclTitle='Members Outside Age Range During Study', exclVar=NotInAgeRange,exclNum=4,where=1,removeExclLines=YES); %waterfall(infile=_SDFDateTable,covType=&j.,exclTitle='Members Without Drug Coverage', exclVar=NotDrugCov,exclNum=5,where=1,removeExclLines=YES); *Different types of coverage; %if %eval(&j.) eq 0 %then %do; %waterfall(infile=_SDFDateTable,covType=&j.,exclTitle='Members Without Medical Coverage', exclVar=NotMedCov,exclNum=6,where=1,removeExclLines=YES); %waterfall(infile=_SDFDateTable,covType=&j., exclTitle='Members Without Sufficient Coverage Prior To Index Date', exclVar=NotCovPrior,exclNum=7,where=1,removeExclLines=YES); %waterfall(infile=_SDFDateTable,covType=&j., exclTitle='Members With Claims Prior To Index Date', exclVar=ClaimBf,exclNum=8,where=1,removeExclLines=YES); data _outofsupprangeEx; merge _outofsupprange(in=a) _SDFDateTable(in=b keep=PatId Excluded); by PatID; if a; run; %waterfall(infile=_outofsupprangeEx,covType=&j., exclTitle='Members With Dispensing Supply Outside Allowable Range', exclVar=NotInSuppRange,exclNum=9,where=Not Excluded,removeExclLines=NO); %end; %if %eval(&j.) eq 1 %then %do; %waterfall(infile=_SDFDateTable,covType=&j.,exclTitle='Members With Medical Coverage', exclVar=MedCov,exclNum=6,where=1,removeExclLines=YES); %waterfall(infile=_SDFDateTable,covType=&j., exclTitle='Members Without Sufficient Coverage Prior To Index Date', exclVar=NotCovPrior,exclNum=7,where=1,removeExclLines=YES); %waterfall(infile=_SDFDateTable,covType=&j., exclTitle='Members With Claims Prior To Index Date', exclVar=ClaimBf,exclNum=8,where=1,removeExclLines=YES); data _outofsupprangeEx; merge _outofsupprange(in=a) _SDFDateTable(in=b keep=PatId Excluded); by PatID; if a; run; %waterfall(infile=_outofsupprangeEx,covType=&j., exclTitle='Members With Dispensing Supply Outside Allowable Range', exclVar=NotInSuppRange,exclNum=9,where=Not Excluded,removeExclLines=NO); %end; * Tricky call here to have correct number of lines in exclusion table since no medical exclusion; %if %eval(&j.) eq 2 %then %do; %waterfall(infile=_SDFDateTable,covType=&j.,exclTitle='Members With Incorrect Medical Coverage', exclVar=NotDrugCov,exclNum=6,where=1,removeExclLines=NO); %waterfall(infile=_SDFDateTable,covType=&j., exclTitle='Members Without Sufficient Coverage Prior To Index Date', exclVar=NotCovPrior,exclNum=7,where=NotDrugCov=0,removeExclLines=YES); %waterfall(infile=_SDFDateTable,covType=&j., exclTitle='Members With Claims Prior To Index Date', exclVar=ClaimBf,exclNum=8,where=1,removeExclLines=YES); data _outofsupprangeEx; merge _outofsupprange(in=a) _SDFDateTable(in=b keep=PatId Excluded); by PatID; if a; run; %waterfall(infile=_outofsupprangeEx,covType=&j., exclTitle='Members With Dispensing Supply Outside Allowable Range', exclVar=NotInSuppRange,exclNum=9,where=Not Excluded,removeExclLines=NO); %end; %end; %mend wrapper; %wrapper(); *Select all claims for this study drug and identify those that are within query dates; 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 _Drugs(where=(Group in("&ITQUERYGROUP.") and StudyStartDate <= RxDate /*<= StudyEndDate*/) keep=PatID Group RxDate ExpireDt RxAmt RxSup StudyStartDate StudyEndDate) end=eof1; if ht.find()=0 then do; if not excluded then output _Quantity; end; end; stop; run; *Coverage stratification; data _Quantity; set _Quantity(in=a) _Quantity(in=b); if a then do; if MedCov then CovType=0; if NotMedCov then CovType=1; end; if b then CovType=2; run; data _CleanQuantity_(drop = NotInSuppRange Excluded); if 0 then set _OutOfSuppRange; declare hash ht (hashexp:16, dataset:'_OutOfSuppRange'); ht.definekey('PatId'); ht.definedata(ALL: 'YES'); ht.definedone(); do until(eof1); set _Quantity end=eof1; if ht.find() ne 0 then do; output _CleanQuantity_; end; end; stop; run; /***********************************************************************************************************/ /* 5.ii. Calculate all gaps in treatment according to user-specified preference */ /* (i.e., percentage or fixed days) */ /***********************************************************************************************************/ Proc SQL Noprint; Create Table _CleanQuantity as Select qty.*, enrol.Enr_Start, enrol.Enr_End From _CleanQuantity_(drop=Enr_Start Enr_End) as qty, _Enrollment as enrol Where qty.PatId = enrol.PatId and qty.CovType = enrol.CovType and enrol.Enr_Start <= qty.RxDate <= enrol.Enr_End Order by PatId, CovType, RxDate; Quit; *Episode creation; data _CleanQuantity; set _CleanQuantity; by PatId CovType; *Create Episode Indicator; LRunOutDate = lag(Expiredt); LEnrStart = lag(Enr_Start); LRxSup = lag(RxSup); InEpisodeGap=0; if first.CovType then do; LRunOutDate=.; LEnrStart=.; LRxSup=RxSup; episode=1; gap=.; end; else do; gap = Rxdate - LRunOutDate - 1; *Change episode if exceed gap; if EpisodeGapType='F' and gap > EpisodeGap then episode=episode+1; else if EpisodeGapType='P' and gap > EpisodeGap*LRxSup/100 then episode=episode+1; else if lEnrStart ne Enr_Start then episode=episode+1; else do; InEpisodeGap=gap; end; end; if (gap = . or gap >= EffWashout) then do; NoclaimBf=1; end; else NoclaimBf=0; retain episode; run; *Find last gap in each episode (default for episode extension); data _lastEpisGap; set _CleanQuantity; by PatId CovType Episode; if last.Episode then output; keep PatID CovType Episode InEpisodeGap LRxSup; run; data _CleanQuantity (rename=InEpisodeGap=LEpisodeGap rename=LRxSup=LEpisodeSup); merge _CleanQuantity(drop=InEpisodeGap LRxSup) _lastEpisGap; by PatID CovType Episode; run; *Create episode summary for all episodes; proc means data=_CleanQuantity nway noprint; var Expiredt Rxdate RxSup NoclaimBf Enr_Start Enr_End; class PatId CovType episode; id Group EffWashout MinEpisDur ExpExtPer MinAgeDate MaxAgeDate LEpisodeGap IndexDt StudyStartDate StudyEndDate EpisodeGap EpisodeGapType LEpisodeSup; output out=_EPisodeSummary(drop=_Type_ rename=_freq_=NumDispensing) min(Rxdate Enr_Start Enr_End)=EpisodeStartDt MinEnrStart MinEnrEnd max(Expiredt NoclaimBf Enr_Start Enr_End)=EpisodeEndDt NoclaimBf MaxEnrStart MaxEnrEnd sum(RxSup)=EpisodeRxSup; run; /***********************************************************************************************************/ /* 4.i. Determine if member is an incident study drug user according to the incident user criteria */ /* defined above (i.e., washout period rule and benefits required prior to index date rule) */ /* 5.iii. Create treatment episodes according to user-specified preference regarding the episode extension */ /* 5.v. Assign a flag and end-date/censoring date to each treatment episode */ /***********************************************************************************************************/ data _NextDate; set _EPisodeSummary; episode=episode-1; rename EpisodeStartDt=NextEpisodeStartDt; keep PatId CovType episode EpisodeStartDt; run; data _ValidEpisodes; merge _EPisodeSummary(in=a) _NextDate; by PatId CovType episode; if a; *Washout; WNumDays = EpisodeStartDt - MinEnrStart; if WNumDays >= 0 and WNumDays >= EffWashout then Wash = 1; else Wash = 0; *Identify if episode is incident or not; if NoclaimBf = 1 and Wash = 1 then Incident = 1; else Incident = 0; *Censoring; CensEnd=0; CensEnrol=0; *Censoring due to end of study period; if EpisodeEndDt > StudyEndDate then do; EpisodeRxSup = EpisodeRxSup-(EpisodeEndDt-StudyEndDate); CensEnd=1; end; else if EpisodeGapType='F' and StudyEndDate < EpisodeEndDt + EpisodeGap then do; CensEnd=1; end; else if EpisodeGapType='P' and StudyEndDate < EpisodeEndDt + EpisodeGap*LEpisodeSup/100 then do; CensEnd=1; end; *Censoring due to disenrollment; if MaxEnrEnd < EpisodeEndDt then do; EpisodeRxSup = min(EpisodeRxSup,EpisodeRxSup-(EpisodeEndDt-MaxEnrEnd)); CensEnrol=1; end; else if EpisodeGapType='F' and MaxEnrEnd < EpisodeEndDt + EpisodeGap then do; CensEnrol=1; end; else if EpisodeGapType='P' and MaxEnrEnd < EpisodeEndDt + EpisodeGap*LEpisodeSup/100 then do; CensEnrol=1; end; *If both censoring types, keep disenrollment if Enrollment End < Study Period End; if CensEnd=1 and CensEnrol = 1 then do; if MaxEnrEnd < StudyEndDate then CensEnd=0; else CensEnrol=0; end; *Episode Duration; if missing(ExpExtPer) then ExpExtPer=LEpisodeGap; EpisodeEndDt=min(EpisodeEndDt+ExpExtPer,NextEpisodeStartDt-1,MaxEnrEnd,StudyEndDate); Excluded=0; EpisTooShort=0; EpisOutsideAge=0; if EpisodeEndDt - EpisodeStartDt + 1 < MinEpisDur then EpisTooShort=1; if MinAgeDate > EpisodeStartDt or MaxAgeDate < EpisodeStartDt then EpisOutsideAge=1; keep PatId Group CovType Episode EpisodeStartDt EpisodeEndDt IndexDt StudyStartDate StudyEndDate NumDispensing EpisodeRxSup Cens: EpisTooShort EpisOutsideAge Excluded; run; *Members with first episode outside age range; proc means noprint nway data= _ValidEpisodes; var EpisOutsideAge; class PatId CovType; where EpisOutsideAge and Episode=1; id Excluded; *All 0; output out=_EpisOutsideAge(drop=_:) max=; run; %macro wrapper(); %do j=0 %to 2; %waterfall(infile=_EpisOutsideAge,covType=&j.,exclTitle='Members With Episode Outside Age Range', exclVar=EpisOutsideAge,exclNum=10,where=covType=&j.,removeExclLines=NO); %end; %mend wrapper; %wrapper(); *Remove episodes outside age range; data _ValidEpisodes; set _ValidEpisodes; where EpisOutsideAge=0; run; /***********************************************************************************************************/ /* 5.iv. Remove members with treatment episodes that do not meet the minimum episode duration rule */ /***********************************************************************************************************/ *The minimum episode duration rule applies to the first episode only; proc means noprint nway data= _ValidEpisodes; var EpisTooShort; class PatId CovType; where EpisTooShort and Episode=1; id Excluded; *All 0; output out=_EpisTooShort(drop=_:) max=; run; %macro wrapper(); %do j=0 %to 2; %waterfall(infile=_EpisTooShort,covType=&j., exclTitle='Members Without Sufficient First Episode Duration', exclVar=EpisTooShort,exclNum=11,where=covType=&j.,removeExclLines=NO); %end; %mend wrapper; %wrapper(); *Remove patients with insufficient first episode duration; data _ValidEpisodes (drop=EpisTooShort); merge _ValidEpisodes _EpisTooShort(in=b keep=PatId CovType); by PatId CovType; if not b; run; data _CleanQuantity; merge _CleanQuantity _EpisTooShort(in=b keep=PatId CovType); by PatId CovType; if not b; run; %macro wrapper(); %do j=0 %to 2; %waterfall(infile=_ValidEpisodes,covType=&j.,exclTitle='Members Kept in Study', exclVar=Episode,exclNum=12,where=covType=&j and episode=1,removeExclLines=NO); %end; %mend wrapper; %wrapper(); %if &i.=1 %then %do; *Episodes; data DPLocal.&RUNID._ValidEpisodes; set _ValidEpisodes(where=(EpisodeEndDt>=EpisodeStartDt)); run; *Raw claims prior to stockpiling and some exclusions; data DPLocal.&RUNID._Quantity; set _Quantity(keep=PatId CovType Group RxDate IndexDt RxSup MinDaySupp MaxDaySupp Birth_Date Sex MinAgeDate MaxAgeDate StudyStartDate); run; *Clean claims after stockpiling and applicable exclusions (those found in episodes); data DPLocal.&RUNID._CleanQuantity; set _CleanQuantity(keep=PatId CovType Group RxDate IndexDt RxSup Birth_Date Sex MinAgeDate MaxAgeDate StudyStartDate); run; %end; %else %do; proc append base = DPLocal.&RUNID._ValidEpisodes data = _ValidEpisodes(where=(EpisodeEndDt>=EpisodeStartDt)) force; run; proc append base = DPLocal.&RUNID._Quantity data = _Quantity(keep=PatId CovType Group RxDate IndexDt RxSup MinDaySupp MaxDaySupp Birth_Date Sex MinAgeDate MaxAgeDate StudyStartDate) force; run; proc append base = DPLocal.&RUNID._CleanQuantity data = _CleanQuantity(keep=PatId CovType Group RxDate IndexDt RxSup Birth_Date Sex MinAgeDate MaxAgeDate StudyStartDate) force; run; %end; %end; %mend CreateEpisodes; %CreateEpisodes(); /***********************************************************************************************************/ /* 6. Create output tables */ /***********************************************************************************************************/ *Raw claims; *Defensive coding: if MaxDaySupp in the input file is greater than the upper bound of the last Dispensing Days Supply macro parameter then take the MaxDaySupp from the input file as the upper bound; proc sql noprint; select max(MaxDaySupp) into :MAXDAYSUP from _settings; quit; %put &MAXDAYSUP.; %macro wrapper; %if %eval(&MAXDAYSUP.)>%eval(&MAXDISPDAYSSUP.) %then %do; %let MAXDISPDAYSSUP = &MAXDAYSUP.; %let DISPDAYSSUPTHRESH = &DISPDAYSSUPTHRESH. &MAXDISPDAYSSUP.; %let NUMDISPDAYSSUP = %eval(&NUMDISPDAYSSUP. + 1); %end; %mend; %wrapper; data _QuantityStats; set DPLocal.&RUNID._Quantity; format AgeGroup $10.; do i=&NUMAGECAT. to 1 by -1; if IndexDt >= intnx(scan("&AGETYP.",i),birth_date,scan("&AGETHRESH.",i),'sameday') then do; AgeGroup = scan("&AGESTRAT.",i,' '); leave; end; end; Valid=1; do i=&NUMDISPDAYSSUP. to 1 by -1; if (i eq &NUMDISPDAYSSUP.) and (max(MinDaySupp,input(scan("&DISPDAYSSUPTHRESH.",i),best.)) <= RxSup < MaxDaySupp+1) then do; SupGroup=i; leave; end; else if i = 1 and MinDaySupp <= RxSup < min(input(scan("&DISPDAYSSUPTHRESH.",i+1),best.),MaxDaySupp+1) then do; SupGroup = i; leave; end; else if max(MinDaySupp,input(scan("&DISPDAYSSUPTHRESH.",i),best.)) <= RxSup < min(input(scan("&DISPDAYSSUPTHRESH.",i+1),best.),MaxDaySupp+1) then do; SupGroup = i; leave; end; if i = 1 then do; SupGroup = &NUMDISPDAYSSUP.+2; *Intentionnaly skip a SupGroup for In-Range subtotal...; Valid=0; end; end; Disp=1; drop i; run; *Clean claims; proc sort data=DPLocal.&RUNID._CleanQuantity out=_CleanQuantityStats; by Group CovType PatId RxDate; run; data _CleanQuantityStats; set _CleanQuantityStats; by Group CovType PatId; format AgeGroup $10.; do i=&NUMAGECAT. to 1 by -1; if IndexDt >= intnx(scan("&AGETYP.",i),birth_date,scan("&AGETHRESH.",i),'sameday') then do; AgeGroup = scan("&AGESTRAT.",i,' '); leave; end; end; if first.PatId then NUsers=1; Disp=1; drop i; run; /*******************************************************************************/ /* Exclusions. Excluded patients and dispensings and their reason */ /*******************************************************************************/ data _exclusions; set _exclusions; if missing(counts) then counts=0; run; proc sort data=_exclusions out=msoc.&RUNID._Exclusions; by Group CovType ExclNumber; run; /*******************************************************************************/ /* Table 2. Distribution of Days Supplied (%) per Dispensing */ /*******************************************************************************/ proc means noprint data = _QuantityStats nway; var Disp; class Group CovType SupGroup; id MinDaySupp MaxDaySupp; output out=_Table2(drop=_:) sum =; run; proc sql noprint; Create table _Table2a as Select *, sum(Disp) as TotDisp from _Table2 Group by Group,CovType Order by Group,CovType,SupGroup; quit; proc means data = _Table2a nway noprint; var Disp; class Group CovType; id TotDisp MinDaySupp MaxDaySupp; where SupGroup <= &NUMDISPDAYSSUP.; output out=_Table2b(drop=_:) sum =; run; data msoc.&RUNID._Table2; set _Table2a (in=a) _Table2b (in=b) _Table2b (in=c); if b then SupGroup=&NUMDISPDAYSSUP.+1; if c then do; SupGroup=&NUMDISPDAYSSUP.+3; Disp=TotDisp; end; run; *MODIF HERE (MANTIS #857); data _square; set _Table2b(where=(CovType=0)); do SupGroup=1 to &NUMDISPDAYSSUP.+3; do CovType=0 to 2; Disp=0; output; end; end; run; proc sort data=msoc.&RUNID._Table2; by Group CovType SupGroup; run; proc sort data=_square; by Group CovType SupGroup; run; data msoc.&RUNID._Table2; merge _square msoc.&RUNID._Table2; by Group CovType SupGroup; run; proc sort data=msoc.&RUNID._Table2; by Group CovType SupGroup; run; /*******************************************************************************/ /* Table 3. Number of Days Supplied per Valid Dispensing */ /*******************************************************************************/ proc means data = _QuantityStats(where=(valid=1)) nway noprint; class Group CovType RxSup; output out=msoc.&RUNID._Table3 (keep=Group CovType RxSup _Freq_ rename=_Freq_=Count) N=; run; /*******************************************************************************/ /* Table 4. Length of the First Treatment Episode */ /*******************************************************************************/ data _Table4; set DPLocal.&RUNID._ValidEpisodes; *Dispensings have been Stockpiled (forward filled); EpisodeLength = EpisodeEndDt - EpisodeStartDt + 1; CensNone=0; if CensEnd=0 and CensEnrol=0 then CensNone=1; keep PatId CovType Episode Group EpisodeStartDt EpisodeEndDt EpisodeLength CensNone CensEnd CensEnrol; run; proc means data = _Table4 nway noprint; class Group CovType Episode CensNone CensEnd CensEnrol EpisodeLength; output out=msoc.&RUNID._Table4(keep=Group CovType Episode CensNone CensEnd CensEnrol EpisodeLength _Freq_ rename=_Freq_=Count) N=; run; /*******************************************************************************/ /* Table 5. Length of the Interval between the First and Second Dispensing */ /*******************************************************************************/ proc sort data = _QuantityStats(keep=Group PatId CovType RxDate RxSup valid where=(valid=1)) out = _table5; by Group CovType PatId RxDate; run; data _table5; set _table5; by Group CovType PatId; format LRxDate mmddyy10.; LRxDate = lag(RxDate); LRxSup = lag(RxSup); if first.PatId then do; LRxDate = .; LRxSup = .; Dispensing = 0; end; Dispensing = Dispensing + 1; retain dispensing; run; *Important here to note that this must be done using data before stockpiling; data _table5; set _table5(where=(dispensing=2 and LRxSup > 0)); DInterval = (RxDate - LRxDate + 1) / LRxSup; run; proc means data = _Table5 nway noprint; class Group CovType DInterval; output out=msoc.&RUNID._Table5(keep=Group CovType DInterval _Freq_ rename=_Freq_=Count) N=; run; /*******************************************************************************/ /* Table 6a. Number of New Users by Month, Age Group, Sex and Benefit */ /* Table 6b. Number of Cumulative Users by Month, Age Group, Sex and Benefit */ /* Table 6c. Total Dispensings by Month, Age Group, Sex and Benefit */ /*******************************************************************************/ proc sort data=DPLocal.&RUNID._ValidEpisodes(where=(Episode=1) keep=PatId CovType Episode Group EpisodeEndDt) out=_Episode1; by Group CovType PatId; run; *MODIF HERE (MANTIS #858); proc sql noprint; select max(int((StudyEndDate - StudyStartDate)/30)+1) into :MAXNBMONTHS from _settings; quit; %put &MAXNBMONTHS.; data _square_; format AgeGroup $10. Sex $1. Percent $30.; Disp = 0; TotDisp=0; NUsers=0; do i=1 to &NUMAGECAT.; *Number of AgeGroups; AgeGroup = scan("&AGESTRAT.",i,' '); do CovType=0 to 2; * Number of Coverage types; do MntsFromApp=1 to &MAXNBMONTHS.; Sex = 'M'; output; Sex = 'F'; output; end; end; end; drop i; run; proc sql noprint; create table _square as select Group, _square_.* from _settings, _square_; quit; *Table 6a and 6b (6b is implicitly included in 6a and will be generated in summary); *Define Month from approval from index date; data _table6a; set _CleanQuantityStats(in=a) _square; if a then MntsFromApp = int((IndexDt - StudyStartDate)/30)+1; run; proc means noprint data = _table6a; var NUsers; class Group CovType Sex AgeGroup MntsFromApp; output out=_table6a(drop=_Freq_) sum=; run; *Table 6c; *Define Month from approval from dispensing date; data _table6c; set _CleanQuantityStats(in=a) _square; if a then MntsFromApp = int((RxDate - StudyStartDate)/30)+1; run; proc means noprint data = _table6c; var Disp; class Group CovType Sex AgeGroup MntsFromApp; output out=_table6c(drop=_Freq_) sum(Disp)=TotDisp; run; * Aggregation of New Users and Total Dispensing by month in a same table; data _table6; set _table6a(in=a) _table6c; if not a then NUsers=0; run; proc means noprint data = _table6; var NUsers TotDisp; class Group CovType Sex AgeGroup MntsFromApp; output out=msoc.&RUNID._table6(drop=_Freq_) sum=; run; /*******************************************************************************/ /* Figure 1. New Users by Age Group and Month */ /*******************************************************************************/ * EVERYTHING IS IN TABLE 6a; /*******************************************************************************/ /* Figure 2. Cumulative Users by Age Group and Month */ /*******************************************************************************/ * EVERYTHING IS IN TABLE 6b; /*******************************************************************************/ /* Figure 3. Total Dispensings by Age Group and Month */ /*******************************************************************************/ * EVERYTHING IS IN TABLE 6c; /*******************************************************************************/ /* Table 7. Descriptive Statistics on Length of Valid Treatment Episodes */ /*******************************************************************************/ data _Table7; set DPLocal.&RUNID._ValidEpisodes(keep=PatId CovType Episode Group EpisodeStartDt EpisodeEndDt CensEnd CensEnrol); * Dispensings have been Stockpiled (forward filled); EpisodeLength = EpisodeEndDt - EpisodeStartDt + 1; CensNone=0; if CensEnd=0 and CensEnrol=0 then CensNone=1; run; proc means data = _Table7 nway noprint; var EpisodeLength; class Group CovType CensNone CensEnd CensEnrol Episode EpisodeLength; output out=msoc.&RUNID._Table7(drop=_:) N=Count; run; /*******************************************************************************/ /* Table 8. Descriptive Statistics for Gaps Between Valid Treatment Episodes */ /*******************************************************************************/ data _Table8; set DPLocal.&RUNID._ValidEpisodes (keep=PatId CovType Episode Group EpisodeStartDt EpisodeEndDt CensEnd CensEnrol); run; proc sort data=_Table8; by Group CovType PatId Episode; run; data _Table8; set _Table8; by Group CovType PatId Episode; LEpisEndDate = lag(EpisodeEndDt); if first.PatId then LEpisEndDate=.; Gap = EpisodeStartDt - LEpisEndDate - 1; GapNo = Episode-1; CensNone=0; if CensEnd=0 and CensEnrol=0 then CensNone=1; run; proc means data = _Table8 nway missing noprint; var Gap; class Group CovType CensNone CensEnd CensEnrol GapNo Gap; output out=msoc.&RUNID._Table8(drop=_Type_ Dummy rename=_Freq_=Count) N=Dummy; run; /*******************************************************************************/ /* Figure 4. Distribution of Number of Episodes Within 12 Months */ /*******************************************************************************/ proc sql noprint; create table _Fig4 as select PatId, Group, CovType, Episode, int((IndexDt - StudyStartDate)/30)+1 as MntsFromApp, max(Episode) as TotalEpisodes from DPLocal.&RUNID._ValidEpisodes Group by PatId, Group, CovType Order by PatId, Group, CovType, Episode; Quit; proc sort nodupkey data = _Fig4; by PatId Group CovType; run; proc means data = _Fig4 nway noprint; var TotalEpisodes; class Group CovType MntsFromApp TotalEpisodes; output out=msoc.&RUNID._Fig4(drop=_Type_ Dummy rename=_Freq_=Count) N=Dummy; run; /*******************************************************************************/ /* Figure 5. Distribution of Total Days Supply Per User */ /*******************************************************************************/ proc means data = _QuantityStats(where=(valid=1)) nway noprint; var RxSup; class Group CovType PatId; output out=_Fig5(drop=_:) sum=TotalDaysSupp; run; data _DaysSupThresh; i=1; do while (scan("&TOTALDAYSSUPTHRESH.",i) ne ''); Thresh="_" || put(scan("&TOTALDAYSSUPTHRESH.",i),$3.) || "Days"; ThreshVal=input(scan("&TOTALDAYSSUPTHRESH.",i),best.); output; i=i+1; end; drop i; run; proc transpose data=_DaysSupThresh out=_DaysSupThresh(drop=_NAME_); var ThreshVal; id Thresh; run; data _Fig5; set _Fig5; if _N_=1 then set _DaysSupThresh; run; data _Fig5; set _Fig5; array Supplies{*} _:; do i=1 to dim(Supplies); if TotalDaysSupp >= Supplies{i} then Supplies{i}=1; else Supplies{i}=0; end; User=1; drop i; run; proc means noprint data = _Fig5 nway; var _:; class Group CovType; output out=_Fig5a(drop=_TYPE_ _FREQ_) sum=; run; proc means noprint data = _Fig5 nway; var User; class Group CovType; output out=_Fig5b(drop=_:) sum=TotUsers; run; data msoc.&RUNID._fig5; merge _Fig5a _Fig5b; by Group CovType; run; /*******************************************************************************/ /* Figure 6. Survival Curve for Length of the First Treatment Episode */ /*******************************************************************************/ data _Fig6; set DPLocal.&RUNID._Validepisodes(where=(Episode=1)); * Dispensings have been Stockpiled (forward filled); EpisodeLength = EpisodeEndDt - EpisodeStartDt + 1; CensNone=0; if CensEnd=0 and CensEnrol=0 then CensNone=1; keep PatId CovType Episode Group EpisodeStartDt EpisodeEndDt EpisodeLength Cens:; run; proc means data=_Fig6 noprint nway; var Episode; class Group CovType CensNone CensEnd CensEnrol EpisodeLength; output out=msoc.&RUNID._Fig6(drop=_:) sum=NPts; run; /*******************************************************************************/ /* Create Request Signature File */ /*******************************************************************************/ data _NULL_; temp=DATETIME(); seconds=temp-&start.; hours=int(seconds/3600); minutes=int((seconds-hours*3600)/60); seconds2=int((seconds-hours*3600-minutes*60)); call symput('STOP',temp); call symput('totalseconds',put(seconds,best.)); call symput('hours',put(hours,4.0)); call symput('minutes',put(minutes,2.0)); call symput('seconds',put(seconds2,2.0)); run; %put TOTAL RUN TIME was &hours. h &minutes. m &seconds. s; *MODIF HERE (MANTIS #878); data signature; MSReqID="&MSReqID."; MSProjID="&MSProjID."; MSWPType="&MSWPType."; MSWPID="&MSWPID."; MSVerID="&MSVerID."; DPID="&DPID."; SITEID="&SITEID."; RunID="&RunID."; MPNum="&MPNum."; MPVer="&MPVer."; ScenarioCnt=strip("&NQUERYGROUP."); format StartTime StopTime datetime21.2; StartTime=trim(left(&START.)); StopTime=trim(left(&STOP.)); format ExecutionTime Seconds $20.; ExecutionTime="&hours. h &minutes. m &seconds. s"; Seconds="&totalseconds. s"; ENROLGAP="&ENROLGAP."; AGESTRAT="&AGESTRAT."; DISPDAYSSUPSTRAT="&DISPDAYSSUPSTRAT."; TOTALDAYSSUPTHRESH="&TOTALDAYSSUPTHRESH."; QUERYFILE="&QUERYFILE"; FLOORCAPFILE="&FLOORCAPFILE"; DATAFLOORDATE="&DATAFLOORDATEc."; DATACAPDATE="&DATACAPDATEc."; OUTTABLESFILE="&OUTTABLESFILE"; run; proc transpose data=signature out=MSOC.&RUNID._signature (rename=_NAME_=Var rename=COL1=VALUE); var _ALL_; run; %macro REMOVETABLE(table=); %if %sysfunc(fileexist(msoc.&RUNID._&table.))=1 %then %do; proc datasets library=MSOC nolist nowarn; delete &RUNID._&table.; quit; %end; %mend REMOVETABLE; %macro OUTPUTSELECTION; %if %upcase("&OUTTABLESFILE.") ne %str("") %then %do; data _null_; set _OUTTABLESFILE; strng=cats('%REMOVETABLE(table=', TabName,');'); if substr(upcase(TabRequired),1,1) ne "Y" then call execute(%nrstr(strng)); run; %end; %mend OUTPUTSELECTION; %OUTPUTSELECTION; proc datasets nolist lib=work; delete _:; quit; proc printto log=log print=print;run; %mend MODULARPROGRAM8; *MODIF HERE; %MODULARPROGRAM8 (MSPROJID=to09y05, MSWPTYPE=mpr, MSWPID=wp01, MSVERID=b01, RUNID=r01, ENROLGAP=45, QUERYFILE=druguse.sas7bdat, FLOORCAPFILE=floorandcap.sas7bdat, OUTTABLESFILE=, AGESTRAT=00-19 20-49 50-64 65+, DISPDAYSSUPSTRAT=0-14 15-29 30-59 60-89 90+, TOTALDAYSSUPTHRESH=030 090 180 270 360 );