**************************************************************************************************** * PROGRAM OVERVIEW **************************************************************************************************** * * PROGRAM: ms_cidatablest4.sas * * Created (mm/dd/yyyy): 08/24/2015 * Last modified: 03/14/2018 * Version: 3.2 * *-------------------------------------------------------------------------------------------------- * PURPOSE: * This macro will create characteristics and medication of interest tables for pregnancy analyses. * * Program inputs: * - a dataset containing MOI drug codes * - a dataset containing pregnancy codes * - a dataset containing defined group pregnancy and control episodes * - a dataset containing MOI claims * * Program outputs: * - Characteristics and Medication of Interest tables * * 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.1 08/10/2016 AP (SOC) Added underscore prefix to proc transpose (QCI-179) * * 2.0 02/16/2017 DM New design for QRP 4.0 * * 2.1 05/05/2017 AP 1. Added race and hispanic stratification for pregnancy cohort * 2. Moved macro call into group loop * * 2.2 06/30/2017 RR Only count MOI episodes that occur during trimester (QRP-395) * * 3.0 10/03/2017 AP 1. Recoded squared tables for scalability and to mirror other Types * 2. Add geographic stratifications * 3. Change PreTerm/PostTerm algorithm (QRP-388) * * 3.1 12/20/2017 AP 1. Add overall npts/episodes to _elig table (QRP-458) * 2. Relabel race/hispanic stratifications (QRP-474) * * 3.2 03/14/2018 RR Changed Group length to $40 (QRP-495) ***************************************************************************************************; %macro ms_cidatablest4(); %put =====> MACRO CALLED: ms_cidatablest4 v3.2; /*************************/ /* Squaring matrix */ /*************************/ data _null_; call symputx('NUMAGECAT',countw("&AGESTRAT."," ")); call symputx('YEARFROM',year(&startfollowup.)); call symputx('YEARTO',year(&enddate.)); run; %put &NUMAGECAT. &YEARFROM. &YEARTO.; data _square1; format AgeGroup $9. PrePostInd $10.; Use90=0; Any1=0; Any2=0; Any3=0; Any=0; Only1=0; Only2=0; Only3=0; All3=0; DelEpisodeInd=0; Has3Trim=0; TrimOnlyWashMet = -1; do AgeGroupNum=1 to &NUMAGECAT.; do Year=&YEARFROM. to &YEARTO.; AgeGroup = scan("&AGESTRAT.",AgeGroupNum,' '); PrePostInd="TERM"; output; PrePostInd="PRETERM"; output; PrePostInd="POSTTERM"; output; PrePostInd="NONE"; output; end; end; run; *Race and Hispanic; %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 _square1 as select base.*, com.race from _square1 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 _square1 as select base.*, com.hispanic from _square1 as base, _hispanic as com; quit; %end; *PregDur codes; proc sort nodupkey data=infolder.&PREGDUR.(where=(PriorityGroup1=1 or PriorityGroup2=1)) out= _prePostTerm(keep=CodeCat CodeType Code duration); by Code; run; data _prePostTerm; set _prePostTerm; Code=compress(Code,'. '); format PrePostInd $10.; if Duration <=258 then do; PrePostInd="PRETERM"; output; end; if 259 <= Duration <=280 then do; PrePostInd="TERM"; output; end; if 281 <= Duration then do; PrePostInd="POSTTERM"; output; end; run; %ISDATA(dataset=_prePostTerm); %IF %EVAL(&NOBS.>=1) %THEN %DO; %MS_ProcessWildcards(InFile=_prePostTerm, CodeVar=code, OutFile=_prePostTerm); %END; proc sql noprint undo_policy=none; create table _square1 as select sq1.*, sq2.Code as PregDurCode from _square1 as sq1 left join _prePostTerm as sq2 on sq1.prepostind = sq2.prepostind; quit; /*Square MP#s*/ proc sort nodupkey data=infolder.&COHORTCODES. out=_square2(keep=T4_INDEX group rename=T4_INDEX=MOIName); by group T4_INDEX; where T4_INDEX =:"MP" and strip(group) = "&itgroup"; run; data _square2; set _square2; by group MoiName; output; if last.group then do; MoiName=""; output; end; run; proc sql noprint undo_policy=none; create table _square1 as select base.*, com.MOIName from _square1 as base, _square2 as com; quit; proc sort nodupkey data=_square1; by _ALL_; run; *Square Geographic stratifications; %IF %STR(&GEOG.) ne %STR() & %LENGTH(&GEOG.) > 0. %then %do; %macro squaregeog(); %let num_geog = %sysfunc(countw(&geog)); %put &num_geog; data _uncertain; zip_uncertain='N'; output; zip_uncertain='Y'; output; run; proc sql noprint undo_policy=none; create table _square1 as select base.*, com.zip_uncertain from _square1 as base, _uncertain as com; quit; %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 prepostind and pregdurcode to prevent unnecessary calculations*/ data _square_&geog_stratification.; set _square1; drop prepostind pregdurcode; run; proc sort data=_square_&geog_stratification. nodup; by _all_; run; proc sql noprint undo_policy=none; create table _square_&geog_stratification. as select base.*, com.&geog_stratification. from _square_&geog_stratification. as base, _geogcodes as com; quit; proc datasets nowarn noprint lib=work; delete _uncertain _geogcodes; quit; %end; %mend; %squaregeog(); %end; %macro CreateCohortTable(cohortfile=, outfile=); * Create dataset with medical exposure during pregnancy; proc sql noprint; create table _ConcEpisodes as select Prim.*, Sec.IndexDt as IndexDt2, Sec.EpisodeEndDt as EpisodeEndDt2, Sec.MOIName from &cohortfile. as Prim left join _PtsMasterList2 as Sec on Prim.PatId=Sec.PatId order by PatId; quit; * Do not count secondary cohort episodes (medical exposure) beginning after the end of primary cohort (pregnancy) episodes; proc sql noprint; create table _PtsWithMOI as select distinct PatId from _ConcEpisodes where MOIName ne "" and IndexDt2 <= EpisodeEndDt order by PatId; quit; data _ConcEpisodes; merge _ConcEpisodes _PtsWithMOI(in=b); by PatId; if IndexDt2 > EpisodeEndDt then do; IndexDt2=.; EpisodeEndDt2=.; MOIName=""; end; run; proc sort nodupkey data=_ConcEpisodes; by _ALL_; run; * Compute trimester use; data _ConcEpisodes; set _ConcEpisodes; Use90=0;Any1=0;Any2=0;Any3=0;Any=0;Only1=0;Only2=0;Only3=0;All3=0; TrimOnlyWashMet=0; DelEpisodeInd=1; if strip(MOIName) ne "" then do; if 0IndexDt) then Use90=1; if IndexDt<=IndexDt2<=IndexDt+90 or IndexDt<=EpisodeEndDt2<=IndexDt+90 or (IndexDt2IndexDt+90) then Any1=1; if IndexDt+91<=IndexDt2<=IndexDt+180 or IndexDt+91<=EpisodeEndDt2<=IndexDt+180 or (IndexDt2IndexDt+180) then Any2=1; if IndexDt+181<=IndexDt2<=EpisodeEndDt or IndexDt+181<=EpisodeEndDt2<=EpisodeEndDt or (IndexDt2EpisodeEndDt) then Any3=1; if Any1 or Any2 or Any3 then Any=1; if IndexDt<=EpisodeEndDt2<=IndexDt+90 then Only1=1; if IndexDt+91<=IndexDt2 and EpisodeEndDt2<=IndexDt+180 then Only2=1; if IndexDt+181<=IndexDt2<=EpisodeEndDt then Only3=1; if Any1 and Any2 and Any3 then All3=1; * Apply the washout criterion for the Trimesters Only statistics; * If ConcWashPer is missing, the exposure to the secondary cohort prior to the beginning of the pregnancy episode will be ignored when assessing the trimesters only statistics; TrimOnlyWashMet=1; if not missing(ConcWashPer) then do; if IndexDt > EpisodeEndDt2 and IndexDt - EpisodeEndDt2 -1 < ConcWashPer then TrimOnlyWashMet=0; end; end; run; /*Merge _ConcEpisodes with _Square to obtain squared table*/ data _ConcEpisodes; set _ConcEpisodes _square1; run; %IF %STR(&GEOG.) ne %STR() & %LENGTH(&GEOG.) > 0. %then %do; %macro wrapper(); %let num_geog = %sysfunc(countw(&geog)); %put &num_geog; %do g = 1 %to %eval(&num_geog); %let geog_stratification = %scan(&geog., &g.); data _ConcEpisodes&geog_stratification.; set _ConcEpisodes(drop=prepostind pregdurcode where=(patid is not missing)) _square_&geog_stratification.; run; %end; %mend; %wrapper(); %end; *This macro will aggregate counts for output variables by specific stratum; %macro MedicationUse(table=, grouping=, out=, level=); proc means data=_ConcEpisodes&table. nway noprint missing; var Use90 Any1 Any2 Any3 Any Only1 Only2 Only3 All3; class PatId DelNum &grouping.; output out=_MOI_by_&out. (drop=_:) max(DelEpisodeInd)=Episodes max(Has3Trim)=Episodes_3Trim min(TrimOnlyWashMet)= max(Use90 Any1 Any2 Any3 Any All3 Only1 Only2 Only3)= sum(Use90)=sumUse90 sum(Any1)=sumAny1 sum(Any2)=sumAny2 sum(Any3)=sumAny3 sum(Any)=sumAny sum(All3)=sumAll3 sum(Only1)=sumOnly1 sum(Only2)=sumOnly2 sum(Only3)=sumOnly3; run; data _MOI_by_&out.; set _MOI_by_&out.; /* Because of this new criterion, the Trimeters Only stats cannot be matched with the current pregnancy program unless ConcWashPer is very high*/ * Trimesters Only criterion; if TrimOnlyWashMet=0 or Any2 or Any3 then Only1=0; if TrimOnlyWashMet=0 or Any1 or Any3 then Only2=0; if TrimOnlyWashMet=0 or Any1 or Any2 then Only3=0; if Any1 and Any2 and Any3 then do; All3=1; sumAll3=sumAny; end; if Only1=0 then sumOnly1=0; if Only2=0 then sumOnly2=0; if Only3=0 then sumOnly3=0; %if %index(&grouping., MOIName) > 0 %then %do; if Any = 0 then do; Episodes = 0; Episodes_3Trim = 0; end; %end; run; proc means data=_MOI_by_&out. nway noprint missing; var Episodes Episodes_3Trim Use90 Any1 Any2 Any3 Any Only1 Only2 Only3 All3 sumUse90 sumAny1 sumAny2 sumAny3 sumAny sumAll3 sumOnly1 sumOnly2 sumOnly3; class PatId &grouping.; output out=_MOI_by_&out. (drop=_:) sum=; run; data _MOI_by_&out.; set _MOI_by_&out.; %if %index(&grouping., MOIName) > 0 %then %do; Npts = 0; if Episodes gt 0 then Npts = 1; %end; %else %do; Npts = 1; %end; if missing(patid) then npts = 0; run; proc means data=_MOI_by_&out. nway noprint missing; var Npts Episodes Episodes_3Trim Use90 Any1 Any2 Any3 Any Only1 Only2 Only3 All3 sumUse90 sumAny1 sumAny2 sumAny3 sumAny sumAll3 sumOnly1 sumOnly2 sumOnly3 ; class &grouping.; output out=_MOI_by_&out. (drop=_:) sum=; run; data _MOI_by_&out.; set _MOI_by_&out.; level = "&level."; %if %index(%upcase(&grouping.), MOINAME)>0 %then %do; where MOINAME ne ""; %end; run; %mend MedicationUse; %MedicationUse(table=, grouping=, out=ALL_ByGroup, level=000); %MedicationUse(table=, grouping= Year, out=Y_ByGroup, level=001); %MedicationUse(table=, grouping= AgeGroup, out=Ag_ByGroup, level=003); %MedicationUse(table=, grouping= AgeGroup Year, out=AgY_ByGroup, level=007); %MedicationUse(table=, grouping= PrePostInd, out=PPI_ByGroup, level=602); %MedicationUse(table=, grouping= AgeGroup PrePostInd, out=AgPPI_ByGroup, level=603); %MedicationUse(table=, grouping= Year PrePostInd, out=YPPI_ByGroup, level=604); %MedicationUse(table=, grouping= AgeGroup Year PrePostInd, out=AgYPPI_ByGroup, level=605); %MedicationUse(table=, grouping= PrePostInd PregDurCode, out=PPIPPC_ByGroup, level=606); %if "&race_out" = "Y" %then %do; %MedicationUse(table=, grouping= race, out=R_ByGroup, level=110); %MedicationUse(table=, grouping= race agegroup, out=RAG_ByGroup, level=112); %MedicationUse(table=, grouping= race year, out=RY_ByGroup, level=113); %MedicationUse(table=, grouping= race PrePostInd, out=RPPI_ByGroup, level=607); %end; %if "&hispanic_out" = "Y" %then %do; %MedicationUse(table=, grouping= hispanic, out=H_ByGroup, level=115); %MedicationUse(table=, grouping= hispanic agegroup, out=HAG_ByGroup, level=117); %MedicationUse(table=, grouping= hispanic year, out=HY_ByGroup, level=118); %MedicationUse(table=, grouping= hispanic PrePostInd, out=HPPI_ByGroup, level=608); %end; **stratify by MOINAME**; %MedicationUse(table=, grouping= MOIName, out=ALL, level=000); %MedicationUse(table=, grouping= MOIName Year, out=Y, level=001); %MedicationUse(table=, grouping= MOIName AgeGroup, out=Ag, level=003); %MedicationUse(table=, grouping= MOIName AgeGroup Year, out=AgY, level=007); %MedicationUse(table=, grouping= MOIName PrePostInd, out=PPI, level=602); %MedicationUse(table=, grouping= MOIName AgeGroup PrePostInd, out=AgPPI, level=603); %MedicationUse(table=, grouping= MOIName Year PrePostInd, out=YPPI, level=604); %MedicationUse(table=, grouping= MOIName AgeGroup Year PrePostInd, out=AgYPPI, level=605); %MedicationUse(table=, grouping= MOIName PrePostInd PregDurCode, out=PPIPPC, level=606); %if "&race_out" = "Y" %then %do; %MedicationUse(table=, grouping= MOIName race, out=R, level=110); %MedicationUse(table=, grouping= MOIName race agegroup, out=RAG, level=112); %MedicationUse(table=, grouping= MOIName race year, out=RY, level=113); %MedicationUse(table=, grouping= MOIName race PrePostInd, out=RPPI, level=607); %end; %if "&hispanic_out" = "Y" %then %do; %MedicationUse(table=, grouping= MOIName hispanic, out=H, level=115); %MedicationUse(table=, grouping= MOIName hispanic agegroup, out=HAG, level=117); %MedicationUse(table=, grouping= MOIName hispanic year, out=HY, level=118); %MedicationUse(table=, grouping= MOIName hispanic PrePostInd, out=HPPI, level=608); %end; *Geographic tables; %IF %STR(&GEOG.) ne %STR() & %LENGTH(&GEOG.) > 0. %then %do; %MedicationUse(table=, grouping= zip_uncertain, out=UN_ByGroup, level=060); %MedicationUse(table=, grouping= zip_uncertain agegroup, out=UN_AG_ByGroup, level=062); %MedicationUse(table=, grouping= zip_uncertain year, out=UN_Y_ByGroup, level=063); %MedicationUse(table=, grouping= MOIName zip_uncertain, out=UN, level=060); %MedicationUse(table=, grouping= MOIName zip_uncertain agegroup, out=UN_AG, level=062); %MedicationUse(table=, grouping= MOIName zip_uncertain year, out=UN_Y, level=063); %macro geowrapper(); %do geocount = 1 %to %sysfunc(countw(&GEOG.)); %let geog_stratification = %scan(&GEOG., &geocount.); %if %index(&geog_stratification, ZIP3) > 0 %then %do; %let num1 = 02; %let num2 = 03; %end; %if %index(&geog_stratification, STATE) > 0 %then %do; %let num1 = 04; %let num2 = 05; %end; %if %index(&geog_stratification, HHS_REG) > 0 %then %do; %let num1 = 07; %let num2 = 08; %end; %if %index(&geog_stratification, CB_REG) > 0 %then %do; %let num1 = 09; %let num2 = 10; %end; %MedicationUse(table=&geog_stratification., grouping= &geog_stratification., out=&geog_stratification._ByGroup, level=&num1.0); %MedicationUse(table=&geog_stratification., grouping= &geog_stratification. zip_uncertain, out=&geog_stratification._UN_ByGroup, level=&num1.1); %MedicationUse(table=&geog_stratification., grouping= &geog_stratification. agegroup, out=&geog_stratification._AG_ByGroup, level=&num1.4); %MedicationUse(table=&geog_stratification., grouping= &geog_stratification. agegroup zip_uncertain, out=&geog_stratification._AG_UN_ByGroup, level=&num1.5); %MedicationUse(table=&geog_stratification., grouping= &geog_stratification. Year, out=&geog_stratification._Y_ByGroup, level=&num1.6); %MedicationUse(table=&geog_stratification., grouping= &geog_stratification. Year zip_uncertain, out=&geog_stratification._Y_UN_ByGroup, level=&num1.7); %MedicationUse(table=&geog_stratification., grouping= MOIName &geog_stratification., out=&geog_stratification., level=&num1.0); %MedicationUse(table=&geog_stratification., grouping= MOIName &geog_stratification. zip_uncertain, out=&geog_stratification._UN, level=&num1.1); %MedicationUse(table=&geog_stratification., grouping= MOIName &geog_stratification. agegroup, out=&geog_stratification._AG, level=&num1.4); %MedicationUse(table=&geog_stratification., grouping= MOIName &geog_stratification. agegroup zip_uncertain, out=&geog_stratification._AG_UN, level=&num1.5); %MedicationUse(table=&geog_stratification., grouping= MOIName &geog_stratification. Year, out=&geog_stratification._Y, level=&num1.6); %MedicationUse(table=&geog_stratification., grouping= MOIName &geog_stratification. Year zip_uncertain, out=&geog_stratification._Y_UN, level=&num1.7); %if "&race_out" = "Y" %then %do; %MedicationUse(table=&geog_stratification., grouping= &geog_stratification. race, out=&geog_stratification._R_ByGroup, level=&num1.8); %MedicationUse(table=&geog_stratification., grouping= &geog_stratification. race zip_uncertain, out=&geog_stratification._R_UN_ByGroup, level=&num1.9); %MedicationUse(table=&geog_stratification., grouping= MOIName &geog_stratification. race, out=&geog_stratification._R, level=&num1.8); %MedicationUse(table=&geog_stratification., grouping= MOIName &geog_stratification. race zip_uncertain, out=&geog_stratification._R_UN, level=&num1.9); %end; %if "&hispanic_out" = "Y" %then %do; %MedicationUse(table=&geog_stratification., grouping= &geog_stratification. hispanic, out=&geog_stratification._H_ByGroup, level=&num2.0); %MedicationUse(table=&geog_stratification., grouping= &geog_stratification. hispanic zip_uncertain, out=&geog_stratification._H_UN_ByGroup, level=&num2.1); %MedicationUse(table=&geog_stratification., grouping= MOIName &geog_stratification. hispanic, out=&geog_stratification._H, level=&num2.0); %MedicationUse(table=&geog_stratification., grouping= MOIName &geog_stratification. hispanic zip_uncertain, out=&geog_stratification._H_UN, level=&num2.1); %end; %end; %mend; %geowrapper(); %end; data _&outfile.; retain Group MOIName Level AgeGroup Year zip3 state hhs_reg cb_reg zip_uncertain race hispanic PrePostInd PregDurCode; length race hispanic zip_uncertain $1. zip3 state hhs_reg cb_reg $7.; set _MOI_by_ALL_ByGroup _MOI_by_ALL _MOI_by_Y_ByGroup _MOI_by_Y _MOI_by_Ag _MOI_by_Ag_ByGroup _MOI_by_AgY _MOI_by_AgY_ByGroup _MOI_by_PPI _MOI_by_PPI_ByGroup _MOI_by_AgPPI _MOI_by_AgPPI_ByGroup _MOI_by_YPPI _MOI_by_YPPI_ByGroup _MOI_by_AgYPPI _MOI_by_AgYPPI_ByGroup _MOI_by_PPIPPC _MOI_by_PPIPPC_ByGroup %if "&race_out" = "Y" %then %do; _MOI_by_R _MOI_by_R_ByGroup _MOI_by_RAG _MOI_by_RAG_ByGroup _MOI_by_RY _MOI_by_RY_ByGroup _MOI_by_RPPI _MOI_by_RPPI_ByGroup %end; %if "&hispanic_out" = "Y" %then %do; _MOI_by_H _MOI_by_H_ByGroup _MOI_by_HAG _MOI_by_HAG_ByGroup _MOI_by_HY _MOI_by_HY_ByGroup _MOI_by_HPPI _MOI_by_HPPI_ByGroup %end; %if %str(&GEOG.) ne %str() & %length(&GEOG.) > 0. %then %do; _MOI_by_UN _MOI_by_UN_ByGroup _MOI_by_UN_AG _MOI_by_UN_AG_ByGroup _MOI_by_UN_Y _MOI_by_UN_Y_ByGroup %do geocount = 1 %to %sysfunc(countw(&GEOG.)); %let geog_stratification = %scan(&GEOG., &geocount.); _MOI_by_&geog_stratification. _MOI_by_&geog_stratification._ByGroup _MOI_by_&geog_stratification._UN _MOI_by_&geog_stratification._UN_ByGroup _MOI_by_&geog_stratification._AG _MOI_by_&geog_stratification._AG_ByGroup _MOI_by_&geog_stratification._AG_UN _MOI_by_&geog_stratification._AG_UN_ByGroup _MOI_by_&geog_stratification._Y _MOI_by_&geog_stratification._Y_ByGroup _MOI_by_&geog_stratification._Y_UN _MOI_by_&geog_stratification._Y_UN_ByGroup %if "&race_out" = "Y" %then %do; _MOI_by_&geog_stratification._R _MOI_by_&geog_stratification._R_ByGroup _MOI_by_&geog_stratification._R_UN _MOI_by_&geog_stratification._R_UN_ByGroup %end; %if "&hispanic_out" = "Y" %then %do; _MOI_by_&geog_stratification._H _MOI_by_&geog_stratification._H_ByGroup _MOI_by_&geog_stratification._H_UN _MOI_by_&geog_stratification._H_UN_ByGroup %end; %end; %end; ; format group $40.; group = "&itgroup."; %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; * Assign AgeGroup number; data _age; numgrp = countw("&AGESTRAT."," ") ; do i=1 to numgrp; AgeGroup=scan("&AGESTRAT",i," "); AgeGroupNum=i; output; end; keep AgeGroup AgeGroupNum; run; proc sql noprint undo_policy=none; create table _&outfile. as select outfile.*, age.AgeGroupNum from _&outfile. as outfile left join _age as age on age.AgeGroup=outfile.AgeGroup; quit; * Create final output; proc sort data=_&outfile.; by group MOIName Level AgeGroupNum Year zip3 state hhs_reg cb_reg zip_uncertain race hispanic PrePostInd PregDurCode; run; data _&outfile.&group.; retain group MOIName Level AgeGroupNum Year zip3 state hhs_reg cb_reg zip_uncertain race hispanic PrePostInd PregDurCode; set _&outfile.; run; *remove labels; proc datasets lib=work nolist noprint; modify _&outfile.&group.; attrib _all_ label=''; *Remove labels; quit; /*save to MSOC*/ %if %eval(&group.=1) %then %do; data MSOC.&RUNID._T4_cida_&outfile.; set _&outfile.&group.; run; %end; %else %do; proc append base=MSOC.&RUNID._T4_cida_&outfile. data=_&outfile.&group. force; run; %end; %mend CreateCohortTable; %CreateCohortTable(cohortfile=_PtsMasterList, outfile=treat); %CreateCohortTable(cohortfile=_ControlGroup, outfile=ctrl); %macro CreateEligTable(grouping=, out=); proc means data=_Deliveries nway noprint missing; class PatId &grouping. ; output out=_Elig_by_&out. (drop=_TYPE_ rename=_FREQ_=Episodes) sum=; run; proc means data=_Elig_by_&out. nway noprint missing; var Episodes; class &grouping.; output out=_Elig_by_&out. (drop=_TYPE_ rename=_FREQ_=Npts) sum=; run; %mend CreateEligTable; %CreateEligTable(grouping=Group, out=overall); %CreateEligTable(grouping=Group EligDays, out=EligDays); %CreateEligTable(grouping=Group Eligible EligDays, out=Elig); %CreateEligTable(grouping=Group Eligible PrePostInd EligDays, out=EligPPI); data _T4_cida_elig; retain Group Level Eligible EligDays PrePostInd Npts Episodes; set _elig_by_overall(in=a) _elig_by_EligDays(in=b) _elig_by_elig (in=c) _elig_by_eligppi (in=d); if a then Level="000"; if b then Level="599"; if c then Level="600"; if d then Level="601"; run; /*save to MSOC*/ %if %eval(&group.=1) %then %do; data MSOC.&RUNID._T4_cida_elig; set _T4_cida_elig; run; %end; %else %do; proc append base=MSOC.&RUNID._T4_cida_elig data=_T4_cida_elig force; run; %end; %put NOTE: ******** END OF MACRO: ms_cidatablest4 v3.2 ********; %mend ms_cidatablest4;