/*********************************************************************************
This Cohort-Building Program includes two sub-programs:
Program I is to identify patients with exposure/control drug, index date, a continuous duration of exposure/control drug use,
follow up time, age and sex at index date, and only include patients that are met a user-defined continuous enrollment 
requirement;
The program II defines outcome, outcome date, and creates as many as desired covariates;

Any suggestion/question: feel free to contact Thuy Thai at email: thuythai@ufl.edu.

*********************************************************************************/


*************************************************************************************************************************
*******************Program I - Define exposure and control groups **********************
*************************************************************************************************************************;

/*Steps:
*Program Ia -Create SAS library and some let statements to modify easily for the main/sensitivy analyses or similar studies;

*Program Ib - Pulling patients with exposure and drug use with the criteria that are defined in Program Ia;
*/

*************************************************************************************************************************
*Program Ia -Create SAS library and some let statements to modify easily for the main/sensitivy analyses or similar studies;


/*User_check-point_1a*/

/* Truven dataset location */
libname truven "raw_data_location";

/*Redbook location */
libname rb  "Redbook_location";

/*location for saving data */
libname d "saving_data_location";

/*Specify Redbook dataset name */
%let redbook2015=name_Redbook_yr_2015 ;
%let redbook2016=name_Redbook_yr_2016 ;
%let redbook2017=name_Redbook_yr_2017 ;
%let redbook2018=name_Redbook_yr_2018 ;
%let redbook2019=name_Redbook_yr_2019 ;


**define exposure and control drugs by NDC and HCPCS codes;
%let exporx=%str(find(gennme,"your_generic_name",'i')); /*an example of one drug as an exposure */
%let contrx=%str(find(gennme,"your_generic_name1",'i')); 
/*if you have >=2 drugs in exposure/control group, you can expand the statement as an follow example:

%let contrx=%str(find(gennme,"your_generic_name1",'i') or find(gennme,"your_generic_name2",'i')); 
*/


%let expopx=%str('HCPCS_code'); /*HCPCs codes of exposure drug*/
%let contpx=%str('HCPCS_code'); /*HCPCs codes of control drug*/
/*in case, your drug does not have a HCPCS code, you can use an invalid code e.g. 'AAAAA' to keep the code run) */


%let maxdaysupp=enter_number; /*upper limit for a day supply is considered as acceptable */
%let gap=enter_number; /*number of day gap allowed to create a continuous duration of drug use */

**look back and follow up time;
%let lookbackce=index-enter_number;  /*requirement of continous enrollment before index, usually this is time for baseline covariates */
%let lookfowardce=index+enter_number; /*requirement of number of day for continous enrollment after index */
%let fu=min(fu_paramater1,fu_paramater2,fu_paramater3,fu_paramater4); /*follow up time from index date  */
/* The user replaces fu_parameter with at least of the 4 parameters (rxend, dtend, deathdt, index+number).
 Notation: earliest date of end of drug use (rxend), enrollment end (dtend), inpatient death date (deathdt), 
 or number days after index (index+number) */

/*User_check_point_ends*/

*************************************************************************************************************************
*Program Ib -Pulling patients with exposure and drug use with the criteria that are defined in Program Ia;



*Create a Redbook for the entire study period which allows the change of NDC over years;
/* Identifying start year and end year when an NDC is valid */
data red2015;
set rb.&redbook2015;
st_yr=2005;
ed_yr=2015;
length st_yr ed_yr 3.;
run;

data red2016;
set rb.&redbook2016;
st_yr=2016;
ed_yr=2016;
length st_yr ed_yr 3.;
run;

data red2017;
set rb.&redbook2017;
st_yr=2017;
ed_yr=2017;
length st_yr ed_yr 3.;
run;

data red2018;
set rb.&redbook2018;
st_yr=2018;
ed_yr=2018;
length st_yr ed_yr 3.;
run;

data red2019;
set rb.&redbook2019;
st_yr=2019;
ed_yr=2019;
length st_yr ed_yr 3.;
run;

data redbook;
set red2015 red2016 red2017 red2018 red2019;
run; /*combine all Redbooks together */


/*create a dataset only including NDC for exposure and control drug */
data rb_expocont;
set redbook;

if &exporx then exporx=1;
if &contrx then contrx=1;
if exporx eq . and contrx eq . then delete;
run;

/*start to pull Rx claim  */
%macro rxyr(var);

proc sql;
create table rx&var
as select   a.enrolid, a.age, a.dobyr, a.sex, a.ndcnum, a.daysupp, a.svcdate, b.exporx, b.contrx, b.gennme, b.mstfmds, b.roads, b.thrdtds
from truven.cc_rx_&var  a
inner join rb_expocont b
on a.ndcnum=b.ndcnum
where b.st_yr<=year(a.svcdate)<=b.ed_yr;
quit;

%mend;
%rxyr(05_4);
%rxyr(06_3);
%rxyr(07_3);
%rxyr(08_3);
%rxyr(09_3);
%rxyr(10_3);
%rxyr(11_3);
%rxyr(12_2);
%rxyr(13_2);
%rxyr(14_1);
%rxyr(15_1);
%rxyr(16_1);
%rxyr(17_1);
%rxyr(18_1);
%rxyr(19_1);


data rx0 (keep=enrolid age dobyr svcdate sex daysupp exporx contrx rx_date ndcnum gennme);
set
rx05_4
rx06_3 
rx07_3 
rx08_3 
rx09_3 
rx10_3 
rx11_3 
rx12_2 
rx13_2 
rx14_1 
rx15_1 
rx16_1 
rx17_1
rx18_1
rx19_1;

rx_date=svcdate;
format rx_date mmddyy10.;
length daysupp exporx contrx 3. rx_date 4.; 
where enrolid ne . ;
run;

**some basic cleaning for rx claims;


proc sort data=rx0 out=rx1;
by enrolid ndcnum svcdate;
run;

data rx2;
set rx1;
rx_id=_n_;
absday=abs(daysupp);
if daysupp<0 then neg_rx=1;
run;

data rxneg;
set rx2;
where neg_rx=1;
run;

data rx_noneg;
set rx2;
where neg_rx ne 1;
run;
proc sql;
create table clean_rx 
as select a.enrolid,  a.ndcnum, a.daysupp, a.gennme, a.svcdate,
 b.daysupp as daysupp2, b.gennme as gennme2, b.svcdate as svcdate2, b.rx_id
from rxneg a 
inner join rx_noneg b 
on a.enrolid=b.enrolid and a.ndcnum=b.ndcnum and a.absday=b.absday  ;
quit;

data clean_rx2 (keep=rx_id );
set clean_rx;
if abs(svcdate-svcdate2)>7 then delete;
run;
data clean_rx3 (keep=rx_id);
set rxneg;
run;
data clean_rx4;
set clean_rx2 clean_rx3;
exclude=1;
run;

proc sort data=clean_rx4 nodups;
by rx_id;
run;
proc sql;
create table rx3 
as select a.*, b.exclude
from rx2 a 
left join clean_rx4 b 
on a.rx_id=b.rx_id;
quit;

data rx4 (drop=rx_id absday neg_rx exclude);
set rx3;
/*some basic cleaning */

if exclude=1 or daysupp=0 or daysupp>=&maxdaysupp then delete;


run;


proc sort data=rx4 out=rx nodups;
by enrolid rx_date;
run;



/*start to pull claim with HCPCS codes  */


%macro ipotyr (var);

data ot&var (keep=enrolid age dobyr svcdate sex rx_date daysupp exporx contrx);
set  truven.cc_ot_svc_&var;
if proc1 in (&expopx) then exporx=1;
if proc1 in (&contpx) then contrx=1;
if exporx=. and contrx=. then delete;
daysupp=0;
rx_date=svcdate;
format rx_date mmddyy10.;
length daysupp exporx contrx 3. rx_date 4.; 
where enrolid ne . ;
run;

data ipsvc&var (keep=enrolid age dobyr svcdate rx_date sex daysupp exporx contrx);
set  truven.cc_ip_svc_&var;
if pproc in (&expopx) or proc1 in (&expopx) then exporx=1;
if pproc in (&contpx) or proc1 in (&contpx) then contrx=1;
if exporx=. and contrx=. then delete;
daysupp=0;
rx_date=svcdate;
format rx_date mmddyy10.;
length daysupp exporx contrx 3. rx_date 4.; 
where enrolid ne . ;
run;

data adm&var (keep=enrolid age dobyr svcdate rx_date sex daysupp exporx contrx);
set  truven.cc_ip_adm_&var;
array px {*} pproc proc1-proc15;

do i=1 to dim(px);
if px(i) in (&expopx) then do;
exporx=1;
daysupp=0;
rx_date=admdate;
svcdate=admdate;
output;
end;
end;

do j=1 to dim(px);
if px(j) in (&contpx) then do;
contrx=1;
daysupp=0;
rx_date=admdate;
svcdate=admdate;
output;
end;
end;

format rx_date svcdate mmddyy10.;
length daysupp exporx contrx 3. rx_date svcdate 4.; 
where enrolid ne . ;
run;


%mend;

%ipotyr (05_4);
%ipotyr (06_3);
%ipotyr (07_3);
%ipotyr (08_3);
%ipotyr (09_3);
%ipotyr (10_3);
%ipotyr (11_3);
%ipotyr (12_2);
%ipotyr (13_2);
%ipotyr (14_1);
%ipotyr (15_1);
%ipotyr (16_1);
%ipotyr (17_1);
%ipotyr (18_1);
%ipotyr (19_1);


/*combine all rx and HCPCS claim together */
data rx_all;
set
ot05_4
ot06_3 
ot07_3 
ot08_3 
ot09_3 
ot10_3 
ot11_3 
ot12_2 
ot13_2 
ot14_1 
ot15_1 
ot16_1 
ot17_1
ot18_1
ot19_1

ipsvc05_4
ipsvc06_3 
ipsvc07_3 
ipsvc08_3 
ipsvc09_3 
ipsvc10_3 
ipsvc11_3 
ipsvc12_2 
ipsvc13_2 
ipsvc14_1 
ipsvc15_1 
ipsvc16_1 
ipsvc17_1
ipsvc18_1  
ipsvc19_1  

adm05_4
adm06_3 
adm07_3 
adm08_3 
adm09_3 
adm10_3 
adm11_3 
adm12_2 
adm13_2 
adm14_1 
adm15_1 
adm16_1 
adm17_1
adm18_1  
adm19_1  

rx
;

run;

/*create a continous duration of exposure and control drug */

**exposure group first;

%macro dur(var);

data &var.1;
set rx_all;
where &var=1;
run;

proc sort data=&var.1 ;
by enrolid  rx_date  ;
run;

data &var.2;
set &var.1;
by enrolid;
retain episode stdt eddt .;

if first.enrolid then do;
	episode=1;
	stdt=rx_date;
	eddt=rx_date+daysupp;
end;
else if rx_date<=eddt then do;
		stdt=stdt;
		episode=episode+0;
		eddt=eddt+daysupp;
	end;
else if eddt<rx_date<=eddt+&gap then do;
		stdt=stdt;
		episode=episode+0;
		eddt=rx_date+daysupp;
	end;
else do;
		stdt=svcdate;
		episode=episode+1;
		eddt=rx_date+daysupp;

	end;
format stdt eddt  mmddyy10.;
length episode 3. stdt eddt 4.;
run;

proc sql;
create table &var.3
	as select  a.enrolid,a.episode, a.sex,  a.exporx, a.contrx, min(age) as indexage, a.stdt, max(eddt) as ed_dt 
	from &var.2 a 
	group by enrolid , episode;
	quit;

/*only keep the first episode - i.e. not allow cohort reentry*/
data &var.4 (drop =episode);
set &var.3;
where episode=1;
run;

proc sort data=&var.4 nodupkeys;
by enrolid ;
run;

%mend;
%dur(exporx);
%dur(contrx);

/* if a patient used both exposure and control drug, select the first drug and censor at the time of switching */
data rx_all2;
set exporx4 contrx4;
rxid=_n_;
length rxid 8. ed_dt 4.;
format ed_dt mmddyy10.;
run;

proc sort data=rx_all2 out=rx_all3;
by enrolid stdt;
run;

proc sort data=rx_all3 out=rx_all4 nodupkeys;
by enrolid ;
run;


proc sql;
create table rx_all5 
	as select a.*, b.exporx2, b.contrx2, b.stdt2, b.ed_dt2, b.rxid2
	from rx_all4 a 
	left join rx_all2 (rename=(exporx=exporx2 contrx=contrx2 stdt=stdt2 ed_dt=ed_dt2 rxid=rxid2)) b
	on a.enrolid=b.enrolid and a.rxid ne b.rxid2 and a.stdt<=b.stdt2<=ed_dt;
quit;


data rx_all6;
set rx_all5;
if exporx=1 then expo=1; 
else if contrx=1 then expo=0;
index=stdt;
rxend=min(ed_dt,stdt2);
length index rxend 4. expo 3.;
format index rxend mmddyy10.;
drop stdt ed_dt rxid exporx2 contrx2 stdt2 ed_dt2 rxid2 exporx contrx;
run; 


/* Apply continous enrollment requirement  */

proc sql;
create table rx_all7 
	as select a.*, b.dtstart, b.dtend 
	from rx_all6 a 
	left join truven.truven_cts_elig_rx_cc_0519 b 
	on a.enrolid=b.enrolid;
	quit;

data rx_all8;
set rx_all7;
where dtstart<=&lookbackce and dtend>=&lookfowardce;
run;

/* find IP death date to finalize end of follow up window */

%macro ipotyr (var);
proc sql;
create table deathadm&var 
	as select a.enrolid, b.disdate, b.dstatus
	from rx_all8 a 
	inner join truven.cc_ip_adm_&var   b 
	on a.enrolid=b.enrolid 
where dstatus='20' or dstatus='40' or dstatus='41' or dstatus='42' ;
quit;

proc sql;
create table deathipsvc&var 
	as select a.enrolid, b.disdate, b.dstatus
	from rx_all8 a 
	inner join truven.cc_ip_svc_&var   b 
	on a.enrolid=b.enrolid 
where dstatus='20' or dstatus='40' or dstatus='41' or dstatus='42' ;
quit;

%mend;

%ipotyr (05_4);
%ipotyr (06_3);
%ipotyr (07_3);
%ipotyr (08_3);
%ipotyr (09_3);
%ipotyr (10_3);
%ipotyr (11_3);
%ipotyr (12_2);
%ipotyr (13_2);
%ipotyr (14_1);
%ipotyr (15_1);
%ipotyr (16_1);
%ipotyr (17_1);
%ipotyr (18_1);
%ipotyr (19_1);

data death;
set 
deathadm05_4
deathadm06_3 
deathadm07_3 
deathadm08_3 
deathadm09_3 
deathadm10_3 
deathadm11_3 
deathadm12_2 
deathadm13_2 
deathadm14_1 
deathadm15_1 
deathadm16_1 
deathadm17_1
deathadm18_1
deathadm19_1

deathipsvc05_4
deathipsvc06_3 
deathipsvc07_3 
deathipsvc08_3 
deathipsvc09_3 
deathipsvc10_3 
deathipsvc11_3 
deathipsvc12_2 
deathipsvc13_2 
deathipsvc14_1 
deathipsvc15_1 
deathipsvc16_1 
deathipsvc17_1
deathipsvc18_1
deathipsvc19_1;

run;

proc sort data=death ;
by enrolid disdate;
run;

proc sort data=death  nodupkeys;
by enrolid ;
run;

proc sql;
create table rx_all9
	as select a.* , b.deathdt
	from rx_all8 a 
	left join death (rename=(disdate=deathdt)) b 
	on a.enrolid=b.enrolid;

quit;


/*finalize expsoure status and follow up time */
data d.exposure;
set rx_all9;
if deathdt ne . and deathdt<=index then delete; /*delete case that died before index */
lookback=&lookbackce ; /*set start of look back period */
fu=&fu; /*set end of follow up time */
format lookback fu mmddyy10.;
length lookback fu 4. indexage 3.;
drop  deathdt;
run;


****************************************************************************************************************************************************
****************************************************************************************************************************************************
*************************************************************** END PROGRAM I **********************************************************************
****************************************************************************************************************************************************
****************************************************************************************************************************************************
***************************************************************************************************************************************************;














/*********************************************************************************
This Cohort-Building Program includes two sub-programs:
Program I is to identify patients with exposure/control drug, index date, a continuous duration of exposure/control drug use,
follow up time, age and sex at index date, and only include patients that are met a user-defined continuous enrollment 
requirement;
The program II defines outcome, outcome date, and creates as many as desired covariates;


FOLLOWING IS PROGRAM II
*********************************************************************************/




*************************************************************************************************************************
*******************Program II - Pull claims and Create inclusion/exclusion/outcome/covariate variables **********************
*************************************************************************************************************************;

/*Steps:
*Program IIa -Create some let statement to define inclusion/exclusion/outcome/covariate that can be easily modified;
*Program IIb -Pull inpatient claims and outpatient claims;
*Program IIc -Create inclusion/exclusion/outcome/covariate variables;
*Program IId -Combine with Exposure dataset to create the final cohort;
*/



*************************************************************************************************************************;
*Program IIa -Create some let statement to define inclusion/exclusion/outcome/covariate that can be easily modified;


/*User_check_point_2a*/


/* Truven dataset location */
libname truven "raw_data_location";


/*location for saving data */
libname d "saving_data_location";


/* of note: 
- d1, d2, d3, etc... variables are defined by diangosis code; 
- p1, p2, p3, etc... variables are defined by procedure/CPT codes
- outcome is Outcome variable;
*/

/*outcome variable */
%let outcomedi9=%str('dx_code'); /*include all ICD9 dx codes to define outcome variable */
%let outcomedi10=%str('dx_code'); /*include all ICD10 dx codes to define outcome variable */   
%let outcomepi9=%str('px_code'); /*include all ICD9 procedure/CPT codes to define outcome variable */
%let outcomepi10=%str('px_code'); /*include all ICD10 procedure/CPT codes to define outcome variable */   


/* 1st variable d1 is defined by diagnosis code */
%let d1i9=%str('dx_code'); /*include all ICD9 dx code to define d1 variable */
%let d1i10=%str('dx_code'); /*include all ICD10 dx code to define d1 variable */   


/* 2nd variable d2 is defined by diagnosis code */
%let d2i9=%str('dx_code'); /*include all ICD9 dx code to define d2 variable */
%let d2i10=%str('dx_code'); /*include all ICD10 dx code to define d2 variable */   

/* 3rd variable d3 is defined by diagnosis code */
%let d3i9=%str('dx_code'); /*include all ICD9 dx code to define d3 variable */
%let d3i10=%str('dx_code'); /*include all ICD10 dx code to define d3 variable */   

/* Continue to define other variables for inclusion/exclusion/outcome/covariates d4, d5, etc... as desire*/



/* 1st variable p1 is defined by procedure code */

%let p1i9=%str('px_code'); /*include all ICD9 procedure/CPT codes to define p1 variable */
%let p1i10=%str('px_code');  /*include all ICD10 procedure/CPT codes to define p1 variable */
/* Similar for other procedure variables */
/* Continue to define other variables as desire*/



/* To make the program run more efficient, only claims with the diangoses and procedure codes to create outcome and covariates are pulled */
%let separator=%str( );

/*specify all ICD9 and ICD10 diagnosis codes are used */
%let all_dx=%sysfunc(catx(&separator,&outcomedi9, &outcomedi10, &d1i9, &d1i10, &d2i9, &d2i10,
			&d3i9, &d3i10 /*continue to enter more if more diagnosis variables are created  */));

/*specify all ICD9 and ICD10 procedure codes are used */
%let all_px=%sysfunc(catx(&separator,&outcomepi9, &outcomepi10, &p1i9, &p1i10
			 /*continue to enter more if more procedure variables are created  */));


/*User_check_point_ends*/

*************************************************************************************************************************;
*Program IIb -Pull inpatient claims and outpatient claims;


data cohort;
set d.exposure;
run;
/*
proc surveyselect data=d.exposure
   method=srs n=1 out=cohort;
run;
*/ 
/* I usually  randomly select 1 or 2 patients from my exposure cohort to write and check my code, 
then I use the full dataset "cohort" in batch mode to save time */



/*Inpatient first */

*Admission files;

%macro yr(var);
proc sql;
create table  adm_&var
as select    b.enrolid, b.pdx length=7, b.dx1 length=7, b.dx2 length=7 ,  b.dx3 length=7, b.dx4 length=7, 
b.dx5 length=7, b.dx6 length=7 ,  b.dx7 length=7, b.dx8 length=7, 
b.dx9 length=7, b.dx10 length=7 ,  b.dx11 length=7, b.dx12 length=7, 
b.dx13 length=7, b.dx14 length=7 ,  b.dx15 length=7, 
b.pproc length=7, b.proc1 length=7, b.proc2 length=7, b.proc3 length=7, b.proc4 length=7, b.proc5 length=7, 
b.proc6 length=7, b.proc7 length=7, b.proc8 length=7, b.proc9 length=7, b.proc10 length=7, b.proc11 length=7,
b.proc12 length=7, b.proc13 length=7, b.proc14 length=7, b.proc15 length=7,
 b.dstatus, b.admdate, 1 as ip length=3, "9" as dxver length=1

from cohort a
inner join truven.cc_ip_adm_&var b
on a.enrolid=b.enrolid 
where lookback<=b.admdate<=fu;
quit;

/*select claims with related Dx and PX only */
data adm&var (drop=i j);
set adm_&var;
array dx {*}  pdx dx1-dx15;
array px {*}  pproc proc1-proc15;

do i=1 to dim(dx);
if dx(i) in: (&all_dx) then output;
end;
do j=1 to dim(px);
if px(j) in: (&all_px) then output;
end;
run;


%mend;
%yr(05_4);
%yr(06_3);
%yr(07_3);
%yr(08_3);
%yr(09_3);
%yr(10_3);
%yr(11_3);
%yr(12_2);
%yr(13_2);
%yr(14_1);

%macro yr(var);
proc sql;
create table  adm_&var
as select    b.enrolid, b.pdx length=7, b.dx1 length=7, b.dx2 length=7 ,  b.dx3 length=7, b.dx4 length=7, 
b.dx5 length=7, b.dx6 length=7 ,  b.dx7 length=7, b.dx8 length=7, 
b.dx9 length=7, b.dx10 length=7 ,  b.dx11 length=7, b.dx12 length=7, 
b.dx13 length=7, b.dx14 length=7 ,  b.dx15 length=7, 
b.pproc length=7, b.proc1 length=7, b.proc2 length=7, b.proc3 length=7, b.proc4 length=7, b.proc5 length=7, 
b.proc6 length=7, b.proc7 length=7, b.proc8 length=7, b.proc9 length=7, b.proc10 length=7, b.proc11 length=7,
b.proc12 length=7, b.proc13 length=7, b.proc14 length=7, b.proc15 length=7,
b.dxver, b.dstatus, b.admdate, 1 as ip length=3

from cohort a
inner join truven.cc_ip_adm_&var b
on a.enrolid=b.enrolid 
where lookback<=b.admdate<=fu;
quit;


/*select claims with related Dx and PX only */
data adm&var (drop=i j);
set adm_&var;
array dx {*}  pdx dx1-dx15;
array px {*}  pproc proc1-proc15;

do i=1 to dim(dx);
if dx(i) in: (&all_dx) then output;
end;
do j=1 to dim(px);
if px(j) in: (&all_px) then output;
end;
run;
%mend;

%yr(15_1);
%yr(16_1);
%yr(17_1);
%yr(18_1);
%yr(19_1);


*IP service files;

%macro yr(var);
proc sql;
create table  ipsvc_&var
as select   b.enrolid, b.dx1 length=7, b.dx2  length=7, b.pdx length=7, /* b.dx3, b.dx4, */ b.svcdate, 
b.proc1 length=7,  b.pproc length=7, b.dstatus, b.admdate, 1 as ip length=3

from cohort a
inner join truven.cc_ip_svc_&var b
on a.enrolid=b.enrolid 
where lookback<=b.svcdate<=fu;

quit;

/*select claims with related Dx and PX only */
data ipsvc&var (drop=i j);
set ipsvc_&var;
array dx {*}  pdx dx1-dx2;
array px {*}  pproc proc1;

do i=1 to dim(dx);
if dx(i) in: (&all_dx) then output;
end;
do j=1 to dim(px);
if px(j) in: (&all_px) then output;
end;
run;


%mend;
%yr(05_4);
%yr(06_3);
%yr(07_3);
%yr(08_3);

%macro yr(var);
proc sql;
create table  ipsvc_&var
as select    b.enrolid, b.dx1 length=7, b.dx2 length=7 , b.pdx length=7, b.dx3 length=7, b.dx4 length=7,  b.svcdate, 
b.proc1 length=7,  b.pproc length=7, b.dstatus, b.admdate, 1 as ip length=3

from cohort a
inner join truven.cc_ip_svc_&var b
on a.enrolid=b.enrolid 
where lookback<=b.svcdate<=fu;

quit;

/*select claims with related Dx and PX only */
data ipsvc&var (drop=i j);
set ipsvc_&var;
array dx {*}  pdx dx1-dx4;
array px {*}  pproc proc1;

do i=1 to dim(dx);
if dx(i) in: (&all_dx) then output;
end;
do j=1 to dim(px);
if px(j) in: (&all_px) then output;
end;
run;


%mend;

%yr(09_3);
%yr(10_3);
%yr(11_3);
%yr(12_2);
%yr(13_2);
%yr(14_1);

%macro yr(var);
proc sql;
create table  ipsvc_&var
as select    b.enrolid, b.dx1 length=7, b.dx2 length=7 , b.pdx length=7, b.dx3 length=7, b.dx4 length=7,  b.svcdate, 
b.proc1 length=7,  b.pproc length=7, b.dxver, b.dstatus, b.admdate, 1 as ip length=3

from cohort a
inner join truven.cc_ip_svc_&var b
on a.enrolid=b.enrolid 
where lookback<=b.svcdate<=fu;
quit;

/*select claims with related Dx and PX only */
data ipsvc&var (drop=i j);
set ipsvc_&var;
array dx {*}  pdx dx1-dx4;
array px {*}  pproc proc1;

do i=1 to dim(dx);
if dx(i) in: (&all_dx) then output;
end;
do j=1 to dim(px);
if px(j) in: (&all_px) then output;
end;
run;


%mend;

%yr(15_1);
%yr(16_1);
%yr(17_1);
%yr(18_1);
%yr(19_1);

/*add more data year if needed */



/* Pulling OT claims */

%macro yr(var);

proc sql;
create table  ot_&var
as select   b.enrolid, b.dx1 length=7 , b.dx2 length=7 , /* b.dx3, b.dx4,  */ b.svcdate, b.proc1 length=7
from cohort a
inner join truven.cc_ot_svc_&var  b
on a.enrolid=b.enrolid 
where lookback<=b.svcdate<=fu;
quit;

/*select claims with related Dx and PX only */
data ot&var (drop=i j);
set ot_&var;
array dx {*}   dx1-dx2;
array px {*}   proc1;

do i=1 to dim(dx);
if dx(i) in: (&all_dx) then output;
end;
do j=1 to dim(px);
if px(j) in: (&all_px) then output;
end;
run;


%mend;
%yr(05_4);
%yr(06_3);
%yr(07_3);
%yr(08_3);


%macro yr(var);

proc sql;
create table  ot_&var
as select  b.enrolid, b.dx1 length=7 , b.dx2 length=7 ,  b.dx3 length=7, b.dx4 length=7, b.svcdate, b.proc1 length=7
from cohort a
inner join truven.cc_ot_svc_&var  b
on a.enrolid=b.enrolid 
where lookback<=b.svcdate<=fu;
quit;

data ot&var (drop=i j);
set ot_&var;
array dx {*}   dx1-dx4;
array px {*}   proc1;

do i=1 to dim(dx);
if dx(i) in: (&all_dx) then output;
end;
do j=1 to dim(px);
if px(j) in: (&all_px) then output;
end;
run;
%mend;
%yr(09_3);
%yr(10_3);
%yr(11_3);
%yr(12_2);
%yr(13_2);
%yr(14_1);



%macro yr(var);

proc sql;
create table  ot_&var
as select   b.enrolid, b.dx1 length=7 , b.dx2 length=7 ,  b.dx3 length=7, b.dx4 length=7, b.svcdate, b.proc1 length=7, b.dxver
from cohort a
left join truven.cc_ot_svc_&var  b
on a.enrolid=b.enrolid 
where lookback<=b.svcdate<=fu;
quit;

data ot&var (drop=i j);
set ot_&var;
array dx {*}   dx1-dx4;
array px {*}   proc1;

do i=1 to dim(dx);
if dx(i) in: (&all_dx) then output;
end;
do j=1 to dim(px);
if px(j) in: (&all_px) then output;
end;
run;

%mend;
%yr(15_1);
%yr(16_1);
%yr(17_1);
%yr(18_1);
%yr(19_1);

/*add more data year if needed */





/*combine IP and OT claims together */
data ipot0;

set 
 adm05_4 
 adm06_3 
 adm07_3 
 adm08_3 
 adm09_3 
 adm10_3 
 adm11_3 
 adm12_2 
 adm13_2 
 adm14_1 
 adm15_1 
 adm16_1 
 adm17_1
 adm18_1
 adm19_1

 ipsvc05_4 
 ipsvc06_3 
 ipsvc07_3 
 ipsvc08_3 
 ipsvc09_3 
 ipsvc10_3 
 ipsvc11_3 
 ipsvc12_2 
 ipsvc13_2 
 ipsvc14_1 
 ipsvc15_1 
 ipsvc16_1 
 ipsvc17_1
 ipsvc18_1
 ipsvc19_1

 ot05_4 
 ot06_3 
 ot07_3 
 ot08_3 
 ot09_3 
 ot10_3 
 ot11_3 
 ot12_2 
 ot13_2 
 ot14_1 
 ot15_1 
 ot16_1 
 ot17_1
 ot18_1
 ot19_1

 ;

if svcdate ne . then cldate=svcdate;
else if admdate ne . then cldate=admdate;

format cldate mmddyy10.;
length cldate 4.;

if year(cldate)<2015 then dxver="9";
/*from year 2015, classify ICD10 and ICD9 claims if dxver missing */
if year(cldate)>=2015 and dxver="" and 
	( substr(dx1,1,1) in ('A', 'B', 'C', 'D', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 
		'R', 'S', 'T', 'U', 'W', 'X', 'Y', 'Z') or 
	substr(dx2,1,1) in ('A', 'B', 'C', 'D', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N','O', 'P', 'Q', 
		'R', 'S', 'T', 'U', 'W', 'X', 'Y', 'Z') or 
	substr(dx3,1,1) in ('A', 'B', 'C', 'D', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 
		'R', 'S', 'T', 'U', 'W', 'X', 'Y', 'Z') or 
	substr(dx4,1,1) in ('A', 'B', 'C', 'D', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 
		'R', 'S', 'T', 'U', 'W', 'X', 'Y', 'Z')  or 
	substr(pdx,1,1) in ('A', 'B', 'C', 'D', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 
		'R', 'S', 'T', 'U', 'W', 'X', 'Y', 'Z')) then dxver="0";
	else if year(cldate)>=2015 and dxver="" and 
		 (substr(dx1,1,1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') or 
		 substr(dx2,1,1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') or 
		 substr(dx3,1,1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') or 
		 substr(dx4,1,1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') or 
		 substr(pdx,1,1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0'))  then dxver="9";
	
if enrolid eq . then delete;
run;

proc sql;
create table ipot 
	as select a.enrolid, a.index, a.fu, b.cldate, b.dx1, b.dx2, b.dx3, b.dx4, b.pdx, b.pproc, b.proc1, b.dxver
	from cohort a 
	left join ipot0 b 
	on a.enrolid=b.enrolid;
	quit;


proc sort data=ipot;
by enrolid cldate;
run;

*************************************************************************************************************************;
*Program IIc -Create inclusion/exclusion/outcome/covariate variables;

data outcome_cov (drop=index  fu cldate dx1-dx15 pdx pproc proc1-proc15 dxver);
set ipot;
by enrolid;


array dx {*}  pdx dx1-dx15;
array px {*}  pproc proc1-proc15;


/*Macro explanation: vr=macro name, var=variable name, i9=code in ICD9 era, i10=code in ICD10 era, 
stdt=start date of evaluation window, eddt=end date of evaluation window */
/*define outcome by diagnosis code */
%macro vr(var,i9,i10, stdt,eddt);

retain &var 0;
retain dt&var .;

if first.enrolid then do;
&var=0;
dt&var=.;
end;

do j=1 to dim(dx); 

			if dxver="9" and dx(j) in: (&i9)  and cldate>=(&stdt) and cldate<=(&eddt) then do ;
						&var=1;
						if dt&var=. then dt&var=cldate;
						else if dt&var ne . and dt&var>cldate then dt&var=cldate;
			end;
			else if dxver="0" and dx(j) in: (&i10)  and cldate>=(&stdt) and cldate<=(&eddt) then do ;
						&var=1;
						if dt&var=. then dt&var=cldate;
						else if dt&var ne . and dt&var>cldate then dt&var=cldate;
			end;
end;
drop j;
length dt&var 4. &var 3.;
%mend;

/*User_check_point_2b*/		
%vr(outcomedx,&outcomedi9, &outcomedi10,evaluation_start_date,evaluation_end_date); 
/* 	outcome is the name of outcome variable
	&outcomei9 is to call and include ICD9 codes to define the outcome as you specify in the let statmente at begining, 
	Similar for ICD10: &outcomei10
and the last two are start date and end date window of your outcome */

/*User_check_point_ends*/	


/*define outcome by procedure code code */
%macro vr(var,i9,i10, stdt,eddt);

retain &var 0;
retain dt&var .;

if first.enrolid then do;
&var=0;
dt&var=.;
end;

do h=1 to dim(px); 

			if dxver="9" and dx(h) in: (&i9)  and cldate>=(&stdt) and cldate<=(&eddt) then do ;
						&var=1;
						if dt&var=. then dt&var=cldate;
						else if dt&var ne . and dt&var>cldate then dt&var=cldate;
			end;
			else if dxver="0" and dx(h) in: (&i10)  and cldate>=(&stdt) and cldate<=(&eddt) then do ;
						&var=1;
						if dt&var=. then dt&var=cldate;
						else if dt&var ne . and dt&var>cldate then dt&var=cldate;
			end;
end;
drop h;
length dt&var 4. &var 3.;
%mend;

/*User_check_point_2c*/		
%vr(outcomepx,&outcomepi9, &outcomepi10,evaluation_start_date, evaluation_end_date); 
/* 	outcomepx is the outcome that is defined by procedure code;
	&outcomepi9 is to call and include ICD9 procedure/CPT to define the outcome as you specify in the let statmente at begining, 
	Similar for ICD10: &outcomepi10
and the last two are start date and end date window of your outcome */

/*User_check_point_ends*/	


/*coding for covariates and/or inclusion/exclusion criteria */
%macro vr(var,i9,i10, stdt,eddt);

retain &var 0;

if first.enrolid then do;
&var=0;
end;

do k=1 to dim(dx); 

			if dxver="9" and dx(k) in: (&i9)  and cldate>=(&stdt) and cldate<=(&eddt) then do ;
						&var=1;
			end;
			else if dxver="0" and dx(k) in: (&i10)  and cldate>=(&stdt) and cldate<=(&eddt) then do ;
						&var=1;
			end;
end;
drop k;
length &var 3.;

%mend;
/*User_check_point_2d*/			
%vr(d1,&d1i9, &d1i10,evaluation_start_date,evaluation_start_date); 
%vr(d2,&d2i9, &d2i10, evaluation_start_date,evaluation_end_date); 
%vr(d3,&d3i9, &d3i10,evaluation_start_date,evaluation_end_date); 

/*you can add more variables as needed here */
/*User_check_point_ends*/			


/*macro for procedure codes */
%macro pvr(pvar,i9,i10, stdt,eddt);

retain &pvar 0;

if first.enrolid then do;
&pvar=0;
end;

do i=1 to dim(px); 

			if dxver="9" and px(i) in: (&i9)  and cldate>=(&stdt) and cldate<=(&eddt) then do ;
						&pvar=1;
			end;
			else if dxver="0" and px(i) in: (&i10)  and cldate>=(&stdt) and cldate<=(&eddt) then do ;
						&pvar=1;
			end;
end;
drop i;
length &pvar 3.;

%mend;
/*User_check_point_2e*/			
%pvr(p1,&p1i9,&p1i10,evaluation_start_date,evaluation_end_date);
/*macro structure is the same as diagnosis variable */
/*add more variables if needed */

/*User_check_point_ends*/			


if last.enrolid then output;
run;


*************************************************************************************************************************;
*Program IId -Combine with Exposure dataset to create the final cohort;


proc sql;
create table outcome_cov2 
	as select *
	from d.exposure a 
	left join outcome_cov b 
	on a.enrolid=b.enrolid;
	quit;

data d.cohort;
set outcome_cov2;
outcome=max(outcomedx,outcomepx);
dtoutcome=min(dtoutcomedx, dtoutcomepx);
array clean {*} outcome d: p: ;
do i=1 to dim(clean);
if clean(i)=. then clean(i)=0;
end;
drop i outcomedx outcomepx dtoutcomedx dtoutcomepx;
if outcome=0 then dtoutcome=.;
run;
/*Now you have a cohort with exposure, outcome, index date, outcome date and covariate variables */
/*depending on the way that the user defines, covariate variables can be considered inclusion, exclusion criteria or confounders */


****************************************************************************************************************************************************
****************************************************************************************************************************************************
*************************************************************** END PROGRAM II **********************************************************************
****************************************************************************************************************************************************
****************************************************************************************************************************************************
****************************************************************************************************************************************************;
















****************************************************************************************************************************************************
*********************************Following is an example of how to input the required parameters for Program I and II ******************************
***************************************************************************************************************************************************;


*************************************************************************************************************************
*******************Program I - Define exposure and control groups **********************
*************************************************************************************************************************;

/*Steps:
*Program Ia -Create SAS library and some let statements to modify easily for the main/sensitivy analyses or similar studies;

*Program Ib - Pulling patients with exposure and drug use with the criteria that are defined in Program Ia;
*/

*************************************************************************************************************************
*Program Ia -Create SAS library and some let statements to modify easily for the main/sensitivy analyses or similar studies;


/*User_check-point_1a*/

/* Truven dataset location  and Redbook name*/
libname truven "I:";

/*Redbook location */
libname rb  "I:";

/*location for saving data */
libname d "H:/Test";

/*Specify Redbook dataset name */

%let redbook2015=redbook_14 ;
%let redbook2016=redbook_16_1 ;
%let redbook2017=redbook_17 ;
%let redbook2018=redbook_18 ;
%let redbook2019=redbook2019 ;



**define exposure and control drugs by NDC and HCPCS codes;
%let exporx=%str(find(gennme,"chloroquine",'i')) or %str(find(gennme,"hydroxychloroquine",'i')); 

%let contrx=%str(find(gennme,"sulfasalazine",'i')); 
/*if you have >=2 drugs in exposure/control group, you can expand the statement as an follow example:

%let contrx=%str(find(gennme,"your_generic_name1",'i') or find(gennme,"your_generic_name2",'i')); 
*/


%let expopx=%str('J0390'); /*HCPCs codes of exposure drug*/
%let contpx=%str('AAAAA'); /*HCPCs codes of control drug*/
/*in case, your drug does not have a HCPCS code, you can use an invalid code e.g. 'AAAAA' to keep the code run) */


%let maxdaysupp=180; /*upper limit for a day supply is considered as acceptable */
%let gap=3; /*number of day gap allowed to create a continuous duration of drug use */

**look back and follow up time;
%let lookbackce=index-180;  /*requirement of continous enrollment before index, usually this is time for baseline covariates */
%let lookfowardce=index+0; /*requirement of number of day for continous enrollment after index */
%let fu=min(rxend, dtend, deathdt, index+180); /*follow up time from index date  */
/* The user replaces fu_parameter with at least of the 4 parameters (rxend, dtend, deathdt, index+number).
 Notation: earliest date of end of drug use (rxend), enrollment end (dtend), inpatient death date (deathdt), 
 or number days after index (index+number) */

/*User_check_point_ends*/

*************************************************************************************************************************
*Program Ib -Pulling patients with exposure and drug use with the criteria that are defined in Program Ia;



*Create a Redbook for the entire study period which allows the change of NDC over years;
/* Identifying start year and end year when an NDC is valid */

data red2015;
set rb.&redbook2015;
st_yr=2005;
ed_yr=2015;
length st_yr ed_yr 3.;
run;

data red2016;
set rb.&redbook2016;
st_yr=2016;
ed_yr=2016;
length st_yr ed_yr 3.;
run;

data red2017;
set rb.&redbook2017;
st_yr=2017;
ed_yr=2017;
length st_yr ed_yr 3.;
run;

data red2018;
set rb.&redbook2018;
st_yr=2018;
ed_yr=2018;
length st_yr ed_yr 3.;
run;

data red2019;
set rb.&redbook2019;
st_yr=2019;
ed_yr=2019;
length st_yr ed_yr 3.;
run;

data redbook;
set red2015 red2016 red2017 red2018 red2019;
run; /*combine all Redbooks together */


/*create a dataset only including NDC for exposure and control drug */
data rb_expocont;
set redbook;

if &exporx then exporx=1;
if &contrx then contrx=1;
if exporx eq . and contrx eq . then delete;
run;

/*start to pull Rx claim  */
%macro rxyr(var);

proc sql;
create table rx&var
as select   a.enrolid, a.age, a.dobyr, a.sex, a.ndcnum, a.daysupp, a.svcdate, b.exporx, b.contrx, b.gennme, b.mstfmds, b.roads, b.thrdtds
from truven.cc_rx_&var  a
inner join rb_expocont b
on a.ndcnum=b.ndcnum
where b.st_yr<=year(a.svcdate)<=b.ed_yr;
quit;

%mend;
%rxyr(05_4);
%rxyr(06_3);
%rxyr(07_3);
%rxyr(08_3);
%rxyr(09_3);
%rxyr(10_3);
%rxyr(11_3);
%rxyr(12_2);
%rxyr(13_2);
%rxyr(14_1);
%rxyr(15_1);
%rxyr(16_1);
%rxyr(17_1);
%rxyr(18_1);
%rxyr(19_1);


data rx0 (keep=enrolid age dobyr svcdate sex daysupp exporx contrx rx_date ndcnum gennme);
set
rx05_4
rx06_3 
rx07_3 
rx08_3 
rx09_3 
rx10_3 
rx11_3 
rx12_2 
rx13_2 
rx14_1 
rx15_1 
rx16_1 
rx17_1
rx18_1
rx19_1;

rx_date=svcdate;
format rx_date mmddyy10.;
length daysupp exporx contrx 3. rx_date 4.; 
where enrolid ne . ;
run;

**some basic cleaning for rx claims;


proc sort data=rx0 out=rx1;
by enrolid ndcnum svcdate;
run;

data rx2;
set rx1;
rx_id=_n_;
absday=abs(daysupp);
if daysupp<0 then neg_rx=1;
run;

data rxneg;
set rx2;
where neg_rx=1;
run;

data rx_noneg;
set rx2;
where neg_rx ne 1;
run;
proc sql;
create table clean_rx 
as select a.enrolid,  a.ndcnum, a.daysupp, a.gennme, a.svcdate,
 b.daysupp as daysupp2, b.gennme as gennme2, b.svcdate as svcdate2, b.rx_id
from rxneg a 
inner join rx_noneg b 
on a.enrolid=b.enrolid and a.ndcnum=b.ndcnum and a.absday=b.absday  ;
quit;

data clean_rx2 (keep=rx_id );
set clean_rx;
if abs(svcdate-svcdate2)>7 then delete;
run;
data clean_rx3 (keep=rx_id);
set rxneg;
run;
data clean_rx4;
set clean_rx2 clean_rx3;
exclude=1;
run;

proc sort data=clean_rx4 nodups;
by rx_id;
run;
proc sql;
create table rx3 
as select a.*, b.exclude
from rx2 a 
left join clean_rx4 b 
on a.rx_id=b.rx_id;
quit;

data rx4 (drop=rx_id absday neg_rx exclude);
set rx3;
/*some basic cleaning */

if exclude=1 or daysupp=0 or daysupp>=&maxdaysupp then delete;


run;


proc sort data=rx4 out=rx nodups;
by enrolid rx_date;
run;



/*start to pull claim with HCPCS codes  */


%macro ipotyr (var);

data ot&var (keep=enrolid age dobyr svcdate sex rx_date daysupp exporx contrx);
set  truven.cc_ot_svc_&var;
if proc1 in (&expopx) then exporx=1;
if proc1 in (&contpx) then contrx=1;
if exporx=. and contrx=. then delete;
daysupp=0;
rx_date=svcdate;
format rx_date mmddyy10.;
length daysupp exporx contrx 3. rx_date 4.; 
where enrolid ne . ;
run;

data ipsvc&var (keep=enrolid age dobyr svcdate rx_date sex daysupp exporx contrx);
set  truven.cc_ip_svc_&var;
if pproc in (&expopx) or proc1 in (&expopx) then exporx=1;
if pproc in (&contpx) or proc1 in (&contpx) then contrx=1;
if exporx=. and contrx=. then delete;
daysupp=0;
rx_date=svcdate;
format rx_date mmddyy10.;
length daysupp exporx contrx 3. rx_date 4.; 
where enrolid ne . ;
run;

data adm&var (keep=enrolid age dobyr svcdate rx_date sex daysupp exporx contrx);
set  truven.cc_ip_adm_&var;
array px {*} pproc proc1-proc15;

do i=1 to dim(px);
if px(i) in (&expopx) then do;
exporx=1;
daysupp=0;
rx_date=admdate;
svcdate=admdate;
output;
end;
end;

do j=1 to dim(px);
if px(j) in (&contpx) then do;
contrx=1;
daysupp=0;
rx_date=admdate;
svcdate=admdate;
output;
end;
end;

format rx_date svcdate mmddyy10.;
length daysupp exporx contrx 3. rx_date svcdate 4.; 
where enrolid ne . ;
run;


%mend;

%ipotyr (05_4);
%ipotyr (06_3);
%ipotyr (07_3);
%ipotyr (08_3);
%ipotyr (09_3);
%ipotyr (10_3);
%ipotyr (11_3);
%ipotyr (12_2);
%ipotyr (13_2);
%ipotyr (14_1);
%ipotyr (15_1);
%ipotyr (16_1);
%ipotyr (17_1);
%ipotyr (18_1);
%ipotyr (19_1);


/*combine all rx and HCPCS claim together */
data rx_all;
set
ot05_4
ot06_3 
ot07_3 
ot08_3 
ot09_3 
ot10_3 
ot11_3 
ot12_2 
ot13_2 
ot14_1 
ot15_1 
ot16_1 
ot17_1
ot18_1
ot19_1

ipsvc05_4
ipsvc06_3 
ipsvc07_3 
ipsvc08_3 
ipsvc09_3 
ipsvc10_3 
ipsvc11_3 
ipsvc12_2 
ipsvc13_2 
ipsvc14_1 
ipsvc15_1 
ipsvc16_1 
ipsvc17_1
ipsvc18_1  
ipsvc19_1  

adm05_4
adm06_3 
adm07_3 
adm08_3 
adm09_3 
adm10_3 
adm11_3 
adm12_2 
adm13_2 
adm14_1 
adm15_1 
adm16_1 
adm17_1
adm18_1  
adm19_1  

rx
;

run;

/*create a continous duration of exposure and control drug */

**exposure group first;

%macro dur(var);

data &var.1;
set rx_all;
where &var=1;
run;

proc sort data=&var.1 ;
by enrolid  rx_date  ;
run;

data &var.2;
set &var.1;
by enrolid;
retain episode stdt eddt .;

if first.enrolid then do;
	episode=1;
	stdt=rx_date;
	eddt=rx_date+daysupp;
end;
else if rx_date<=eddt then do;
		stdt=stdt;
		episode=episode+0;
		eddt=eddt+daysupp;
	end;
else if eddt<rx_date<=eddt+&gap then do;
		stdt=stdt;
		episode=episode+0;
		eddt=rx_date+daysupp;
	end;
else do;
		stdt=svcdate;
		episode=episode+1;
		eddt=rx_date+daysupp;

	end;
format stdt eddt  mmddyy10.;
length episode 3. stdt eddt 4.;
run;

proc sql;
create table &var.3
	as select  a.enrolid,a.episode, a.sex,  a.exporx, a.contrx, min(age) as indexage, a.stdt, max(eddt) as ed_dt 
	from &var.2 a 
	group by enrolid , episode;
	quit;

/*only keep the first episode - i.e. not allow cohort reentry*/
data &var.4 (drop =episode);
set &var.3;
where episode=1;
run;

proc sort data=&var.4 nodupkeys;
by enrolid ;
run;

%mend;
%dur(exporx);
%dur(contrx);

/* if a patient used both exposure and control drug, select the first drug and censor at the time of switching */
data rx_all2;
set exporx4 contrx4;
rxid=_n_;
length rxid 8. ed_dt 4.;
format ed_dt mmddyy10.;
run;

proc sort data=rx_all2 out=rx_all3;
by enrolid stdt;
run;

proc sort data=rx_all3 out=rx_all4 nodupkeys;
by enrolid ;
run;


proc sql;
create table rx_all5 
	as select a.*, b.exporx2, b.contrx2, b.stdt2, b.ed_dt2, b.rxid2
	from rx_all4 a 
	left join rx_all2 (rename=(exporx=exporx2 contrx=contrx2 stdt=stdt2 ed_dt=ed_dt2 rxid=rxid2)) b
	on a.enrolid=b.enrolid and a.rxid ne b.rxid2 and a.stdt<=b.stdt2<=ed_dt;
quit;


data rx_all6;
set rx_all5;
if exporx=1 then expo=1; 
else if contrx=1 then expo=0;
index=stdt;
rxend=min(ed_dt,stdt2);
length index rxend 4. expo 3.;
format index rxend mmddyy10.;
drop stdt ed_dt rxid exporx2 contrx2 stdt2 ed_dt2 rxid2 exporx contrx;
run; 


/* Apply continous enrollment requirement  */

proc sql;
create table rx_all7 
	as select a.*, b.dtstart, b.dtend 
	from rx_all6 a 
	left join truven.truven_cts_elig_rx_cc_0519 b 
	on a.enrolid=b.enrolid;
	quit;

data rx_all8;
set rx_all7;
where dtstart<=&lookbackce and dtend>=&lookfowardce;
run;

/* find IP death date to finalize end of follow up window */

%macro ipotyr (var);
proc sql;
create table deathadm&var 
	as select a.enrolid, b.disdate, b.dstatus
	from rx_all8 a 
	inner join truven.cc_ip_adm_&var   b 
	on a.enrolid=b.enrolid 
where dstatus='20' or dstatus='40' or dstatus='41' or dstatus='42' ;
quit;

proc sql;
create table deathipsvc&var 
	as select a.enrolid, b.disdate, b.dstatus
	from rx_all8 a 
	inner join truven.cc_ip_svc_&var   b 
	on a.enrolid=b.enrolid 
where dstatus='20' or dstatus='40' or dstatus='41' or dstatus='42' ;
quit;

%mend;

%ipotyr (05_4);
%ipotyr (06_3);
%ipotyr (07_3);
%ipotyr (08_3);
%ipotyr (09_3);
%ipotyr (10_3);
%ipotyr (11_3);
%ipotyr (12_2);
%ipotyr (13_2);
%ipotyr (14_1);
%ipotyr (15_1);
%ipotyr (16_1);
%ipotyr (17_1);
%ipotyr (18_1);
%ipotyr (19_1);

data death;
set 
deathadm05_4
deathadm06_3 
deathadm07_3 
deathadm08_3 
deathadm09_3 
deathadm10_3 
deathadm11_3 
deathadm12_2 
deathadm13_2 
deathadm14_1 
deathadm15_1 
deathadm16_1 
deathadm17_1
deathadm18_1
deathadm19_1

deathipsvc05_4
deathipsvc06_3 
deathipsvc07_3 
deathipsvc08_3 
deathipsvc09_3 
deathipsvc10_3 
deathipsvc11_3 
deathipsvc12_2 
deathipsvc13_2 
deathipsvc14_1 
deathipsvc15_1 
deathipsvc16_1 
deathipsvc17_1
deathipsvc18_1
deathipsvc19_1;

run;

proc sort data=death ;
by enrolid disdate;
run;

proc sort data=death  nodupkeys;
by enrolid ;
run;

proc sql;
create table rx_all9
	as select a.* , b.deathdt
	from rx_all8 a 
	left join death (rename=(disdate=deathdt)) b 
	on a.enrolid=b.enrolid;

quit;


/*finalize expsoure status and follow up time */
data d.exposure;
set rx_all9;
if deathdt ne . and deathdt<=index then delete; /*delete case that died before index */
lookback=&lookbackce ; /*set start of look back period */
fu=&fu; /*set end of follow up time */
format lookback fu mmddyy10.;
length lookback fu 4. indexage 3.;
drop  deathdt;
run;


/*********************************************************************************
END PROGRAM I
*********************************************************************************/




*************************************************************************************************************************
*******************Program II - Pull claims and Create inclusion/exclusion/outcome/covariate variables **********************
*************************************************************************************************************************;

/*Steps:
*Program IIa -Create some let statement to define inclusion/exclusion/outcome/covariate that can be easily modified;
*Program IIb -Pull inpatient claims and outpatient claims;
*Program IIc -Create inclusion/exclusion/outcome/covariate variables;
*Program IId -Combine with Exposure dataset to create the final cohort;
*/



*************************************************************************************************************************;
*Program IIa -Create some let statement to define inclusion/exclusion/outcome/covariate that can be easily modified;


/*User_check_point_2a*/

/* Truven dataset location */
libname truven "I:";


/*location for saving data */
libname d "H:/Test";



/* of note: 
- d1, d2, d3, etc... variables are defined by diangosis code; 
- p1, p2, p3, etc... variables are defined by procedure/CPT codes
- outcome is Outcome variable;
*/

/*outcome variable */
%let outcomedi9=%str('4275' '798' '7981' '7982' '4271' '4274' '42741' '42742'); /*include all ICD9 dx codes to define outcome variable */
%let outcomedi10=%str('I46' 'I469'  'I472' 'I490' 'I4901' 'I4902' 'R99'); /*include all ICD10 dx codes to define outcome variable */   
%let outcomepi9=%str('AAAAA'); /*include all ICD9 procedure/CPT codes to define outcome variable */
%let outcomepi10=%str('AAAAA'); /*include all ICD10 procedure/CPT codes to define outcome variable */   


/* 1st variable d1 is defined by diagnosis code */
%let d1i9=%str('3051', '64900' '64901' '64902' '64903' '64904' '98984'); /*include all ICD9 dx code to define d1 variable */
%let d1i10=%str('F17200' 'F17201'
'F17203' 'F17208' 'F17209' 'F17210' 'F17211' 'F17213' 'F17218' 'F17219' 'F17220' 'F17221' 'F17223'
'F17228' 'F17229' 'F17290' 'F17291' 'F17293' 'F17298' 'F17299' 'O99330' 'O99331' 'O99332' 'O99333'
'O99334' 'O99335' 'T65211A' 'T65212A' 'T65213A' 'T65214A' 'T65221A' 'T65222A' 'T65223A' 'T65224A'
'T65291A' 'T65292A' 'T65293A' 'T65294A'); /*include all ICD10 dx code to define d1 variable */   


/* 2nd variable d2 is defined by diagnosis code */
%let d2i9=%str('6954' '7100'); /*include all ICD9 dx code to define d2 variable */
%let d2i10=%str('L93' 'M32'); /*include all ICD10 dx code to define d2 variable */   

/* 3rd variable d3 is defined by diagnosis code */
%let d3i9=%str('714'); /*include all ICD9 dx code to define d3 variable */
%let d3i10=%str('M05' 'M06' 'M08'); /*include all ICD10 dx code to define d3 variable */   

/* Continue to define other variables for inclusion/exclusion/outcome/covariates d4, d5, etc... as desire*/



/* 1st variable p1 is defined by procedure code */
%let p1i9=%str('99406' '99407'); /*include all ICD9 procedure/CPT codes to define p1 variable */
%let p1i10=%str('99406' '99407');  /*include all ICD10 procedure/CPT codes to define p1 variable */
/* Similar for other procedure variables */
/* Continue to define other variables as desire*/



/* To make the program run more efficient, only claims with the diangoses and procedure codes to create outcome and covariates are pulled */
%let separator=%str( );

/*specify all ICD9 and ICD10 diagnosis codes are used */
%let all_dx=%sysfunc(catx(&separator,&outcomedi9, &outcomedi10, &d1i9, &d1i10, &d2i9, &d2i10,
			&d3i9, &d3i10 /*continue to enter more if more diagnosis variables are created  */));

/*specify all ICD9 and ICD10 procedure codes are used */
%let all_px=%sysfunc(catx(&separator,&outcomepi9, &outcomepi10, &p1i9, &p1i10
			 /*continue to enter more if more procedure variables are created  */));


/*User_check_point_ends*/

*************************************************************************************************************************;
*Program IIb -Pull inpatient claims and outpatient claims;


data cohort;
set d.exposure;
run;
/*
proc surveyselect data=d.exposure
   method=srs n=1 out=cohort;
run;
*/ 
/* I usually  randomly select 1 or 2 patients from my exposure cohort to write and check my code, 
then I use the full dataset "cohort" in batch mode to save time */



/*Inpatient first */

*Admission files;

%macro yr(var);
proc sql;
create table  adm_&var
as select    b.enrolid, b.pdx length=7, b.dx1 length=7, b.dx2 length=7 ,  b.dx3 length=7, b.dx4 length=7, 
b.dx5 length=7, b.dx6 length=7 ,  b.dx7 length=7, b.dx8 length=7, 
b.dx9 length=7, b.dx10 length=7 ,  b.dx11 length=7, b.dx12 length=7, 
b.dx13 length=7, b.dx14 length=7 ,  b.dx15 length=7, 
b.pproc length=7, b.proc1 length=7, b.proc2 length=7, b.proc3 length=7, b.proc4 length=7, b.proc5 length=7, 
b.proc6 length=7, b.proc7 length=7, b.proc8 length=7, b.proc9 length=7, b.proc10 length=7, b.proc11 length=7,
b.proc12 length=7, b.proc13 length=7, b.proc14 length=7, b.proc15 length=7,
 b.dstatus, b.admdate, 1 as ip length=3, "9" as dxver length=1

from cohort a
inner join truven.cc_ip_adm_&var b
on a.enrolid=b.enrolid 
where lookback<=b.admdate<=fu;
quit;

/*select claims with related Dx and PX only */
data adm&var (drop=i j);
set adm_&var;
array dx {*}  pdx dx1-dx15;
array px {*}  pproc proc1-proc15;

do i=1 to dim(dx);
if dx(i) in: (&all_dx) then output;
end;
do j=1 to dim(px);
if px(j) in: (&all_px) then output;
end;
run;


%mend;
%yr(05_4);
%yr(06_3);
%yr(07_3);
%yr(08_3);
%yr(09_3);
%yr(10_3);
%yr(11_3);
%yr(12_2);
%yr(13_2);
%yr(14_1);

%macro yr(var);
proc sql;
create table  adm_&var
as select    b.enrolid, b.pdx length=7, b.dx1 length=7, b.dx2 length=7 ,  b.dx3 length=7, b.dx4 length=7, 
b.dx5 length=7, b.dx6 length=7 ,  b.dx7 length=7, b.dx8 length=7, 
b.dx9 length=7, b.dx10 length=7 ,  b.dx11 length=7, b.dx12 length=7, 
b.dx13 length=7, b.dx14 length=7 ,  b.dx15 length=7, 
b.pproc length=7, b.proc1 length=7, b.proc2 length=7, b.proc3 length=7, b.proc4 length=7, b.proc5 length=7, 
b.proc6 length=7, b.proc7 length=7, b.proc8 length=7, b.proc9 length=7, b.proc10 length=7, b.proc11 length=7,
b.proc12 length=7, b.proc13 length=7, b.proc14 length=7, b.proc15 length=7,
b.dxver, b.dstatus, b.admdate, 1 as ip length=3

from cohort a
inner join truven.cc_ip_adm_&var b
on a.enrolid=b.enrolid 
where lookback<=b.admdate<=fu;
quit;


/*select claims with related Dx and PX only */
data adm&var (drop=i j);
set adm_&var;
array dx {*}  pdx dx1-dx15;
array px {*}  pproc proc1-proc15;

do i=1 to dim(dx);
if dx(i) in: (&all_dx) then output;
end;
do j=1 to dim(px);
if px(j) in: (&all_px) then output;
end;
run;
%mend;

%yr(15_1);
%yr(16_1);
%yr(17_1);
%yr(18_1);
%yr(19_1);


*IP service files;

%macro yr(var);
proc sql;
create table  ipsvc_&var
as select   b.enrolid, b.dx1 length=7, b.dx2  length=7, b.pdx length=7, /* b.dx3, b.dx4, */ b.svcdate, 
b.proc1 length=7,  b.pproc length=7, b.dstatus, b.admdate, 1 as ip length=3

from cohort a
inner join truven.cc_ip_svc_&var b
on a.enrolid=b.enrolid 
where lookback<=b.svcdate<=fu;

quit;

/*select claims with related Dx and PX only */
data ipsvc&var (drop=i j);
set ipsvc_&var;
array dx {*}  pdx dx1-dx2;
array px {*}  pproc proc1;

do i=1 to dim(dx);
if dx(i) in: (&all_dx) then output;
end;
do j=1 to dim(px);
if px(j) in: (&all_px) then output;
end;
run;


%mend;
%yr(05_4);
%yr(06_3);
%yr(07_3);
%yr(08_3);

%macro yr(var);
proc sql;
create table  ipsvc_&var
as select    b.enrolid, b.dx1 length=7, b.dx2 length=7 , b.pdx length=7, b.dx3 length=7, b.dx4 length=7,  b.svcdate, 
b.proc1 length=7,  b.pproc length=7, b.dstatus, b.admdate, 1 as ip length=3

from cohort a
inner join truven.cc_ip_svc_&var b
on a.enrolid=b.enrolid 
where lookback<=b.svcdate<=fu;

quit;

/*select claims with related Dx and PX only */
data ipsvc&var (drop=i j);
set ipsvc_&var;
array dx {*}  pdx dx1-dx4;
array px {*}  pproc proc1;

do i=1 to dim(dx);
if dx(i) in: (&all_dx) then output;
end;
do j=1 to dim(px);
if px(j) in: (&all_px) then output;
end;
run;


%mend;

%yr(09_3);
%yr(10_3);
%yr(11_3);
%yr(12_2);
%yr(13_2);
%yr(14_1);

%macro yr(var);
proc sql;
create table  ipsvc_&var
as select    b.enrolid, b.dx1 length=7, b.dx2 length=7 , b.pdx length=7, b.dx3 length=7, b.dx4 length=7,  b.svcdate, 
b.proc1 length=7,  b.pproc length=7, b.dxver, b.dstatus, b.admdate, 1 as ip length=3

from cohort a
inner join truven.cc_ip_svc_&var b
on a.enrolid=b.enrolid 
where lookback<=b.svcdate<=fu;
quit;

/*select claims with related Dx and PX only */
data ipsvc&var (drop=i j);
set ipsvc_&var;
array dx {*}  pdx dx1-dx4;
array px {*}  pproc proc1;

do i=1 to dim(dx);
if dx(i) in: (&all_dx) then output;
end;
do j=1 to dim(px);
if px(j) in: (&all_px) then output;
end;
run;


%mend;

%yr(15_1);
%yr(16_1);
%yr(17_1);
%yr(18_1);
%yr(19_1);

/*add more data year if needed */



/* Pulling OT claims */

%macro yr(var);

proc sql;
create table  ot_&var
as select   b.enrolid, b.dx1 length=7 , b.dx2 length=7 , /* b.dx3, b.dx4,  */ b.svcdate, b.proc1 length=7
from cohort a
inner join truven.cc_ot_svc_&var  b
on a.enrolid=b.enrolid 
where lookback<=b.svcdate<=fu;
quit;

/*select claims with related Dx and PX only */
data ot&var (drop=i j);
set ot_&var;
array dx {*}   dx1-dx2;
array px {*}   proc1;

do i=1 to dim(dx);
if dx(i) in: (&all_dx) then output;
end;
do j=1 to dim(px);
if px(j) in: (&all_px) then output;
end;
run;


%mend;
%yr(05_4);
%yr(06_3);
%yr(07_3);
%yr(08_3);


%macro yr(var);

proc sql;
create table  ot_&var
as select  b.enrolid, b.dx1 length=7 , b.dx2 length=7 ,  b.dx3 length=7, b.dx4 length=7, b.svcdate, b.proc1 length=7
from cohort a
inner join truven.cc_ot_svc_&var  b
on a.enrolid=b.enrolid 
where lookback<=b.svcdate<=fu;
quit;

data ot&var (drop=i j);
set ot_&var;
array dx {*}   dx1-dx4;
array px {*}   proc1;

do i=1 to dim(dx);
if dx(i) in: (&all_dx) then output;
end;
do j=1 to dim(px);
if px(j) in: (&all_px) then output;
end;
run;
%mend;
%yr(09_3);
%yr(10_3);
%yr(11_3);
%yr(12_2);
%yr(13_2);
%yr(14_1);



%macro yr(var);

proc sql;
create table  ot_&var
as select   b.enrolid, b.dx1 length=7 , b.dx2 length=7 ,  b.dx3 length=7, b.dx4 length=7, b.svcdate, b.proc1 length=7, b.dxver
from cohort a
left join truven.cc_ot_svc_&var  b
on a.enrolid=b.enrolid 
where lookback<=b.svcdate<=fu;
quit;

data ot&var (drop=i j);
set ot_&var;
array dx {*}   dx1-dx4;
array px {*}   proc1;

do i=1 to dim(dx);
if dx(i) in: (&all_dx) then output;
end;
do j=1 to dim(px);
if px(j) in: (&all_px) then output;
end;
run;

%mend;
%yr(15_1);
%yr(16_1);
%yr(17_1);
%yr(18_1);
%yr(19_1);

/*add more data year if needed */





/*combine IP and OT claims together */
data ipot0;

set 
 adm05_4 
 adm06_3 
 adm07_3 
 adm08_3 
 adm09_3 
 adm10_3 
 adm11_3 
 adm12_2 
 adm13_2 
 adm14_1 
 adm15_1 
 adm16_1 
 adm17_1
 adm18_1
 adm19_1

 ipsvc05_4 
 ipsvc06_3 
 ipsvc07_3 
 ipsvc08_3 
 ipsvc09_3 
 ipsvc10_3 
 ipsvc11_3 
 ipsvc12_2 
 ipsvc13_2 
 ipsvc14_1 
 ipsvc15_1 
 ipsvc16_1 
 ipsvc17_1
 ipsvc18_1
 ipsvc19_1

 ot05_4 
 ot06_3 
 ot07_3 
 ot08_3 
 ot09_3 
 ot10_3 
 ot11_3 
 ot12_2 
 ot13_2 
 ot14_1 
 ot15_1 
 ot16_1 
 ot17_1
 ot18_1
 ot19_1

 ;

if svcdate ne . then cldate=svcdate;
else if admdate ne . then cldate=admdate;

format cldate mmddyy10.;
length cldate 4.;

if year(cldate)<2015 then dxver="9";
/*from year 2015, classify ICD10 and ICD9 claims if dxver missing */
if year(cldate)>=2015 and dxver="" and 
	( substr(dx1,1,1) in ('A', 'B', 'C', 'D', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 
		'R', 'S', 'T', 'U', 'W', 'X', 'Y', 'Z') or 
	substr(dx2,1,1) in ('A', 'B', 'C', 'D', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N','O', 'P', 'Q', 
		'R', 'S', 'T', 'U', 'W', 'X', 'Y', 'Z') or 
	substr(dx3,1,1) in ('A', 'B', 'C', 'D', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 
		'R', 'S', 'T', 'U', 'W', 'X', 'Y', 'Z') or 
	substr(dx4,1,1) in ('A', 'B', 'C', 'D', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 
		'R', 'S', 'T', 'U', 'W', 'X', 'Y', 'Z')  or 
	substr(pdx,1,1) in ('A', 'B', 'C', 'D', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 
		'R', 'S', 'T', 'U', 'W', 'X', 'Y', 'Z')) then dxver="0";
	else if year(cldate)>=2015 and dxver="" and 
		 (substr(dx1,1,1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') or 
		 substr(dx2,1,1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') or 
		 substr(dx3,1,1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') or 
		 substr(dx4,1,1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') or 
		 substr(pdx,1,1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0'))  then dxver="9";
	
if enrolid eq . then delete;
run;

proc sql;
create table ipot 
	as select a.enrolid, a.index, a.fu, b.cldate, b.dx1, b.dx2, b.dx3, b.dx4, b.pdx, b.pproc, b.proc1, b.dxver
	from cohort a 
	left join ipot0 b 
	on a.enrolid=b.enrolid;
	quit;


proc sort data=ipot;
by enrolid cldate;
run;

*************************************************************************************************************************;
*Program IIc -Create inclusion/exclusion/outcome/covariate variables;

data outcome_cov (drop=index  fu cldate dx1-dx15 pdx pproc proc1-proc15 dxver);
set ipot;
by enrolid;


array dx {*}  pdx dx1-dx15;
array px {*}  pproc proc1-proc15;


/*Macro explanation: vr=macro name, var=variable name, i9=code in ICD9 era, i10=code in ICD10 era, 
stdt=start date of evaluation window, eddt=end date of evaluation window */
/*define outcome by diagnosis code */
%macro vr(var,i9,i10, stdt,eddt);

retain &var 0;
retain dt&var .;

if first.enrolid then do;
&var=0;
dt&var=.;
end;

do j=1 to dim(dx); 

			if dxver="9" and dx(j) in: (&i9)  and cldate>=(&stdt) and cldate<=(&eddt) then do ;
						&var=1;
						if dt&var=. then dt&var=cldate;
						else if dt&var ne . and dt&var>cldate then dt&var=cldate;
			end;
			else if dxver="0" and dx(j) in: (&i10)  and cldate>=(&stdt) and cldate<=(&eddt) then do ;
						&var=1;
						if dt&var=. then dt&var=cldate;
						else if dt&var ne . and dt&var>cldate then dt&var=cldate;
			end;
end;
drop j;
length dt&var 4. &var 3.;
%mend;

/*User_check_point_2b*/		
%vr(outcomedx,&outcomedi9, &outcomedi10,index+1,fu); 
/* 	outcome is the name of outcome variable
	&outcomei9 is to call and include ICD9 codes to define the outcome as you specify in the let statmente at begining, 
	Similar for ICD10: &outcomei10
and the last two are start date and end date window of your outcome */

/*User_check_point_ends*/	


/*define outcome by procedure code code */
%macro vr(var,i9,i10, stdt,eddt);

retain &var 0;
retain dt&var .;

if first.enrolid then do;
&var=0;
dt&var=.;
end;

do h=1 to dim(px); 

			if dxver="9" and dx(h) in: (&i9)  and cldate>=(&stdt) and cldate<=(&eddt) then do ;
						&var=1;
						if dt&var=. then dt&var=cldate;
						else if dt&var ne . and dt&var>cldate then dt&var=cldate;
			end;
			else if dxver="0" and dx(h) in: (&i10)  and cldate>=(&stdt) and cldate<=(&eddt) then do ;
						&var=1;
						if dt&var=. then dt&var=cldate;
						else if dt&var ne . and dt&var>cldate then dt&var=cldate;
			end;
end;
drop h;
length dt&var 4. &var 3.;
%mend;

/*User_check_point_2c*/		
%vr(outcomepx,&outcomepi9, &outcomepi10,index+1,fu); 
/* 	outcomepx is the outcome that is defined by procedure code;
	&outcomepi9 is to call and include ICD9 procedure/CPT to define the outcome as you specify in the let statmente at begining, 
	Similar for ICD10: &outcomepi10
and the last two are start date and end date window of your outcome */

/*User_check_point_ends*/	


/*coding for covariates and/or inclusion/exclusion criteria */
%macro vr(var,i9,i10, stdt,eddt);

retain &var 0;

if first.enrolid then do;
&var=0;
end;

do k=1 to dim(dx); 

			if dxver="9" and dx(k) in: (&i9)  and cldate>=(&stdt) and cldate<=(&eddt) then do ;
						&var=1;
			end;
			else if dxver="0" and dx(k) in: (&i10)  and cldate>=(&stdt) and cldate<=(&eddt) then do ;
						&var=1;
			end;
end;
drop k;
length &var 3.;

%mend;
/*User_check_point_2d*/			
%vr(d1,&d1i9, &d1i10,index-180,index-1); 
%vr(d2,&d2i9, &d2i10, index-180,index-1); 
%vr(d3,&d3i9, &d3i10,index-180,index-1); 

/*you can add more variables as needed here */
/*User_check_point_ends*/			


/*macro for procedure codes */
%macro pvr(pvar,i9,i10, stdt,eddt);

retain &pvar 0;

if first.enrolid then do;
&pvar=0;
end;

do i=1 to dim(px); 

			if dxver="9" and px(i) in: (&i9)  and cldate>=(&stdt) and cldate<=(&eddt) then do ;
						&pvar=1;
			end;
			else if dxver="0" and px(i) in: (&i10)  and cldate>=(&stdt) and cldate<=(&eddt) then do ;
						&pvar=1;
			end;
end;
drop i;
length &pvar 3.;

%mend;
/*User_check_point_2e*/			
%pvr(p1,&p1i9,&p1i10,index-90,index-1);
/*macro structure is the same as diagnosis variable */
/*add more variables if needed */

/*User_check_point_ends*/			


if last.enrolid then output;
run;


*************************************************************************************************************************;
*Program IId -Combine with Exposure dataset to create the final cohort;


proc sql;
create table outcome_cov2 
	as select *
	from d.exposure a 
	left join outcome_cov b 
	on a.enrolid=b.enrolid;
	quit;

data d.cohort;
set outcome_cov2;
outcome=max(outcomedx,outcomepx);
dtoutcome=min(dtoutcomedx, dtoutcomepx);
array clean {*} outcome d: p: ;
do i=1 to dim(clean);
if clean(i)=. then clean(i)=0;
end;
drop i outcomedx outcomepx dtoutcomedx dtoutcomepx;
if outcome=0 then dtoutcome=.;
run;
/*Now you have a cohort with exposure, outcome, index date, outcome date and covariate variables */
/*depending on the way that the user defines, covariate variables can be considered inclusion, exclusion criteria or confounders */



*************End program II************************;