**************************************************************************************************** * PROGRAM OVERVIEW **************************************************************************************************** * * PROGRAM: ms_cidatables.sas * * Created (mm/dd/yyyy): 12/19/2014 * Last modified: 02/26/2018 * Version: 1.6 * *-------------------------------------------------------------------------------------------------- * PURPOSE: * This macro will create the output tables for type 1 and type 2 cohorts. * * Program inputs: * -infolder.&cohortfile. * -DPLocal.&RUNID._mstr * -DPLOCAL.&RUNID._DenomCounts * * Program outputs: * -DPLOCAL.&RUNID._NumCounts * -MSOC.&RUNID._t1_CIDA * -MSOC.&RUNID._t2_CIDA * * PARAMETERS: * * 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 11/14/16 AP Added two new output tables: t1/t2_cida_zip3 and t1/t2_cida_state * * 1.4 05/05/17 AP 1. Added race and hispanic stratified output * 2. Moved macro call to group loop * 3. Removed CCIELIX and NumVisits stratifications * * 1.5 10/06/17 AP 1. Added new HHS and Census region stratifications * 2. Combined zip3/state table into t1/t2_cida table * * 1.6 02/26/18 RR Ability to create table for Never exposed cohort (QRP-465) ***************************************************************************************************; %macro ms_cidatables(); %put =====> MACRO CALLED: ms_cidatables v1.6; *creating age group dataset; data _age; numgrp = countw("&AGESTRAT."," ") ; do i=1 to numgrp; AgeGroup=scan("&AGESTRAT",i," "); AgeGroupSort=i; output; end; keep AgeGroup AgeGroupSort; run; /****************************************************************/ /* Squaring (or making sure all possible values are represented */ /****************************************************************/ data _AgeYearMonthSex; set cohortfile(where=(strip(group) in ("&itgroup.", "&itgroup._nvrexp")) drop=sex); format sex $1.; Type = &type; format AgeGroup $9.; numgrp = countw("&AGESTRAT."," ") ; NumDisp=0; TotRxSup=0; TotRxAmt=0; NumEvents=0; tte=0; do i=1 to numgrp; AgeGroup=scan("&AGESTRAT",i," "); AgeGroupSort=i; do per=1 to &nummonths.; *create year month variables; date=intnx('month',&startfollowup.,per-1,'begin'); month=month(date); year=year(date); sex="F"; output; sex="M"; output; sex="O"; output; end; end; keep group Type AgeGroup AgeGroupSort sex year month NumDisp TotRxSup TotRxAmt NumEvents tte; run; %if "&race_out" = "Y" %then %do; data _race; format race $1.; do i=0 to 5; race=i; output; end; run; proc sql noprint undo_policy=none; create table _AgeYearMonthSex as select base.*, com.race from _AgeYearMonthSex as base, _race as com; quit; %end; %if "&hispanic_out" = "Y" %then %do; data _hispanic; format hispanic $1.; hispanic="N"; output; hispanic="Y"; output; hispanic="U"; output; run; proc sql noprint undo_policy=none; create table _AgeYearMonthSex as select base.*, com.hispanic from _AgeYearMonthSex as base, _hispanic as com; quit; %end; /*Square uncertain geography*/ %if %str(&geog.) ne %str() or %length(&geog.) > 0. %then %do; data _uncertain; zip_uncertain='N'; output; zip_uncertain='Y'; output; run; proc sql noprint undo_policy=none; create table _AgeYearMonthSex as select base.*, com.zip_uncertain from _AgeYearMonthSex as base, _uncertain as com; quit; %end; /*Merge squared values with _mstr*/ %macro createdata(table=); proc sql noprint; create table _mstr&table. as select mstr.*, age.AgeGroupSort from _PtsMasterList as mstr left join _age as age on age.AgeGroup=mstr.AgeGroup; quit; data _mstr&table.; set _mstr&table.(in=a) _AgeYearMonthSex&table.(in=b); if a then do; %if %str(&table.) = %str() %then %do; month=month(indexdt); %end; patient=1; if NumEvents>0 then HadEvent=1; end; else do; patient=0; HadEvent=0; end; run; %mend; %createdata(table=); /*Create geographic stratified tables - will do separately because geographic stratifications do not include month and include zip_uncertain. Include these in 1 table will lead to unncessary summarization calculations*/ %if %str(&geog.) ne %str() or %length(&geog.) > 0. %then %do; %macro squaregeog(); %let num_geog = %sysfunc(countw(&geog)); %put &num_geog; %do g = 1 %to %eval(&num_geog); %let geog_stratification = %scan(&geog., &g.); %put Squaring &geog_stratification.; %if "&geog_stratification" = "ZIP3" %then %do; %let select = substr(zip,1,3) as zip3; %end; %if "&geog_stratification" = "STATE" %then %do; %let select = statecode as state; %end; %if "&geog_stratification" = "HHS_REG" %then %do; %let select = hhs_region as hhs_reg; %end; %if "&geog_stratification" = "CB_REG" %then %do; %let select = cb_region as cb_reg; %end; proc sql noprint; create table _geogcodes as select distinct &select. format=$7. length=7 from infolder.&zipfile.; quit; /*Add Missing and Invalid rows*/ data _geogcodes; set _geogcodes end=eof; format &geog_stratification. $7.; output; if eof then do; &geog_stratification.='Missing'; output; &geog_stratification.='Invalid'; output; %if "&geog_stratification" = "HHS_REG" | "&geog_stratification" = "CB_REG" %then %do; &geog_stratification.='Other'; output; %end; end; run; /*drop month to prevent unnecessary calculations*/ data _AgeYearMonthSex&geog_stratification.; set _AgeYearMonthSex; drop month; run; proc sort data=_AgeYearMonthSex&geog_stratification. nodup; by _all_; run; proc sql noprint undo_policy=none; create table _AgeYearMonthSex&geog_stratification. as select base.*, com.&geog_stratification. from _AgeYearMonthSex&geog_stratification. as base, _geogcodes as com; quit; proc datasets nowarn noprint lib=work; delete _uncertain _geogcodes; quit; *merge with _mstr; %createdata(table=&geog_stratification.); %end; %mend; %squaregeog(); %END; /**********************/ /* Compute Numerators */ /**********************/ %macro createNumTableEntries(table=,class=,outfile=,level=); *One record per patient for the class stratification; proc means data=_mstr&table. nway noprint missing; var Patient NumDisp TotRxSup TotRxAmt HadEvent NumEvents tte; class Group type PatId &class.; id patient; output out=_num0(drop=_:) max(Patient)=Npts sum(Patient)=Episodes sum(NumDisp)=Dispensings sum(TotRxSup)=DaySupp sum(TotRxAmt)=AmtSupp sum(HadEvent)=Eps_wEvents sum(NumEvents)=All_Events sum(tte)=tte; run; *One record per patient for the class stratification; proc means data=_num0 missing nway noprint; var Npts Episodes Dispensings DaySupp AmtSupp Eps_wEvents All_Events tte; ; class Group type &class.; output out=&outfile.(drop=_:) sum=; run; %IF %length(&class.)=0 %THEN %DO; data _NumCounts; *retain group sex agegroupsort year month DenNumPts DenNumMemDays; set &outfile.; format level $3.; Level="&level."; run; %END; %ELSE %DO; data _NumCounts; *retain group sex agegroupsort year month DenNumPts DenNumMemDays; set _NumCounts &outfile.(in=a); if a then do; Level="&level."; end; run; %END; %mend; *Overall; %createNumTableEntries(table=,class=,outfile=_ALL,level=000); *Base Tables; %createNumTableEntries(table=,class=year,outfile=_Y,Level=001); %createNumTableEntries(table=,class=Sex,outfile=_S,Level=002); %createNumTableEntries(table=,class=AgeGroup agegroupsort,outfile=_AG,Level=003); %createNumTableEntries(table=,class=Sex AgeGroup agegroupsort,outfile=_SAG,Level=004); %createNumTableEntries(table=,class=Sex AgeGroup agegroupsort year,outfile=_SAGY,Level=005); %createNumTableEntries(table=,class=Sex AgeGroup agegroupsort year month,outfile=_SAGYM,Level=006); %createNumTableEntries(table=,class=AgeGroup agegroupsort year,outfile=_AGY,Level=007); %createNumTableEntries(table=,class=AgeGroup agegroupsort year month,outfile=_AGYM,Level=008); %createNumTableEntries(table=,class=Sex year,outfile=_SY,Level=009); %createNumTableEntries(table=,class=Sex year month,outfile=_SYM,Level=010); %createNumTableEntries(table=,class=year month,outfile=_YM,Level=011); *Geographic Tables; %if %index(&geog.,ZIP3) > 0 %then %do; %createNumTableEntries(table=ZIP3,class=zip3,outfile=_Z,Level=020); %createNumTableEntries(table=ZIP3,class=zip3 zip_uncertain,outfile=_Z_UN,Level=021); %createNumTableEntries(table=ZIP3,class=zip3 Sex,outfile=_Z_S,Level=022); %createNumTableEntries(table=ZIP3,class=zip3 Sex zip_uncertain,outfile=_Z_S_UN,Level=023); %createNumTableEntries(table=ZIP3,class=zip3 AgeGroup agegroupsort,outfile=_Z_AG,Level=024); %createNumTableEntries(table=ZIP3,class=zip3 AgeGroup agegroupsort zip_uncertain ,outfile=_Z_AG_UN,Level=025); %createNumTableEntries(table=ZIP3,class=zip3 year,outfile=_Z_Y,Level=026); %createNumTableEntries(table=ZIP3,class=zip3 year zip_uncertain,outfile=_Z_Y_UN,Level=027); *Race/Hispanic Tables; %if "&race_out" = "Y" %then %do; %createNumTableEntries(table=ZIP3,class=zip3 race,outfile=_Z_R,Level=028); %createNumTableEntries(table=ZIP3,class=zip3 race zip_uncertain,outfile=_Z_R_UN,Level=029); %end; %if "&hispanic_out" = "Y" %then %do; %createNumTableEntries(table=ZIP3,class=zip3 hispanic,outfile=_Z_H,Level=030); %createNumTableEntries(table=ZIP3,class=zip3 hispanic zip_uncertain,outfile=_Z_H_UN,Level=031); %end; %end; %if %index(&geog.,STATE) > 0 %then %do; %createNumTableEntries(table=STATE,class=STATE,outfile=_S,Level=040); %createNumTableEntries(table=STATE,class=STATE zip_uncertain,outfile=_S_UN,Level=041); %createNumTableEntries(table=STATE,class=STATE Sex,outfile=_S_S,Level=042); %createNumTableEntries(table=STATE,class=STATE Sex zip_uncertain,outfile=_S_S_UN,Level=043); %createNumTableEntries(table=STATE,class=STATE AgeGroup agegroupsort,outfile=_S_AG,Level=044); %createNumTableEntries(table=STATE,class=STATE AgeGroup agegroupsort zip_uncertain ,outfile=_S_AG_UN,Level=045); %createNumTableEntries(table=STATE,class=STATE year,outfile=_S_Y,Level=046); %createNumTableEntries(table=STATE,class=STATE year zip_uncertain,outfile=_S_Y_UN,Level=047); *Race/Hispanic Tables; %if "&race_out" = "Y" %then %do; %createNumTableEntries(table=STATE,class=STATE race,outfile=_S_R,Level=048); %createNumTableEntries(table=STATE,class=STATE race zip_uncertain,outfile=_S_R_UN,Level=049); %end; %if "&hispanic_out" = "Y" %then %do; %createNumTableEntries(table=STATE,class=STATE hispanic,outfile=_S_H,Level=050); %createNumTableEntries(table=STATE,class=STATE hispanic zip_uncertain,outfile=_S_H_UN,Level=051); %end; %end; %if %index(&geog.,HHS_REG) > 0 %then %do; %createNumTableEntries(table=HHS_REG,class=HHS_REG,outfile=_HHS,Level=070); %createNumTableEntries(table=HHS_REG,class=HHS_REG zip_uncertain,outfile=_HHS_UN,Level=071); %createNumTableEntries(table=HHS_REG,class=HHS_REG Sex,outfile=_HHS_S,Level=072); %createNumTableEntries(table=HHS_REG,class=HHS_REG Sex zip_uncertain,outfile=_HHS_S_UN,Level=073); %createNumTableEntries(table=HHS_REG,class=HHS_REG AgeGroup agegroupsort,outfile=_HHS_AG,Level=074); %createNumTableEntries(table=HHS_REG,class=HHS_REG AgeGroup agegroupsort zip_uncertain ,outfile=_HHS_AG_UN,Level=075); %createNumTableEntries(table=HHS_REG,class=HHS_REG year,outfile=_HHS_Y,Level=076); %createNumTableEntries(table=HHS_REG,class=HHS_REG year zip_uncertain,outfile=_HHS_Y_UN,Level=077); *Race/Hispanic Tables; %if "&race_out" = "Y" %then %do; %createNumTableEntries(table=HHS_REG,class=HHS_REG race,outfile=_HHS_R,Level=078); %createNumTableEntries(table=HHS_REG,class=HHS_REG race zip_uncertain,outfile=_HHS_R_UN,Level=079); %end; %if "&hispanic_out" = "Y" %then %do; %createNumTableEntries(table=HHS_REG,class=HHS_REG hispanic,outfile=_HHS_H,Level=080); %createNumTableEntries(table=HHS_REG,class=HHS_REG hispanic zip_uncertain,outfile=_HHS_H_UN,Level=081); %end; %end; %if %index(&geog.,CB_REG) > 0 %then %do; %createNumTableEntries(table=CB_REG,class=CB_REG,outfile=_CB,Level=090); %createNumTableEntries(table=CB_REG,class=CB_REG zip_uncertain,outfile=_CB_UN,Level=091); %createNumTableEntries(table=CB_REG,class=CB_REG Sex,outfile=_CB_S,Level=092); %createNumTableEntries(table=CB_REG,class=CB_REG Sex zip_uncertain,outfile=_CB_S_UN,Level=093); %createNumTableEntries(table=CB_REG,class=CB_REG AgeGroup agegroupsort,outfile=_CB_AG,Level=094); %createNumTableEntries(table=CB_REG,class=CB_REG AgeGroup agegroupsort zip_uncertain ,outfile=_CB_AG_UN,Level=095); %createNumTableEntries(table=CB_REG,class=CB_REG year,outfile=_CB_Y,Level=096); %createNumTableEntries(table=CB_REG,class=CB_REG year zip_uncertain,outfile=_CB_Y_UN,Level=097); *Race/Hispanic Tables; %if "&race_out" = "Y" %then %do; %createNumTableEntries(table=CB_REG,class=CB_REG race,outfile=_CB_R,Level=098); %createNumTableEntries(table=CB_REG,class=CB_REG race zip_uncertain,outfile=_CB_R_UN,Level=099); %end; %if "&hispanic_out" = "Y" %then %do; %createNumTableEntries(table=CB_REG,class=CB_REG hispanic,outfile=_CB_H,Level=100); %createNumTableEntries(table=CB_REG,class=CB_REG hispanic zip_uncertain,outfile=_CB_H_UN,Level=101); %end; %end; *Race Tables; %if "&race_out" = "Y" %then %do; %createNumTableEntries(table=,class=race,outfile=_R,Level=110); %createNumTableEntries(table=,class=race sex,outfile=_R_S,Level=111); %createNumTableEntries(table=,class=race agegroup agegroupsort,outfile=_RAG,Level=112); %createNumTableEntries(table=,class=race year,outfile=_RY,Level=113); %createNumTableEntries(table=,class=race year month,outfile=_RYM,Level=114); %end; *Hispanic Tables; %if "&hispanic_out" = "Y" %then %do; %createNumTableEntries(table=,class=hispanic,outfile=_H,Level=115); %createNumTableEntries(table=,class=hispanic sex,outfile=_H_S,Level=116); %createNumTableEntries(table=,class=hispanic agegroup agegroupsort,outfile=_HAG,Level=117); %createNumTableEntries(table=,class=hispanic year,outfile=_HY,Level=118); %createNumTableEntries(table=,class=hispanic year month,outfile=_HYM,Level=119); %end; *Uncertain Geographic; %if %str(&geog.) ne %str() or %length(&geog.) > 0. %then %do; %createNumTableEntries(table=,class=zip_uncertain,outfile=ZU,Level=060); %createNumTableEntries(table=,class=zip_uncertain sex,outfile=ZU_S,Level=061); %createNumTableEntries(table=,class=zip_uncertain agegroup,outfile=ZU_AG,Level=062); %createNumTableEntries(table=,class=zip_uncertain year,outfile=ZU_Y,Level=063); %end; *The table to be generated in MSOC will be the result of a merge between numerators and donominators; data _NumCounts; set _NumCounts; length race hispanic zip_uncertain $1. zip3 state hhs_reg cb_reg $7.; %if "&race_out" = "N" %then %do; race = ''; %end; %if "&hispanic_out" = "N" %then %do; hispanic = ''; %end; %if %index(&geog, ZIP3) = 0 %then %do; zip3 = ''; %end; %if %index(&geog, STATE) = 0 %then %do; state = ''; %end; %if %index(&geog, HHS_REG) = 0 %then %do; hhs_reg = ''; %end; %if %index(&geog, CB_REG) = 0 %then %do; cb_reg = ''; %end; %if %str(&geog.) = %str() or %length(&geog.) = 0. %then %do; zip_uncertain = ''; %end; run; proc sort data=_NumCounts; by group Level sex agegroupsort year month zip3 state hhs_reg cb_reg zip_uncertain race hispanic ; run; proc sort data=_DenomCounts; by group Level sex agegroupsort year month zip3 state hhs_reg cb_reg zip_uncertain race hispanic ; run; data _t&type._CIDA; length race hispanic zip_uncertain $1. zip3 state hhs_reg cb_reg $7.; merge _NumCounts (in=a) _DenomCounts (in=b); by group Level sex agegroupsort year month zip3 state hhs_reg cb_reg zip_uncertain race hispanic ; if not b then do; DenNumPts=0; DenNumMemDays=0; end; if b and missing(DenNumPts) then do; DenNumPts=0; DenNumMemDays=0; 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; rename agegroupsort=agegroupnum; run; * Output to DPLOCAL and MSOC; %IF %EVAL(&group.=1) %THEN %DO; data DPLOCAL.&RUNID._numcounts; set _NumCounts; run; data MSOC.&RUNID._t&type._cida; retain group Level sex agegroup agegroupnum year month zip3 state hhs_reg cb_reg zip_uncertain race hispanic Npts Episodes Dispensings DaySupp AmtSupp Eps_wEvents All_Events tte DenNumPts DenNumMemDays; set _t&type._CIDA; keep group Level sex agegroup agegroupnum year month zip3 state hhs_reg cb_reg zip_uncertain race hispanic Npts Episodes Dispensings DaySupp AmtSupp Eps_wEvents All_Events tte DenNumPts DenNumMemDays; run; %END; %ELSE %DO; proc append base=DPLOCAL.&RUNID._numCounts data=_NumCounts force; run; proc append base=MSOC.&RUNID._t&type._cida data=_t&type._CIDA(keep=group Level sex agegroup agegroupnum year month zip3 state hhs_reg cb_reg zip_uncertain race hispanic Npts Episodes Dispensings DaySupp AmtSupp Eps_wEvents All_Events tte DenNumPts DenNumMemDays) force; run; %END; %put NOTE: ******** END OF MACRO: ms_cidatables v1.6 ********; %mend ms_cidatables;