**************************************************************************************************** * PROGRAM OVERVIEW **************************************************************************************************** * * PROGRAM: ms_stockpiling.sas * * Created (mm/dd/yyyy): 07/31/2014 * Last modified: 09/22/2017 * Version: 2.2 * *-------------------------------------------------------------------------------------------------- * PURPOSE: * This program converts overlapping drug dispensings into non-overlapping dispensings. Two * different stockpiling algorithms are possible. For the first stockpiling algorithm, the * dispensing date of each overlapping claim is pushed forward to the end of days of supply of the * previous dispensing. For the second algorithm, users have the option to specify a maximum * allowable overlap percentage which, if not exceeded, the algorithm will stockpile as in the first * algorithm. Conversely, if the extent of the overlap is greater than the user-specified percentage, * the first dispensing will end on the day prior to the second dispensing and the second dispensing * will start on its original date. * * Program inputs: * -SAS data file with claims data to be stockpiled * * Program outputs: * -SAS data file (.SAS7BDAT format) containing the stockpiled claims * * PARAMETERS: * -INFILE = Input file with drug claims data * -CLMDATE = Name of variable in INFILE used to identify the claim date * -CLMSUP = Name of variable in INFILE used to identify the claim days of supply * -CLMAMT = Name of variable in INFILE used to identify the claim amount dispensed * -PROCFLAG = Name of variable used to identify claims other than dispensing claims in INFILE | * -PERCENTDAYS = Maximum percentage of overlap to push claim date forward * -GROUPING = Variables in INFILE used to group claims * -SAMEDAY = Specify how the final RxSup and RxAmt values will be determined in INFILE * -SUPRANGE = Range of RxSup values that are allowed in INFILE * -AMTRANGE = Range of RxAmt values that are allowed in INFILE * -ID = Variables from INFILE to keep that are not listed in the GROUPING parameter * -OUTFILE = Output file with stockpiled claims | * -OUTFILEEXCL = Output file with excluded claims information * * Programming Notes: * * * *-------------------------------------------------------------------------------------------------- * CONTACT INFO: * Mini-Sentinel Coordinating Center * info@mini-sentinel.org * *-------------------------------------------------------------------------------------------------- * CHANGE LOG: * * Version Date Initials Comment (reference external documentation when available) * ------- -------- -------- --------------------------------------------------------------- * 1.3 09/22/2015 AP Convert missing &PERCENTDAYS input from . to '' when * input file is specified * 2.0 11/09/2015 VC Use an episode-based approach to manage replacement claims * (when the value of the PERCENTDAYS parameter is exceeded) & * output replaced/negative supply claims * * 2.1 09/14/2017 AP Bug fix (QRP-436) * * 2.2 09/22/2017 AP Bug Fix (QRP-441) * ***************************************************************************************************; %macro MS_STOCKPILING(INFILE=,CLMDATE=,CLMSUP=,CLMAMT=,PROCFLAG=,PERCENTDAYS=, GROUPING=,SAMEDAY=,SUPRANGE=,AMTRANGE=,ID=,OUTFILE=,OUTFILEEXCL=); %put =====> MACRO CALLED: MS_STOCKPILING v2.2; %macro CREATE_WORDS(B=); /*---------------------------------------------------------------------------*/ /*--Create translation of format mapping to use to display ranges */ /*---------------------------------------------------------------------------*/ /* Parameter: B ==> Segment/(or section) of the format specifications to be converted to words*/ %LET BB = &B; %IF %EVAL(%INDEX(&BB,HIGH) NE 0) %THEN %DO; %LET P = %INDEX(&B,HIGH); %LET BB = %SUBSTR(&BB,1,%EVAL(&P-1))High; %END; %IF %EVAL(%INDEX(&BB,<-<) NE 0) %THEN %DO; %LET P = %INDEX(&B,<-<); %LET WRD = > %SUBSTR(&BB,1,%EVAL(&P-1)) to < %SUBSTR(&BB,%EVAL(&P+3)); %END; %ELSE %IF %EVAL(%INDEX(&BB,-<) NE 0) %THEN %DO; %LET P = %INDEX(&BB,-<); %LET WRD = %SUBSTR(&BB,1,%EVAL(&P-1)) to < %SUBSTR(&BB,%EVAL(&P+2)); %END; %ELSE %IF %EVAL(%INDEX(&BB,<-) NE 0) %THEN %DO; %LET P = %INDEX(&BB,<-); %LET WRD = > %SUBSTR(&BB,1,%EVAL(&P-1)) to %SUBSTR(&BB,%EVAL(&P+2)); %END; %ELSE %IF %EVAL(%INDEX(&BB,-) NE 0) %THEN %DO; %LET P = %INDEX(&BB,-); %LET WRD = %SUBSTR(&BB,1,%EVAL(&P-1)) to %SUBSTR(&BB,%EVAL(&P+1)); %END; %ELSE %DO; %LET WRD = &B; %END; &WRD %mend; %macro PARSE_RANGE(FMT=,A_=); /*------------------------------------------------------------------------------*/ /*--Parse the ranges specifications and create formats */ /*------------------------------------------------------------------------------*/ /* Parameters: FMT ==> Format Name A_ ==> User provided string defining the ranges*/ %GLOBAL WRD; /*---Low values not allowed, so convert to >= 0 ---*/ data _null_; length tmp_string $ 100; tmp_string = upcase("&A_"); i = index(tmp_string,'LOW'); if i then substr(tmp_string,i,3) = " 0<"; call symput("A_",trim(tmp_string)); run; proc format; /*---Create format for mapping values into ranges---*/ value &FMT. %LET I_ = 1; %LET C_ = %SCAN(&A_,&I_,' '); %DO %WHILE (%EVAL(%LENGTH(&C_) > 1)); &C_ = "&I_" %LET I_ = %EVAL(&I_+1); %LET C_ = %SCAN(&A_,&I_,' '); %END; OTHER=" "; /*---Create format for translation of formated category into words---*/ value &FMT.x %LET I_ = 1; %LET C_ = %SCAN(&A_,&I_,' '); %DO %WHILE (%EVAL(%LENGTH(&C_) > 1)); &I_ = "%CREATE_WORDS(B=&C_)" %LET I_ = %EVAL(&I_+1); %LET C_ = %SCAN(&A_,&I_,' '); %END; ; quit; %mend; *Determine lowest level of the grouping variable to apply algorithm; %let STOCKDIMS = PatId &GROUPING.; %put &STOCKDIMS; %let DIMCNT = %SYSFUNC(COUNTW(&STOCKDIMS.)); %put &DIMCNT; data _null_; call symput('SINGDIM',compress(scan("&STOCKDIMS.",&DIMCNT.))); run; %put &SINGDIM; *Determine if claims supply and amount are within allowable ranges; %if %str("&SUPRANGE.") eq %str("") %then %do; %let SUPRANGE=0<-HIGH; %end; %if %str("&AMTRANGE.") eq %str("") %then %do; %let AMTRANGE=0<-HIGH; %end; %PARSE_RANGE(FMT=suppfmt,A_=&SUPRANGE.); %PARSE_RANGE(FMT=amtfmt,A_=&AMTRANGE.); *Exclude dispensings with unacceptable values for days supplied and/or dispensed amount; data &OUTFILE. &OUTFILEEXCL.; set &INFILE.; *Check if excluded range: Outside of allowable format values; excluded_sup = (put(&CLMSUP.,suppfmt.) = ' '); excluded_amt = (put(&CLMAMT.,amtfmt.) = ' '); %if %str("&PROCFLAG.") ne %str("") %then %do; if &PROCFLAG. in(-2,-1,1,2) then do; output &OUTFILE.; end; else do; if (excluded_amt + excluded_sup) then do; ExclAmt = (excluded_amt=1 and excluded_sup=0); ExclSup = (excluded_amt=0 and excluded_sup=1); ExclBoth = (excluded_amt=1 and excluded_sup=1); Patient=1; output &OUTFILEEXCL.; end; else output &OUTFILE.; end; %end; %else %do; if (excluded_amt + excluded_sup) then do; ExclAmt = (excluded_amt=1 and excluded_sup=0); ExclSup = (excluded_amt=0 and excluded_sup=1); ExclBoth = (excluded_amt=1 and excluded_sup=1); Patient=1; output &OUTFILEEXCL.; end; else output &OUTFILE.; %end; run; *Defensive coding: ensure SAMEDAY parameter has valid values.; %if %eval(%length(&SAMEDAY.) < 2) %then %do; %let SAMEDAY=aa; %end; %let SAMEDAY_SUPP = %lowcase(%substr(&SAMEDAY.,1,1)); %let SAMEDAY_AMT = %lowcase(%substr(&SAMEDAY.,2,1)); %if %str("&SAMEDAY_SUPP.") ne %str("a") and %str("&SAMEDAY_SUPP.") ne %str("n") and %str("&SAMEDAY_SUPP.") ne %str("x") and %str("&SAMEDAY_SUPP.") ne %str("m") %then %do; %let SAMEDAY_SUPP = a; %end; %if %str("&SAMEDAY_AMT.") ne %str("a") and %str("&SAMEDAY_AMT.") ne %str("n") and %str("&SAMEDAY_AMT.") ne %str("x") and %str("&SAMEDAY_AMT.") ne %str("m") %then %do; %let SAMEDAY_AMT = a; %end; *Determine which operator to use for same day supplies; %if %str("&SAMEDAY_SUPP.") eq %str("a") %then %do; %let SAMEDAY_SUPP_OPER = sum; %end; %if %str("&SAMEDAY_SUPP.") eq %str("n") %then %do; %let SAMEDAY_SUPP_OPER = min; %end; %if %str("&SAMEDAY_SUPP.") eq %str("x") %then %do; %let SAMEDAY_SUPP_OPER = max; %end; %if %str("&SAMEDAY_SUPP.") eq %str("m") %then %do; %let SAMEDAY_SUPP_OPER = mean; %end; %if %str("&SAMEDAY_AMT.") eq %str("a") %then %do; %let SAMEDAY_AMT_OPER = sum; %end; %if %str("&SAMEDAY_AMT.") eq %str("n") %then %do; %let SAMEDAY_AMT_OPER = min; %end; %if %str("&SAMEDAY_AMT.") eq %str("x") %then %do; %let SAMEDAY_AMT_OPER = max; %end; %if %str("&SAMEDAY_AMT.") eq %str("m") %then %do; %let SAMEDAY_AMT_OPER = mean; %end; %put &SAMEDAY_SUPP. &SAMEDAY_SUPP_OPER. &SAMEDAY_AMT. &SAMEDAY_AMT_OPER.; *Force RxSup to 1 for non dispensing claims and discard them temporarily; %if %str("&PROCFLAG.") ne %str("") %then %do; data _procs &OUTFILE.; set &OUTFILE.; if &PROCFLAG. in(-2,-1,1,2) then do; &CLMSUP.=1; &CLMAMT.=1; NumDispensing=1; drop &PROCFLAG.; output _procs; end; if &PROCFLAG. not in(-2,-1,1,2) then do; drop &PROCFLAG.; output &OUTFILE.; end; run; %end; *Manage quantities occuring on the same day; proc means noprint data = &OUTFILE. nway missing; var &CLMSUP. &CLMAMT.; class PatId &GROUPING. &CLMDATE.; id &ID.; where &CLMSUP. > 0; output out=&OUTFILE.(drop=_:) &SAMEDAY_SUPP_OPER.(&CLMSUP.)= &SAMEDAY_AMT_OPER.(&CLMAMT.)= N(&CLMSUP.)=NumDispensing; run; *Adjust missing PERCENTDAYS parameter in the presence of stockpiling input file; %if %str("&PERCENTDAYS.") eq %str(".") %then %do; %let PERCENTDAYS=; %end; *Adjust claim dates; data &OUTFILE.; set &OUTFILE.; by PatId &GROUPING.; format &CLMDATE. ExpireDt lexpiredt mmddyy10.; ExpireDt=&CLMDATE.+(&CLMSUP.-1); if first.&SINGDIM. then do; overlap=.; StockEpisode=1; end; else do; overlap=lexpiredt-&CLMDATE.+1; if overlap > 0 then do; PercentDays = overlap/L&CLMSUP.; %if %str("&PERCENTDAYS.") eq %str("") %then %do; StockEpisode=StockEpisode; &CLMDATE.=lexpiredt+1; expireDt=&CLMDATE.+(&CLMSUP.-1); %end; %else %do; if PercentDays<=&PERCENTDAYS. then do; StockEpisode=StockEpisode; &CLMDATE.=lexpiredt+1; expireDt=&CLMDATE.+(&CLMSUP.-1); end; else do; StockEpisode=StockEpisode+1; FlagPercentDays=1; end; %end; end;*end if overlap > 0; *If no overlap then start new episode; else if overlap <= 0 then do; StockEpisode=StockEpisode+1; end; end; lexpiredt=ExpireDt; l&CLMSUP.=&CLMSUP.; retain StockEpisode lexpiredt L&CLMSUP. ; %if %str("&PERCENTDAYS.") eq %str("") %then %do; drop overlap lexpiredt StockEpisode l&CLMSUP. PercentDays; %end; run; %if %str("&PERCENTDAYS.") ne %str("") %then %do; /*Ensure condfrom/condto not missing*/ data &OUTFILE.; set &OUTFILE.; if condfrom = . then do; condfrom = -999999; end; if condto = . then do; condto = 999999; end; run; *Get minimum CLMDATE for each StockEpisodes; proc means data=&OUTFILE. nway noprint; var &CLMDATE. expiredt; class PatId &GROUPING. StockEpisode; output out=_Min&CLMDATE.(drop=_:) min(&CLMDATE.)= max(FlagPercentDays)= ; run; data &OUTFILE.; set &OUTFILE.; if condfrom = -999999 then do; condfrom = .; end; if condto = 999999 then do; condto = .; end; run; data _replacement; set _Min&CLMDATE.; StockEpisode=StockEpisode-1; keep PatId &GROUPING. StockEpisode &CLMDATE. FlagPercentDays; run; *Note: keep old amt and supply to recalculate new amt; data &OUTFILE. (rename=(NewExpireDt=ExpireDt)) _toexclude; ; merge &OUTFILE.(in=a drop=FlagPercentDays lexpiredt l&CLMSUP. rename=(&CLMSUP.=Orig&CLMSUP. &CLMAMT.=Orig&CLMAMT.)) _replacement (rename=(&CLMDATE.=&CLMDATE._r)); by PatId &GROUPING. StockEpisode; if a; format NewExpiredt mmddyy10.; if FlagPercentDays=1 then do; if expiredt>=&CLMDATE._r then NewExpiredt=&CLMDATE._r-1; else NewExpiredt=expiredt; end; else do; NewExpiredt=expiredt; end; &CLMSUP.=NewExpiredt-rxdate+1; *Calculate new CLMAMT; ChangeSUP=(Orig&CLMSUP.-&CLMSUP.)/Orig&CLMSUP.; &CLMAMT.=Orig&CLMAMT.-(ChangeSUP*Orig&CLMAMT.); drop overlap stockepisode PercentDays RxDate_r FlagPercentDays ExpireDt ChangeSUP; *Discard replaced claims (keep claims with positive supply); if &CLMSUP.>0 then do; output &OUTFILE.; end; else do; output _toexclude; end; run; *Append excluded claims; data &OUTFILEEXCL.; length rxsup rxamt 8; set &OUTFILEEXCL. _toexclude; run; %end;*end if PERCENTDAYS NE ""; *Append the procedures discarded above; %if %str("&PROCFLAG.") ne %str("") %then %do; data &OUTFILE.; set &OUTFILE. _procs(in=b); if b then ExpireDt = &CLMDATE.; run; %end; proc datasets library = work nolist nowarn; delete _deduct _procs _replacement _Min&CLMDATE. _toexclude; quit; %SYMDEL WRD; %put NOTE: ******** END OF MACRO: MS_STOCKPILING v2.2 ********; %mend MS_STOCKPILING;