Preparing the data

Section Progress

0% Complete

Identifying data dimensions

In our case study of the association of high potency statins and incident diabetes, we will use 5 dimensions:

hospital diagnoses
hospital procedures
physician visits diagnoses
physician visits tariff codes (procedures)
drug dispensations

Setting up dimension data sets

Define the look-back period as 1 year prior to index date. For each of the dimensions, only keep codes occurring within 1 year prior to an individual’s index date.
Each dimension should have two columns:
- the ID (e.g., PHIN) and
- the code (e.g., ICD codes for diagnoses, ATC for dispensations).
Each occurrence of a diagnosis, procedure or drug dispensation should be listed on a separate row (i.e., if an individual has the same diagnosis on 3 different dates, then each will be listed on a separate row.
If the data set includes more than one coding system (e.g. ICD-9 and ICD-10 for diagnoses), you have to convert one into the other. It would be easier in this example to convert ICD-10 to ICD-9 using available mapping resources.

Guided exercise 1. Setting up the dimensions

Set up the five dimensions according to the four criteria described above. First try doing this on your own. Reveal the example code below if you need a hint or want to check your work.

Example code: Setting up the dimensions (click to reveal)

option nocenter;
libname sim 'C:\Users\WKSTN3\Desktop\TARITA\ANALYSIS\DATA';
libname tm 'C:\Users\WKSTN3\Desktop\TARITA\ANALYSIS\TABLES';

proc print data=tm.base_cohort (obs=10);
run;

*******************************************************************************
**********************Prepare datasets for generating hdPS*********************
******************************************************************************;

*Get all diagnoses 365 days prior to cohort entry date;
proc sql;
create table temp_hdps_dx as
select a.id, a.cohort_entry_date, b.dx, b.date
	from tm.end_fwup_caco a,
		 sim.sd_table_diag b
where a.id=b.id and
	  a.cohort_entry_date>b.date and
	  a.cohort_entry_date-b.date<=365
;
run;quit;

proc print data=temp_hdps_dx (obs=10);
run;
/* 34,346 */


*Flag cohort-defining event;
*Anyone with a cohort-defining event on same day as cohort entry date has already been deleted;
proc sql;
create table temp_CDE_DX as
select a.id, a.dx as cde_dx, a.date as cde_dt, b.dx, b.date
	from temp_hdps_dx a,
		 tm.base_cohort b
where a.id=b.id and
	  a.date=b.date and
	  a.dx=b.dx
;
run;quit;

proc print data=temp_CDE_DX (obs=10);
run;
/* 3,277 */

*Delete cohort-defining event;
data tm.HDPS_DX (keep=id dx);
merge temp_hdps_dx (in=a) temp_CDE_DX (in=b);
by id dx date;
if a and not b then output;
run;

proc print data=tm.HDPS_DX (obs=10);
run;
/* 31,069 */

****************************************************************************;
*Convert ICD-10 to ICD-9 dx;
*This is a simplified form of converting ICD-10 to ICD-9 as the simulated data does not differentiate between ICD-9 and ICD-10 and is just labeled as DX in the data
****************************************************************************;
proc sql;
create table temp_icd10 as
select a.*, b.icd10cm, b.icd9cm
	from tm.HDPS_DX a
	left join tm.icd10toicd9 b
on trim(a.dx)=trim(b.icd10cm)
order by dx_num;
run;quit;

proc print data=temp_icd10 (obs=15);
run;

*Convert ICD10 dx to ICD9;
data temp_icd9;
set temp_icd10;
if trim(dx)=trim(icd10cm) then dx=trim(icd9cm);
run;

proc print data=temp_icd9 (obs=15);
run;
/* 31,078 (Some ICD-10 codes were converted more than once as there are more than one ICD-9 conversions available in the conversion file.)*/

*Get rid of multiples, just keep first conversion of ICD10 code;
data tm.HDPS_DX9 (keep=id dx);
set temp_icd9;
by dx_num;
if first.dx_num then output;
run;

proc print data=tm.HDPS_DX9 (obs=11);
run;
/* 31,069 */


*Get all prescriptions 365 days prior to cohort entry date;
proc sql;
create table temp_hdps_rx as
select a.id, a.cohort_entry_date, b.atc, b.date
	from tm.end_fwup_caco a,
		 sim.sd_table_drug b
where a.id=b.id and
	  a.cohort_entry_date>b.date and
	  a.cohort_entry_date-b.date<=365
;
run;quit;

proc print data=temp_hdps_rx (obs=10);
run;	  
/* 120,155 */

data tm.HDPS_RX (drop=cohort_entry_date date);
set temp_hdps_rx;
run;

Setting up the cohort data set

The cohort data set should include the ID, exposure, outcome, demographic, and predefined variables that will be included in HDPS estimation. You can either remove the other variables or use the vars_ignore option in the macro call below.

Guided exercise 2. Setting up the cohort data set

Set up the cohort data set according to the criteria described above. First try doing this on your own. Reveal the example code below if you need a hint or want to check your work.

Example code: Setting up the cohort data set (click to reveal)

option nocenter;
libname sim 'C:\Users\WKSTN3\Desktop\TARITA\ANALYSIS\DATA';
libname tm 'C:\Users\WKSTN3\Desktop\TARITA\ANALYSIS\TABLES';


**Over 4 distinct Rx's;
proc sql;
create table tm.hdps_rx_count as
select  id,
		CASE WHEN count(distinct atc)>4
				THEN 1 ELSE 0
		END as OVER4_RX
	from tm.HDPS_RX
group by id;
run;quit;

proc print data=tm.hdps_rx_count (obs=10);
run;
/* 3,457 */

**Over 4 Dx's;
proc sql;
create table tm.hdps_dx_count as
select  id,
		CASE WHEN count(dx)>4
				THEN 1 ELSE 0
		END as OVER4_DX
	from tm.HDPS_DX
group by id;
run;quit;

proc print data=tm.hdps_dx_count (obs=10);
run;
/* 3,545 */

*Flag prior hypertension, hypercholesterolemia, peripheral vascular disease, CHF;
proc sql;
create table hdps_dx_char as
select a.id, b.dx,
	   case when substr(b.dx,1,3) in ('401','402','403','404','405','I10','I11','I12','I13','I14','I15') then 1
	   	else 0
	   end as hi_blood,
	   case when substr(b.dx,1,3) in ('272','E78') then 1
	   	else 0
	   end as hi_chol,
	   case when substr(b.dx,1,3) in ('443','I73') then 1
	   	else 0
	   end as PVD,
	   case when substr(b.dx,1,3) in ('428','I50') then 1
	   	else 0
	   end as CHF

	from tm.end_fwup_caco a,
		 sim.sd_table_diag b

where a.id=b.id and
	  a.cohort_entry_date>b.date and
	  a.cohort_entry_date-b.date<=365 and
	  (substr(b.dx,1,3) in ('401','402','403','404','405','I10','I11','I12','I13','I14','I15',
						   '272','E78',
						   '443','I73',
						   '428','I50'))
;
run;quit;

proc print data=hdps_dx_char (obs=10);
run;
/* 2,175 */

proc sql;
create table tm.hdps_dx_char as
select distinct id, sum(hi_blood) as hi_blood, sum(hi_chol) as hi_chol, sum(PVD) as PVD, sum(CHF) as CHF
	from hdps_dx_char
group by id
;
run;quit;

proc print data=tm.hdps_dx_char (obs=10);
run;
/* 1,252 */

*Flag prior Loop diuretics;
proc sql;
create table hdps_loop_char as
select distinct id, atc
	from tm.hdps_rx
where substr(atc,1,5) in ('C03CA')
;
run;quit;

proc print data=hdps_loop_char (obs=10);
run;
/* 622 */

data tm.hdps_loop_char (drop=atc);
set hdps_loop_char;
format loop 8.;
loop=1;
run;

proc print data=tm.hdps_loop_char (obs=10);
run;

*Create binary variables for hdPS demographics, add exposure variable;
proc sql;
create table tm.hdps_demo as
select a.id, a.caco as outcome, b.exposure, 
	   case when a.male=0 then 1 else 0
	   end as female,

	   case when a.age<=44 then 1 else 0
	   end as age_4044,
	   case when 45<=a.age<=49 then 1 else 0
	   end as age_4549,
	   case when 50<=a.age<=54 then 1 else 0
	   end as age_5054,
	   case when 55<=a.age<=59 then 1 else 0
	   end as age_5559,
	   case when 60<=a.age<=64 then 1 else 0
	   end as age_6064,
	   case when 65<=a.age<=69 then 1 else 0
	   end as age_6569,
	   case when 70<=a.age<=74 then 1 else 0
	   end as age_7074,
	   case when 75<=a.age<=79 then 1 else 0
	   end as age_7579,
	   case when 80<=a.age<=84 then 1 else 0
	   end as age_8084,
	   case when a.age>=85 then 1 else 0
	   end as age_85up,

	   case when year(a.cohort_entry_date)=2009 then 1 else 0
	   end as cohyr_2009,
	   case when year(a.cohort_entry_date)=2010 then 1 else 0
	   end as cohyr_2010,
	   case when year(a.cohort_entry_date)=2011 then 1 else 0
	   end as cohyr_2011,
	   case when year(a.cohort_entry_date)=2012 then 1 else 0
	   end as cohyr_2012,
	   case when year(a.cohort_entry_date)=2013 then 1 else 0
	   end as cohyr_2013,
	   case when year(a.cohort_entry_date)=2014 then 1 else 0
	   end as cohyr_2014,
	   case when year(a.cohort_entry_date)=2015 then 1 else 0
	   end as cohyr_2015,
	   case when year(a.cohort_entry_date)=2016 then 1 else 0
	   end as cohyr_2016,
	   case when year(a.cohort_entry_date)=2017 then 1 else 0
	   end as cohyr_2017

	from tm.end_fwup_caco a,
	 	 tm.HDLD_exposure b

where a.id=b.id
;
run;quit;

proc print data=tm.hdps_demo (obs=10);
run;
/* 3,587 */

**********************Combine datasets for HDPS*************************************;
data tm.HDPS_COH;
merge tm.hdps_demo tm.hdps_rx_count tm.hdps_dx_count tm.hdps_dx_char tm.hdps_loop_char;
by id;
if over4_rx=. then over4_rx=0;
if over4_dx=. then over4_dx=0;
if hi_blood>1 then hi_blood=1;
if hi_chol>1 then hi_chol=1;
if pvd>1 then pvd=1;
if chf>1 then chf=1;
if loop>1 then loop=1;
if hi_blood=. then hi_blood=0;
if hi_chol=. then hi_chol=0;
if pvd=. then pvd=0;
if chf=. then chf=0;
if loop=. then loop=0;
run;
/* 3,587 */

proc print data=tm.hdps_coh (obs=10);
run;

Propensity Scores

Participants 174