**************************************************************************************************** * PROGRAM OVERVIEW **************************************************************************************************** * * PROGRAM: ms_cidatables.sas * Created (mm/dd/yyyy): 12/19/2014 * *-------------------------------------------------------------------------------------------------- * PURPOSE: * This macro will create the output tables for type 1 and type 2 cohorts. * * Program inputs: * -infolder.&cohortfile. * -infolder.&USERSTRATA. * -DPLocal.&RUNID._mstr * -DPLOCAL.&RUNID._DenomCounts * * Program outputs: * -DPLOCAL.&RUNID._NumCounts * -MSOC.&RUNID._t1_CIDA * -MSOC.&RUNID._t2_CIDA * -MSOC.&RUNID._t2_CIDA_PREV * * PARAMETERS: * -ds_suffix: suffix to add to output dataset - * either will be missing or set to _prev for prevalent tables * -where_clause: where clause to restrict &runid._mstr for computations * -followuptimevar: followuptime variable - will only exist for type 2 * * Programming Notes: * * *-------------------------------------------------------------------------------------------------- * CONTACT INFO: * Sentinel Coordinating Center * info@sentinelsystem.org * ***************************************************************************************************; %macro ms_cidatables(ds_suffix =, where_clause = , followuptimevar=); %put =====> MACRO CALLED: ms_cidatables; /*************************************************************************************************** Create separate prevalent and incident datasets when requested ***************************************************************************************************/ *Select all output levels relevant to t1_cida/t2_cida; data _t&type._levels; set userstrata; where lowcase(tableID) = "t&type.cida%sysfunc(compress(&ds_suffix.,_))"; run; %isdata(dataset=_t&type._levels); %if %eval(&nobs.>0) %then %do; /* Loop through groups. To prevent unnecessary looping, only loop if AgeGroup differs between groups If Prevalent datasets are requested only perform analysis for prevalent groups */ %if %str(&ds_suffix.) ne %str() %then %do; data _cohortfile; set infolder.&COHORTFILE. (where = (cohortgrp in ("&prevgroups."))); run; %end; %do group=1 %to %eval(&&cidatablesgroup&ds_suffix..); data _null_; set %if %str(&ds_suffix.) ne %str() %then %do; _cohortfile %end; %else %do; infolder.&COHORTFILE. %end;; if _n_ = &Group. then do; *Age stratification; if missing(agestrat) then do; call symputx("agestrat","00-01 02-04 05-09 10-14 15-18 19-21 22-44 45-64 65-74 75+"); end; else do; call symputx("agestrat",agestrat); end; call symputx("itgroup", cohortgrp); end; run; *if running all groups, grouplist is blank, otherwise only aggregate data for current Group and optional nvrexp group; %let grouplist = ; %if %eval(&&cidatablesgroup&ds_suffix.. >1) %then %do; %let grouplist = "&itgroup." "&itgroup._nvrexp"; %end; %else %if %eval(&&cidatablesgroup&ds_suffix.. >0) and %str(&ds_suffix.) ne %str() %then %do; %let grouplist = "&prevgroups."; %end; *reset macro vars; %let commonlevellist = ; %let uncommonlevellist = ; /**********************/ /* Compute Numerators */ /**********************/ %macro createNumTableEntries(class=, level=); *One record per patient for the class stratification; proc means data=_mstr nway noprint missing; var Patient AdjustedDisp RawDisp TotRxSup TotRxAmt HadEvent NumEvents timetocensor &followuptimevar.; class group PatId &class.; id patient; output out=_num0(drop=_:) max(Patient)=Npts sum(Patient)=Episodes sum(AdjustedDisp)=AdjustedCodeCount sum(RawDisp)=RawCodeCount sum(TotRxSup)=DaySupp sum(TotRxAmt)=AmtSupp sum(HadEvent)=Eps_wEvents sum(NumEvents)=All_Events sum(timetocensor)=timetocensor %if %str(&followuptimevar.) ne %str() %then %do; sum(&followuptimevar.)=&followuptimevar. %end; ; run; *One record per patient for the class stratification; proc means data=_num0 missing nway noprint; var Npts Episodes AdjustedCodeCount RawCodeCount DaySupp AmtSupp Eps_wEvents All_Events &followuptimevar. timetocensor; class group &class.; output out=_outlvl(drop=_:) sum=; run; %if %eval(&s.=1) %then %do; data _numcounts; set _outlvl; format level $3.; Level="&level."; run; %end; %else %do; data _numcounts; set _numcounts _outlvl(in=a); if a then do; Level="&level."; end; run; %end; proc datasets nowarn noprint lib=work; delete _outlvl _num0; quit; %mend; %isdata(dataset=_t&type._levels); %if %eval(&nobs.>0) %then %do; %do s = 1 %to %eval(&nobs.); data _null_; set _t&type._levels; if _n_ = &s. then do; call symputx('levelid', levelid); call symputx('levelvars', levelvars); end; run; *Square stratifications vars; %ms_squaredtableshell(squarevars =&levelvars., analysisvars=AdjustedDisp RawDisp TotRxSup TotRxAmt NumEvents &followuptimevar. timetocensor, groups=&grouplist. , analysis=N); /*Merge squared values with _mstr*/ data _mstr; set dplocal.&runid._mstr (in=a %if %length(&grouplist.) > 0 and %length(&where_clause.) > 0 %then %do; where=(group in (&grouplist.) and &where_clause.) %end; %else %if %length(&grouplist.) > 0 %then %do; where=(group in (&grouplist.)) %end; %else %if %length(&where_clause.) > 0 %then %do; where=(&where_clause.) %end;) _mastersquare(in=b); length patient hadevent 3; if a then do; patient=1; if NumEvents>0 then do; HadEvent=1; end; else do; HadEvent=0; NumEvents = 0; end; %if %str(&followuptimevar.) ne %str() %then %do; if &followuptimevar. = . then &followuptimevar. = 0; %end; end; else do; patient=0; HadEvent=0; end; run; /*Summarize data */ %createNumTableEntries(class=&levelvars.,level=&levelid.); proc datasets nowarn noprint lib=work; delete _mastersquare _mstr; quit; %end; %end; *The table to be generated in MSOC will be the result of a merge between numerators and donominators; data _numcounts; set _numcounts; *initialize missing vars to prevent merge w a r n i n g s; length race hispanic zip_uncertain $1. zip3 state hhs_reg cb_reg $7. agegroupnum month year quarter 3; if _n_ = 1 then do; dsid = open("_NumCounts"); if varnum(dsid,"race") = 0 then race =''; if varnum(dsid,"hispanic") = 0 then hispanic =''; if varnum(dsid,"zip_uncertain") = 0 then zip_uncertain =''; if varnum(dsid,"zip3") = 0 then zip3 =''; if varnum(dsid,"state") = 0 then state =''; if varnum(dsid,"hhs_reg") = 0 then hhs_reg =''; if varnum(dsid,"cb_reg") = 0 then cb_reg =''; if varnum(dsid,"sex") = 0 then sex =''; if varnum(dsid,"agegroup") = 0 then agegroup =''; if varnum(dsid,"agegroupnum") = 0 then agegroupnum =.; if varnum(dsid,"year") = 0 then year =.; if varnum(dsid,"month") = 0 then month =.; if varnum(dsid,"quarter") = 0 then quarter =.; rc= close(dsid); end; drop rc dsid; run; proc sort data=_numcounts; by group level sex AgeGroupNum year month quarter zip3 state hhs_reg cb_reg zip_uncertain race hispanic ; run; proc sort data=dplocal.&runid._denomCounts&ds_suffix. out=_denomcounts; by group level sex AgeGroupNum year month quarter zip3 state hhs_reg cb_reg zip_uncertain race hispanic ; %if %length(&grouplist.) > 0 %then %do; where group in (&grouplist.); %end; run; /*list of groups without denominators*/ %let nodenomgroups = ; %let mdenomgroups = ; proc sql noprint; select "'"||strip(group)||"'" into: nodenomgroups separated by ', ' from infolder.&&type&type.file. where upcase(outputdenom) = 'N'; select "'"||strip(group)||"'" into: mdenomgroups separated by ', ' from infolder.&&type&type.file. where upcase(outputdenom) = 'M'; quit; *Common level values between Nums and Denoms - merge these then merge in Num only data; proc sql noprint; select distinct quote(trim(left(num.level))) into: commonlevellist separated by ' ' from _numcounts as num, _denomcounts as den where num.level= den.level; quit; %if %str("&commonlevellist") ne %str("") %then %do; proc sql noprint; select distinct quote(trim(left(num.level))) into: uncommonlevellist separated by ' ' from _numcounts as num where num.level not in (&commonlevellist); quit; %end; %put levels with denoms: &commonlevellist.; %put levels without denoms: &uncommonlevellist.; data _commonlevels; length race hispanic zip_uncertain $1. zip3 state hhs_reg cb_reg $7.; merge _NumCounts (in=a) _DenomCounts (in=b); by group Level sex AgeGroupNum year month quarter zip3 state hhs_reg cb_reg zip_uncertain race hispanic ; %if %str("&commonlevellist") ne %str("") %then %do; where level in (&commonlevellist); %end; if not b then do; DenNumPts=0; DenNumMemDays=0; end; if b and missing(DenNumPts) then do; DenNumPts=0; DenNumMemDays=0; end; run; data _t&type._cida&ds_suffix.; set _commonlevels (in=a) %if %str(&uncommonlevellist.) ne %str() %then %do; _numcounts (in=b where=(level in (&uncommonlevellist.))) %end; ; %if %str(&uncommonlevellist.) ne %str() %then %do; if b then do; DenNumPts=.; DenNumMemDays=.; end; %end; %if %str(&nodenomgroups.) ne %str() %then %do; if group in (&nodenomgroups.) then do; DenNumPts=.; DenNumMemDays=.; end; %end; %if %str(&mdenomgroups.) ne %str() %then %do; if group in (&mdenomgroups.) then do; DenNumMemDays=.; end; %end; /*Do not report denonminators if running surveillance mode or cohort defined by age anniversary or calendar date*/ %if "&agedatecohort" = "Y" | (("&SURVEILLANCEMODE."="f" or "&SURVEILLANCEMODE."="p") and %eval(&Type. = 2)) %then %do; DenNumPts=.; DenNumMemDays=.; %end; /*Do no report denominators if the group is unexposed*/ if group = "&ITGROUP._nvrexp" then do; DenNumPts=.; DenNumMemDays=.; end; /*Do not report followuptime, Eps_wEvents, All_Events when conducting signal detection analysis*/ %if "&analysis." = "tree" %then %do; followuptime = .; eps_wevents = .; all_events = .; %end; run; *List of covariates; proc sql noprint; select distinct levelvars into: levelvars separated by ' ' from _t&type._levels; quit; %if %str("&levelvars.") = %str("") | %length(&levelvars) = 0 %then %do; %let covarstrat = ; %end; %else %do; %nonrep(invar = levelvars, outvar = levelvars1); *put covariates into macro var; %do c = 1 %to %sysfunc(countw(&levelvars1.)); %if %index(%scan(&levelvars1., &c.), covar) > 0 %then %do; %let covarstrat = &covarstrat. %scan(&levelvars1., &c.); %end; %end; %end; * Output to DPLOCAL and MSOC; %if %eval(&group.=1) %then %do; data DPLOCAL.&RUNID._numcounts&ds_suffix.; set _numcounts; run; data MSOC.&RUNID._t&type._cida&ds_suffix.; retain group Level sex agegroup agegroupnum year month quarter zip3 state hhs_reg cb_reg zip_uncertain race hispanic &covarstrat. Npts Episodes AdjustedCodeCount RawCodeCount DaySupp AmtSupp Eps_wEvents All_Events &followuptimevar. timetocensor DenNumPts DenNumMemDays; set _t&type._cida&ds_suffix.; keep group Level sex agegroup agegroupnum year month quarter zip3 state hhs_reg cb_reg zip_uncertain race hispanic &covarstrat. Npts Episodes AdjustedCodeCount RawCodeCount DaySupp AmtSupp Eps_wEvents All_Events &followuptimevar. timetocensor DenNumPts DenNumMemDays; run; %end; %else %do; proc append base=DPLOCAL.&RUNID._numcounts&ds_suffix. data=_numcounts force; run; proc append base=MSOC.&RUNID._t&type._cida&ds_suffix. data=_t&type._cida&ds_suffix.(keep=group Level sex agegroup agegroupnum year month quarter zip3 state hhs_reg cb_reg zip_uncertain &covarstrat. race hispanic Npts Episodes AdjustedCodeCount RawCodeCount DaySupp AmtSupp Eps_wEvents All_Events &followuptimevar. timetocensor DenNumPts DenNumMemDays) force; run; %end; %end; *loop through groups; %end; *output cida table; %put NOTE: ******** END OF MACRO: ms_cidatables ********; %mend ms_cidatables;