/*********************************************
* Chris Stewart
* Group Health Research Institute
* (206) 287-2142
* stewart.c@ghc.org
* Purpose: Calculate Denominator numbers, Rx, psychotherapy, and dx Rates in population
having continuous coverage and drug coverage and in sub-populations defined by rx or dx
stratified by 8+ demographic variables - NEW SUPREME DM race/eth classifications.
*Dependencies:
*Output: FOUR files:
MHRN_denominators2011
MHRN_RxRates2011
MHRN_DxRates2011
MHRN_PTRates2011
* Version Date 1/8/12
changes:
Fix for 2001 rx rates singly-stratified data;
fix for race/ethnicity stratification in dx in rx pop rates
Repeat for 2009 and 2010
/* CHANGE: 1. INCLUDE REFERS TO YOUR LOCAL COPY OF STDVARS -
this will set path to input data as well */
%include "[path to StdVars.sas]" ;
run;
/* CHANGE: 2. DESTINATION LIBRARY PATH FOR OUTPUT DATASET */
%let outlib=[path to output directory];
/* CHANGE: 3. CREATE A PROJECT-SPECIFIC WORK DIRECTORY
and supply path here */
%let worklib=[path to work directory];
/* CHANGE: 4. Put the list of rxcodes in the work library and supply name here */
************DOWNLOAD UPDATED NDC FILE!**********************;
%let ndclist = worklib.MHRN_NDC2011;
*************************************************************;
%include vdw_macs ;
/* NOTE: OUTPUT SAS DATASET with sitecode as suffix , see note below -- */
*values for your sitecode and site abbreviations should be already set in your
copy of StdVars.sas ;
/*
Site code and site abbreviation:
01 = GHC
02 = KPNW
03 = KPNC
04 = KPSC
05 = KPHI
06 = KPCO
07 = HP (HealthPartners)
08 = HPHC (Harvard Pilgrim Health Care)
10 = HFHS (Henry Ford)
11 = KPG
*/
%macro MaskSmallCells(inds, outds, countvar, othervar);
data &outds;
set &inds;
if &countvar < 6 then do;
&countvar = .;
&othervar = .;
end;
run;
%mend;
%macro MakeEnrollFile(year, enrlout);
proc format ;
value agecat
low - 19 = '00-19'
20 - 39 = '20-39'
40 - 64 = '40-64'
65 - high = '65+'
;
value youth_agecat
low - 5 = '0 to 5'
6 - 11 = '6 to 11'
12 - 17 = '12 to 17'
18 - high = '18+'
;
run;
%let first_day = mdy(1, 1, &year) ;
%let last_day = mdy(12, 31, &year) ;
*enrollment - make a file in work directory containing biggest denominator;
proc sql;
create table nYear as
select mrn, enr_start, enr_end, drugcov,
ins_medicare as ins_medicare, ins_medicaid, ins_commercial, ins_privatepay, ins_other
from &_vdw_enroll
where enr_start le &last_day and enr_end ge &first_day
;
quit;
*compute months per record;
data comp_mon;
length enr_start enr_end 4;
set nYear;
length num_mm 3;
if . < enr_start < &first_day then enr_start = &first_day;
if enr_end > &last_day then enr_end = &last_day;
if enr_start ne . & enr_end ne . then num_mm = intck('month', enr_start, enr_end) + 1;
else delete;
run;
proc datasets; delete nYear;
run;
*collapse to one record per person;
proc sql;
create table sum_mon as
select mrn, min(enr_start) as EnrollStart, max(enr_end) as EnrollEnd,
max(drugcov) as DrugCov, sum(num_mm) as TotalMonths,
max(ins_medicare) as medicare, max(ins_medicaid) as medicaid, max(ins_commercial) as commercial,
max(ins_privatepay) as privatepay, max(ins_other) as other
from comp_mon
group by mrn
;
quit;
proc datasets; delete comp_mon;
run;
data ins_count;
set sum_mon;
flagcount = 0;
if medicare = 'Y' then flagcount = flagcount + 1;
if medicaid = 'Y' then flagcount = flagcount + 1;
if commercial = 'Y' then flagcount = flagcount + 1;
if privatepay = 'Y' then flagcount = flagcount + 1;
if other = 'Y' then flagcount = flagcount + 1;
run;
proc datasets; delete sum_mon;
run;
*join for census data;
proc sql;
create table enrl_census as
select e.*, c.medhousincome, c.education6, c.education7
from ins_count e left outer join &_vdw_census c on e.mrn = c.mrn
;
quit;
data enrl_census;
set enrl_census;
length LowerIncome $1.;
label LowerIncome='Y if median neighborhood income lt $40,000';
if medhousincome lt 40000 then LowerIncome = 'Y';
else do;
if medhousincome ge 40000 then LowerIncome = 'N';
else LowerIncome = .;
end;
length LowerEduc $1.;
label LowerEduc = 'Y if lt 25% of census block has college degree';
if sum(education6, education7) ge .25 then LowerEduc = 'N';
else do;
if sum(education6, education7) lt .25 then LowerEduc = 'Y';
else Lowereduc = .;
end;
run;
*join for demographics & insurance;
proc sql ;
create table &enrlout&year as
select &year as year, g.mrn, d.gender, d.race1, d.race2, d.hispanic,
put(floor(int((intck('month',d.birth_date,mdy(7,1,&year)) - (day(mdy(7,1,&year)) < day(d.birth_date))) /12)), agecat.)
as agegroup,
put(floor(int((intck('month',d.birth_date,mdy(7,1,&year)) - (day(mdy(7,1,&year)) < day(d.birth_date))) /12)), youth_agecat.)
as youth_agegroup,
LowerIncome, LowerEduc, drugcov, g.TotalMonths,
CASE WHEN totalmonths<10 THEN 'N' ELSE 'Y' END as EnrlTenPlusMonths,
CASE when g.medicare = 'Y' then 'medicare'
when g.medicaid = 'Y' then 'medicaid'
when g.commercial = 'Y' then 'commercial'
when g.privatepay = 'Y' then 'privatepay'
else 'other'
END
as InsuranceType, g.medicare, g.medicaid, g.commercial, g.privatepay, g.other
from enrl_census as g inner JOIN &_vdw_demographic as d
on g.mrn = d.mrn
where d.gender in ('M', 'F')
;
quit ;
*new recommendatins regarding race and ethnicity;
**TEST ME!;
data &enrlout&year;
set &enrlout&year;
length Race $2. Hisp $1. RaceEth $2.;
if hispanic = 'Y' then Hisp = 'Y';
else hisp = 'N';
if missing(race1) or race1 = 'UN' then Race = 'UN';
else do;
if missing(race2) or race2 = 'UN' or race1=race2 then Race = Race1;
else Race = 'MU';
end;
if Hisp = 'Y' then RaceEth = 'HI';
else RaceEth = Race;
run;
proc freq data=&enrlout&year;
where Race = 'MU';
tables race1 *race2/norow nocol;
run;
proc datasets; delete ins_count;
run;
%mend;
%macro MakeMHRxFile(year, rxlist, outds);
*file mhrx&year is persisted in worklib for later use;
%let first_day = mdy(1, 1, &year) ;
%let last_day = mdy(12, 31, &year) ;
*get all rxes;
proc sql;
create table rx1 as
select *
from &_vdw_rx
where rxdate ge &first_day and rxdate le &last_day
;
*collapse to one record per person and ndc;
create table worklib.rxes&year as
select mrn, ndc, min(rxdate) as firstRx, max(rxdate) as lastRx, count(rxdate) as Fills
from rx1
group by mrn, ndc
;
quit;
proc datasets; delete rx1; run;
proc sql;
create table &outds&year as
select *
from worklib.rxes&year
where ndc in (select ndc from &rxlist)
;
quit;
%mend;
%macro MakeMHDxFile(year, outds);
*file MHdx&year is persisted in worklib for later use;
*dont need demographics - see rxRates macro;
%let startdate = mdy(1, 1, &year) ;
%let enddate = mdy(12, 31, &year) ;
proc sql ;
create table worklib.dxes&year as
select *, compress(dx, '.') as dx_comp
from &_vdw_dx
where adate between &startdate and &enddate
and enctype not in ('LO', 'RO')
order by 1, 2
;
quit;
data dx_raw;
set worklib.dxes&year;
length dx_class $40.;
if(
dx_comp in: ( '2962', '2963', '29682', '2980', '3004', '3090', '30112', '3091', '30928' '311' )
or dx_comp in:('2961', '2964', '2965', '2966', '2967', '29680', '29681', '29689')
or dx_comp in:('3000', '3002', '30920', '30921', '30924', '30981' )
or dx_comp in:('2990', '2991', '2998' )
or dx_comp in:('314' )
or dx_comp in:('295', '2971', '2973', '2988', '2989', '30122')
or dx_comp in:('E95', 'E98')
or dx_comp in: ( '291', '292', '303', '304', '305' )
or dx_comp in:('290' )
);
if dx_comp in: ('30393', '30303', '29285', '30493', '30483', '30473', '30543', '30453', '30443', '30433', '30543', '30413', '30403',
'30593', '30583', '30573', '30563', '30553', '30543', '30533', '30523', '30513', '30503', '3051') then delete;
if dx_comp in: ( '291', '292', '303', '304', '305' ) then dx_class = 'Substance Abuse Disorder';
if dx_comp in: ( '2962', '2963', '29682', '2980', '3004', '3090', '30112', '3091', '30928' '311' )
then dx_class = 'Depressive Disorder';
else if dx_comp in:('2961', '2964', '2965', '2966', '2967', '29680', '29681', '29689')
then dx_class = 'Bipolar Spectrum Disorder';
else if dx_comp in:('3000', '3002', '30920', '30921', '30924', '30981' )
then dx_class = 'Anxiety Disorder';
else if dx_comp in:('314' ) then dx_class = 'Attention Deficit Disorder';
else if dx_comp in:('2990', '2991', '2998' ) then dx_class = 'Autism Spectrum Disorder';
else if dx_comp in:('295') then dx_class = 'Schizophrenia Spectrum Disorder';
else if dx_comp in:('290', '78093') then dx_class = 'Dementia';
else if dx_comp in:('2971', '2973', '2988', '2989', '30122') then dx_class = 'Other Psychosis';
else if dx_comp in:('E95') then do;
if dx_comp ='E959' then delete;
else dx_class = 'Self-Inflicted Injury';
end;
else if dx_comp in:('E98') then do;
if dx_comp ='E989' then delete;
ELSE dx_class = 'Possibly Self-Inflicted Injury';
end;
run;
proc sql;
create table dx_dept as
select d.*, u.department
from dx_raw d inner join &_vdw_utilization u
on d.mrn = u.mrn and d.adate = u.adate
and d.enctype = u.enctype and d.provider = u.provider
;
create table dx_spec as
select d.*, specialty as Prov_spec
from dx_dept d left outer join &_vdw_provider_specialty s
on d.provider = s.provider
;
quit;
*clean up provider and department - make zero-length string missing;
data &outds&year;
set dx_spec;
year=&year;
if compress(department, ' ') = '' then department = .;
if compress(prov_spec, ' ') = '' then prov_spec = .;
run;
proc datasets; delete dx_tmp1;delete dx_raw;delete dx_dept; delete dx_spec;
run;
proc contents data=&outds&year;
run;
%mend;
%macro MakePXFile (year, outds);
*file mhpx&year is persisted in worklib for later use;
%let startdate = mdy(1, 1, &year) ;
%let enddate = mdy(12, 31, &year) ;
proc sql ;
create table worklib.pxes&year as
select *
from &_vdw_px
where Enctype = 'AV' and adate between &startdate and &enddate
;
quit ;
data px_raw;
set worklib.pxes&year;
if px in: ( '90801', '90802','90804','90805','90806','90807','90808','90809','90810',
'90810', '90811', '90812', '90813', '90814', '90815', '90862');
run;
proc sql;
create table px_dept as
select p.*, u.department
from px_raw p inner join &_vdw_utilization u
on p.mrn = u.mrn and p.adate = u.adate
and p.enctype = u.enctype and p.provider = u.provider
;
create table px_spec as
select p.*, s.specialty as Prov_spec
from px_dept p left outer join &_vdw_provider_specialty s
on p.provider = s.provider
;
quit;
*clean up provider and department - make zero-length string missing;
data &outds&year;
set px_spec;
year=&year;
if compress(department, ' ') = '' then department = .;
if compress(prov_spec, ' ') = '' then prov_spec = .;
run;
proc datasets;
delete px_tmp1; delete px_raw; delete px_dept; delete px_spec;
run;
proc contents data=&outds&year;
run;
%mend;
%macro MHRXRates_byYear(year, rxList, denomds, stratifyVar1, stratifyvar2, outds);
***from dx macro, try union, get denom & divide in same step;
%if &stratifyvar1 = None and &stratifyvar2 = None %then %do; ***NOT STRATIFIED;
proc sql;
*people receiving individual drugs;
create table drug_people as
select mrn, active_ingred, category, Sum(Fills) as SumFills
from worklib.mhrx&year a inner join &rxlist r on a.ndc = r.ndc
WHERE MRN in (SELECT MRN from &denomds)
group by mrn, active_ingred, category
;
*union with drug classes, any mh drug, any drug;
create table drug_people_plus_agg as
select mrn, active_ingred, category, SumFills
from drug_people
UNION
select mrn, 'Any ' || category as active_ingred, category, Sum(SumFills) as SumFills
from drug_people
group by mrn, category
UNION
select mrn, 'Any MH Drug' as active_ingred, 'Any MH Drug' as category, Sum(sumFills) as SumFills
from drug_people
group by mrn
UNION
select mrn, 'ANY DRUG' as active_ingred, 'ANY DRUG' AS category, sum(fills) as sumFills
from worklib.rxes&year
where mrn in (SELECT MRN from &denomds)
group by mrn
;
create table drug_counts as
select &_sitecode as site, &year as Year, active_ingred, category,
count(mrn) as number_exposed
from drug_people_plus_agg
group by active_ingred, category
;
quit;
proc datasets; delete Drug_people; delete drug_people_plus_agg; run;
*get the appropriate denominator, join and divide;
proc sql;
create table DenomCount as
select &year as Year, DenominatorType, count(MRN) as TotalCovered
from &denomds
group by DenominatorType
;
create table &outds as
select a.site, a.year, a.active_ingred, a.category, '' as stratvar1, '' as stratvar2, a.number_exposed,
(a.Number_exposed/c.TotalCovered) as RxRate, c.TotalCovered as Denominator, c.DenominatorType
from drug_counts a inner join DenomCount c on a.year = c.year
;
quit;
%end;
%if &stratifyvar1 ne None and &stratifyvar2 = None %then %do; ***SINGLE STRAT;
proc sql;
*exposure to individual drugs;
create table Drug_StratPeople as
select a.mrn, r.active_ingred, r.category, e.&stratifyvar1, sum(a.fills) as Sumfills
from worklib.mhrx&year a inner join &rxlist r on a.ndc = r.ndc
inner join &denomds e on a.mrn = e.mrn
group by a.mrn, r.active_ingred, r.category, e.&stratifyvar1
;
*union with drug classes, any mh drug, any drug;
create table Drug_StratPeople_plus_agg as
select mrn, active_ingred, category, &stratifyvar1, Sumfills
from Drug_StratPeople
UNION
select mrn, 'Any ' || category as active_ingred, category, &stratifyvar1, sum(Sumfills) as sumfills
from Drug_StratPeople
group by mrn, category, &stratifyvar1
UNION
select mrn, 'Any MH Drug' as active_ingred, 'Any MH Drug' as category, &stratifyvar1, Sum(SumFills) as SumFills
from Drug_StratPeople
group by mrn, &stratifyvar1
UNION
select r.mrn, 'ANY DRUG' AS active_ingred, 'ANY DRUG' AS category, e.&stratifyvar1, sum(r.fills) as sumFills
from worklib.rxes&year r inner join &denomds e
on r.mrn = e.mrn
group by r.mrn, e.&stratifyvar1
;
create table Drug_stratCounts as
select &_sitecode as site, &year as Year, active_ingred, category, &stratifyvar1, count(mrn) as number_exposed
from Drug_StratPeople_plus_agg
group by category, active_ingred, &stratifyvar1
;
quit;
proc datasets; delete Drug_StratPeople; delete Drug_StratPeople_plus_agg; run;
*get the appropriate denominator, join and divide;
proc sql;
create table DenomCount as
select &year as Year, &stratifyvar1, DenominatorType, count(MRN) as TotalCovered
from &denomds
group by &stratifyvar1, DenominatorType
;
create table &outds as
select a.site, a.Year, a.active_ingred, a.category, a.&stratifyvar1 as stratvar1, '' as Stratvar2,
a.number_exposed, (a.Number_exposed/c.TotalCovered) as RxRate, c.TotalCovered as Denominator, c.DenominatorType
from Drug_stratCounts a inner join DenomCount c on a.Year = c.Year and a.&stratifyvar1 = c.&stratifyvar1
;
quit;
%end;
%if &stratifyvar1 ne None and &stratifyvar2 ne None %then %do; ***DOUBLE STRAT;
proc sql;
*exposure to individual drugs ;
create table Drug_StratPerson as
select a.mrn, r.active_ingred, r.category, e.&stratifyVar1, e.&stratifyVar2, sum(a.fills) as Sumfills
from worklib.mhrx&year a inner join &rxlist r on a.ndc = r.ndc
inner join &denomds e on a.mrn = e.mrn
group by a.mrn, r.active_ingred, r.category, e.&stratifyVar1, e.&stratifyVar2
;
*union with drug classes, any mh drug, any drug;
create table Drug_StratPerson_plus_agg as
select mrn, active_ingred, category, &stratifyVar1, &stratifyVar2, Sumfills
from Drug_StratPerson
UNION
select mrn, 'Any ' || Category as active_ingred, category, &stratifyVar1, &stratifyVar2, sum(sumfills) as sumfills
from Drug_StratPerson
group by mrn, category, &stratifyVar1, &stratifyVar2
UNION
select mrn, 'Any MH Drug' as active_ingred, 'Any MH Drug' as category, &stratifyVar1, &stratifyVar2, sum(sumfills) as sumfills
from Drug_StratPerson
group by mrn, &stratifyVar1, &stratifyVar2
UNION
select r.mrn, 'ANY DRUG' AS active_ingred, 'ANY DRUG' AS category, e.&stratifyVar1, e.&stratifyVar2, sum(r.fills) as sumFills
from worklib.rxes&year r inner join &denomds e
on r.mrn = e.mrn
group by r.mrn, e.&stratifyVar1, e.&stratifyVar2
;
create table Drug_StratCounts as
select &_sitecode as site, &year as Year, active_ingred, category,
&stratifyVar1, &stratifyVar2 , count(mrn) as number_exposed
from Drug_StratPerson_plus_agg
group by category, active_ingred, &stratifyVar1, &stratifyVar2
;
quit;
proc datasets; delete Drug_StratPerson; delete Drug_StratPerson_plus_agg; run;
*get the appropriate denominator, join and divide;
proc sql;
create table DenomCount as
select &year as Year, &stratifyVar1, &stratifyVar2, Denominatortype, count(MRN) as TotalCovered
from &denomds
group by &stratifyVar1, &stratifyVar2, Denominatortype
;
create table &outds as
select a.site, a.year, a.active_ingred, a.category, a.&stratifyVar1 as stratvar1, a.&stratifyVar2 as stratvar2,
a.number_exposed, (a.Number_exposed/c.TotalCovered) as RxRate, c.TotalCovered as Denominator, c.DenominatorType
from Drug_StratCounts a inner join DenomCount c
on a.Year = c.Year and a.&stratifyVar1 = c.&stratifyVar1 and a.&stratifyVar2 = c.&stratifyVar2
;
quit;
%end;
proc datasets; delete DRUG_StratCounts; delete DenomCount; delete AllCounts; run;
proc contents data=&outds varnum;
run;
proc print data=&outds (obs=50);
run;
%mend;
%macro RxRatesInDxPop(dxfile, rxfile, rxlist, denomds, finalfile, stratifyvar1, stratifyvar2);
*other stat theoretically possible is "any mh rx population" then what dxes do they have?;
%if &stratifyvar1 eq None and &stratifyvar2 eq None %then %do; *not stratified;
proc sql;
create table dx_peeps as
select mrn, dx_class, count(dx) as dxcount
from &dxfile
where mrn IN (SELECT MRN from &denomds)
group by mrn, dx_class
UNION
select mrn, 'Any MH dx' as dx_class, count(dx) as dxcount
from &dxfile
where mrn IN (SELECT MRN from &denomds)
group by mrn
;
create table dx_peep_fills as
select p.mrn, p.dx_class,
CASE when a.mrn is null THEN 'No MH rxes' ELSE r.category END as rx_category,
Sum(a.Fills) as SumFills
from Dx_peeps p left outer join &rxfile a on p.mrn = a.mrn
left outer join &rxlist r on a.ndc = r.ndc
group by p.mrn, p.dx_class, calculated rx_category
UNION
select p.mrn, p.dx_class, 'Any MH rx' as rx_category, Sum(a.Fills) as SumFills
from Dx_peeps p inner join &rxfile a on p.mrn = a.mrn
group by p.mrn, p.dx_class
;
*add any mhdx;
create table &finalfile as
select d.dx_class as dxname, d.rx_category, '' as stratvarvalue1, '' as stratvarvalue2,
count(mrn) as number_of_members,
(select count(mrn) from dx_peeps where dx_class = d.dx_class) as dx_denominator
from dx_peep_fills d
group by d.dx_class, d.rx_category
;
quit;
%end;
%if &stratifyvar1 ne None and &stratifyvar2 eq None %then %do; *single stratification;
proc sql;
create table dx_peeps as
select mrn, dx_class, &stratifyvar1, count(dx) as dxcount
from &dxfile
where mrn IN (SELECT MRN from &denomds)
group by mrn, dx_class, &stratifyvar1
UNION
select mrn, 'Any MH dx' as dx_class, &stratifyvar1, count(dx) as dxcount
from &dxfile
where mrn IN (SELECT MRN from &denomds)
group by mrn, &stratifyvar1
;
create table dx_peep_fills as
select p.mrn, p.dx_class, p.&stratifyvar1,
CASE when a.mrn is null THEN 'No MH rxes' ELSE r.category END as rx_category,
Sum(a.Fills) as SumFills
from Dx_peeps p left outer join &rxfile a on p.mrn = a.mrn
left outer join &rxlist r on a.ndc = r.ndc
group by p.mrn, p.dx_class, p.&stratifyvar1, calculated rx_category
UNION
select p.mrn, p.dx_class, p.&stratifyvar1, 'Any MH rx' as rx_category, Sum(a.Fills) as SumFills
from Dx_peeps p inner join &rxfile a on p.mrn = a.mrn
group by p.mrn, p.dx_class, p.&stratifyvar1
;
create table &finalfile as
select d.dx_class as dxname, d.rx_category, d.&stratifyvar1 as stratvarvalue1, '' as stratvarvalue2,
count(mrn) as number_of_members,
(select count(mrn) from dx_peeps
where dx_class = d.dx_class and &stratifyvar1= d.&stratifyvar1) as dx_denominator
from dx_peep_fills d
group by d.dx_class, d.rx_category, d.&stratifyvar1
;
quit;
%end;
%if &stratifyvar1 ne None and &stratifyvar2 ne None %then %do; *double stratification;
proc sql;
create table dx_peeps as
select mrn, dx_class, &stratifyvar1, &stratifyvar2, count(dx) as dxcount
from &dxfile
where mrn IN (SELECT MRN from &denomds)
group by mrn, dx_class, &stratifyvar1, &stratifyvar2
UNION
select mrn, 'Any MH dx' as dx_class, &stratifyvar1, &stratifyvar2, count(dx) as dxcount
from &dxfile
where mrn IN (SELECT MRN from &denomds)
group by mrn, &stratifyvar1, &stratifyvar2
;
create table dx_peep_fills as
select p.mrn, p.dx_class, p.&stratifyvar1, p.&stratifyvar2,
CASE when a.mrn is null THEN 'No MH rxes' ELSE r.category END as rx_category,
Sum(a.Fills) as SumFills
from Dx_peeps p left outer join &rxfile a on p.mrn = a.mrn
left outer join &rxlist r on a.ndc = r.ndc
group by p.mrn, p.dx_class, p.&stratifyvar1, p.&stratifyvar2, calculated rx_category
UNION
select p.mrn, p.dx_class, p.&stratifyvar1, p.&stratifyvar2, 'Any MH rx' as rx_category,
Sum(a.Fills) as SumFills
from Dx_peeps p inner join &rxfile a on p.mrn = a.mrn
group by p.mrn, p.dx_class, p.&stratifyvar1, p.&stratifyvar2
;
create table &finalfile as
select d.dx_class as dxname, d.rx_category, d.&stratifyvar1 as stratvarvalue1,
d.&stratifyvar2 as stratvarvalue2, count(mrn) as number_of_members,
(select count(mrn) from dx_peeps where dx_class = d.dx_class
and &stratifyvar1= d.&stratifyvar1 and &stratifyvar2= d.&stratifyvar2) as dx_denominator
from dx_peep_fills d
group by d.dx_class, d.rx_category, d.&stratifyvar1, d.&stratifyvar2
;
quit;
%end;
proc datasets; delete dx_peeps; delete dx_peep_fills; run;
proc print data=&finalfile (obs=30);
run;
%mend;
%macro MHpx_Rates_byyear (year, denomds, ptds, stratifyvar1, stratifyvar2, outds);
*ptds already limited to people in denomds;
%if &stratifyvar1 = None and &stratifyvar2 = None %then %do; ***NOT STRATIFIED;
*exposure to psychotherpay by cpt, collapse to counts;
proc sql;
create table PTcode_Person as
select mrn, px as active_ingred, count(adate) as dummycount
from &ptds
group by mrn, px
UNION
select mrn, 'Any Psychotherapy' AS active_ingred, count(adate) as dummycount
from &ptds
group by mrn
UNION
select mrn, 'Any Visit' AS active_ingred, count(adate) as dummycount
from worklib.pxes&year
where mrn in (SELECT MRN from &denomds)
group by mrn
;
create table PT_Count as
select &_sitecode as site, &year as year, active_ingred, count(mrn) as Number_exposed
from PTcode_Person
group by active_ingred
;
quit;
*get the appropriate denominator, join and divide;;
proc sql;
create table DenomCount as
select &year as year, denominatorType, count(MRN) as TotalCovered
from &denomds
;
create table &outds as
select a.site, a.year, a.active_ingred, 'Psychotherapy' as tx_category, '' as stratvar1, '' as stratvar2,
a.Number_exposed, (a.Number_exposed/c.TotalCovered)as PTRate, c.TotalCovered as Denominator, c.DenominatorType
from PT_Count a inner join DenomCount c on a.year = c.year
;
quit;
%end;
%if &stratifyvar1 ne None and &stratifyvar2 = None %then %do; ***SINGLE STRAT;
*exposure to psychotherapy by cpt4 code, collapse to counts ;
proc sql;
create table PTcode_Person as
select p.mrn, p.px as active_ingred, e.&stratifyVar1, count(p.adate) as dummycount
from &ptds p inner join &denomds e on p.mrn = e.mrn
group by p.mrn, p.px, e.&stratifyVar1
UNION
select p.mrn, 'Any Psychotherapy' AS active_ingred, e.&stratifyVar1, count(adate) as dummycount
from &ptds p inner join &denomds e on p.mrn = e.mrn
group by p.mrn, e.&stratifyVar1
UNION
select p.mrn, 'Any Visit' AS active_ingred, e.&stratifyVar1, count(adate) as dummycount
from worklib.pxes&year p inner join &denomds e on p.mrn = e.mrn
group by p.mrn, e.&stratifyVar1
;
create table PT_Count as
select &_sitecode as site, &year as year, active_ingred, &stratifyVar1, count(mrn) as Number_exposed
from PTcode_Person
group by active_ingred, &stratifyVar1
;
quit;
*uget the appropriate denominator, join and divide;
proc sql;
create table DenomCount_strat as
select &year as year, &stratifyvar1, DenominatorType, count(MRN) as TotalCovered
from &denomds
group by &stratifyvar1
;
create table &outds as
select a.site, a.year, a.active_ingred, 'Psychotherapy' as tx_category,
a.&stratifyVar1 as stratvar1, '' as stratvar2, a.Number_exposed,
(a.Number_exposed/c.TotalCovered) as PTRate, c.TotalCovered as Denominator, c.DenominatorType
from PT_Count a inner join DenomCount_strat c on a.year = c.year and a.&stratifyvar1 = c.&stratifyvar1
;
quit;
%end;
%if &stratifyvar1 ne None and &stratifyvar2 ne None %then %do; ***DOUBLE STRAT;
*exposure to psychotherapy by cpt4 code, collapse to counts ;
proc sql;
create table PTcode_Person as
select p.mrn, p.px as active_ingred, e.&stratifyVar1, e.&stratifyVar2, count(p.adate) as dummycount
from &ptds p inner join &denomds e on p.mrn = e.mrn
group by p.mrn, p.px, e.&stratifyVar1, e.&stratifyVar2
UNION
select p.mrn, 'Any Psychotherapy' AS active_ingred, e.&stratifyVar1, e.&stratifyVar2, count(adate) as dummycount
from &ptds p inner join &denomds e on p.mrn = e.mrn
group by p.mrn, e.&stratifyVar1, e.&stratifyVar2
UNION
select p.mrn, 'Any Visit' AS active_ingred, e.&stratifyVar1, e.&stratifyVar2, count(adate) as dummycount
from worklib.pxes&year p inner join &denomds e on p.mrn = e.mrn
group by p.mrn, e.&stratifyVar1, e.&stratifyVar2
;
*UNION incorporate any visit count;
create table PT_Count as
select &_sitecode as site, &year as year, active_ingred, &stratifyVar1, &stratifyVar2, count(mrn) as Number_exposed
from PTcode_Person
group by active_ingred, &stratifyVar1, &stratifyVar2
;
quit;
* get the appropriate denominator, join and divide;
proc sql;
create table DenomCount_strat as
select &year as year, &stratifyvar1, &stratifyvar2, denominatortype, count(MRN) as TotalCovered
from &denomds
group by &stratifyvar1, &stratifyvar2
;
create table &outds as
select a.site, a.year, a.active_ingred, 'Psychotherapy' as tx_category,
a.&stratifyVar1 as stratvar1, a.&stratifyVar2 as stratvar2, a.Number_exposed,
(a.Number_exposed/c.TotalCovered) as PTRate, c.TotalCovered as Denominator, c.DenominatorType
from PT_Count a inner join DenomCount_strat c on a.year = c.year
and a.&stratifyvar1 = c.&stratifyvar1 and a.&stratifyvar2 = c.&stratifyvar2
;
quit;
%end;
proc datasets; delete PTcode_Person; delete PT_Count; delete DenomCount; run;
proc print data=&outds (obs=50);
run;
%mend;
%macro PTRatesinDxPop(dxfile, ptfile, denomds, finalfile, stratifyvar1, stratifyvar2);
*add in pt for any mh dx;
%if &stratifyvar1 = None and &stratifyvar2 = None %then %do; ***NOT STRATIFIED;
proc sql;
create table dx_peeps as
select mrn, dx_class, count(dx) as dxcount
from &dxfile
where mrn in (SELECT MRN from &denomds)
group by mrn, dx_class
;
create table dx_peep_visits as
select p.mrn, p.dx_class, 'Psychotherapy' as tx_category,
count(a.ADATE) as VistCount
from Dx_peeps p inner join &ptfile a on p.mrn = a.mrn
group by p.mrn, p.dx_class
;
create table &finalfile as
select dx_class as dxname, 'Any Psychotherapy' as active_ingred, tx_category, '' as Stratvarvalue1, '' as Stratvarvalue2,
count(mrn) as number_of_members, (select count(mrn) from dx_peeps) as dx_denominator,
(select min(denominatorType) from &denomds) as denominatorType
from dx_peep_visits
group by dx_class, tx_category
UNION
select 'Any MH dx' as dxname,'Any Psychotherapy' as active_ingred, tx_category, '' as Stratvarvalue1, '' as Stratvarvalue2,
count(mrn) as number_of_members, (select count(mrn) from dx_peeps) as dx_denominator,
(select min(denominatorType) from &denomds) as denominatorType
from dx_peep_visits
group by tx_category
;
quit;
%end;
*single stratification;
%if &stratifyvar1 ne None and &stratifyvar2 eq None %then %do;
proc sql;
create table dx_peeps as
select mrn, dx_class, &stratifyVar1, count(dx) as dxcount
from &dxfile
where mrn in (SELECT MRN from &denomds)
group by mrn, dx_class, &stratifyVar1
;
create table dx_peep_visits as
select p.mrn, p.&stratifyVar1, p.dx_class, 'Psychotherapy' as tx_category,
Count(a.ADATE) as VistCount
from Dx_peeps p inner join &ptfile a on p.mrn = a.mrn
group by p.mrn, p.&stratifyVar1, p.dx_class
;
create table &finalfile as
select d.dx_class as dxname, 'Any Psychotherapy' as active_ingred, d.tx_category,
d.&stratifyVar1 as Stratvarvalue1, '' as Stratvarvalue2, count(d.mrn) as number_of_members,
(select count(mrn) from dx_peeps where &stratifyvar1 = d.&stratifyvar1) as dx_denominator,
(select min(denominatorType) from &denomds) as denominatorType
from dx_peep_visits d
group by d.tx_category, d.dx_class, d.&stratifyVar1
UNION
select 'Any MH dx' as dxname, 'Any Psychotherapy' as active_ingred, d.tx_category,
d.&stratifyVar1 as Stratvarvalue1, '' as Stratvarvalue2, count(d.mrn) as number_of_members,
(select count(mrn) from dx_peeps where &stratifyvar1 = d.&stratifyvar1) as dx_denominator,
(select min(denominatorType) from &denomds) as denominatorType
from dx_peep_visits d
group by d.tx_category, d.&stratifyVar1
;
quit;
%end;
*double stratification;
%if &stratifyvar1 ne None and &stratifyvar2 ne None %then %do;
proc sql;
create table dx_peeps as
select mrn, dx_class, &stratifyVar1, &stratifyVar2, count(dx) as dxcount
from &dxfile
where mrn in (SELECT MRN from &denomds)
group by mrn, dx_class, &stratifyVar1, &stratifyVar2
;
create table dx_peep_visits as
select p.mrn, p.&stratifyVar1, p.&stratifyVar2, p.dx_class, 'Psychotherapy' as tx_category,
Count(a.ADATE) as VistCount
from Dx_peeps p inner join &ptfile a on p.mrn = a.mrn
group by p.mrn, p.&stratifyVar1, p.&stratifyVar2, p.dx_class
;
create table &finalfile as
select d.dx_class as dxname, 'Any Psychotherapy' as active_ingred, d.tx_category,
d.&stratifyVar1 as StratvarValue1, d.&stratifyVar2 as StratvarValue2, count(d.mrn) as number_of_members,
(select count(mrn) from dx_peeps where &stratifyvar1 = d.&stratifyvar1 and &stratifyvar2 = d.&stratifyvar2)
as dx_denominator,(select min(denominatorType) from &denomds) as denominatorType
from dx_peep_visits d
group by d.tx_category, d.dx_class, d.&stratifyVar1, d.&stratifyVar2
UNION
select 'Any MH dx' as dxname, 'Any Psychotherapy' as active_ingred, d.tx_category,
d.&stratifyVar1 as StratvarValue1, d.&stratifyVar2 as StratvarValue2, count(d.mrn) as number_of_members,
(select count(mrn) from dx_peeps where &stratifyvar1 = d.&stratifyvar1 and &stratifyvar2 = d.&stratifyvar2)
as dx_denominator,(select min(denominatorType) from &denomds) as denominatorType
from dx_peep_visits d
group by d.tx_category, d.&stratifyVar1, d.&stratifyVar2
;
quit;
%end;
proc datasets; delete dx_peeps; delete dx_peep_visits; run;
proc print data=&finalfile (obs=30); run;
%mend;
%macro MHDxRates_byyear(year, dxfile, denomds, stratifyvar1, stratifyvar2, outds);
%if &stratifyvar1 = None and &stratifyvar2 = None %then %do; ***NOT STRATIFIED
*group by dx class, union with any mh dx and any dx totals, then collapse to counts;
proc sql;
create table Dx_people as
select mrn, dx_class, count(dx) as dxCount
from &dxfile
WHERE MRN in (SELECT MRN from &denomds)
group by mrn, dx_class
UNION
select mrn, 'Any MH dx' as dx_class, count(dx) as dxCount
from &dxfile
WHERE MRN in (SELECT MRN from &denomds)
group by mrn
UNION
select mrn, 'ANY DX' AS dx_class, count(dx) as dxcount
from worklib.dxes&year
where mrn in (SELECT MRN from &denomds)
group by mrn
;
create table Dx_Counts as
select &_sitecode as site, &year as year, dx_class, count(mrn) as Number_of_members
from Dx_people
group by dx_class
;
quit;
*get the appropriate denominator, join and divide, union two datasets;
proc sql;
create table denomcount as
select &year as year, count(MRN) as TotalCovered, denominatortype
from &denomds
group by denominatortype
;
create table &outds as
select a.site, a.year, a.dx_class, '' as StratVar1, '' as StratVar2, a.Number_of_members,
a.Number_of_members/c.totalcovered as DxRate, c.TotalCovered as Denominator, c.denominatortype
from Dx_Counts a inner join denomCount c on a.year = c.year
;
quit;
%end;
%if &stratifyvar1 ne None and &stratifyvar2 = None %then %do; ***SINGLE STRAT;
proc sql;
*group by dx class, union with any mh dx and any dx totals, then collapse to counts;
create table DX_StratPeople as
select mrn, dx_class, &stratifyVar1, count(Adate) as dxCount
from &dxfile
WHERE MRN in (SELECT MRN from &denomds)
group by mrn, dx_class, &stratifyvar1
UNION
select mrn, 'Any MH dx' as dx_class, &stratifyVar1, count(Adate) as dxCount
from &dxfile
WHERE MRN in (SELECT MRN from &denomds)
group by mrn, &stratifyvar1
UNION
select x.mrn, 'ANY DX' AS dx_class, e.&stratifyVar1, count(x.Adate) as dxCount
from worklib.dxes&year x inner join &denomds e on x.mrn = e.mrn
group by x.mrn, e.&stratifyvar1
;
create table DX_Counts as
select &_sitecode as site, &year as year, dx_class, &stratifyVar1, count(mrn) as number_of_members
from DX_StratPeople
group by dx_class, &stratifyVar1
;
quit;
*get the appropriate denominator, join and divide;
*dummycount is defensive programming - may or may not be necessary;
proc sql;
create table denomCount as
select &year as year, &stratifyVar1, count(MRN) as TotalCovered, denominatortype
from &denomds
group by year, &stratifyVar1, denominatortype
;
create table &outds as
select a.site, a.year, a.&stratifyVar1 as Stratvar1, '' as stratvar2, a.dx_class, a.number_of_members,
(a.number_of_members/c.TotalCovered) as DxRate, c.TotalCovered as Denominator, c.denominatorType,
count(c.year) as dummycount
from DX_Counts a inner join denomCount c
on a.year = c.year and a.&stratifyVar1 = c.&stratifyVar1
group by a.site, a.year, a.dx_class, a.&stratifyvar1, c.totalcovered, c.denominatorType
;
quit;
%end;
%if &stratifyvar1 ne None and &stratifyvar2 ne None %then %do; ***DOUBLE STRAT;
*diagnoses by class, then collapse;
proc sql;
create table DX_Person as
select mrn, dx_class, &stratifyVar1, &stratifyVar2, count(Adate) as dxCount
from &dxfile
WHERE MRN in (SELECT MRN from &denomds)
group by mrn, dx_class, &stratifyvar1, &stratifyVar2
UNION
select mrn, 'Any MH dx' as dx_class, &stratifyVar1, &stratifyVar2, count(Adate) as dxCount
from &dxfile
WHERE MRN in (SELECT MRN from &denomds)
group by mrn, &stratifyvar1, &stratifyVar2
UNION
select x.mrn, 'ANY DX' AS dx_class, e.&stratifyVar1, e.&stratifyVar2, count(x.Adate) as dxCount
from worklib.dxes&year x inner join &denomds e on x.mrn = e.mrn
group by x.mrn, e.&stratifyvar1, e.&stratifyVar2
;
create table DX_Counts as
select &_sitecode as site, &year as year, dx_class, &stratifyVar1, &stratifyVar2,
count(mrn) as number_of_members
from DX_Person
group by site, year, dx_class, &stratifyVar1, &stratifyVar2
;
quit;
*get the appropriate denominator, join and divide;
*dummycount is defensive programming - may or may not be necessary;
proc sql;
create table denomCount as
select &year as year, &stratifyVar1, &stratifyVar2, count(MRN) as TotalCovered, denominatortype
from &denomds
group by year, &stratifyVar1, &stratifyVar2, denominatortype
;
create table &outds as
select a.site, a.year, a.&stratifyVar1 as Stratvar1, a.&stratifyVar2 as stratvar2, a.dx_class, a.number_of_members,
(a.number_of_members/c.TotalCovered) as DxRate, c.TotalCovered as Denominator, c.denominatorType,
count(c.year) as dummycount
from DX_Counts a inner join denomCount c
on a.year = c.year and a.&stratifyVar1 = c.&stratifyVar1 and a.&stratifyVar2 = c.&stratifyVar2
group by a.site, a.year, a.dx_class, a.&stratifyvar1, a.&stratifyVar2, c.totalcovered, c.denominatorType
;
quit;
%end;
proc datasets; delete Dx_Person; delete ANYDX_Person; delete DX_Count; delete ANYDX_Count; delete denomCount;
run;
proc print data=&outds (obs=10); run;
%mend;
%macro DxRatesInRxPop(dxfile, rxfile, rxlist, denomds, finalfile, stratifyvar1, stratifyvar2);
*try to shorten based on PT macro - yes this was faster 0.1 vs 0.4 hr.;
%if &stratifyvar1 eq None and &stratifyvar2 eq None %then %do; *not stratified;
proc sql;
*added "any mh rx population" as a denominator;
create table rx_peeps as
select a.mrn, r.category, Sum(a.Fills) as SumFills
from &rxfile a inner join &rxlist r on a.ndc = r.ndc
WHERE a.MRN in (SELECT MRN from &denomds)
group by a.mrn, r.category
UNION
select mrn, 'Any MH rx' as category, Sum(Fills) as SumFills
from &rxfile
where mrn IN (SELECT MRN from &denomds)
group by mrn
;
create table rx_peep_dxes as
select r.mrn, r.category,
CASE when d.dx_class is null THEN 'No MH diagnosis' ELSE d.dx_class END as dx_category,
count(d.dx) as DxCount
from rx_peeps r left outer join &dxfile d on r.mrn = d.mrn
group by r.mrn, r.category, calculated dx_category
UNION
select r.mrn, r.category, 'Any MH dx' as dx_category, count(d.dx) as DxCount
from rx_peeps r inner join &dxfile d on r.mrn = d.mrn
group by r.mrn, r.category
;
*modify here, see if dx peeps with "any mh rx" = 1-dxRate for "No MH dx"; ;
create table &finalfile as
select r.category as drugname, r.dx_category as dx_class, count(r.mrn) as Number_of_members,
'' as Stratvarvalue1, '' as Stratvarvalue2,
(SELECT count(mrn) from rx_peeps where category = r.category) as rx_denominator
from rx_peep_dxes r
group by r.category, r.dx_category
;
quit;
%end;
%if &stratifyvar1 ne None and &stratifyvar2 eq None %then %do; *single stratification;
proc sql;
create table rx_peeps as
select a.mrn, a.&stratifyvar1, r.category, Sum(a.Fills) as SumFills
from &rxfile a inner join &rxlist r on a.ndc = r.ndc
WHERE a.MRN in (SELECT MRN from &denomds)
group by a.mrn, a.&stratifyvar1, r.category
UNION
select mrn, &stratifyvar1, 'Any MH rx' as category, Sum(Fills) as SumFills
from &rxfile
where mrn IN (SELECT MRN from &denomds)
group by mrn, &stratifyvar1
;
create table rx_peep_dxes as
select r.mrn, r.category, r.&stratifyvar1,
CASE when d.dx_class is null THEN 'No MH diagnosis' ELSE d.dx_class END as dx_category,
count(d.dx) as DxCount
from rx_peeps r left outer join &dxfile d
on r.mrn = d.mrn
group by r.mrn, r.category, r.&stratifyvar1, calculated dx_category
UNION
select r.mrn, r.category, r.&stratifyvar1, 'Any MH dx' as dx_category, count(d.dx) as DxCount
from rx_peeps r inner join &dxfile d on r.mrn = d.mrn
group by r.mrn, r.category, r.&stratifyvar1
;
create table &finalfile as
select r.category as drugname, r.dx_category as dx_class, r.&stratifyvar1 as Stratvarvalue1,
'' as Stratvarvalue2, count(r.mrn) as Number_of_members,
(SELECT count(mrn) from rx_peeps
where category = r.category and &stratifyvar1 = r.&stratifyvar1) as rx_denominator
from rx_peep_dxes r
group by r.category, r.dx_category, r.&stratifyvar1
;
quit;
%end;
%if &stratifyvar1 ne None and &stratifyvar2 ne None %then %do; *double stratification;
proc sql;
create table rx_peeps as
select a.mrn, a.&stratifyvar1, a.&stratifyvar2, r.category, Sum(Fills) as SumFills
from &rxfile a inner join &rxlist r on a.ndc = r.ndc
WHERE a.MRN in (SELECT MRN from &denomds)
group by a.mrn, a.&stratifyvar1, a.&stratifyvar2, r.category
UNION
select mrn, &stratifyvar1, &stratifyvar2, 'Any MH rx' as category, Sum(Fills) as SumFills
from &rxfile
where mrn IN (SELECT MRN from &denomds)
group by mrn, &stratifyvar1, &stratifyvar2
;
create table rx_peep_dxes as
select r.mrn, r.category, r.&stratifyvar1, r.&stratifyvar2,
CASE when d.dx_class is null THEN 'No MH diagnosis' ELSE d.dx_class END as dx_category,
count(d.dx) as DxCount
from rx_peeps r left outer join &dxfile d
on r.mrn = d.mrn
group by r.mrn, r.category, r.&stratifyvar1, r.&stratifyvar2, calculated dx_category
UNION
select r.mrn, r.category, r.&stratifyvar1, r.&stratifyvar2, 'Any MH dx' as dx_category, count(d.dx) as DxCount
from rx_peeps r inner join &dxfile d on r.mrn = d.mrn
group by r.mrn, r.category, r.&stratifyvar1, r.&stratifyvar2
;
create table &finalfile as
select r.category as drugname, r.dx_category as dx_class, r.&stratifyvar1 as Stratvarvalue1,
r.&stratifyvar2 as Stratvarvalue2,
count(r.mrn) as Number_of_members,
(SELECT count(mrn) from rx_peeps where category = r.category
and &stratifyvar1 = r.&stratifyvar1 and &stratifyvar2 = r.&stratifyvar2) as rx_denominator
from rx_peep_dxes r
group by r.category, r.dx_category, r.&stratifyvar1, r.&stratifyvar2
;
quit;
%end;
proc datasets; delete rx_peeps; delete rx_peep_dxes; run;
proc print data=&finalfile ; run;
%mend;
%macro compile_oneway (type);
%if &type = rx %then %do;
proc sql;
create table RxRates_Oneway as
select site, year, active_ingred, category as rx_category, 'None' as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_exposed, denominator, RxRate, DenominatorType
from MHRx_Rates
UNION select site, year, active_ingred, category , &agelbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_exposed, denominator, RxRate, DenominatorType
from MHRx_Rates_byAge
UNION select site, year, active_ingred, category, &yagelbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_exposed, denominator, RxRate, DenominatorType
from MHRx_Rates_byYage
UNION select site, year, active_ingred, category, &sexlbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_exposed, denominator, RxRate, DenominatorType
from MHRx_Rates_bySex
UNION select site, year, active_ingred, category , &inslbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_exposed, denominator, RxRate, DenominatorType
from MHRx_Rates_byIns
UNION select site, year, active_ingred, category , &inclbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_exposed, denominator, RxRate, DenominatorType
from MHRx_Rates_byInc
UNION select site, year, active_ingred, category, &edulbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_exposed, denominator, RxRate, DenominatorType
from MHRx_Rates_byEdu
UNION select site, year, active_ingred, category, &racelbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_exposed, denominator, RxRate, DenominatorType
from MHRx_Rates_byrace
UNION select site, year, active_ingred, category, &hisplbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_exposed, denominator, RxRate, DenominatorType
from MHRx_Rates_byEth
UNION select site, year, active_ingred, category, &RElbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_exposed, denominator, RxRate, DenominatorType
from MHRx_Rates_byraceEth
UNION select site, year, active_ingred, category, &agelbl as stratifyvar1, stratvar1 as Stratvarvalue1,
&sexlbl as stratifyvar2, stratvar2 as Stratvarvalue2, number_exposed, denominator, RxRate, DenominatorType
from MHRx_Rates_byAgeSex
UNION select site, year, active_ingred, category, &yagelbl as stratifyvar1, stratvar1 as Stratvarvalue1,
&sexlbl as stratifyvar2, stratvar2 as Stratvarvalue2, number_exposed, denominator, RxRate, DenominatorType
from MHRx_Rates_byYageSex
;
quit;
proc print data=rxrates_oneway (OBS=40);
run;
proc datasets;
delete MHRx_Rates; delete MHRx_Rates_byAge; delete MHRx_Rates_byYAge; delete MHRx_Rates_bySex;
delete MHRx_Rates_byIns; delete MHRx_Rates_byInc; delete MHRx_Rates_byEdu;
delete MHRx_Rates_byrace; delete MHRx_Rates_byEth; delete MHRx_Rates_byraceEth;
delete MHRx_Rates_byAgeSex; delete MHRx_Rates_byYAgeSex;
run;
%end;
%if &type = dx %then %do;
proc sql;
create table MHDX_rates_oneway as
select site, year, dx_class, 'None' as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_of_members, dxrate, denominator, denominatortype
from MHDXRates
UNION
select site, year, dx_class, &agelbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_of_members, dxrate, denominator, denominatortype
from MHDXRates_byAge
UNION
select site, year, dx_class, &yagelbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_of_members, dxrate, denominator, denominatortype
from MHDXRates_byYage
UNION
select site, year, dx_class, &sexlbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_of_members, dxrate, denominator, denominatortype
from MHDXRates_bySex
UNION
select site, year, dx_class, &inslbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_of_members, dxrate, denominator, denominatortype
from MHDXRates_byIns
UNION
select site, year, dx_class, &inclbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_of_members, dxrate, denominator, denominatortype
from MHDXRates_byInc
UNION
select site, year, dx_class, &edulbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_of_members, dxrate, denominator, denominatortype
from MHDXRates_byEdu
UNION
select site, year, dx_class, &racelbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_of_members, dxrate, denominator, denominatortype
from MHDXRates_byRace
UNION
select site, year, dx_class, &hisplbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_of_members, dxrate, denominator, denominatortype
from MHDXRates_byEth
UNION
select site, year, dx_class, &RElbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_of_members, dxrate, denominator, denominatortype
from MHDXRates_byRaceeth
UNION
select site, year, dx_class, &agelbl as stratifyvar1, stratvar1 as Stratvarvalue1,
&sexlbl as stratifyvar2, stratvar2 as Stratvarvalue2, number_of_members, dxrate, denominator, denominatortype
from MHDXRates_byAgeSex
UNION
select site, year, dx_class, &yagelbl as stratifyvar1, stratvar1 as Stratvarvalue1,
&sexlbl as stratifyvar2, stratvar2 as Stratvarvalue2, number_of_members, dxrate, denominator, denominatortype
from MHDXRates_byYageSex
;
quit;
proc print data=MHDX_rates_oneway (obs=40);
run;
proc datasets;
delete MHDXRates; delete MHDXRates_byAge; delete MHDXRates_byYage; delete MHDXRates_bySex;
delete MHDXRates_byIns; delete MHDXRates_byInc; delete MHDXRates_byEdu;
delete MHDXRates_byRace; delete MHDXRates_byEth; delete MHDXRates_byRaceEth;
delete MHDXRates_byAgeSex; delete MHDXRates_byYAgeSex;
run;
%end;
%if &type = px %then %do;
proc sql;
create table PxRates_Oneway as
select site, year, active_ingred, 'Psychotherapy' as Tx_category, 'None' as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_exposed, denominator, PTRate, DenominatorType
from MHPx_Rates
UNION select site, year, active_ingred, 'Psychotherapy', &agelbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_exposed, denominator, PTRate, DenominatorType
from MHPx_Rates_byAge
UNION select site, year, active_ingred, 'Psychotherapy', &yagelbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_exposed, denominator, PTRate, DenominatorType
from MHPx_Rates_byYage
UNION select site, year, active_ingred, 'Psychotherapy', &sexlbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_exposed, denominator, PTRate, DenominatorType
from MHPx_Rates_bySex
UNION select site, year, active_ingred, 'Psychotherapy', &inslbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_exposed, denominator, PTRate, DenominatorType
from MHPx_Rates_byIns
UNION select site, year, active_ingred, 'Psychotherapy', &inclbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_exposed, denominator, PTRate, DenominatorType
from MHPx_Rates_byInc
UNION select site, year, active_ingred, 'Psychotherapy', &edulbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_exposed, denominator, PTRate, DenominatorType
from MHPx_Rates_byEdu
UNION select site, year, active_ingred, 'Psychotherapy', &racelbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_exposed, denominator, PTRate, DenominatorType
from MHPx_Rates_byrace
UNION select site, year, active_ingred, 'Psychotherapy', &hisplbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_exposed, denominator, PTRate, DenominatorType
from MHPx_Rates_byEth
UNION select site, year, active_ingred, 'Psychotherapy', &RElbl as stratifyvar1, stratvar1 as Stratvarvalue1,
'None' as stratifyvar2, stratvar2 as Stratvarvalue2, number_exposed, denominator, PTRate, DenominatorType
from MHPx_Rates_byraceEth
UNION select site, year, active_ingred, 'Psychotherapy', &agelbl as stratifyvar1, stratvar1 as Stratvarvalue1,
&sexlbl as stratifyvar2, stratvar2 as Stratvarvalue2, number_exposed, denominator, PTRate, DenominatorType
from MHPx_Rates_byAgeSex
UNION select site, year, active_ingred, 'Psychotherapy', &yagelbl as stratifyvar1, stratvar1 as Stratvarvalue1,
&sexlbl as stratifyvar2, stratvar2 as Stratvarvalue2, number_exposed, denominator, PTRate, DenominatorType
from MHPx_Rates_byYageSex
;
quit;
proc datasets;
delete MHPx_Rates; delete MHPx_Rates_byAge; delete MHPx_Rates_byYAge; delete MHPx_Rates_bySex;
delete MHPx_Rates_byIns; delete MHPx_Rates_byInc; delete MHPx_Rates_byEdu;
delete MHPx_Rates_byrace; delete MHPx_Rates_byEth; delete MHPx_Rates_byraceEth;
delete MHPx_Rates_byAgeSex; delete MHPx_Rates_byYAgeSex;
run;
%end;
%mend;
%macro compile_twoway (type);
%if &type = rx %then %do;
proc sql;
create table RXinDXpop as
select &_sitecode as site, &year as year, rx_category, 'Dx of: ' || dxname as denominatortype,
'None' as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, number_of_members/dx_denominator as RxRate
from RXes_in_DXPop where dx_denominator ne .
UNION
select &_sitecode as site, &year as year, rx_category, 'Dx of: ' || dxname as denominatortype,
&agelbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, number_of_members/dx_denominator as RxRate
from RXes_in_DXPopAge where dx_denominator ne .
UNION
select &_sitecode as site, &year as year, rx_category, 'Dx of: ' || dxname as denominatortype,
&yagelbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, number_of_members/dx_denominator as RxRate
from RXes_in_DXPopYAge where dx_denominator ne .
UNION
select &_sitecode as site, &year as year, rx_category, 'Dx of: ' || dxname as denominatortype,
&sexlbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, number_of_members/dx_denominator as RxRate
from RXes_in_DXPopSex where dx_denominator ne .
UNION
select &_sitecode as site, &year as year, rx_category, 'Dx of: ' || dxname as denominatortype,
&inslbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, number_of_members/dx_denominator as RxRate
from RXes_in_DXPopIns where dx_denominator ne .
UNION
select &_sitecode as site, &year as year, rx_category, 'Dx of: ' || dxname as denominatortype,
&inclbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, number_of_members/dx_denominator as RxRate
from RXes_in_DXPopInc where dx_denominator ne .
UNION
select &_sitecode as site, &year as year, rx_category, 'Dx of: ' || dxname as denominatortype,
&edulbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, number_of_members/dx_denominator as RxRate
from RXes_in_DXPopEdu where dx_denominator ne .
UNION
select &_sitecode as site, &year as year, rx_category, 'Dx of: ' || dxname as denominatortype,
&racelbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, number_of_members/dx_denominator as RxRate
from RXes_in_DXPopRace where dx_denominator ne .
UNION
select &_sitecode as site, &year as year, rx_category, 'Dx of: ' || dxname as denominatortype,
&hisplbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, number_of_members/dx_denominator as RxRate
from RXes_in_DXPopEth where dx_denominator ne .
UNION
select &_sitecode as site, &year as year, rx_category, 'Dx of: ' || dxname as denominatortype,
&RElbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, number_of_members/dx_denominator as RxRate
from RXes_in_DXPopRaceEth where dx_denominator ne .
UNION
select &_sitecode as site, &year as year, rx_category, 'Dx of: ' || dxname as denominatortype,
&agelbl as stratifyvar1, Stratvarvalue1, &sexlbl as stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, number_of_members/dx_denominator as RxRate
from RXes_in_DXPopAgeSex where dx_denominator ne .
UNION
select &_sitecode as site, &year as year, rx_category, 'Dx of: ' || dxname as denominatortype,
&yagelbl as stratifyvar1, Stratvarvalue1, &sexlbl as stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, number_of_members/dx_denominator as RxRate
from RXes_in_DXPopYAgeSex where dx_denominator ne .
;
quit;
proc datasets;
delete RXes_in_DXPop; delete RXes_in_DXPopAge; delete RXes_in_DXPopYAge; delete RXes_in_DXPopSex;
delete RXes_in_DXPopIns; delete RXes_in_DXPopInc; delete RXes_in_DXPopEdu;
delete RXes_in_DXPopRace; delete RXes_in_DXPopEth; delete RXes_in_DXPopRaceEth;
delete RXes_in_DXPopAgeSex; delete RXes_in_DXPopYAgeSex;
run;
%end;
%if &type = dx %then %do;
proc sql;
create table DXinRXpop as
select &_sitecode as site, &year as Year, drugname, dx_class,
'None' as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, Rx_Denominator , number_of_members/Rx_Denominator as pctRxPopWithDx,
'Continuous and Drug Coverage' as DenominatorType
from DXes_in_RxPop where Rx_Denominator ne .
UNION
select &_sitecode as site, &year as Year, drugname, dx_class,
&agelbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, Rx_Denominator, number_of_members/Rx_Denominator as pctRxPopWithDx,
'Continuous and Drug Coverage' as DenominatorType
from DXes_in_RxPopAge where Rx_Denominator ne .
UNION
select &_sitecode as site, &year as Year, drugname, dx_class,
&yagelbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, Rx_Denominator, number_of_members/Rx_Denominator as pctRxPopWithDx,
'Continuous and Drug Coverage' as DenominatorType
from DXes_in_RxPopYAge where Rx_Denominator ne .
UNION
select &_sitecode as site, &year as Year, drugname, dx_class,
&sexlbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, Rx_Denominator, number_of_members/Rx_Denominator as pctRxPopWithDx,
'Continuous and Drug Coverage' as DenominatorType
from DXes_in_RxPopSex where Rx_Denominator ne .
UNION
select &_sitecode as site, &year as Year, drugname, dx_class,
&inslbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, Rx_Denominator, number_of_members/Rx_Denominator as pctRxPopWithDx,
'Continuous and Drug Coverage' as DenominatorType
from DXes_in_RxPopIns where Rx_Denominator ne .
UNION
select &_sitecode as site, &year as Year, drugname, dx_class,
&inclbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, Rx_Denominator, number_of_members/Rx_Denominator as pctRxPopWithDx,
'Continuous and Drug Coverage' as DenominatorType
from DXes_in_RxPopInc where Rx_Denominator ne .
UNION
select &_sitecode as site, &year as Year, drugname, dx_class,
&edulbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, Rx_Denominator, number_of_members/Rx_Denominator as pctRxPopWithDx,
'Continuous and Drug Coverage' as DenominatorType
from DXes_in_RxPopEdu where Rx_Denominator ne .
UNION
select &_sitecode as site, &year as Year, drugname, dx_class,
&racelbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, Rx_Denominator, number_of_members/Rx_Denominator as pctRxPopWithDx,
'Continuous and Drug Coverage' as DenominatorType
from DXes_in_RxPopRace where Rx_Denominator ne .
UNION
select &_sitecode as site, &year as Year, drugname, dx_class,
&hisplbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, Rx_Denominator, number_of_members/Rx_Denominator as pctRxPopWithDx,
'Continuous and Drug Coverage' as DenominatorType
from DXes_in_RxPopEth where Rx_Denominator ne .
UNION
select &_sitecode as site, &year as Year, drugname, dx_class,
&RElbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, Rx_Denominator, number_of_members/Rx_Denominator as pctRxPopWithDx,
'Continuous and Drug Coverage' as DenominatorType
from DXes_in_RxPopRaceEth where Rx_Denominator ne .
UNION
select &_sitecode as site, &year as Year, drugname, dx_class,
&agelbl as stratifyvar1, Stratvarvalue1, &sexlbl as stratifyvar2, Stratvarvalue2,
number_of_members, Rx_Denominator, number_of_members/Rx_Denominator as pctRxPopWithDx,
'Continuous and Drug Coverage' as DenominatorType
from DXes_in_RxPopAgeSex where Rx_Denominator ne .
UNION
select &_sitecode as site, &year as Year, drugname, dx_class,
&yagelbl as stratifyvar1, Stratvarvalue1, &sexlbl as stratifyvar2, Stratvarvalue2,
number_of_members, Rx_Denominator, number_of_members/Rx_Denominator as pctRxPopWithDx,
'Continuous and Drug Coverage' as DenominatorType
from DXes_in_RxPopYAgeSex where Rx_Denominator ne .
;
quit;
proc datasets;
delete DXes_in_RxPop; delete DXes_in_RxPopAge; delete DXes_in_RxPopYAge; delete DXes_in_RxPopSex;
delete DXes_in_RxPopIns; delete DXes_in_RxPopInc; delete DXes_in_RxPopEdu;
delete DXes_in_RxPopRace; delete DXes_in_RxPopEth; delete DXes_in_RxPopRaceEth;
delete DXes_in_RxPopAgeSex; delete DXes_in_RxPopYAgeSex;
run;
%end;
%if &type = px %then %do;
proc sql;
create table PTinDXpop as
select &_sitecode as site, &year as year, active_ingred, tx_category, 'Dx of: ' || dxname as denominatortype,
'None' as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, number_of_members/dx_denominator as pctDxPopWithPT
from MHPT_rates
UNION
select &_sitecode as site, &year as year, active_ingred, tx_category, 'Dx of: ' || dxname as denominatortype,
&agelbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, number_of_members/dx_denominator as pctDxPopWithPT
from MHPT_rate_byAge
UNION
select &_sitecode as site, &year as year, active_ingred, tx_category, 'Dx of: ' || dxname as denominatortype,
&yagelbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, number_of_members/dx_denominator as pctDxPopWithPT
from MHPT_rate_byYAge
UNION
select &_sitecode as site, &year as year, active_ingred, tx_category, 'Dx of: ' || dxname as denominatortype,
&sexlbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, number_of_members/dx_denominator as pctDxPopWithPT
from MHPT_rate_bySex
UNION
select &_sitecode as site, &year as year, active_ingred, tx_category, 'Dx of: ' || dxname as denominatortype,
&inslbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, number_of_members/dx_denominator as pctDxPopWithPT
from MHPT_rate_byIns
UNION
select &_sitecode as site, &year as year, active_ingred, tx_category, 'Dx of: ' || dxname as denominatortype,
&inclbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, number_of_members/dx_denominator as pctDxPopWithPT
from MHPT_rate_byInc
UNION
select &_sitecode as site, &year as year, active_ingred, tx_category, 'Dx of: ' || dxname as denominatortype,
&edulbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, number_of_members/dx_denominator as pctDxPopWithPT
from MHPT_rate_byEdu
UNION
select &_sitecode as site, &year as year, active_ingred, tx_category, 'Dx of: ' || dxname as denominatortype,
&racelbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, number_of_members/dx_denominator as pctDxPopWithPT
from MHPT_rate_byrace
UNION
select &_sitecode as site, &year as year, active_ingred, tx_category, 'Dx of: ' || dxname as denominatortype,
&hisplbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, number_of_members/dx_denominator as pctDxPopWithPT
from MHPT_rate_byEth
UNION
select &_sitecode as site, &year as year, active_ingred, tx_category, 'Dx of: ' || dxname as denominatortype,
&RElbl as stratifyvar1, Stratvarvalue1, 'None' as stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, number_of_members/dx_denominator as pctDxPopWithPT
from MHPT_rate_byraceEth
UNION
select &_sitecode as site, &year as year, active_ingred, tx_category, 'Dx of: ' || dxname as denominatortype,
&agelbl as stratifyvar1, Stratvarvalue1, &sexlbl as stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, number_of_members/dx_denominator as pctDxPopWithPT
from MHPT_rate_byAgeSex
UNION
select &_sitecode as site, &year as year, active_ingred, tx_category, 'Dx of: ' || dxname as denominatortype,
&Yagelbl as stratifyvar1, Stratvarvalue1, &sexlbl as stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, number_of_members/dx_denominator as pctDxPopWithPT
from MHPT_rate_byYageSex
;
quit;
proc datasets;
delete MHPT_rates; delete MHPT_rate_byAge; delete MHPT_rate_byYAge; delete MHPT_rate_bySex;
delete MHPT_rate_byIns; delete MHPT_rate_byInc; delete MHPT_rate_byEdu;
delete MHPT_rate_byrace; delete MHPT_rate_byEth; delete MHPT_rate_byraceEth;
delete MHPT_rate_byAgeSex; delete MHPT_rate_byYAgeSex;
run;
%end;
%mend;
libname outlib "&outlib" ;
libname worklib "&worklib" ;
*RX RATES;
proc sql;
create table drug_list as
select * from &ndclist
where active_ingred not in ('PROCHLORPERAZINE', 'COMPAZINE', 'CHLORDIAZEPOXIDE/CLIDINIUM', 'CHLORDIAZEPOXIDE/ESTROGENS', 'DEXTROAMPHETAMINE/AMOBARBITAL',
'FLUOXETINE/OLANZAPINE', 'MEPROBAMATE/ASPIRIN', 'MEPROBAMATE/BENACTYZINE', 'MEPROBAMATE/ETHOHEPTAZINE', 'MEPROBAMATE/TRIDIHEXETHYL')
;
update drug_list set category = 'Older Antidepressant'
where active_ingred in ('TRAZODONE', 'BUPROPION', 'AMITRIPTYLINE', 'AMITRIPTYLINE/CHLORDIAZEPOXIDE', 'AMITRIPTYLINE/PERPHENAZINE',
'CLOMIPRAMINE','DESIPRAMINE', 'DOXEPIN', 'IMIPRAMINE', 'NORTRIPTYLINE', 'PROTRIPTYLINE', 'TRIMIPRAMINE')
;
quit;
%let agelbl = 'Age Group';
%let Yagelbl = 'Youth Age Group';
%let sexlbl = 'Gender';
%let inslbl = 'Insurance Type';
%let inclbl = 'Lower Income';
%let edulbl = 'Lower Education';
%let racelbl = 'Race';
%let hisplbl = 'Hispanic Ethnicity';
%let RElbl = 'Race/Ethnicity';
%let year = 2011;
%MakeEnrollFile(&year, worklib.enroll);
%MakeMHRxFile(year=&year, rxlist=drug_list, outds=worklib.mhrx);
%MakeMHDxFile(year=&year, outds=worklib.mhdx);
%MakePXFile (year=&year, outds=worklib.mhpx);
proc sql;
create table MHRNdenom_age_sex as
select &_sitecode as site, &year as Year, enrltenplusmonths, drugcov, insurancetype, gender, agegroup, youth_agegroup, count(mrn) as number_of_members
from worklib.enroll&year
group by enrltenplusmonths, drugcov, insurancetype, gender, agegroup, youth_agegroup
;
create table MHRNdenom_census as
select &_sitecode as site, &year as Year, enrltenplusmonths, drugcov, insurancetype, lowerincome, lowereduc, count(mrn) as number_of_members
from worklib.enroll&year
group by enrltenplusmonths, drugcov, insurancetype, lowerincome, lowereduc
;
create table MHRNdenom_raceeth_new as
select &_sitecode as site, &year as Year, enrltenplusmonths, drugcov, insurancetype, race, hisp, raceeth, count(mrn) as number_of_members
from worklib.enroll&year
group by enrltenplusmonths, drugcov, insurancetype, race, hisp, raceeth
;
quit;
data onedenomfile;
set MHRNdenom_age_sex MHRNdenom_census MHRNdenom_raceeth_new;
run;
proc print data=onedenomfile;
run;
%MaskSmallCells(onedenomfile, outlib.MHRNdenominator&year._&_sitecode, number_of_members, number_of_members);
*set up denominator;
data denom_cdcov;
set worklib.enroll&year ;
if drugcov = 'Y' and EnrlTenPlusMonths ='Y';
DenominatorType = 'Continuous and Drug Coverage';
run;
%MHRXRates_byYear(year=&year, rxlist=drug_list, denomds=denom_cdcov,
stratifyvar1=None, stratifyvar2=None, outds=MHRx_Rates);
%MHRXRates_byYear(year = &year, rxlist = drug_list, denomds = denom_cdcov,
stratifyVar1=AgeGroup, stratifyvar2=None, outds = MHRx_Rates_byAge) ;
%MHRXRates_byYear(year = &year, rxlist = drug_list, denomds = denom_cdcov,
stratifyVar1=Youth_AgeGroup, stratifyvar2=None, outds = MHRx_Rates_byYage) ;
%MHRXRates_byYear(year = &year, rxlist = drug_list, denomds = denom_cdcov,
stratifyVar1=Gender, stratifyvar2=None, outds = MHRx_Rates_bySex) ;
%MHRXRates_byYear(year = &year, rxlist = drug_list, denomds = denom_cdcov,
stratifyVar1=InsuranceType, stratifyvar2=None, outds = MHRx_Rates_byIns) ;
%MHRXRates_byYear(year = &year, rxlist = drug_list, denomds = denom_cdcov,
stratifyVar1=LowerIncome, stratifyvar2=None, outds = MHRx_Rates_byInc) ;
%MHRXRates_byYear(year = &year, rxlist = drug_list, denomds = denom_cdcov,
stratifyVar1=LowerEduc, stratifyvar2=None, outds = MHRx_Rates_byEdu) ;
%MHRXRates_byYear(year = &year, rxlist = drug_list, denomds = denom_cdcov,
stratifyVar1=Race, stratifyvar2=None, outds = MHRx_Rates_byrace) ;
%MHRXRates_byYear(year = &year, rxlist = drug_list, denomds = denom_cdcov,
stratifyVar1=Hisp, stratifyvar2=None, outds = MHRx_Rates_byEth) ;
%MHRXRates_byYear(year = &year, rxlist = drug_list, denomds = denom_cdcov,
stratifyVar1=RaceEth, stratifyvar2=None, outds = MHRx_Rates_byraceEth) ;
%MHRXRates_byYear(year = &year, rxlist = drug_list, denomds = denom_cdcov,
stratifyVar1=AgeGroup, stratifyvar2=Gender, outds = MHRx_Rates_byAgeSex) ;
%MHRXRates_byYear(year = &year, rxlist = drug_list, denomds = denom_cdcov,
stratifyVar1=Youth_AgeGroup, stratifyvar2=Gender, outds = MHRx_Rates_byYageSex) ;
%Compile_oneway (rx);
proc sql;
create table dxfile as
select d.*, e.AgeGroup, e.Youth_agegroup, e.Gender,
e.InsuranceType, e.LowerIncome, e.LowerEduc, e.Race, e.Hisp, e.RaceEth
from worklib.mhdx&year d inner join denom_cdcov e on d.mrn = e.mrn
;
quit;
%RxRatesInDxPop (dxfile, worklib.MHRX&year, drug_list, denom_cdcov, RXes_in_DXPop, None, None);
%RxRatesInDxPop (dxfile, worklib.MHRX&year, drug_list, denom_cdcov, RXes_in_DXPopAge, AgeGroup, None);
%RxRatesInDxPop (dxfile, worklib.MHRX&year, drug_list, denom_cdcov, RXes_in_DXPopYage, Youth_AgeGroup, None);
%RxRatesInDxPop (dxfile, worklib.MHRX&year, drug_list, denom_cdcov, RXes_in_DXPopSex, Gender, None);
%RxRatesInDxPop (dxfile, worklib.MHRX&year, drug_list, denom_cdcov, RXes_in_DXPopIns, InsuranceType, None);
%RxRatesInDxPop (dxfile, worklib.MHRX&year, drug_list, denom_cdcov, RXes_in_DXPopInc, LowerIncome, None);
%RxRatesInDxPop (dxfile, worklib.MHRX&year, drug_list, denom_cdcov, RXes_in_DXPopEdu, LowerEduc, None);
%RxRatesInDxPop (dxfile, worklib.MHRX&year, drug_list, denom_cdcov, RXes_in_DXPopRace, Race, None);
%RxRatesInDxPop (dxfile, worklib.MHRX&year, drug_list, denom_cdcov, RXes_in_DXPopEth, Hisp, None);
%RxRatesInDxPop (dxfile, worklib.MHRX&year, drug_list, denom_cdcov, RXes_in_DXPopRaceEth, RaceEth, None);
%RxRatesInDxPop (dxfile, worklib.MHRX&year, drug_list, denom_cdcov, RXes_in_DXPopAgeSex, AgeGroup, Gender);
%RxRatesInDxPop (dxfile, worklib.MHRX&year, drug_list, denom_cdcov, RXes_in_DXPopYAgeSex, Youth_AgeGroup, Gender);
%Compile_twoway (rx);*combine 2 rx rates files;
proc sql;
create table mhrn_RxRates as
select site, year, active_ingred, rx_category, stratifyvar1, Stratvarvalue1, stratifyvar2, Stratvarvalue2,
number_exposed as number_of_members, denominator, DenominatorType, RxRate
from rxrates_oneway
UNION
select site, year, '' as active_ingred, rx_category, stratifyvar1, Stratvarvalue1, stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, DenominatorType, RxRate
from RXinDXpop
;
quit;
%MaskSmallCells(mhrn_RxRates, mhrn_RxRates1, Number_of_members, RxRate);
%MaskSmallCells(mhrn_RxRates1, outlib.MHRN_RxRates&year._&_sitecode, denominator, RxRate);
%MHDxRates_byyear(year=&year, dxfile=dxfile, denomds=denom_cdcov, stratifyvar1=None, stratifyvar2=None, outds=MHDXRates);
%MHDxRates_byyear(year=&year, dxfile=dxfile, denomds=denom_cdcov, stratifyvar1=AgeGroup, stratifyvar2=None, outds=MHDXRates_byAge);
%MHDxRates_byyear(year=&year, dxfile=dxfile, denomds=denom_cdcov, stratifyvar1=Youth_AgeGroup, stratifyvar2=None, outds=MHDXRates_byYage);
%MHDxRates_byyear(year=&year, dxfile=dxfile, denomds=denom_cdcov, stratifyvar1=Gender, stratifyvar2=None, outds=MHDXRates_bySex);
%MHDxRates_byyear(year=&year, dxfile=dxfile, denomds=denom_cdcov, stratifyvar1=InsuranceType, stratifyvar2=None, outds=MHDXRates_byIns);
%MHDxRates_byyear(year=&year, dxfile=dxfile, denomds=denom_cdcov, stratifyvar1=LowerIncome, stratifyvar2=None, outds=MHDXRates_byInc);
%MHDxRates_byyear(year=&year, dxfile=dxfile, denomds=denom_cdcov, stratifyvar1=LowerEduc, stratifyvar2=None, outds=MHDXRates_byEdu);
%MHDxRates_byyear(year=&year, dxfile=dxfile, denomds=denom_cdcov, stratifyvar1=Race, stratifyvar2=None, outds=MHDXRates_byRace);
%MHDxRates_byyear(year=&year, dxfile=dxfile, denomds=denom_cdcov, stratifyvar1=Hisp, stratifyvar2=None, outds=MHDXRates_byEth);
%MHDxRates_byyear(year=&year, dxfile=dxfile, denomds=denom_cdcov, stratifyvar1=RaceEth, stratifyvar2=None, outds=MHDXRates_byRaceEth);
%MHDxRates_byyear(year=&year, dxfile=dxfile, denomds=denom_cdcov, stratifyvar1=AgeGroup, stratifyvar2=Gender, outds=MHDXRates_byAgeSex);
%MHDxRates_byyear(year=&year, dxfile=dxfile, denomds=denom_cdcov, stratifyvar1=Youth_AgeGroup, stratifyvar2=Gender, outds=MHDXRates_byYageSex);
%Compile_oneway (dx);
*****DX in RX pop;
proc sql;
create table rxfile as
select r.*, e.AgeGroup, e.Youth_agegroup, e.Gender,
e.InsuranceType, e.LowerIncome, e.LowerEduc, e.Race, e.Hisp, e.RaceEth
from worklib.mhrx&year r inner join denom_cdcov e on r.mrn = e.mrn
;
quit;
%DxRatesInRxPop(dxfile=dxfile, rxfile=rxfile, rxlist=drug_list,
denomds=denom_cdcov, finalfile=DXes_in_RxPop, stratifyvar1=None, stratifyvar2=None);
%DxRatesInRxPop(dxfile, rxfile, drug_list, denom_cdcov, DXes_in_RxPopAge, AgeGroup, None);
%DxRatesInRxPop(dxfile, rxfile, drug_list, denom_cdcov, DXes_in_RxPopYage, Youth_AgeGroup, None);
%DxRatesInRxPop(dxfile, rxfile, drug_list, denom_cdcov, DXes_in_RxPopSex, Gender, None);
%DxRatesInRxPop(dxfile, rxfile, drug_list, denom_cdcov, DXes_in_RxPopIns, InsuranceType, None);
%DxRatesInRxPop(dxfile, rxfile, drug_list, denom_cdcov, DXes_in_RxPopInc, LowerIncome, None);
%DxRatesInRxPop(dxfile, rxfile, drug_list, denom_cdcov, DXes_in_RxPopEdu, LowerEduc, None);
%DxRatesInRxPop(dxfile, rxfile, drug_list, denom_cdcov, DXes_in_RxPopRace, Race, None);
%DxRatesInRxPop(dxfile, rxfile, drug_list, denom_cdcov, DXes_in_RxPopEth, Hisp, None);
%DxRatesInRxPop(dxfile, rxfile, drug_list, denom_cdcov, DXes_in_RxPopRaceEth, RaceEth, None);
%DxRatesInRxPop(dxfile, rxfile, drug_list, denom_cdcov, DXes_in_RxPopAgeSex, AgeGroup, Gender);
%DxRatesInRxPop(dxfile, rxfile, drug_list, denom_cdcov, DXes_in_RxPopYAgeSex, Youth_AgeGroup, Gender);
%Compile_twoway (dx);
proc sql;
create table MHRN_DXrates as
select site, year, dx_class, stratifyvar1, Stratvarvalue1, stratifyvar2, Stratvarvalue2,
number_of_members, dxrate, denominator, denominatortype
from MHDX_Rates_oneway
UNION
select site, year, dx_class, stratifyvar1, Stratvarvalue1, stratifyvar2, Stratvarvalue2,
number_of_members, pctRxPopWithDx, Rx_Denominator, 'Rx for: ' || drugname as DenominatorType
from DXinRXpop
;
quit;
%MaskSmallCells(mhrn_DXRates, mhrn_DXRates1, Number_of_members, DXRate);
%MaskSmallCells(mhrn_DXRates1, outlib.MHRN_DXRates&year._&_sitecode, denominator, DXRate);
proc sql;
create table pxfile as
select p.*, e.AgeGroup, e.Youth_agegroup, e.Gender,
e.InsuranceType, e.LowerIncome, e.LowerEduc, e.Race, e.Hisp, e.RaceEth
from worklib.mhpx&year p inner join denom_cdcov e on p.mrn = e.mrn
where p.px NOT in ('90804', '90805', '90810', '90811' '90862')
;
quit;
%MHpx_Rates_byyear (year=&year, denomds=denom_cdcov, ptds=pxfile, stratifyvar1=None, stratifyvar2=None, outds=MHPx_Rates);
%MHpx_Rates_byyear (&year, denom_cdcov, pxfile, AgeGroup, None, MHPx_Rates_byAge);
%MHpx_Rates_byyear (&year, denom_cdcov, pxfile, Youth_AgeGroup, None, MHPx_Rates_byYage);
%MHpx_Rates_byyear (&year, denom_cdcov, pxfile, Gender, None, MHPx_Rates_bySex);
%MHpx_Rates_byyear (&year, denom_cdcov, pxfile, InsuranceType, None, MHPx_Rates_byIns);
%MHpx_Rates_byyear (&year, denom_cdcov, pxfile, LowerIncome, None, MHPx_Rates_byInc);
%MHpx_Rates_byyear (&year, denom_cdcov, pxfile, LowerEduc, None, MHPx_Rates_byEdu);
%MHpx_Rates_byyear (&year, denom_cdcov, pxfile, Race, None, MHPx_Rates_byrace);
%MHpx_Rates_byyear (&year, denom_cdcov, pxfile, Hisp, None, MHPx_Rates_byEth);
%MHpx_Rates_byyear (&year, denom_cdcov, pxfile, RaceEth, None, MHPx_Rates_byraceEth);
%MHpx_Rates_byyear (&year, denom_cdcov, pxfile, AgeGroup, Gender, MHPx_Rates_byAgeSex);
%MHpx_Rates_byyear (&year, denom_cdcov, pxfile, Youth_AgeGroup, Gender, MHPx_Rates_byYageSex);
%compile_oneway (px);
%PTRatesinDxPop(dxfile=dxfile, ptfile=pxfile, denomds=denom_cdcov, finalfile=MHPT_rates, stratifyvar1=None, stratifyvar2=None);
%PTRatesinDxPop(dxfile, pxfile, denom_cdcov, MHPT_rate_byAge, stratifyvar1=AgeGroup, stratifyvar2=None);
%PTRatesinDxPop(dxfile, pxfile, denom_cdcov, MHPT_rate_byYAge, stratifyvar1=Youth_AgeGroup, stratifyvar2=None);
%PTRatesinDxPop(dxfile, pxfile, denom_cdcov, MHPT_rate_bySex, stratifyvar1=Gender, stratifyvar2=None);
%PTRatesinDxPop(dxfile, pxfile, denom_cdcov, MHPT_rate_byIns, stratifyvar1=Insurancetype, stratifyvar2=None);
%PTRatesinDxPop(dxfile, pxfile, denom_cdcov, MHPT_rate_byInc, stratifyvar1=LowerIncome, stratifyvar2=None);
%PTRatesinDxPop(dxfile, pxfile, denom_cdcov, MHPT_rate_byEdu, stratifyvar1=LowerEduc, stratifyvar2=None);
%PTRatesinDxPop(dxfile, pxfile, denom_cdcov, MHPT_rate_byrace, stratifyvar1=Race, stratifyvar2=None);
%PTRatesinDxPop(dxfile, pxfile, denom_cdcov, MHPT_rate_byEth, stratifyvar1=Hisp, stratifyvar2=None);
%PTRatesinDxPop(dxfile, pxfile, denom_cdcov, MHPT_rate_byraceEth, stratifyvar1=RaceEth, stratifyvar2=None);
%PTRatesinDxPop(dxfile, pxfile, denom_cdcov, MHPT_rate_byAgeSex, stratifyvar1=AgeGroup, stratifyvar2=Gender);
%PTRatesinDxPop(dxfile, pxfile, denom_cdcov, MHPT_rate_byYageSex, stratifyvar1=Youth_AgeGroup, stratifyvar2=Gender);
%Compile_twoway (px);
*combine 2 PT rates files;
proc sql;
create table mhrn_PTRates as
select site, year, active_ingred, tx_category, stratifyvar1, Stratvarvalue1, stratifyvar2, Stratvarvalue2,
number_exposed as number_of_members, denominator, DenominatorType, PTRate
from PXrates_oneway
UNION
select site, year, active_ingred, tx_category, stratifyvar1, Stratvarvalue1, stratifyvar2, Stratvarvalue2,
number_of_members, dx_Denominator, DenominatorType, pctDxPopWithPT
from PTinDXpop
;
quit;
%MaskSmallCells(mhrn_PTRates, mhrn_PTRates1, Number_of_members, PTRate);
%MaskSmallCells(mhrn_PTRates1, outlib.MHRN_PTRates&year._&_sitecode, denominator, PTRate);
/*********************************************
code end
*********************************************/