**************************************************************************************************** * PROGRAM OVERVIEW **************************************************************************************************** * * PROGRAM: ms_its.sas * * Created (mm/dd/yyyy): 05/15/2018 * *-------------------------------------------------------------------------------------------------- * PURPOSE: * * Program inputs: * * Program outputs: * * PARAMETERS: * * Programming Notes: * * *-------------------------------------------------------------------------------------------------- * CONTACT INFO: * Sentinel Coordinating Center * info@sentinelsystem.org * ***************************************************************************************************; %macro ms_its(ds_suffix =, where_clause = ,data=);; %put =====> MACRO CALLED: ms_its; %ms_starttimer(timestart=itsruntime); /*Determine whether to produce AgeGroup, Sex, Race, and Hispanic stratifications*/ data _t2itsfile(rename=itstableID=tableID); set userstrata_its_lookup(drop=tableid); where includestratum="Y" and lowcase(itstableID) in ("t2its%sysfunc(compress(&ds_suffix.,_))"); run; %isdata(dataset=_t2itsfile); %if %eval(&nobs.>0) %then %do; %macro createitsnumcounts(class=,level=); proc means data=_mstr_its nway noprint missing; var numpts numEpisodes NumPtsTr NumEpisodesTr AdherenceNumPts AdherenceNumEpisodes AdherenceNumPtsTr AdherenceNumEpisodesTr %do num = 1 %to 2; %if &num. = 1 %then %let trunc = ; %else %let trunc = tr; Sex_M_NumEpisodes&trunc. Sex_F_NumEpisodes&trunc. Sex_O_NumEpisodes&trunc. Hispanic_Y_NumEpisodes&trunc. Hispanic_N_NumEpisodes&trunc. Hispanic_U_NumEpisodes&trunc. Sex_M_NumPts&trunc. Sex_F_NumPts&trunc. Sex_O_NumPts&trunc. Hispanic_Y_NumPts&trunc. Hispanic_N_NumPts&trunc. Hispanic_U_NumPts&trunc. %do race = 0 %to 5; Race_&race._NumEpisodes&trunc. Race_&race._NumPts&trunc. %end; %do age = 1 %to %sysfunc(countw(&AGESTRAT.," ")); AgeGroup_&age._NumEpisodes&trunc. AgeGroup_&age._NumPts&trunc. %end; %end; ; class analysisgrp interval_date &class. %if %index(&class., agegroupnum) %then %do; agegroup %end;; output out=_outits(drop=_:) sum=; run; proc sort data=_outits; by analysisgrp interval_date &class.; run; %if %eval(&s=1) %then %do; data _itscounts; set _outits; format level $3.; Level="&level."; run; %end; %else %do; data _itscounts; set _itscounts _outits(in=a); if a then do; level="&level."; end; run; %end; %mend; /* If Prevalent datasets are requested, reduce secondaryinputfile to only desired analysis groups */ proc sql noprint; create table _itsfile as select its.* ,sec.primary from infolder.&ITSFILE. its inner join secondaryinputfile %if %str("&ds_suffix.") ne %str("") %then %do; (where =(analysisgrp in ("&prevanalysisgrps."))) %end; sec on its.analysisgrp = sec.analysisgrp; quit; /*Loop through each ITS analysis*/ %isdata(dataset=_itsfile); %let itsloop = &nobs.; %do r = 1 %to %eval(&itsloop.); /*Assign macro variables*/ data _null_; set _itsfile (rename = (interval = interval_in)); length interval $10; if upcase(substr(interval_in,1,1)) = "Y" then interval = "year"; else if upcase(substr(interval_in,1,1)) = "Q" then interval = "quarter"; else if upcase(substr(interval_in,1,1)) = "M" then interval = "month"; if _n_ = &r. then do; call symputx("analysisgrp", analysisgrp); call symputx("interval", lowcase(interval)); call symputx("its_enrollment", lowcase(its_enrollment)); call symputx("interventiondate1", interventiondate1); call symputx("interventiondate2", interventiondate2); call symputx("adherenceID", adherenceID); call symputx("primary", primary); end; run; %put Analysis Group = &analysisgrp. interval = &interval.; %if "&interval" = "quarter" %then %let function = qtr; %else %let function = &interval; %if &adherenceID.>0 and %str("&adherenceid.") ne %str("A") %then %do; %let id = _&adherenceID; %end; %else %let id = ; *creating age group dataset; data _null_; set infolder.&cohortfile.; where cohortgrp = "&primary."; 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; run; /* Create warning if there are less than 6 data points between each segment Less than 6 INTERVALs between query start and INTERVENTIONDATE1 Less than 6 INTERVALs between INTERVENTIONDATE1 and INTERVENTIONDATE2 Less than 6 INTERVALS between INTERVENTIONDATE2 and query end date */ data _null_; if (intck("&function",&startdate.,&interventiondate1.) < 6 or (&interventiondate2. ne %str(.) and (intck("&function",&interventiondate1.,&interventiondate2.) < 6 or intck("&function",&interventiondate2.,&enddate.) < 6)) or intck("&function",&interventiondate1., &enddate.) < 6) then do; put "WARNING: (Sentinel) Less than 6 data points between each segment. Regression estimates may not be produced."; end; run; %PUT &StartYear. &EndYear. &&num&interval.s.; proc sql noprint; create table _mstr_its as select mstr.* , case when mstr.sex = 'M' then 1 else 0 end as Sex_M_NumEpisodes , case when mstr.sex = 'F' then 1 else 0 end as Sex_F_NumEpisodes , case when mstr.sex = 'O' then 1 else 0 end as Sex_O_NumEpisodes , case when mstr.hispanic = 'Y' then 1 else 0 end as Hispanic_Y_NumEpisodes , case when mstr.hispanic = 'N' then 1 else 0 end as Hispanic_N_NumEpisodes , case when mstr.hispanic = 'U' then 1 else 0 end as Hispanic_U_NumEpisodes %do race = 0 %to 5; , case when mstr.race = "&race." then 1 else 0 end as Race_&race._NumEpisodes %end; %do age = 1 %to %sysfunc(countw(&AGESTRAT.," ")); , case when mstr.AgeGroupNum= %eval(&age.) then 1 else 0 end as AgeGroup_&age._NumEpisodes %end; from DPLocal.&RUNID.&data. (where = (analysisgrp = "&analysisgrp" &where_clause.)) as mstr /*Determine if enrollment criteria met*/ %if "&its_enrollment" = "y" %then %do; and mstr.enr_start <= &startdate. and mstr.enr_end >= &enddate. %end; order by patid; quit; data _mstr_its_all; set _mstr_its; by patid; /*Assign interval date to the start of the interval for indexdt*/ interval_date = intnx("&function.",indexdt,0,'begin'); numEpisodes=1; if first.patid and not missing(patid) then do; numpts = 1; Sex_M_Numpts= Sex_M_NumEpisodes; Sex_f_Numpts = Sex_F_NumEpisodes; Sex_o_Numpts = Sex_O_NumEpisodes; Hispanic_Y_Numpts = Hispanic_Y_NumEpisodes; Hispanic_N_Numpts = Hispanic_N_NumEpisodes; Hispanic_U_Numpts = Hispanic_U_NumEpisodes; %do race = 0 %to 5; Race_&race._Numpts = Race_&race._NumEpisodes; %end; %do age = 1 %to %sysfunc(countw(&AGESTRAT.," ")); AgeGroup_&age._Numpts = AgeGroup_&age._NumEpisodes; %end; end; else do; numpts = 0; Sex_M_Numpts= 0; Sex_F_Numpts = 0; Sex_O_Numpts = 0; Hispanic_Y_Numpts = 0; Hispanic_N_Numpts = 0; Hispanic_U_Numpts = 0; %do race = 0 %to 5; Race_&race._Numpts = 0; %end; %do age = 1 %to %sysfunc(countw(&AGESTRAT.," ")); AgeGroup_&age._Numpts = 0; %end; end; %if &adherenceID. = . %then %do; AdherenceNumEpisodes = .; AdherenceNumEpisodesTr = .; AdherenceNumPts = .; AdherenceNumPtsTr =.; %end; %else %do; AdherenceNumPts = adherence_pat&id.; AdherenceNumPtsTr = adherence_pat&id._truncated; if adherence&id. = . then AdherenceNumEpisodes = 0; else AdherenceNumEpisodes = adherence&id.; if adherence&id._truncated = . then AdherenceNumEpisodesTr = 0; else AdherenceNumEpisodesTr = adherence&id._truncated; %end; if indenominator = 1 then do; Sex_M_NumEpisodesTr = Sex_M_NumEpisodes; Sex_F_NumEpisodesTr = Sex_F_NumEpisodes; Sex_O_NumEpisodesTr = Sex_O_NumEpisodes; Hispanic_Y_NumEpisodesTr = Hispanic_Y_NumEpisodes; Hispanic_N_NumEpisodesTr = Hispanic_N_NumEpisodes; Hispanic_U_NumEpisodesTr = Hispanic_U_NumEpisodes; NumPtsTr = NumPts; %do race = 0 %to 5; Race_&race._NumEpisodesTr = Race_&race._NumEpisodes; Race_&race._NumptsTr = Race_&race._Numpts; %end; %do age = 1 %to %sysfunc(countw(&AGESTRAT.," ")); AgeGroup_&age._NumEpisodesTr = AgeGroup_&age._NumEpisodes; AgeGroup_&age._NumptsTr = AgeGroup_&age._Numpts; %end; Sex_M_NumptsTr = Sex_M_Numpts; Sex_F_NumptsTr = Sex_F_Numpts; Sex_O_NumptsTr = Sex_O_Numpts; Hispanic_Y_NumptsTr = Hispanic_Y_Numpts; Hispanic_N_NumptsTr = Hispanic_N_Numpts; Hispanic_U_NumptsTr = Hispanic_U_Numpts; end; else do; Sex_M_NumEpisodesTr = 0; Sex_F_NumEpisodesTr = 0; Sex_O_NumEpisodesTr = 0; Hispanic_Y_NumEpisodesTr = 0; Hispanic_N_NumEpisodesTr = 0; Hispanic_U_NumEpisodesTr = 0; NumPtsTr = 0; %do race = 0 %to 5; Race_&race._NumEpisodesTr = 0; Race_&race._NumptsTr = 0; %end; %do age = 1 %to %sysfunc(countw(&AGESTRAT.," ")); AgeGroup_&age._NumEpisodesTr = 0; AgeGroup_&age._NumptsTr = 0; %end; Sex_M_NumptsTr = 0; Sex_F_NumptsTr = 0; Sex_O_NumptsTr = 0; Hispanic_Y_NumptsTr = 0; Hispanic_N_NumptsTr = 0; Hispanic_U_NumptsTr = 0; end; format interval_date date9.; run; /* Only use rows where the desired interval is specified */ data _t2its_levels; set _t2itsfile (where = (index(levelvars,"&interval.") > 0 )); %if &interval. eq year %then %do; if index(levelvars,"quarter") > 0 then delete; else if index(levelvars,"month") > 0 then delete; %end; run; %isdata(dataset=_t2its_levels); %if %eval(&nobs.>0) %then %do; %do s = 1 %to %eval(&nobs.); data _null_; set _t2its_levels; if _n_ = &s. then do; call symputx('levelid', levelid); call symputx('levelvars', levelvars); end; run; %put &=levelid. and &=levelvars.; /*square table based on interval*/ %ms_squaredtableshell(squarevars =&levelvars., analysisvars=, groups="&analysisgrp.", analysis=Y); data _mstr_its (rename=(indenominator=NumEpisodesTr)); set _mstr_its_all (in = mstr) _mastersquare (in = sqr rename = (date = interval_date)); if sqr and not mstr then do; dennumpts = 0; Numepisodes = 0; indenominator = 0; Numpts = 0; NumPtsTr = 0; %if &adherenceID. = . %then %do; AdherenceNumEpisodes = .; AdherenceNumEpisodesTr = .; AdherenceNumPts = .; AdherenceNumPtsTr = .; %end; %else %do; AdherenceNumEpisodes = 0; AdherenceNumEpisodesTr = 0; AdherenceNumPts = 0; AdherenceNumPtsTr = 0; %end; %do num = 1 %to 2; /*Need numerators 1 and 2 (non-truncated and truncated)*/ %if &num. = 1 %then %let trunc = ; %else %let trunc = tr; Sex_M_NumEpisodes&trunc. = 0; Sex_F_NumEpisodes&trunc. = 0; Sex_O_NumEpisodes&trunc. = 0; Hispanic_Y_NumEpisodes&trunc. = 0; Hispanic_N_NumEpisodes&trunc. = 0; Hispanic_U_NumEpisodes&trunc. = 0; %do race = 0 %to 5; Race_&race._NumEpisodes&trunc. = 0; Race_&race._NumPts&trunc. = 0; %end; %do age = 1 %to %sysfunc(countw(&AGESTRAT.," ")); AgeGroup_&age._NumEpisodes&trunc. = 0; AgeGroup_&age._NumPts&trunc. = 0; %end; Sex_M_Numpts&trunc. = 0; Sex_F_Numpts&trunc. = 0; Sex_O_Numpts&trunc. = 0; Hispanic_Y_Numpts&trunc. = 0; Hispanic_N_Numpts&trunc. = 0; Hispanic_U_Numpts&trunc. = 0; %end; end; run; %createitsnumcounts(class=&levelvars.,level=&levelid.); %end; data t2_its; length sex hispanic race $1 year month quarter 8. agegroup $9 agegroupnum 3; call missing(year, month, quarter, agegroupnum, sex, race, hispanic, agegroup); /* Initialize values */ set _itscounts; run; proc sort data = t2_its; by level year month quarter sex race hispanic agegroupnum; run; /*-------------------------------------------------------- Summarize denominator values by stratifiers (sex, agegroupnum, race, hispanic) --------------------------------------------------------*/ proc sql noprint; select distinct(strip(levelid)) into: all_levelvars separated by ' ' from _t2its_levels; select distinct(strip(quote(levelid))) into: all_levelvars_quoted separated by ',' from _t2its_levels; quit; /* Need all stratifications for denominators */ proc sql; create table _denomcounts_sum as select level ,year ,month ,quarter ,sex ,race ,hispanic ,agegroupnum ,case when missing(denNumpts) then 0 else dennumpts end as dennumpts from dplocal.&runid._denomcounts&ds_suffix. where group= "&primary" order by level ,year ,month ,quarter ,sex ,race ,hispanic ,agegroupnum; quit; /* Start looping through the stratas user specified */ %do user_count = 1 %to %sysfunc(countw(&all_levelvars)); %let user_level = %scan(&all_levelvars,&USER_COUNT); /* Store ordered level var */ data _null_; set _t2its_levels; if levelid = "&USER_LEVEL" then call symputx("levelvarlist",levelvars); run; %let sex_level=; %let agegroupnum_level=; %let race_level=; %let hispanic_level=; %macro getitslevel(levelvars=, levelname=); data _denomcurlevelvars(rename=curlevelvars_out=curlevelvars); %if %index(&levelvars., &levelname.) >0 %then %do; curlevelvars="&levelvars."; %end; %else %do; curlevelvars="&levelvars. &levelname."; %end; %alphabetizevarutil(array=a, in=curlevelvars, out=curlevelvars_out); run; proc sql noprint; select curlevelvars into :curlevelvars trimmed from _denomcurlevelvars; quit; proc sql noprint; select levelid into :&&levelname._level from userstrata_its_lookup where itstableid in ("t2its%sysfunc(compress(&ds_suffix.,_))") and levelvars="&curlevelvars."; quit; %mend getitslevel; /* Create dummy stratas, strata names and by group variables for each combination of user defined stratas to merge later */ %getitslevel(levelvars=%str(&levelvarlist.), levelname=sex); %getitslevel(levelvars=%str(&levelvarlist.), levelname=agegroupnum); %getitslevel(levelvars=%str(&levelvarlist.), levelname=race); %getitslevel(levelvars=%str(&levelvarlist.), levelname=hispanic); data its_&USER_LEVEL.; set t2_its(where=(level="&USER_LEVEL.")); sex_level = "&sex_level."; agegroupnum_level = "&agegroupnum_level."; race_level = "&race_level."; hispanic_level = "&hispanic_level."; call symputx("BYGROUP", "level &levelvarlist."); call symputx('STRATA_NAMES', 'sex agegroupnum race hispanic'); call symputx("INNER_STRATA", catx(' ', sex_level,agegroupnum_level,race_level,hispanic_level)); run; /* loop stratas within denominator data set based on dummy stratas and subset the data */ %do INNER_COUNT = 1 %to %sysfunc(countw(&INNER_STRATA)); %let SINGLE_STRATA = %scan(&INNER_STRATA,&INNER_COUNT); %let STRATA_NAME = %scan(&STRATA_NAMES,&INNER_COUNT); proc sort data = _denomcounts_sum(keep=&BYGROUP. &STRATA_NAME. denNumpts) out=_denoms(where=(level = "&SINGLE_STRATA")); by &BYGROUP; run; /* Transpose data so it is in proper structure for merge */ proc transpose data = _denoms out =denoms_trans_&USER_LEVEL._&STRATA_NAME.(drop=_name_) prefix=&STRATA_NAME._ suffix=_dennumpts; var dennumpts; by &BYGROUP; id &STRATA_NAME; run; proc contents data = DENOMS_TRANS_&USER_LEVEL._&STRATA_NAME. out = DENOMS_TRANS_META(keep=name) noprint; run; proc sql noprint; /* Dynamically create stratas and join condition */ select distinct 'b.'||name into :GETSTRATAS separated by ',' from DENOMS_TRANS_META where find(name,"dennumpts",'i'); select distinct 'a.'||name||' = b.'||name into :JOINCOND separated by ' and ' from DENOMS_TRANS_META where ^find(name,"dennumpts",'i') and ^find(name,"level",'i') ; /* Join all stratified columns back to its dataset */ create table set_denoms_&USER_LEVEL._&STRATA_NAME. as select a.level, a.year, a.month, a.quarter, a.sex, a.agegroupnum, a.race, a.hispanic, &GETSTRATAS from its_&USER_LEVEL a left join denoms_trans_&USER_LEVEL._&STRATA_NAME b on a.&STRATA_NAME._level = b.level and &JOINCOND order by level ,year ,month ,quarter ,sex ,race ,hispanic ,agegroupnum; quit; %end; %end; /* Merge denomcounts for original dennumpts variable, and all denominator strata datasets back to original ITS dataset */ data _t2_its (drop = month quarter); merge t2_its (in = its) _denomcounts_sum(where=(level in (&all_levelvars_quoted)) keep=level year month quarter sex race hispanic agegroupnum denNumpts) set_denoms:; by level year month quarter sex race hispanic agegroupnum; if missing(denNumpts) then denNumpts = 0; if missing(Sex_M_DenNumpts) then Sex_M_DenNumpts = 0; if missing(Sex_F_DenNumpts) then Sex_F_DenNumpts = 0; if missing(Sex_O_DenNumpts) then Sex_O_DenNumpts = 0; if missing (Hispanic_Y_DenNumpts) then Hispanic_Y_DenNumpts = 0; if missing(Hispanic_N_DenNumpts) then Hispanic_N_DenNumpts = 0; if missing(Hispanic_U_DenNumpts) then Hispanic_U_DenNumpts = 0; %do age = 1 %to %sysfunc(countw(&AGESTRAT.," ")); if missing(AgeGroupNum_&age._DenNumPts) then AgeGroupNum_&age._DenNumPts = 0; %end; %do race = 0 %to 5; if missing(race_&race._DenNumPts) then race_&race._DenNumPts = 0; %end; rename %do age = 1 %to %sysfunc(countw(&AGESTRAT.," ")); AgeGroupNum_&age._Dennumpts=AgeGroup_&age._Dennumpts %end; ; if its then output; run; /* Make sure level are the same as those provided in USERSTRATA */ proc sql noprint undo_policy=none; create table _t2_its(drop=level rename=itslevelid=level) as select a.*, b.itslevelid from _t2_its as a join userstrata_its_lookup as b on a.level=b.levelid where lowcase(b.itstableID) in ("t2its%sysfunc(compress(&ds_suffix.,_))"); quit; %end; %else %do; data _null_; Put "WARNING: (Sentinel) &interval. not listed as a stratifying variable in USERSTRATA. No output will be produced for analysisgrp=&analysisgrp. on t2_its&ds_suffix."; run; %end; /* If _t2_its was created then append to msoc data */ %isdata(dataset=_t2_its); %if %eval(&nobs.) > 0 %then %do; %if &r. = 1 %then %do; data msoc.&runid._t2_its&ds_suffix. (rename=(interval_date=interval)); retain analysisgrp level interval_date year sex agegroupnum race hispanic agegroup; set _t2_its; run; %end; %else %do; proc append base=msoc.&runid._t2_its&ds_suffix. data=_t2_its (rename=(interval_date=interval)) force; %end; proc datasets nowarn noprint lib=work; delete _mstr_its _mstr_its_all _t2its_level t2_its _denomcounts_sum: _itscounts _mastersquare _t2_its: _outits setdenoms set_denoms_: DENOMS_TRANS_: its_: _denom: _numcounts _t2its_levels _t2_levels _t2_cida:; quit; %end; %end; /*loop through Analyses*/ proc sort data = msoc.&runid._t2_its&ds_suffix.; by analysisgrp level interval year sex agegroup race hispanic; run; %end; /*ITS output exists in USERSTRATA*/ proc datasets nowarn noprint lib=work; delete _itsfile _t2itsfile ; quit; /*Output ITS runtime*/ %ms_stoptimer(timestart=itsruntime); %ms_outputruntimes(timevar=itsruntime, step=%str(ITS Module), group=t2its%sysfunc(compress(&ds_suffix.,_)), monitoringperiod=); %put NOTE: ********END OF MACRO: ms_its ********; %mend ms_its;